[Oracle/기본]22. 고급 명시적 커서 개념

● 매개변수와 커서

 

커서가 열릴 때 커서로 매개변수 값을 전달하고, 커서가 실행될 때 질의에서 그 값이 사용될 수 있다.

 

DECLARE

   CURSOR C1

   (v_deptno NUMBER, v_job VARCHAR2) IS

     SELECT empno, ename

      FROM emp

     WHERE deptno = v_deptno

        AND job = v_job;

BEGIN

   OPEN c1(10, 'CLERK');

...

 

● FOR UPDATE 절

 

SELECT ...

 FROM ...

FOR UPDATE [OF column_reference] [NOWAIT]

 

column_reference 수행된 질의에 대응하는 테이블 열 또는 열의 집합이다.

NOWAIT            다른 섹션이 행을 잠그면 오라클 에러를 리턴한다.

 

- 커서가 열릴 때 영향을 미치는 행을 잠그기 위해 커서 질의에서 FOR UPDATE 절을 추가한다.

- ORDER BY 절이 있다 해도, SELECT 문장에서 마지막 절이 된다.

 

Ex. DECLARE

       CURSOR c1 IS

           SELECT empno, ename

            FROM emp

           FOR UPDATE NOWAIT;

 

● WHERE CURRENT OF 절

 

- 명시적 커서에서 현재 행을 참조할 때 WHERE CURRENT OF 절을 사용한다.

  이를 통해서 명시적으로 ROWID를 참조하지 않고 현재 처리 중인 행을 갱신하고 삭제할 수 있게 해 준다.

- 행을 OPEN시에 잠기게 하기 위해서 커서질의에 FOR UPDATE 절을 포함해야 한다.

 

DECLARE

   CURSOR c1 IS

       SELECT ...

       FOR UPDATE NOWAIT;

BEGIN

...

   FOR emp_record IN c1 LOOP

      UPDATE ...

         WHERE CURRENT OF c1;

      ...

   END LOOP;

   COMMIT;

END;

 

☞ WHERE CURRENT OF 절을 사용할 때, 참조되는 커서는 커서 질의에서 FOR UPDATE 절을 포함해야 하고 존재해야 한다.

☞ WHERE CURRENT OF 절은 ROWID를 명시적으로 참조할 필요 없이, 현재 처리된 행에 대해 갱신과 삭제를 할 수 있도록 해준다.

 

● 서브쿼리와 커서

 

DECLARE

    CURSOR my_cursor IS

        SELECT t1.deptno, dname, STAFF

          FROM dept t1, (SELECT deptno, count(*) STAFF

                                FROM emp

                               GROUP BY deptno) t2

         WHERE t1.deptno = t2.deptno

            AND STAFF >= 5;