PL/SQL 1일차

> sqlplus / as sysdba > startup > conn hr/hr

HR> select first_name from employees where employee_id=100;

FIRST_NAME

--------------------------------------------------

Steven

HR> DECLARE

  2  v_fname VARCHAR2(20);

  3  BEGIN

  4  SELECT first_name INTO v_fname FROM employees

  5  WHERE employee_id=100;

  6  END;

  7  /

PL/SQL procedure successfully completed.

HR> ed

Wrote file afiedt.buf

  1  DECLARE

  2  v_fname VARCHAR2(20);

  3  BEGIN

  4  SELECT first_name INTO v_fname FROM employees

  5  WHERE employee_id=100;

  6* END;

HR> show serveroutput

serveroutput OFF

HR> set serveroutput on

HR> ed      

Wrote file afiedt.buf

  1  DECLARE

  2  v_fname VARCHAR2(20);

  3  BEGIN

  4  SELECT first_name INTO v_fname FROM employees

  5  WHERE employee_id=100;

  6  DBMS_OUTPUT.PUT_LINE(' The First Name of the Employee is ' || v_fname);

  7* END;

HR> /

The First Name of the Employee is Steven

PL/SQL procedure successfully completed.

HR> BEGIN

HR> DBMS_OUTPUT.PUT_LINE('Hello World');

SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.

HR> begin

  2  dbms_output.put_line('Hello World');

  3  end;

  4  /

Hello World

PL/SQL procedure successfully completed.

HR> save lab_01_02

Created file lab_01_02.sql

* 변수에 & 해서 old, new값 안 볼 때 : set verify off

* procedure successfully ~ 보기 싫을 때 : set feedback off

declare

  v_maxdid departments.department_id%type;

  v_rows varchar2(100);

begin

  select max(department_id)

  into v_maxdid

  from departments;

  dbms_output.put_line('Maximum department id is '||v_maxdid);

  insert into departments

  values (v_maxdid+10, '&b_dname', null, null);

  v_rows := sql%rowcount || ' row inserted';

  dbms_output.put_line(v_rows);

end;

/