7장. 명시적 커서 사용

7. 명시적 커서 사용

ex1)많은 행을 가져오므로 ERROR

SQL>

SET SERVEROUTPUT ON

DECLARE

  emp_rec emp%ROWTYPE ;

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE deptno = 10 ;

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

END ;

                    /

ERROR at line 1: ORA-01422: exact

FETCH returns more than requested NUMBER OF rows ORA-06512: at line 4

 

EX2) 커서 생성을 통한 여러 행 불러오기.

DECLARE

 CURSOR emp_cur   --Cursor 생성.

  IS

    SELECT * FROM emp WHERE deptno = 10 ;

  emp_rec emp%ROWTYPE ;

BEGIN

  OPEN emp_cur ;   --open이 되어야 문장이 실행된다.

  FETCH emp_cur INTO emp_rec ;  --첫번째 행만 FETCH.

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

  FETCH emp_cur INTO emp_rec ; --그 다음행 선별하여 꺼내온다.

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

  CLOSE emp_cur ;  --메모리 할당 해제.

END ;

                       /

7782 CLARK 7839 KING PL/SQL

PROCEDURE successfully completed.

 

Ex3) LOOP를 통한 반복.

DECLARE

  CURSOR emp_cur

  IS

    SELECT * FROM emp WHERE deptno = 10 ;

  emp_rec emp%ROWTYPE ;

BEGIN

  OPEN emp_cur ;

  LOOP

    FETCH emp_cur INTO emp_rec ;

    EXIT

  WHEN emp_cur%NOTFOUND ;

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

  END LOOP ;

  CLOSE emp_cur ;

END ;

                                   /

7782 CLARK 7839 KING 7934 MILLER PL/SQL

PROCEDURE successfully completed.

 

Ex3) For Loop문과 결합된 Cursor :  암시적으로 OPEN / FETCH / CLOSE가 수행된다.

DECLARE

  CURSOR emp_cur

  IS

    SELECT * FROM emp WHERE deptno = 10 ;

BEGIN

  FOR emp_rec IN emp_cur   --emp_rec선언 하지않아도 자동적으로 선언됨.

  LOOP

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

  END LOOP ;

END ;

                                   /

7782 CLARK 7839 KING 7934 MILLER PL/SQL

PROCEDURE successfully completed.

 

Ex4)

BEGIN

  FOR emp_rec IN

  ( SELECT * FROM emp WHERE deptno = 10

  )

  LOOP

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

  END LOOP ;

END ;

/

7782 CLARK

7839 KING

7934 MILLER

PL/SQL procedure successfully completed.

 

 

 

Ex4) Parameter가 포함된 커서.

DECLARE

  CURSOR emp_cur ( p_deptno NUMBER )

  IS

    SELECT * FROM emp WHERE deptno = p_deptno ;

BEGIN

  FOR emp_rec IN emp_cur (10)

  LOOP

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

  END LOOP ;

  FOR emp_rec IN emp_cur (20)

  LOOP

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

  END LOOP ;

END ;

/

 

 

 

SELECT empno, ename, sal, deptno FROM emp

WHERE deptno = 10 FOR UPDATE ; --검색된 행에대해 LOCK이 걸린다.

 

※ WHERE CURRENT OF 절 사용

 

Ex1)현재 내가 FETCH 해온 그 행에대해 직접적인 UPDATE를 한다.

DECLARE

  CURSOR emp_cur

  IS

    SELECT * FROM emp WHERE deptno = 10 FOR UPDATE ;

BEGIN

  FOR emp_rec IN emp_cur

  LOOP

    IF emp_rec.sal < 2000 THEN

      UPDATE emp SET sal = sal * 1.1 WHERE CURRENT OF emp_cur ; 

                                                                  /* WHERE empno = emp_rec.empno */         --최선이아니다!.   

    END IF ;

  END LOOP ;

END ;

/

 

전재 조건으로 반드시 해당 커서는 선언될 때 FOR UPDATE 절이 있어야한다.

LOCK이 많이 발생해 자주 쓸 순 없다.

 

* 해결방안. #음성178 확인50분 부터

DECLARE

  CURSOR emp_cur

  IS

    SELECT a.*, rowid rid from a  FROM emp a WHERE deptno = 10 FOR UPDATE ;

BEGIN

  FOR emp_rec IN emp_cur

  LOOP

    IF emp_rec.sal < 2000 THEN

      UPDATE emp SET sal = sal * 1.1 WHERE emp_rec.rid;

    END IF ;

  END LOOP ;

END ;

/