Oracle PL/SQL REF 커서

C프로그램의 포인터와 유사하며 값 자체가 아닌 메모리 위치를 포인트하는 변수로, 정적 커서는 쿼리의 결과를 작업 영역에 담는 것에 비해 REF커서변수는 작업 영역에 대한 포인터를 가진다.

 

 

REF 커서 타입

 

REF 커서는 오라클에서 미리 선언해둔 타입인 SYS_REFCURSOR와 사용자가 REF 커서 타입을 정의하여 사용하는 두 가지 종류가 있다.

 

REF 커서 타입선언 시 반환타입을 설정할 수 있는데, 반환 타입이 없을 경우에는 어느 쿼리에서나 사용이 될 수 있지만 (Weak),RETURN 구문을 사용하여 반환 타입을 설정한 경우에는 반드시 같은 타입의 커서에서만 사용이 가능하다

 

 --반환 타입이 없는 약한 타입설정(weak)           
TYPE EMPCURTYPE IS REF CURSOR;
--반환 타입이 있는 강한 타입 설정(Strong)
--dept 테이블의 커서로만 사용 가능함
TYPE DEPTCURTYPE IS REF CURSOR RETURN DEPT%ROWTYPE;
--오라클에서 기본적으로 제공하는 REF커서
I_RECUR SYS_REFCURSOR;

 

REF 커서 사용 예

CREATE OR REPLACE PACKAGE REFCUR_PKG AS
--사용자 REF 커서 선언
TYPE refcur IS REF CURSOR;
END REFCUR_PKG;

--사용자 정의 REF 커서를 사용하는경우 
CREATE OR REPLACE PROCEDURE GET_REFCUR_PROC(
            P_REFCUR IN OUT REFCUR_PKG.refcur,
            P_TAB VARCHAR2 DEFAULT 'EMP')
      --패키지 명세에서 선언한 REF커서 대신 오라클에서 기본적으로 제공하는 SYS_REFCURSOR 를 사용함
      --REF커서를 입력으로 받고 출력하도록 IN OUT 설정
     IS 
      I_SQL VARCHAR2(2000) := 'SELECT * FROM DEPT';
      
      BEGIN
        --REF 커서가 오픈되었는지 여부 확인
         IF NOT P_REFCUR%ISOPEN THEN
         --P_TAB 값에 따라 다른 테이블을 오픈하도록 구분함
         --커서의 결과를 담는 작업 영역에 대한 포인터를 REF커서에 저장함
         CASE P_TAB
            --EMP 입력시 emp테이블을 오픈함
            WHEN 'EMP' THEN
             OPEN P_REFCUR FOR SELECT * FROM EMP WHERE EMPNO = 20;
             --그펗지 않을 경우 DEPT 테이블을 오픈함
             ELSE 
                OPEN P_REFCUR FOR I_SQL;
                
                END CASE;
                
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error in get_refcur_porc');
                DBMS_OUTPUT.PUT_LINE('Outer Error :' || SQLERRM(SQLCODE));
            END;
            /

커서 오픈시 'OPEN REF_커서명 FOR SELECT_STATEMNET' 구문을 활용하여 오픈하도록 했다. 

 

REF커서를 오픈만 해놓고 Fetch 및 CLOSE 가 수행되지 않았다. 일반 커서일 경우에는 한 프로그램 내에서 처리를 해야 하지만 REF커서가 사용되기 때문에 위에서 오픈한 REF 커서를 전달받은 다른 프로시저에서 FETCH및 Close처리가 가능해진다.

 

 

CREATE OR REPLACE PACKAGE REFCUR_PKG AS
--사용자 REF 커서 선언
TYPE refcur IS REF CURSOR;
END REFCUR_PKG;
/
--------------------------------------------------------------------------
--사용자 정의 REF 커서를 사용하는경우 
CREATE OR REPLACE PROCEDURE GET_REFCUR_PROC(
            P_REFCUR IN OUT REFCUR_PKG.refcur,
            P_TAB VARCHAR2 DEFAULT 'EMP')
      --패키지 명세에서 선언한 REF커서 대신 오라클에서 기본적으로 제공하는 SYS_REFCURSOR 를 사용함
      --REF커서를 입력으로 받고 출력하도록 IN OUT 설정
     IS 
      I_SQL VARCHAR2(2000) := 'SELECT * FROM DEPT';
      
      BEGIN
        --REF 커서가 오픈되었는지 여부 확인
         IF NOT P_REFCUR%ISOPEN THEN
         --P_TAB 값에 따라 다른 테이블을 오픈하도록 구분함
         --커서의 결과를 담는 작업 영역에 대한 포인터를 REF커서에 저장함
         CASE P_TAB
            --EMP 입력시 emp테이블을 오픈함
            WHEN 'EMP' THEN
             OPEN P_REFCUR FOR SELECT * FROM EMP WHERE DEPTNO = 20;
             --그펗지 않을 경우 DEPT 테이블을 오픈함
             ELSE 
                OPEN P_REFCUR FOR I_SQL;
                
                END CASE;
                
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error in get_refcur_porc');
                DBMS_OUTPUT.PUT_LINE('Outer Error :' || SQLERRM(SQLCODE));
            END;
            /
--------------------------------------------------------------------------
--선언한 REF커서 대신 오라클이 선행 선언한 SYS_REFCURSOR 를 사용함
--REF 커서를 입력으로 받아 출력하는 프로그램
CREATE OR REPLACE PROCEDURE PRINT_REFCUR_PROC(
                P_REFCUR IN SYS_REFCURSOR,
                P_TAB VARCHAR2 DEFAULT 'EMP')
                
IS
    I_EMP EMP%ROWTYPE;
    I_DEPT DEPT%ROWTYPE;
    
BEGIN
    LOOP
        --입력 변수가 emp이면 emp테이블의 로우 타입에 저장
        IF P_TAB ='EMP' THEN
         FETCH P_REFCUR INTO I_EMP;
        --입력 변수가 dept 이면 dept 테이블의 로우 타입에 저장 
        ELSIF P_TAB = 'DEPT' THEN
         FETCH P_REFCUR INTO I_DEPT;
         
        --위 두 값과 일치하지 않으면 에러 발생
        ELSE
            RAISE_APPLICATION_ERROR(-20901,'The specified table is crap!!');
        END IF;
        --추출할 결과가 없으면 종료
         EXIT WHEN P_REFCUR%NOTFOUND;
        
        IF P_TAB = 'EMP' THEN
            DBMS_OUTPUT.PUT_LINE('EMP : ' || I_EMP.SAL);
        ELSIF P_TAB = 'DEPT' THEN
            DBMS_OUTPUT.PUT_LINE('DEPT: ' || I_DEPT.DNAME);
    END IF;
  END LOOP;
--REF 커서 닫음
CLOSE P_REFCUR;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Errors in print_refcur_proc');
            DBMS_OUTPUT.PUT_LINE('Outer Error : ' || SQLERRM(SQLCODE));
END;
/
--------------------------------------------------------------------------
DECLARE
    --위에서 생성한 REF커서를 사용해도 무방함
    --I_REFCUR REFCUR_PKG.refcur;
    I_REFCUR SYS_REFCURSOR;
    
    BEGIN
        --커서를 오픈후 dept테이블에 관련된 커서를 반환하는 프로그램 수행
        GET_REFCUR_PROC(I_REFCUR,'DEPT');
        --커서를 오픈후 emp 테이블에 관련된 커서를 반환하는 프로구램 수행
       GET_REFCUR_PROC(I_REFCUR,'EMP');
        --REF커서를 이용해 결과를 추출 하는 프로그램 수행
       PRINT_REFCUR_PROC(I_REFCUR,'DEPT');
       --REF커서를 이용해 결과를 추출 하는 프로그램 수행
      PRINT_REFCUR_PROC(I_REFCUR,'EMP');
         
         EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
                
     END;
                /

 

REF 커서를 활용하면 커서를 오픈 하는 부분과 실제 Fetch, Close 하는 부분을 분리 하여 구현할수 있다.