DBMS_SQL 패키지를 이용한 REF CURSOR(참조커서)

DBMS_SQL 패키지를 이용한 REF CURSOR(참조커서)

 ORACLE 11g이후 DBMS_SQL과 Native Dynamic SQL은 2개의 새로운 API를 통해 REF CURSOR와 DBMS_SQL의 CURSOR를 서로 변경할 수 있도록 있다.

DBMS_SQL.TO_REFCURSOR

 DBMS_SQL을 이용하여 만든 커서를 참조커서(REF CURSOR)로 변환한다.

-- 아래 예제는 EMP 테이블에서 입력한 부서코드의 사원을 출력하는 예제이다.
SQL> SET SERVEROUTPUT ON
SQL> ACCEPT p_deptno PROMPT 'Enter the Deptno:'
SQL> DECLARE
          -- 테이블타입은 데이터를 배열처럼 다룰 수 있는 타입인데
          -- 배열첨자를 위해 4바이트 정수형 PLS_INTEGER를 사용했다.
          TYPE emp_record IS TABLE OF emp%ROWTYPE
          INDEX BY PLS_INTEGER;
          v_emps    emp_record;
          v_sql    CLOB;
          v_cursor  PLS_INTEGER;
          v_rc      SYS_REFCURSOR;  --REF CURSOR형
          v_execute PLS_INTEGER;
      BEGIN
        v_cursor := DBMS_SQL.OPEN_CURSOR;
        v_sql := 'SELECT * FROM emp WHERE deptno = :deptno';
 
        -- Dynamic SQL문 파싱
        DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
 
        -- 바인드변수 바인딩
        DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', '&p_deptno');
 
        -- SQL문 실행
        v_execute := DBMS_SQL.EXECUTE(v_cursor);
 
        -- REF CURSOR로 변환
        v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);
 
        -- REF CURSOR에서 데이터 추출
        LOOP
            FETCH v_rc BULK COLLECT INTO v_emps LIMIT 100;
            FOR i IN 1 .. v_emps.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE(v_emps(i).ename || v_emps(i).sal);
            END LOOP;
            EXIT WHEN v_rc%NOTFOUND;
        END LOOP;
        CLOSE v_rc;
      END;
    /
'Enter the Deptno:: 20

구  19:      DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', '&deptno');
신  19:      DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', '20');
SMITH800
……
FORD3000

-- 이번에는 REF CURSOR를 리턴해 보자.
-- 아래처럼 함수의 리턴타입을 SYS_REFCURSOR로 하면 컴파일은 되지만 클라이언트쪽에서 출력되지 않는다.(Oracle 11g Release2 이후에는 가능하다)

SQL> CREATE OR REPLACE FUNCTION emplist (p_deptno emp.deptno%TYPE)
  2      RETURN SYS_REFCURSOR
  3  IS
  4        v_sql    CLOB;
  5        v_cursor  PLS_INTEGER;
  6        v_rc      SYS_REFCURSOR;  --REF CURSOR형
  7        v_execute PLS_INTEGER;
  8    BEGIN
  9        v_cursor := DBMS_SQL.OPEN_CURSOR;
 10      v_sql :='SELECT * FROM emp WHERE deptno = :deptno';
 11
 12        -- Dynamic SQL문 파싱
 13        DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
 14
 15        -- 바인드변수 바인딩
 16        DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', p_deptno);
 17
 18        -- SQL문 실행
 19        v_execute := DBMS_SQL.EXECUTE(v_cursor);
 20
 21        -- REF CURSOR로 변환
 22        v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);
 23
 24        return v_rc;
 25    END;
 26    /

함수가 생성되었습니다.

SQL> set autoprint on
SQL> variable refcur REFCURSOR
SQL> BEGIN
  2      :refcur := emplist(10);
  3  END;
  4  /
BEGIN
*
1행에 오류:
ORA-03001: 현재에는 구현되어 있지 않은 기능입니다
ORA-06512: "SYS.DBMS_SQL",  1104행
ORA-06512: "SCOTT.EMPLIST",  13행
ORA-06512:  2행

ERROR:
ORA-24338: 문장 처리가 수행되지 않았습니다

-- 출력이 되도록 하고 싶으면 이전에 작성한 형태대로 별도의 패키지등에서 REF CURSOR 타입을 미리 정의하면 된다.
SQL> create or replace package types
as
type curtype is ref cursor;
end;
/

SQL> CREATE OR REPLACE FUNCTION emplist (p_deptno emp.deptno%TYPE)
    RETURN TYPES.CURTYPE
IS         
      v_sql    CLOB;
      v_cursor  PLS_INTEGER;
      v_rc      TYPES.CURTYPE;  --REF CURSOR형
      v_execute PLS_INTEGER;
  BEGIN 
      v_cursor := DBMS_SQL.OPEN_CURSOR;     
    v_sql :='SELECT *  FROM emp WHERE deptno = :deptno';
 
      -- Dynamic SQL문 파싱
      DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
 
      -- 바인드변수 바인딩
      DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', p_deptno);
 
      -- SQL문 실행
      v_execute := DBMS_SQL.EXECUTE(v_cursor);
     
      -- REF CURSOR로 변환
      v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);
     
      return v_rc;
  END;
/

SQL> set autoprint on
SQL> variable refcur REFCURSOR
SQL> BEGIN
  2      :refcur := emplist(10);
  3  END;
  4  /

    EMPNO ENAME      JOB          MGR  HIREDATE    SAL      COMM    DEPTNO
    -----------------------------------------------------------------------------------------------------
      7782  CLARK      MANAGER    7839    81/06/09    3404.4              10
      7839  KING      PRESIDENT            81/11/17    6946.8              10
      7934  MILLER    CLERK        7782    82/01/23    2257.5              10