PL/SQL ; SQL Cursor

해당 예제에 사용되는 테이블들 덤프파일(exp사용, 10.2.0.1, scott/oracle)

example.dmp

참고사이트 :  http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sql_cursor.htm#LNPLS01348 
참고예제 : 서진수강사님 PL/SQL 강의안

1. SQL Cursor
; SQL문을 실행할때마다 처리(Parse, Execute)를 위해서 사용되는 메모리공간을 말합니다..(다른말로 Private SQL Area)
PL/SQL이 실행될 때에도 내부에 포함된 SQL문장에 대해 SQL Cursor가 자동적으로 생성되며, 필요에 따라 사용자가 직접 선언할 수도 있습니다.
만약에, 명시적 커서(직접 선언한 커서)를 사용하였으면, 별도의 정리(Clean-up)작업(Close)을 해줘야합니다.

2. SQL Cursor메모리 공간이 위치하는 곳
참고사이트 : https://forums.oracle.com/forums/thread.jspa?threadID=1073216 

Dedicated Server환경이면 PGA에 위치
MTS(Shared server)환경이면 SGA의 Library Cache에 위치

3. 묵시적 커서 : 자동적으로 생성되는 커서로, 기본적으로 PL/SQL내에서 select, DML(insert, update, delete)문이 실행될때마다 묵시적커서가 생성됩니다.
3.1 주의점 : 세션내에 단 한개만이 선언되어 사용되었다가 문장이 종료됨과 동시에 정리됩니다.
                     --> 동시에 여러개의 묵시적커서가 사용되는 것이 아니라는 점입니다.
3.2 속성 : SQL문의 실행된 결과를 커서속성을 통해 제공하고 있는데, 주의할 점은 가장 마지막에 실행된 SQL문의 결과라는 점입니다.

>> 묵시적 커서 속성(Cursor Attribute)

- SQL%ROWCOUNT

   가장 마지막에 실행된 SQL 문장이 처리한 데이터 행의 총 개수를 가지는 속성변수.
 

- SQL%FOUND

   가장 마지막에 실행된 SQL 문장이 처리한 데이터 행이 있을 경우에는 TRUE(참) 진리값을,

   처리한 데이터 행이 없을 경우에는 FALSE(거짓) 진리값을 가지는 속성 Boolean 변수.
 

- SQL%NOTFOUND

   가장 마지막에 실행된 SQL 문장이 처리한 데이터 행이 없을 경우에는 TRUE(참) 진리값을,

   처리한 데이터 행이 있을 경우에는 FALSE(거짓) 진리값을 가지는 속성 Boolean 변수.
 

- SQL%ISOPEN

   현재 묵시적 커서가 메모리에 확보되어 있을 경우에는 TRUE(참) 진리값을,

   그렇지 않을 경우에는 FALSE(거짓) 진리값을 가지는 속성 Boolean변수. (단, 묵시적 커서는

   문장이 실행 종료됨과 동시에 정리(Clean-up)되므로 이 커서속성은 항상 FALSE(거짓) 진리값을

   가지게 됨에 주의한다.)

**** 주의사항
SELECT의 경우는, 묵시적커서의 경우에는 한건의 데이터를 처리하는 경우에만 정상적으로 사용가능합니다.
여러 행의 데이터의 경우에는 묵시적커서는 예외사항이 발생합니다.(TOO_MANY_ROWS)
만약, 여러 행이 select될 경우에는 명시적커서를 사용해야만 합니다.


4. 명시적 커서 : 필요에 따라 사용자가 선언하여 사용하는 SQL커서, 기본적으로 여러개의 행을 처리할 경우 사용합니다.

  오라클은 사용자가 정의한 명시적 커서 영역에 SELECT 문에 의해 검색된 여러건의 데이터를 임시적으로 저장하고,
한 건씩 처리할 수 있도록 명시적 커서 기능을 제공합니다.

  명시적 커서는 동시에 여러 개가 선언되어 사용될 수 있으며, 묵시적 커서와 마찬가지로 커서 속성 변수를 제공합니다.
(의미는 묵시적 커서와 약간씩 다를 수 있습니다.)

  명시적 커서는 여러 개가 선언될 수 있으므로,
커서 속성 변수는 ‘명시적 커서명(Cursor Name)%’을 커서 속성 변수의 접두어(Prefix)로 붙여서 사용합니다.
4.1 속성 : 

>> 명시적 커서 속성(Cursor Attribute)

- %ROWCOUNT

   FETCH 문에 의해 읽혀진 데이터의 총 건수를 가지는 속성변수.

- %FOUND

   FETCH 문이 수행되었을 경우, 읽혀진(Fetch) 행이 있을 경우에는 TRUE(참) 진리값을,

   그렇지 않을 경우에는 FALSE(거짓) 진리값을 가지는 속성

 

- %NOTFOUND

   FETCH 문이 수행되었을 경우, 읽혀진(Fetch) 행이 없을 경우에는 TRUE(참) 진리값을,

   그렇지 않을 경우에는 FALSE(거짓) 진리값을 가지는 속성

- %ISOPEN

   명시적 커서가 메모리에 확보(선언)되어 있을 경우에는 TRUE(참) 진리값을, 그렇지 않을 경우

   에는 FALSE(거짓) 진리값을 가지는 속성 Boolean 변수. 

5. 커서사용법

선언 --> 오픈 --> 저장(fetch) --> 닫기(close)

참조 :  http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/cursor_declaration.htm#i33637 

CURSOR cursor_name
IS select-statement;
(PL/SQL블록의 select~into와는 다른 절입니다.)


5.1 커서사용의 예제

declare
  vempno number(4);
  vename varchar2(20);
  vsal number(7);
  cursor c1 is
    select empno, ename, sal from emp
    where deptno=20;
begin
  open c1;
  dbms_output.put_line('번호  이름  급여');
  loop
    fetch c1 into vempno, vename, vsal;
  exit when c1%notfound;
  dbms_output.put_line(vempno||' '||vename||' '||vsal);
  end loop;
  close c1;
end;
/

결과

번호  이름  급여
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000

PL/SQL procedure successfully completed.

5.2 커서사용의 예제(cursor for loop사용) ; 위의 예제와 결과는 같습니다.
(OPEN, FETCH, CLOSE의 단계가 자동으로 수행)
 
5.2.1 사용법 
FOR record_name IN cursor_name LOOP
  statement1;
  statement2;
END LOOP;


5.2.2 예제

declare
  vemp emp%rowtype;
  cursor c1 is
    select empno, ename, sal from emp
    where deptno = 20;
begin
  dbms_output.put_line('번호  이름  급여');
  for vemp in c1 loop
    exit when c1%NOTFOUND;
    dbms_output.put_line(vemp.empno||' '||vemp.ename||' '||vemp.sal);
  end loop;
end;
/

결과

번호  이름  급여
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000

PL/SQL procedure successfully completed.

6. 파라미터 커서(변수를 받을 수 있는 커서)
6.1 사용법
CURSOR cursor_name(parameter_name datatype, ...)
IS select-statement;

6.2 예제:

declare
  cursor emp_cur(p_dno emp.deptno%type)
  is
  select empno, ename, sal from emp
  where deptno = p_dno;
  vempno emp.empno%type;
  vename emp.ename%type;
  vsal emp.sal%type;
begin
  open emp_cur(20);
  dbms_output.put_line('NO   NAME  SALARY for deptno=20');
  loop
    fetch emp_cur
    into vempno, vename, vsal;
  exit when emp_cur%NOTFOUND;
  dbms_output.put_line(vempno||' '||vename||' '||vsal);
  end loop;
  close emp_cur;
  open emp_cur(30);
  dbms_output.put_line('NO   NAME  SALARY for deptno=30');
  loop
    fetch emp_cur
    into vempno, vename, vsal;
  exit when emp_cur%NOTFOUND;
  dbms_output.put_line(vempno||' '||vename||' '||vsal);
  end loop;
  close emp_cur;
end;
/

결과

NO   NAME  SALARY for deptno=20
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
NO   NAME  SALARY for deptno=30
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7844 TURNER 1500
7900 JAMES 950

PL/SQL procedure successfully completed.

- SQL CURSOR 연습문제
1. 부서코드를 입력 받아 해당 부서에 속한 사원들을 삭제한 후 삭제된 사원 건수를 출력하세요.
(묵시적 커서를 사용하세요)

2. 부서코드를 입력 받아 그 부서에 속한 사원들의 이름, 급여를 출력하세요.
(여러 건의 데이터를 작업해야 하니까 명시적 커서와 Basic Loop 문을 사용하세요)





1번답

declare
  vdeptno emp.deptno%type := &dno;
begin
   dbms_output.put_line('DEPTNO '||vdeptno||' will be deleted');
   delete emp where deptno=vdeptno;
   dbms_output.put_line(SQL%ROWCOUNT||' rows deleted');
end;
/

결과

Enter value for dno: 10
old   2:   vdeptno emp.deptno%type := &dno;
new   2:   vdeptno emp.deptno%type := 10;
DEPTNO 10 will be deleted
3 rows deleted

PL/SQL procedure successfully completed.

2번답(1) ; 일부러 파라미터커서를 사용해봤습니다. 파라미터커서말고 일반적인커서를 사용해도 됩니다.

declare
  vdeptno emp.deptno%type := &dno;
  vemp emp%rowtype;
  cursor emp_cur(vdeptno emp.deptno%type)
  is
    select empno, ename, sal from emp
    where deptno=vdeptno;
begin
  open emp_cur(vdeptno);
  dbms_output.put_line('NO  NAME  SALARY for deptno '||vdeptno);
  loop
    fetch emp_cur
    into vemp.empno, vemp.ename, vemp.sal;
  exit when emp_cur%NOTFOUND;
  dbms_output.put_line(vemp.empno||' '||vemp.ename||' '||vemp.sal);
  end loop;
end;
/ 

결과

Enter value for dno: 20
old   2:   vdeptno emp.deptno%type := &dno;
new   2:   vdeptno emp.deptno%type := 20;
NO  NAME  SALARY for deptno 20
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000

PL/SQL procedure successfully completed.

2번답(2) ; 일반적인 커서와 cursor for loop 사용

declare
  vdeptno emp.deptno%type := &dno;
  vemp emp%rowtype;
  cursor c1 is
  select empno, ename, sal from emp
  where deptno=vdeptno;
begin
  dbms_output.put_line('NO  NAME  SALARY');
  for vemp in c1 loop
    exit when c1%NOTFOUND;
    dbms_output.put_line(vemp.empno||' '||vemp.ename||' '||vemp.sal);
  end loop;
end;
/

결과

Enter value for dno: 20
old   2:   vdeptno emp.deptno%type := &dno;
new   2:   vdeptno emp.deptno%type := 20;
NO  NAME  SALARY
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000

PL/SQL procedure successfully completed.