본문 바로가기
Oracle/PLSQL 02

PL/SQL 02

Soul-Learner 2008. 7. 3. 07:12

PL/SQL에서 사용되는 주석
-- 한라인을 주석처리
/*     */ 여러 라인을 주석처리

PL/SQL기본적으로 화면에 출력하는 기능이 지원되지 않으므로 다음과 같이 설정해야 출력이 가능하다

SQL> set serveroutput on;
SQL> begin
  2  dbms_output.put_line('Hello PL/SQL');
  3  end;
  4  /
Hello PL/SQL


SQLPlus에서 선언된 외부변수를 PL/SQL 블럭 안에서 사용하려면....

SQL> var num number; -- bind 변수 선언
SQL> begin
  2  :num := 100;
  3  dbms_output.put_line('변수의 값' || :num);
  4  end;
  5  /
변수의 값:100

 

SQL> declare
  2  num number;
  3  begin
  4  num := 10000;
  5  dbms_output.put_line('num:'|| num);
  6  end;
  7  /
num:10000


PL/SQL 블럭 안에서 선언된 내부변수를 사용하는 예

SQL> declare
  2 myVar number;
  3  begin
  4 myvar := 100;
  5 dbms_output.put_line('myVar:' || myVar);
  6  end;
  7  /
myVar:100

 

SQL> var str varchar2(20);
SQL> exec :str := 'Hello World';

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print str;

STR
--------------------------------
Hello World



-- print 함수로 bind 변수를 출력하는 예

SQL> var string varchar2(20);
SQL> begin
  2  :string := 'Hello World';
  3  end;
  4  /

SQL> print string;

STRING
--------------------------------
Hello World



내부변수에 SQL Query의 결과를 저장하고 출력하는 예

SQL> declare
  2  myVar number;
  3  begin
  4  select sal into myVar from emp where ename='KING';
  5  dbms_output.put_line('KING salary:' || myVar);
  6  end;
  7  /
KING salary:5000

SQLPlus에서 프롬프트를 띄우고 입력을 받아 출력하는 예

SQL> ed pmt.sql

accept num prompt '수를 입력하세요';
declare
myNum number;
begin
myNum := #
dbms_output.put_line('입력된 값은 '||myNum||'입니다');
end;
/

SQL> @pmt.sql;
수를 입력하세요5
구   4: myNum := #
신   4: myNum := 5;
입력된 값은 5입니다



두수를 입력받고 덧셈결과를 출력하는 예

SQL> ed add.sql
accept num1 prompt '첫번째 수를 입력하세요';
accept num2 prompt '두번째 수를 입력하세요';
declare
a number;
b number;
result number;
begin
a := &num1;
b := &num2;
result := a+b;
dbms_output.put_line(a||'+'||b||'='||result);
end;
/

SQL> @add.sql
첫번째 수를 입력하세요
두번째 수를 입력하세요
구   6: a := &num1;
신   6: a := 4;
구   7: b := &num2;
신   7: b := 6;
4+6=10



입력된 수가 홀수인지 짝수인지 식별하는 제어문

accept num prompt '수를 입력하세요';
declare
myNum number := #
begin
if mod(myNum,2)=0 then
dbms_output.put_line('짝수');
else
dbms_output.put_line('홀수');
end if;
end;
/

if ~ elsif ~ else ~ end if 사용예(elseif 가 아니라 elsif 임을 주의하라)

accept num prompt '수를 입력하세요'
declare
myNum number := #
str varchar2(10);
begin
if myNum=1 then str := '월요일';
elsif myNum=2 then str := '화요일';
elsif myNum=3 then str := '수요일';
else str := '그외';
end if;
dbms_output.put_line(str);
end;
/

SMITH가 사원 중에 있는지 확인하는 예

declare
cnt number;
begin
select count(*) into cnt from emp where ename='SMITH';
if cnt>0 then dbms_output.put_line('스미스 찾았음');
else dbms_output.put_line('스미스 없음');
end if;
end;
/

테이블에 있으면 경고를 띄우고 없으면 레코드를 입력하는 예

SQL> declare
  2  cnt number;
  3  begin
  4  select count(*) into cnt from t1 where name='SMITH';
  5  if(cnt=0) then
  6  insert into t1 values(4, 'SMITH', '1999-09-23');
  7  else dbms_output.put_line('이미 명단에 등록된 사원입니다.');
  8  end if;
  9  end;
 10  /
이미 명단에 등록된 사원입니다.

구구단 출력 예제(loop ~ exit when ~ end loop)

SQL>  declare
  2   dan number :=7;
  3   i number :=1;
  4   begin
  5   loop
  6   dbms_output.put_line(dan||' x '||i||' = '||(dan*i));
  7   i := i+1;
  8   exit when i>9;
  9   end loop;
 10   end;
 11  /
7 x 1 = 7
7 x 2 = 14
7 x 3 = 21
7 x 4 = 28
7 x 5 = 35
7 x 6 = 42
7 x 7 = 49
7 x 8 = 56
7 x 9 = 63

구구단 출력 예제 (for idx in 1..9 loop ~ end loop)

SQL> declare
  2  dan number := 8;
  3  i number :=1;
  4  begin
  5  for i in 1..9 loop
  6  dbms_output.put_line(dan||' x '||i||' = '||(dan*i));
  7  end loop;
  8  end;
  9  /
8 x 1 = 8
8 x 2 = 16
8 x 3 = 24
8 x 4 = 32
8 x 5 = 40
8 x 6 = 48
8 x 7 = 56
8 x 8 = 64
8 x 9 = 72

구구단 출력 예제(while i<10 loop ~ end loop)

SQL> declare
  2  dan number := 9;
  3  i number :=1;
  4  begin
  5  while i<10 loop
  6  dbms_output.put_line(dan||' x '||i||' = '||(dan*i));
  7  i := i+1;
  8  end loop;
  9  end;
 10  /
9 x 1 = 9
9 x 2 = 18
9 x 3 = 27
9 x 4 = 36
9 x 5 = 45
9 x 6 = 54
9 x 7 = 63
9 x 8 = 72
9 x 9 = 81

%ROWTYPE 변수를 이용하는 예제

SQL> set serveroutput on;
SQL> declare
  2  emprow emp%rowtype;
  3  begin
  4  select * into emprow from emp where ename='SMITH';
  5  dbms_output.put_line(emprow.empno);
  6  end;
  7  /
7369

%TYPE 변수를 이용하는 예제

SQL> declare
  2  name emp.ename%type;
  3  num emp.empno%type;
  4  begin
  5  name := 'SMITH';
  6  select empno into num from emp where ename=name;
  7  dbms_output.put_line(num);
  8  end;
  9  /
7369

사번을 입력받아 사원의 정보를 검색하는 예

SQL> ed search
accept sabun prompt '검색할 사원의 사번을 입력하세요 :';
declare
num emp.empno%type := &sabun;
empRec emp%ROWTYPE;
begin
select * into empRec from emp where empno=num;
dbms_output.put_line(empRec.empno||' '||empRec.ename||' '||empRec.sal||' '||empRec.deptno);
end;
/

SQL> @search

검색할 사원의 사번을 입력하세요 :7369
구   2: num emp.empno%type := &sabun;
신   2: num emp.empno%type := 7369;
7369 SMITH 800 20


테이블 타입 (배열) 사용 예

set SERVEROUTPUT ON;

declare

 vdeptno emp2.deptno%type := 20;

 type arr_ename_type is table of emp2.ename%type index by binary_integer; --테이블 자료형을 선언한다

 type arr_sal_type is table of emp2.sal%type index by binary_integer;

 i binary_integer := 0;   -- 배열의 원소를 참조할 때 사용할 인덱스를 선언한다

 arr_ename arr_ename_type;   -- 위에서 선언한 자료형을 이용하여 테이블 변수(배열)를 생성한다

 arr_sal arr_ename_type;

begin

  for r in ( select ename,sal from emp2 where deptno=vdeptno) loop

    arr_ename(i) := r.ename; -- 배열의 원소를 초기화한다

    arr_sal(i) := r.sal;

    i := i+1;

  end loop;

  for k in 0..(i-1) loop

    DBMS_OUTPUT.PUT_LINE(arr_ename(k)||'-'||arr_sal(k)); --배열의 원소를 출력한다

  end loop;

end;


====================== Cursor ==========================

2개이상의 레코드를 가져오는 경우라면 Cursor를 사용해야 한다.

  declare
  cursor mycur is
  select ename from emp;
  begin
  for cur in mycur loop
  dbms_output.put_line(cur.ename);
  end loop;
  commit;
  end;
 /

 

declare
  CURSOR c_emp IS
    select empno, ename from emp;
  v_empno emp.empno%type;
  v_ename emp.ename%type;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into v_empno,v_ename;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(v_empno || ', ' || v_ename);
  End loop;
  CLOSE c_emp;
End;
/



=================== REF CURSOR ===========================

 

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  en emp.ename%type;
begin
  open c_emp for select ename from emp;
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
  close c_emp;
end;
/

 

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  er emp%rowtype;
begin
  open c_emp for select * from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.ename || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
/

 

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  open c_emp for select ename,sal from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
/

 

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  open c_emp for select ename,sal from emp where deptno = 10;
  dbms_output.put_line('Department: 10');
  dbms_output.put_line('--------------');
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
  open c_emp for select ename,sal from emp where deptno = 20;
  dbms_output.put_line('Department: 20');
  dbms_output.put_line('--------------');
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
/

 

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  for i in (select deptno,dname from dept)
  loop
    open c_emp for select ename,sal from emp where deptno = i.deptno;
    dbms_output.put_line(i.dname);
    dbms_output.put_line('--------------');
    loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
    close c_emp; 
  end loop;
end;
/


 

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
  procedure PrintEmployeeDetails is
  begin
    loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
  end;
begin
  for i in (select deptno,dname from dept)
  loop
    open c_emp for select ename,sal from emp where deptno = i.deptno;
    dbms_output.put_line(i.dname);
    dbms_output.put_line('--------------');
    PrintEmployeeDetails;
    close c_emp; 
  end loop;
end;
/

 

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  procedure PrintEmployeeDetails(p_emp r_cursor) is
    er rec_emp;
  begin
    loop
      fetch p_emp into er;
      exit when p_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
  end;
begin
  for i in (select deptno,dname from dept)
  loop
    open c_emp for select ename,sal from emp where deptno = i.deptno;
    dbms_output.put_line(i.dname);
    dbms_output.put_line('--------------');
    PrintEmployeeDetails(c_emp);
    close c_emp; 
  end loop;
end;
/

--사원중에서 최소연봉자의 부서에 근무하는 직원 중에서 최고 연봉자의 정보를 출력하는 예

SQL> declare
  2  minsal number;
  3  dn number;
  4  mxsal number;
  5  Type empcursor is REF CURSOR;
  6  ec empcursor;
  7  emprec emp%ROWTYPE;
  8  begin
  9  select min(sal) into minsal from emp;
 10  select deptno into dn from emp where sal=minsal;
 11  select max(sal) into mxsal from emp where deptno=dn;
 12  open ec for select * from emp where deptno=dn and sal=mxsal;
 13  LOOP
 14  FETCH ec into emprec;
 15  exit when ec%notfound;
 16  dbms_output.put_line(emprec.ename||' '||emprec.sal||' '||emprec.deptno);
 17  END LOOP;
 18  close ec;
 19  end;
 20  /
SCOTT 3000 20
FORD 3000 20


====================== SYS_REFCURSOR ===================

SYS_REFCURSOR : Oracle에서 제공하는 REF CURSOR

SQL> set serveroutput on;
SQL>  declare
  2   empSysRefCsr SYS_REFCURSOR;
  3   empRec emp%ROWTYPE;
  4   begin
  5   open empSysRefCsr for
  6   select * from emp;
  7   LOOP
  8   fetch empSysRefCsr into empRec;
  9   exit when empSysRefCsr%NOTFOUND;
 10   dbms_output.put_line(empRec.ename||' '||empRec.sal||' '||empRec.deptno);
 11   END LOOP;
 12   end;
 13   /
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10

PL/SQL 처리가 정상적으로 완료되었습니다.




SYS_REFCURSOR를 IN OUT 파라미터로 설정하여 사용하는 예------------------------

create or replace procedure proc1(mySysRefCsr IN OUT SYS_REFCURSOR) is
empRec emp%ROWTYPE;
begin
open mySysRefCsr for select * from emp;
LOOP
exit when mySysRefCsr%NOTFOUND;
FETCH mySysRefCsr INTO empRec;
dbms_output.put_line(empRec.ename||' '||empRec.job);
END LOOP;
end proc1;
/

SQL> declare
  2  sysRefCsr SYS_REFCURSOR;
  3  begin
  4  proc1(sysRefCsr);
  5  end;
  6  /
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
MILLER CLERK

PL/SQL 처리가 정상적으로 완료되었습니다.





==========================================================================
SYS_REFCURSOR를 OUT파라미터로 설정하여 사용하는 예
==========================================================================

create or replace procedure proc1(sysRefCsr OUT SYS_REFCURSOR) is
begin
open sysRefCsr for select * from emp;
end;
/



 

declare
sysRefCsr SYS_REFCURSOR;
empRec emp%ROWTYPE;
begin
proc1(sysRefCsr);
LOOP
FETCH sysRefCsr INTO empRec;
exit when sysRefCsr%notfound;
dbms_output.put_line(empRec.ename||' '||empRec.job);
END LOOP;
end
SQL> /
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK

PL/SQL 처리가 정상적으로 완료되었습니다.



 

====================== Procedures =======================

SQL> create or replace procedure emp_search
  2  (num in emp.empno%TYPE) IS
  3  empRec emp%ROWTYPE;
  4  BEGIN
  5  select * into empRec from emp where empno=num;
  6  dbms_output.put_line(empRec.empno||' '||empRec.ename||' '||empRec.sal);
  7  end;
  8  /

프로시저가 생성되었습니다.

SQL> exec emp_search(7369);
7369 SMITH 800

PL/SQL 처리가 정상적으로 완료되었습니다.




-- 여러개의 튜플(레코드)이 나오는 경우에는 Cursor를 사용해야한다.
-- 20번 부서의 직원정보를 출력하는 예

SQL> create or replace procedure proc1(dno number) is
  2  Type empcursor is REF CURSOR;
  3  ec empcursor;
  4  rec emp%ROWTYPE;
  5  begin
  6  open ec for select * from emp where deptno=dno;
  7  LOOP
  8  fetch ec into rec;
  9  exit when ec%notfound;
 10  dbms_output.put_line(rec.ename||' '||rec.job||' '||rec.sal||' '||rec.deptno);
 11  END LOOP;
 12  close ec;
 13  end proc1;
 14  /

프로시저가 생성되었습니다.



 

SQL> exec proc1(20);
SMITH CLERK 800 20
JONES MANAGER 2975 20
SCOTT ANALYST 3000 20
ADAMS CLERK 1100 20
FORD ANALYST 3000 20

PL/SQL 처리가 정상적으로 완료되었습니다.


SQL>


티스토리툴바