6.2.1 명시적 커서(EXPLICIT CURSOR)

출처 http://www.gurubee.net/lecture/1064

커서란 무엇인가?

– 커서는 Private SQL의 작업 영역이다.
– 오라클 서버에 의해 실행되는 모든 SQL문은 연관된 각각의 커서를 소유하고 있다.
– 커서의 종류
- 암시적 커서 : 모든 DML과 PL/SQL SELECT문에 대해 선언된다.
- 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서이다.

Explicit Cursor의 흐름도?

문법(Syntax)

커서 열기(OPEN)

– 커서의 열기는 OPEN문을 사용한다.
– 커서안의 검색이 실행되며 아무런 데이터행을 추출하지 못해도 에러가 발생하지 않는다.
– 문법 : OPEN cursor_name;

커서 패치(FETCH)

– 커서의 FETCH는 현재 데이터 행을 OUTPUT변수에 반환 한다.
– 커서의 SELECT문의 컬럼의 수와 OUTPUT변수의 수가 동일해야 한다.
– 커서 컬럼의 변수의 타입과 OUTPUT변수의 데이터 타입도 동일해야 한다.
– 커서는 한 라인씩 데이터를 패치 한다.
– 문법 : FETCH cursor_name INTO variable1, variable2;

커서 닫기(CLOSE)

– 사용을 맞친 커서는 반드시 닫아 주어야 한다.
– 필요하다면 커서를 다시 열 수 있다.
– 커서를 닫은 상태에서 FETCH를 할 수 없다.
– 문법 : CLOSE cursor_name;

Explicit Cursor 예제

 
-- 특정 부서의 평균급여와 사원수를 출력..
SQL> CREATE OR REPLACE PROCEDURE ExpCursor_Test 
       (v_deptno IN  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;
   /

-- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON ;

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