PL/SQL CURSOR

커서 - 묵시적 커서(Implicit Cusor) : 필요할 경우 오라클이 자동적으로 선언하여 사용한 후 자동적으로 정리

 - 명시적 커서(Explicit Cursor) : 사용자가 정의한 커서른 선언하여 사용하고, 커서의 사용이 끝난 후에는 별도의 정리                                                작업을 수행해줘야 함

묵시적 커서

- 세션 내에 단 한 개만이 선언되어 사용되었다가 문장이 종료됨과 동시에 정리, 1개의 데이터만 저장됨

묵시적 커서 속성

- SQL%ROWCOUNT

해당 커서에서 실행한 총 행의 개수(가장 마지막 행이 몇 번째 행인지 카운트)를 반환

- SQL%FOUND

해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE 값을 반환하고 없을경우 FALSE 값을 반환

- SQL%NOTFOUND

해당 커서 안에 아직 수행해야 할 데이터가 없을 경우 TRUE 값을 반환하고 있을경우 FALSE 값을 반환

- SQL%ISOPEN

현재 묵시적 커서가 메모리에 OPEN 되어 있을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가지는 속성

사용 예)

SCOTT>DECLARE

BEGIN

DELETE emp

WHERE sal < 1000 ;

DBMS_OUTPUT.PUT_LINE('1000 이하 : '||sql%rowcount||'건 삭제되었습니다') ;

DELETE emp

WHERE sal BETWEEN 1000 AND 2000 ;

DBMS_OUTPUT.PUT_LINE('1000~2000 사이 : '||sql%rowcount||'건 삭제 되었습니다') ;

END ;

/

-> 1000 이하 : 2 건 삭제 되었습니다

     1000 ~2000 사이 : 6 건 삭제 되었습니다

명시적 커서

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

명시적 커서 속성

- 커서이름%ROWCOUNT

FETCH 문에 의해 읽혀진 데이터의 총 행 수를 가지는 속성. 가장 마지막에 처리된 행이 몇 번째 인지 반환

- 커서이름%FOUND

FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 있을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가짐

- 커서이름%NOTFOUND

FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 없을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가짐

- 커서이름%ISOPEN

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

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

      

SCOTT>DECLARE

BEGIN

DBMS_OUTPUT.PUT_LINE('학과명과 학과의 위치 안내 입니다') ;

DBMS_OUTPUT.PUT_LINE('---------------------------------') ;

FOR dept_cur IN (SELECT dname,build

                                     FROM department 

                   WHERE build IS NOT NULL

 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(dept_cur.dname||'-->'||dept_cur.build||'에 있습니다') ;

END LOOP ;

END ;

/

-> 학과명과 학과의 위치 안내 입니다

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

기계공학과 --> 기계실험관 에 있습니다

멀티미디어공학과 --> 멀티미디어관 에 있습니다

문헌정보학과 --> 인문관 에 있습니다

.... 

명시적 커서 처리 단계

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

PL/SQL의 선언부(DECLARE)에 다른 변수와 마찬가지로 선언되어야 하며, 형식은 아래와 같다

- 문법 :

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

- 커서명 : 명시적 커서의 이름

- 서브쿼리 : 처리하고자 하는 데이터를 검색하는 SELECT 문장을 이부분에 기술

2) 명시적 커서 오픈(OPEN)

선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져 온다는 뜻. 

- 문법 :

OPEN 커서 이름 ;

- 커서이름 : 열고자 하는 명시적 커서의 이름

3) 명시적 커서로부터 데이터 읽어서 변수로 할당하기(FETCH)

명시적 커서의 데이터들로부터 데이터를 한 건씩 읽어 변수로 할당하기 위해 FETCH문을 사용

- 문법 :

FETCH 커서 이름 INTO 변수

- 커서이름 : 읽어(FETCH) 오고자 하는 명시적 커서의 이름으로 반드시 OPEN 되어 있어야 FETCH 가능

- 변수들 : 명시적 커서로부터 읽어온(FETCH) 데이터 행(레코드) 을 PL/SQL 블록 내에서 처리하기 위해서   

               변수에 저장해서 사용. 

4) 명시적 커서 닫기(CLOSE)

명시적 커서의 정리 작업을 하는 명령으로, 명시적 커서가 다 사용된 후에는 반드시 '닫기'를 해야 함.

커서를 닫는다 함은 작업이 끝난 메모리 공간을 반환하고 정리한다는 뜻

- 문법 :

CLOSE 커서 이름 ;

- 커서이름 : 닫고자 하는 명시적 커서의 이름으로 Close 된 명시적 커서는 더 이상 사용하지 않는다

사용 예)

SCOTT>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 ;

/

명시적 커서와 Cursor FOR LOOP 문 활용

문법 :

FOR record_name IN cursor_name LOOP

-- 명시적 커서의 OPEN, FETCH 가 자동적으로 수행됨.

statement1 ;

statement2 ;

....

END LOOP ; -- 루프문을 빠져 나갈 때 자동적으로 커서가 CLOSE 됨.

- record_name : 커서로부터 FETCH 된 하나의 레코드를 저장하기 위한 커서 레코드 변수, 자동으로 만들어짐

- cursor_name : 처리하고자 하는 명시적 커서명으로, 선언부에서 정의되어야 함. 

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

emp 테이블에서 empno, ename 값을 가져와서 커서에 저장 후 출력하세요.

SCOTT>DECLARE

 CURSOR emp_cur IS   -- 커서 선언

       SELECT empno, ename

       FROM emp ;

    BEGIN

       FOR emp_rec IN emp_cur      -- 커서의 데이터를 저장할 emp_rec 변수 선언

        LOOP

          DBMS_OUTPUT.PUT_LINE(emp_rec.empno||' '||emp_rec.ename) ;

        END LOOP ;

    END ;

  /

파라미터 Explicit Cursor

- 명시적 커서를 선언 한 후 OPEN 할 때 값을 바꾸어서 수행해야 할 경우, 커서를 OPEN 할 때 필요한 값만 파라미터로 

전달해서 반복 수행 할 수 있는 방법

문법 :

CURSOR cursor_name

[(parameter_name dataTYPE ,...)] 

IS select-statement ;

- parameter_name : 파라미터 변수명으로 변수선언과 동일하며 여러 개의 파라미터를 지정할 수 있다

파라미터 Explicit Cursor 사용 예 :

SCOTT>DECLARE

CURSOR prof_cur (v_deptno IN NUMBER)

IS SELECT *

FROM professor

WHERE deptno=v_deptno ;

v_prof professor%ROWTYPE ;

BEGIN

DBMS_OUTPUT.PUT_LINE('============================') ;

DBMS_OUTPUT.PUT_LINE('101번 학과 교수님 번호와 이름 출력') ;

DBMS_OUTPUT.PUT_LINE('----------------------------') ;

OPEN prof_cur(101) ;

LOOP

FETCH prof_cur INTO v_prof ;

EXIT WHEN prof_cur%NOTFOUND ;

DBMS_OUTPUT.PUT_LINE(v_prof.profno||' 번 교수님 이름은 '||v_prof.name||' 입니다') ;

END LOOP ;

CLOSE prof_cur ;

DBMS_OUTPUT.PUT_LINE('============================') ;

DBMS_OUTPUT.PUT_LINE('102번 학과 교수님 번호와 이름 출력') ;

DBMS_OUTPUT.PUT_LINE('----------------------------') ;

OPEN prof_cur(102) ;

LOOP   

FETCH prof_cur INTO v_prof ;

EXIT WHEN prof_cur%NOTFOUND ;

DBMS_OUTPUT.PUT_LINE(v_prof.profno||' 번 교수님 이름은 '||v_prof.name||' 입니다') ;

END LOOP ;

CLOSE prof_cur ;

END ;

/

Cursor 와 Select....FOR UPDATE 문장

명시적 커서를 선언해 데이터를 조회한 후 변경을 하려는 중에 다른 세션에서 현 세션이 작업하는 내용을 변경 할 수 있다.

예를 들어 조회 할 시점에 A라는 데이터였지만 조회나 변경하는 중에 다른 세션이 B 라는 데이터로 바꿀수 있다는 뜻

그래서 커서를 선언 할 때 커서에 있는 행들에 대해 잠금을 수행.

1) 기본 문법 

이 실습을 위해 두 개의 세션에서 작업 실행

-세션 1-                                                                    -세션 2-

SCOTT>SELECT empno,ename,sal                                SCOTT>UPDATE emp

FROM emp                                                                  SET sal = 1000

WHERE empno = 7900                                                  WHERE empno = 7900 ;

FOR update ;                                                    -> 대기 상태로 반응 없습니다.

EMPNO    ENAME    SAL

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

    7900     JAMES    950

위와 같이 세션 1 에서 select 구문에 for update를 함께 쓰면 해당 데이터에 lock이 설정되어 다른 세션에서 dml 작업을 할 경우 대기상태로 들어감.

이 상태에서 세션 1 에서 commit 이나 rollback 명령을 수행하면 세션 2 에서 작업이 진행됨

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

문법 :

SELECT ...

FROM ...

FOR UPDATE [OF column_reference][NOWAIT|WAIT n] ;

위 옵션중 NOWAIT 옵션은 다른 세션에서 행을 잠글 경우 즉시 오라클 에러를 발생 시켜주는 옵션.

다른 세션에서 해당 행에 LOCK 을 설정했을 경우 NOWAIT 옵션을 사용하면 즉시 에러를 발생시킴

그러나 만약 NOWAIT 옵션을 사용하지 않을 경우 LOCK 이 확보 될 때 까지 무한 대기상태

WAIT n = n자리에 초를 설정할 수 있으며 해당 초가 지난 후에도 LOCK을 확보할 수 없으며 에러발생

Cursor 에서 FOR UPDATE 구문 사용 예 :

DECLARE

CURSOR emp_cur IS

SELECT empno, 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 ;