4. PL/SQL Cursor(커서) 정리해야됨.

4장. PL/SQL Cursor(커서)

 

1.SQL커서란 ?

: Context Area(sql문 실행할 때마다 처리하기 위해 SGA내부에서 사용하는 개별적인 메모리 공간)에 있는 데이터를 사람이 접근 할 수 있도록 연결해주는 일종의 연결통로.(포인터라고도 부른다.)

즉, 사용자가 요청하는 데이터를 데이터베이스 버퍼 캐쉬에서  PL/SQL 이 사용하는 개별적인 메모리공간으로 복사 해 온 후(복사 한 데이터 : Active Set),

-> 커서를 통해서  Context Area 있는  Active Set 중에서  원하는  데이터에  접근한  후  필요한  데이터를  추출하여(FETCH),

-> PL/SQL  변수에  담고  후속  작업을  하게  된다는  뜻이다. 

 

오라클의  서버  프로세스  구성이 Dedicated Server 환경이냐 또는 MTS(Multi-Threaded Server)환경이냐에 따라 서버 내에 위치되는 곳이 다르다.

- Context Area : PL/SQL을 사용하여 작업을  할  경우 오라클이 항상 해당 세션에게 SGA 내부에 해당세션만 사용할 수 있도록 개별(Private)로 할당하는 공간..

- Context Area 에는  SQL Query 와 해당  SQL문장의 파싱 관련 정보들과 결과(data set)가 모두 들어있다.
- 사용자가  PL/SQL 을 통해서 특정  SQL (  SELECT나  DML ) 을 처리하고자 할 경우 일단 Database Buffer Cache에서 원하는 데이터를 뽑아서  Context Area 로 데이터를 가져 온 후  PL/SQL 변수에 담아서 후속 작업을 진행한다.

- 이때 가져오는 데이터가  1 건일 경우에는  Context Area 에 오라클이 자동으로 묵시적 커서를 생성해서 사용하고

 만약 여러 건을 가져와야 할 경우 사용자가 명시적 커서를 미리 생성해야 한다.  

- Cursor  는  실제  테이블을  가리키는  포인터가  아니라 , 결과셋  값을  가리키는 포인터 정보이다.

 

사용자가  T1 , T2 , T3 테이블을 조인을 하는 커서를 만들고 그 결과를  PL/SQL 변수에 담을 경우에

커서가 OPEN 이 되면 DB Buffer Cache 에서 필요한 컬럼만 빼서  Cursor Area 에 가져다 놓는다.

그리고 커서를 통해서  Cursor Area 에 있는 데이터의 위치를 찾아가서 데이터를 꺼내와서 (FETCH 단계)PL/SQL 변수에 담는다.

 

PL/SQL 커서는 크게 묵시적 커서(Implicit Cursor)와 명시적 커서(Explicit Cursor)로 나눌 수 있다.  

묵시적 커서는 필요할 경우 오라클이 자동적으로 선언하여 사용한 후 자동적으로 정리(Clean-up) 한다. 

PL/SQL 블록이 실행될 때 내부에 포함된  SQL 문장에 대해  SQL 커서가 자동적으로 생성된다. 즉 사용자가 생성을 하지 않아도 자동으로 생성된다.

 

2. 묵시적 커서(Implicit Cursor)
 

묵시적 커서는 오라클에서 자동적으로 선언해주는  SQL 커서로서, 사용자 입장에서는 생성 유무를 알 수 없다.

기본적으로  PL/SQL 블록 내에서의  SELECT문, DML(INSERT, UPDATE, DELETE) 문이 실행될 때마다 묵시적 커서가 선언된다.

주의해야 할 점은 묵시적 커서의 경우 세션 내에 단 한 개만이 선언되어 사용되었다가 문장이 종료됨과 동시에 정리된다.

그리고 묵시적 커서에 저장되는 데이터는 1 행만 가능하여, 여러 행을 저장해서 작업 해야 할 경우에는 묵시적 커서를 사용할 수 없다.

 

 * 묵시적 커서 속성(Cursor Attribute) (별로안중요)

 

SQL%ROWCOUNT	해당 커서에서 실행한 총 행의 개수(가장 마지막 행이 몇 번째 행인지 카운트합니다)를 반환.
SQL%FOUND	해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE 값을 반환하고, 없을 경우FALSE값을 반환하는 속성.
SQL%NOTFOUND	해당 커서 안에 수행해야 할 데이터가 없을 경우 TRUE값을 반환하고 있을 경우  FALSE값을 반환하는 속성
SQL%ISOPEN	현재 묵시적 커서가 메모리에 OPEN   되어 있을 경우  TRUE값을, 그렇지 않을 경우에는  FALSE값을 가지는 속성

 

* 묵시적 커서 사용 예:

- emp table 에서    sal 을 조회한 화면.

SCOTT>SELECTsal    2   FROM emp    3  ORDER BY 1;           SAL ----------------         800         950        1100        1250        1250        1300        1500        1600        2450 (이하 생략) 14 rows selected.

 

- 묵시적 커서를 활용해서  emp table 에서  sal 이 100이하인 경우와  1000 - 2000 사이인 경우를 지우고 각 몇 건이 지워졌는지 화면에 출력.

 

SCOTT>DECLARE    2  BEGIN    3     delete emp    4     WHERE sal < 1000;    5  DBMS_OUTPUT.PUT_LINE('=====================================');    6  DBMS_OUTPUT.PUT_LINE('1000 이하  : '||sql%rowcount||' 건 삭제되었습니다');    7    8     delete emp    9      WHERE sal between 1000 and 2000 ;   10  DBMS_OUTPUT.PUT_LINE('=====================================');   11  DBMS_OUTPUT.PUT_LINE('1000 - 2000 사이  : '||sql%rowcount||' 건 삭제되었습니다');   12   END;   13  / ============================ 1000 이하  : 2 건 삭제되었습니다 ============================ 1000 - 2000 사이  : 6 건 삭제되었습니다   PL/SQL procedure successfully completed.

 

 

3. 명시적 커서(Explicit Cursor)

명시적 커서는 사용자가 선언하여 생성 후 사용하는  SQL 커서로, 주로 여러 개의 행을 처리하고자 할 경우 사용.

(만약 여러건을 검색하는  SELECT문장의  경우  묵시적  커서를  사용할  경우  오라클은  예외사항(TOO_MANY_ROWS)을 발생한다.)

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

(묵시적  커서는  커서가  하나밖에  없었기  때문에 접두어로  SQL 을 사용)

 

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

 

커서이름%ROWCOUNT	FETCH 문에 의해 읽혀진 데이터의 총 행 수. 가장 마지막에 처리된 행이 몇 번째 인지를 반환.
커서이름%FOUND	FETCH 문이 수행되었을 경우,  읽혀진(FETCH) 행이 있을 경우에는 TRUE값을, 그렇지 않을 경우에는  FALSE값을 가지는 속성
커서이름%NOTFOUND	FETCH 문이 수행되었을 경우,  읽혀진(FETCH ) 행이 없을 경우에는 TRUE값을, 그렇지 않을 경우에는  FALSE값을 가지는 속성
커서이름%ISOPEN	명시적 커서가 메모리에 확보(선언)되어 있을 경우에는  TRUE값을, 그렇지 않을 경우에는  FALSE(거짓) 값을 가지는 속성.

 

* 묵시적 커서와  FOR LOOP 문 활용하기

 

SCOTT> DECLARE    2  BEGIN    3   DBMS_OUTPUT.PUT_LINE(' 학과명과  학과의 위치 안내 입니다');    4   DBMS_OUTPUT.PUT_LINE('------------------------------------------');    5     FOR depart_cur IN ( SELECTdname,build    6                         FROM department    7                       WHERE build is not null    8                       ORDER BY 1)  서브쿼리    9     LOOP   10     DBMS_OUTPUT.PUT_LINE(depart_cur.dname||' ---> '||   11                              depart_cur.build||' 에 있습니다');   12     END LOOP;   13 END;   14 /   학과명과  학과의 위치 안내 입니다 ------------------------------------------------------------- 기계공학과  ---> 기계실험관 에 있습니다 멀티미디어공학과  ---> 멀티미디어관 에 있습니다 문헌정보학과  ---> 인문관 에 있습니다 소프트웨어공학과  ---> 소프트웨어관 에 있습니다 전자공학과  ---> 전자제어관 에 있습니다 컴퓨터공학과  ---> 정보관 에 있습니다 화학공학과  ---> 화학실습관 에 있습니다 ================================================ PL/SQL procedure successfully completed.

 

4. 명시적 커서(Explicit Cursor) 처리 단계 

 

1) 명시적 커서 선언(Declaration)

해당 커서를 사용 하겠다고  PL/SQL 에 알려주는 역할만 하며,실제 메모리 할당이 이루어지는 것은 아니다.

[문 법]

 

CURSOR   커서명     IS    커서에 담고 싶은 내용을 가져오는 서브쿼리

 

2) 명시적 커서 열기(OPEN )

커서 선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져 온다. 이 때 메모리에 실제 커서가 사용할 메모리 공간이 할당이 된다.

명시적 커서 영역에 자리잡은 데이터의 첫 번째 행에 커서 포인터(pointer)가 설정되고 바로 이 포인터 위치의 데이터 행을 다음 단계인 FETCH에서 읽는다. 

[문법]

OPEN   커서 이름  ;

 

3) 명시적 커서로부터 데이터 읽어서 변수로 할당하기(FETCH )
 : 명시적 커서의 데이터들(Active Set)로부터 데이터를 한 건씩 읽어 변수로 할당.

 이때 읽게 되는 데이터 행은 포인터(Pointer)에 의해서 지정되며 한 행이  FETCH 되면 자동적으로 포인터는 다음 행으로 이동한다.
 일반적으로 명시적 커서에는 데이터가 여러 건 들어있기 때문에 많은 데이터들을 읽어 처리하기 위해서  FETCH 문은 반복문과 함께 사용하는 경우가 많다.  
- FETCH 문도 FETCH 후에 변수에 값을 할당하기 위해  INTO 절을 사용한다. 
[문 법] 

 

FETCH    커서_이름  INTO   변수

- 커서_이름은 읽어오고자(FETCH) 하는 명시적 커서의 이름으로, 반드시 OPEN   되어 있어야  FETCH가 가능. 
- 변수들:
 명시적 커서로부터 읽어온(FETCH) 데이터 행(레코드)을  PL/SQL 블록 내에서 처리하기 위해서는
변수에 저장해서 사용합니다. 이때  variable 에는 단순변수와 복합변수가 올 수 있으며, 당연히 선언부에 선언된 변수만 올 수 있다. 

 만약 단순변수를 사용한다면, 커서에서 정의된  SELECT리스트의 개수만큼 선언하고, SELECT 리스트의 위치대로  FETCH의  INTO절에 차례대로 적어야 한다.

만약 복합변수를 사용한다면, 커서 레코드 변수(커서명%ROWTYPE  )를 선언하여 사용한다.

4) 명시적 커서 닫기(Close) : 작업이 끝난 메모리 공간을 반환하고 정리.(명시적 커서의 정리(clean-up)작업을 하는 명령)
닫기를  하지  않게  되면  메모리  낭비도  많이  되고.  만약  동일한  커서를  다른  PL/SQL BLOCK 에서 동일한 이름의 커서를 사용할 경우 에러가 생긴다.
만약 사용자가  명시적  커서를  닫지  않고  다른  명시적  커서를  OPEN한다면  SGA  에  Cursor Work Area 에 메모리 공간 낭비가 심해진다.

 

 [문 법]

CLOSE 커서_이름  ;

- 명시적 커서 선언 및 사용 예  :

 

 

5. 명시적 커서와 Cursor FOR LOOP 문 활용하기

명시적 커서에는 일반적으로 여러 건의 데이터가 들어있으므로, 여러 번  FETCH 를 수행하게  되고  이를 위해  반복문을  사용한다. 

그  중 가장 많이 사용되는 것이 FOR 문장인데 PL/SQL에서는  FOR 반복문과 커서를 결합하여 CURSOR FOR 반복 기능을 제공한다.

 

[문 법] 

FOR  record_name IN cursor_name LOOP  -- 명시적 커서의 OPEN , FETCH 가 자동으로 수행됨.      statement1 ;     statement2 ; .... END LOOP ; -- 루프문을 빠져 나갈 때 자동적으로 커서가 CLOSE 됨.

 

 

- 명시적 CURSOR와 CURSOR FOR LOOP 문 사용 예: 

 emp 테이블에서  empno, ename 값을 가져와서 커서에 저장 후 출력. (커서 이름과 변수이름은 임의로 지정)

 

SCOTT>DECLARE    2     CURSOR emp_cur  IS   -- 커서 선언    3        SELECTempno ,ename    4        FROM emp;    5  BEGIN    6     FOR emp_rec  IN emp_cur   -- 커서의 데이터를 저장할  emp_rec 변수 선언.     7      LOOP     8      DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'  '||emp_rec.ename);    9     END LOOP;   10   END;   11  /   1000  홍길동 2000  일지매 7369  SMITH 7499  ALLEN (이하 생략)

 

 

6. 파라미터  Explicit Cursor
 
파라미터 커서란? : 명시적 커서를 선언 한 후 OPEN할 때 값을 바꾸어서 수행해야 할 경우가 종종 있는데,이때 커서를 OPEN 할 때 필요한 값만 파라미터로 전달해서 반복 수행 할 수 있는 방법이다.

 

[문 법] 

 

CURSOR cursor_name [ (parameter_name dataTYPE , …) ] IS    select-statement ; -   parameter_name

- 여러 개의 파라미터를 지정할 수 있다.

 

 

 

 

 

- 파라미터  Explicit Cursor 사용 예  :

 

SCOTT> DECLARE    2   CURSOR prof_cur (v_deptno IN   NUMBER )    3    IS   SELECT*    4        FROM professor    5       WHERE deptno=v_deptno ;    6   v_prof  professor%ROWTYPE    ;    7  BEGIN    8      9  DBMS_OUTPUT.PUT_LINE('101번 학과 교수님 번호와 이름 출력');   10  DBMS_OUTPUT.PUT_LINE('--------------------------------------------');   11  OPEN   prof_cur(101);   12   LOOP   13      FETCH   prof_cur    INTO     v_prof;   14      EXIT   WHEN prof_cur%NOTFOUND ;   15     DBMS_OUTPUT.PUT_LINE(v_prof.profno||' 번 교수님 이름은  '||   16                                v_prof.name||' 입니다') ;   17   END LOOP ;   18  CLOSE prof_cur ;   19  DBMS_OUTPUT.PUT_LINE('========================= ');   20  DBMS_OUTPUT.PUT_LINE('102번 학과 교수님 번호와 이름 출력');   21  DBMS_OUTPUT.PUT_LINE('--------------------------------------------');   22  OPEN   prof_cur(102) ;   23   LOOP   24      FETCH   prof_cur INTO    v_prof;   25      EXIT   WHEN   prof_cur%NOTFOUND;   26     DBMS_OUTPUT.PUT_LINE(v_prof.profno||' 번 교수님 이름은  '||   27                               v_prof.name||' 입니다') ;   28   END LOOP ;   29  CLOSE prof_cur ;   30    END;   31  /

 

 

7. Cursor 와  SELECT.....FOR UPDATE 문장  
 
명시적 커서를 선언해서 데이터를 조회한 후 변경을 하려는 중에 다른 세션에서 현 세션이 작업하는 내용을 변경 할 수 있는데, 이때 커서를 선언할 때 커서에 있는 행들에 대해 잠금(LOCK)을 수행할 수 있다.

- select문장의 가장 마지막 부분에 SELECT....  FOR UPDATE 문장을 사용하면 된다.

1) 기본 문법

 

[준비과정] : 두 개의 세션 준비.

- 세션  1 -

SCOTT>SELECT empno,ename,sal    2   FROM emp    3  WHERE empno=7900    4   FOR update ;  내가commit치거나 rollback치기전까지는 아무도못바꾸게 막아라.     EMPNO    ENAME  SAL --------- --------- --------      7900     JAMES   950

- 세션  2 -

SCOTT>UPDATE emp 다른 세션에서 DML 작업을 할 경우 대기 상태로 들어갑니다    2   SET sal=1000    3  WHERE empno=7900 ;   대기 상태로 반응 없다.

 

 : 세션  1 에서  commit 이나  rollback 명령을 수행해서  transaction 을 종료하면 세션  2 에서도 작업이 진행이 계속 됩니다. 
Q.세션1 에서  SELECT... FOR  update 를 사용하려 할 때 이미 다른 세션에서 해당 데이터를 변경하고 있을 경우에는?
A. 이럴 경우 세션1에서는 대기상태로 자기 순서를 기다리게된다. 이런 경우를 대비해서  FOR  update 에 두가지 옵션을 사용할 수 있다.

 - FOR  update nowait 옵션 : 만약 다른 사용자가 데이터를 먼저 변경하고 있을 경우 바로 에러를 발생하게 된다. 그리고 또 한가지는 

 - FOR  udpate wait  n 모드 : n 부분에 기다릴 시간을 지정해 주면 그 시간 동안 선행 작업이 끝날 때 까지 기다리다가, 그 시간내에 종료가 안되면 에러를 발생.

                                         만약 시간을 지정 안하고  FOR  update wait 만 사용할 경우 무한정 기다리게 되므로 꼭 숫자를 적어줘야 한다.
- 이렇게  Lock이 걸린 내역을 확인하려면 다른 터미널을 하나 더 열어서 관리자 계정으로 로그인 한 후조회하면 된다. 

SYS> SELECT a.sid , a.serial# , b.TYPE , c.object_name    2   FROM V$SESSION A, V$LOCK B, USER_OBJECTS C    3  WHERE a.sid=b.sid    4  AND b.id1=c.object_id    5  AND a.schemaname= 'SCOTT' ;    SID SERIAL# TYPE    OBJECT_NAM  --- -------- ----- -----------------    45   3533      AE     ORA$BASE   39   2415      AE     ORA$BASE

- 위와 같이  SELECT... FOR update 는  Lock 을 설정하게 되어 다른 사용자의  Transaction 진행에 방해를 할 수 있으므로 사용에 아주 주의한다.

 

2) Cursor 에서  SELECT... FOR update 사용하기

 

[문 법]

SELECT... FROM ... FOR UPDATE [OF column_reference][NOWAIT | WAIT n];

 

- Cursor 에서  FOR UPDATE 구문 사용 예: 
 

DECLARE    CURSOR emp_cur IS       SELECTEMPNO, ENAME, SAL       FROM EMP      WHERE DEPTNO = 20       FOR UPDATE   -- 커서 선언시에  FOR UPDATE 로 행을 잠금       NO WAIT ; .... BEGIN ....    OPEN    emp_cur ; ....      UPDATE emp       SET sal = sal * 2      WHERE CURRENT OF emp_cur    -- 커서 선언 시에 잠긴 행 갱신. ....    CLOSE emp_cur ; END;

 

8. REF Cursor   (현업에서 아주 많이 사용되고 있으나, 많이 어려우니 일단 건너뛰자.)

 묵시적Cursor와 명시적Cursor는 Cursor가 선언이 될 때 수행 될  SQL을 미리 지정하고 실행하는 방법만을 지원한다.

예를 들어 사용자가  1 번을 선택하면 학생테이블을 조회하고, 2번을  선택하면  교수  테이블을  조회하고  3번을  선택하면  학과  테이블을  조회해서  결과를  출력해야 할 경우 3개의 Cursor를 만들어야 한다는 단점이 있다.

->묵시적 cursor와 명시적 cursor는  cursor가 선언될 때 sql이 지정되면, 이후 변경할 수 없으므로 정적(static)cursor라 한다.

Ref Cursor란 ?  Cursor를  선언하는 시점에는 SQL을  지정하지  않고 Cursor가  OPEN이 되는  시점에 SQL 문장을  지정할 수 있다. -> Ref Cursor 는 동적  (Dynamic) Cursor 라고 부른다.

 

*Ref cursor 사용하기 위해 거치는 과정.

  1) 선언부에서 Ref Cursor 이름을 선언한다. ( Sub Query 는 선언하지 않는다. )
  2) RefCursor 를 사용할 변수를 선언한다. -> cursor가 open될 때 서브쿼리를 수행해서 그 결과를 임시로 담는 역할을 한다.
   (이것은 앞에서 살펴본 Record TYPE   이나  Table TYPE을 선언하고 해당  TYPE 을 사용하는 변수를 추가로 선언해 준 방법과 동일. )  
   -> 만약  Strong Ref Cursor라면  Return TYPE 을 적어준다. 
  3)Cursor변수에 담긴 데이터를 옮겨 담을, 실제 변수를 선언한다. -> Cursor변수에 임시로 담겨 있는 데이터는 이 과정에서 만들어진 변수로 FETCH 된다. 
   (스칼라 변수도 괜찮고 만약 데이터가 여러 건일 경우는 앞에서 배운 Record TYPE   이나   Table TYPE   변수를 사용해도 된다.)  

 - Ref Cursor 는 다시 Weak Ref Cursor (약한 타입-비 제한적 타입)와  Strong Ref Cursor (강한 타입-제한적 타입)으로 나눌 수 있다.

Strong Ref Cursor 는  Cursor 를 수행 후에 반환되는 데이터타입을 명시적으로 지정해 주어야 한다.

반면 Weak Ref Cursor 는 반환되는 데이터타입을 지정하지 않아도 됩니다.

 

 

여기부터

 

1. Weak Ref Cursor (약한 타입- 비 제한적 Ref Cursor )
 
사용자에게   1) Student , 2) Professor , 3) Department 의 메시지를 보인 후에 사용자가  1 을 입력
하면 학생들의 학번(studno), 이름  (name) 을 출력하고  , 2를 입력하면 교수들의 교수번호(profno),
이름  (name)을 출력하고  , 3을 입력하면 학과의 번호(deptno), 학과명(dname) 을 출력하는 예입니
다.

 

 

 

SQL> @weak_ref1.sql    <--- 실행합니다.
 
'출력을 원하는 번호를 선택하세요'
1) Student , 2) Professor , 3) Department : 1    <---- student table 선택합니다
 
Printing Student InFORmation---------
no: 9411 , name: 서진수
no: 9412 , name: 서재수
no: 9413 , name: 이미경

no: 9414 , name: 김재수
no: 9415 , name: 박동호
no: 9511 , name: 김신영
no: 9512 , name: 신은경
no: 9513 , name: 오나라
no: 9514 , name: 구유미
no: 9515 , name: 임세현
no: 9611 , name: 일지매
no: 9612 , name: 김진욱
no: 9613 , name: 안광훈
no: 9614 , name: 김문호
no: 9615 , name: 노정호
no: 9711 , name: 이윤나
no: 9712 , name: 안은수
no: 9713 , name: 인영민
no: 9714 , name: 김주현
no: 9715 , name: 허우
 
PL/SQL procedure successfully completed.
 
SQL> @weak_ref1.sql
 
'출력을 원하는 번호를 선택하세요'
1) Student , 2) Professor , 3) Department : 2   <---  professor 선택합니다
 
Printing Professor InFORmation---------
no: 1001 , name: 조인형
no: 1002 , name: 박승곤
no: 1003 , name: 송도권
no: 2001 , name: 양선희
no: 2002 , name: 김영조
no: 2003 , name: 주승재
no: 3001 , name: 김도형

no: 3002 , name: 나한열
no: 3003 , name: 김현정
no: 4001 , name: 심슨
no: 4002 , name: 최슬기
no: 4003 , name: 박원범
no: 4004 , name: 차범철

no: 4005 , name: 바비
no: 4006 , name: 전민
no: 4007 , name: 허은
 
PL/SQL procedure successfully completed.
 
SQL>  @weak_ref1.sql
 
'출력을 원하는 번호를 선택하세요'
1) Student , 2) Proffessor , 3) Department : 3   <- Department 선택합니다.
 
Printing Department InFORmation---------
no: 101 , name: 컴퓨터공학과
no: 102 , name: 멀티미디어공학과
no: 103 , name: 소프트웨어공학과
no: 201 , name: 전자공학과
no: 202 , name: 기계공학과
no: 203 , name: 화학공학과
no: 301 , name: 문헌정보학과
no: 100 , name: 컴퓨터정보학부
no: 200 , name: 메카트로닉스학부
no: 300 , name: 인문사회학부
no: 10 , name: 공과대학
no: 20 , name: 인문대학
 
PL/SQL procedure successfully completed.
================================================================
위와 같이 커서를 OPEN   하는 시점에 해당 작업을 할  SQL 이 지정되기 때문에 사용자가 입력한
값에 따라 다른 결과를 출력하게 됩니다.

 

2. Strong Ref Cursor (강한 타입- 제한적 Ref Cursor)
 
앞에서 살펴본 Weak Ref Cursor 은 Cursor 를 선언할 때 리턴 할 데이터 타입을 지정하지 않아도
잘 작동했지만  Strong Ref Cursor 는  Cursor 를 선언할 때 반드시 리턴 할 데이터 타입을 지정해
야 합니다. 그래서  Strong Ref Cursor 은  Cursor 로 부터 데이터를 받아서 저장할 데이터 타입을
먼저 선언을 해 놓고 Cursor 를 선언해야 한다는 부분이 Weak Ref Cursor 와 다른 점 입니다.
아래의 실습으로 테스트 해 보겠습니다.
 
아래의 실습은  emp 테이블에서  empno, ename,job 을  ref cursor 를 사용하여 변수에 담아 출력
하는 예제입니다.  
이 예제에서는 여러 컬럼와 여러 로우를 쉽게 저장하기 위해서  record  TYPE   변수형을 선언해서
사용했습니다. 물론 이 예처럼  record  TYPE   변수를 써야 하는 것은 아니며 다른 변수도 얼마든
사용할 수 있습니다.  
 

 

 

 

위 예에서 알 수 있듯이  Strong ref Cursor 는  Cursor 에서 변수로 값을 돌려줄 때  (Return) 형식
이 지정되어 있어야 하므로 제한적이라고 하는 것입니다. 그러나 Weak ref Cursor 는 형식이 지정
되지 않기 때문에 데이터 형이  다른 다양한 데이터를 선택해서 조회 할 경우 유용하게 사용될
수 있고 현재도 많이 사용되는 Cursor 이므로 꼭 습득하시기 바랍니다.
 
 

 

- SQL CURSOR 연습문제
 
1. 부서코드를 입력 받아 해당 부서에 속한 사원들을 삭제한 후 삭제된 사원 건수를 출력하세요.
(모든 변수와 커서의 이름은 임의로 지정하세요)  
 
2.명시적 커서를 사용하여 사용자에게 부서코드를 입력 받아 그 부서에 속한 사원들의 이름, 급여
를 출력하세요. (모든 변수와 커서의 이름은 임의로 지정하세요)
 
3. department 테이블을 조회하여 명시적 커서를 응용해서 아래와 같이 출력하세요.
단 커서이름이나 변수명 등은 각자 임의로 사용하세요.