Oracle Cursor

Oracle Cursor

오라클 서버에 의해 실행되는 모든 SQL문은 연관된 각각의 커서를 소유하고 있다. 오라클 서버는 명령문의 구문을 분석한 뒤 메모리에 가져오는 행을 임시 저장하는데, 이 메모리 영역을 커서(Cursor)라고 한다. 즉, 커서는 Private SQL의 작업영역이다.

커서에는 암시적 커서(Implicit Cursor)와 명시적 커서(Explicit Cursor)가 있다.
우리가 사용하는 모든 SQL은 자동으로 커서를 사용하고 있으며, 이것을 암시적 커서라고 한다. 암시적 커서를 명명된 이름을 붙여 여러 용도로 사용할 수 있으며, 이것을 명시적 커서라고 한다.

- 암시적 커서 : 모든 DML과 PL/SQL SELECT문에 대해 선언
- 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서 

명시적 커서의 장점
- 특정 행을 건너 뛰고 처리 가능
- 현재 처리되고 있는 행을 추적 가능
- 프로그래머가 PL/SQL 블록에서 수동으로 제어가능

명시적 커서의 흐름도


문법(Syntax)

DECLARE --커서 정의
  CURSOR cur_name IS SELECT statement -- 커서의 선언
BEGIN
  OPEN cur_name; -- cursor분석 및 실행
  LOOP
    FETCH cur_name into variable1, variable2; -- cursor의 현재 행을 변수에 로드
  END LOOP; -- 가져올 행이 없으면 종료
  CLOSE cur_name; -- 커서를 닫는다
END;
/

Explicit Cursor 예제 - 특정 부서의 평균급여와 사원수를 출력...

SQL>CREATE OR REPLACE PROCEDURE ExpCursor_Test
        (v_deptno   dept.deptno%TYPE)
        IS

        CURSOR dept_avg IS
        SELECT b.dname, COUNT(a.empno) cnt, ROUND(AVG(a.sal),3) salary
        FROM emp a, dept b
        WHERE a.deptno = b.deptno
            AND b.deptno = v_deptno
        GROUP BY b.dname ;
 
        -- 커서를 패치하기 위한 편수 선언
         v_dname   dept.dname%TYPE;
         emp_cnt    NUMBER;
         sal_avg     NUMBER;

      BEGIN

        -- 커서의 오픈
        OPEN dept_avg;

        -- 커서의 패치 
        FETCH dept_avg INTO v_dname, emp_cnt, sal_avg;
   
           DBMS_OUTPUT.PUT_LINE('부서명 : ' || v_dname);
           DBMS_OUTPUT.PUT_LINE('사원수 : ' || emp_cnt);
           DBMS_OUTPUT.PUT_LINE('평균급여 : ' || sal_avg);

       -- 커서의 CLOSE
       CLOSE dept_avg;      
 
     EXCEPTION

       WHEN OTHERS THEN

         DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');

     END;
     /

프로시져가 생성되었습니다.

실행 결과
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)

SQL> EXECUTE ExpCursor_Test(30);
부서명 : SALES
사원수 : 6
평균급여 : 1550.833

PL/SQL 처리가 정상적으로 완료되었습니다.