[IT실무고급교육★탑크리에듀]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