Oracle PL/SQL REF 커서 [ 바인드변수 사용]

REF커서를 사용해야 할 경우 USING 절을 이용해야 한다 대부분의 경우 WHERE 조건의 변수 값만 달리한 거의 동일한 SQL 이 반복수행되는데, USING 절이 생략되면 SQL 공유가 되지 않아 매번 하드 파싱을 유발하면 성능 문제를 일으킬수 있다.

 

CREATE OR REPLACE PROCEDURE OPEN_REFCUR_PROC ( P_ENAME IN VARCHAR2 ,
                                               O_CURSOR OUT SYS_REFCURSOR)
                                               
IS
    I_SQL_STATEMENT VARCHAR2(32676);
    
BEGIN
    --변수명 앞에 콜론 을 추가하여 USING 구문 대상임을 선언해야함
    I_SQL_STATEMENT := '
            SELECT /*USING*/ ENAME,JOB 
            FROM EMP
            WHERE ENAME := P_ENAME';
        --USING 구문을 SQL 공유가 가능함
        --USING 다음에 :p_ename 바인드 변수에 대응하는 변수명을 입력
        
        OPEN O_CURSOR FOR I_SQL_STATEMENT USING P_ENAME;
    END;
    /

emp 테이블에서 사원 이름만 변경하여 조회하는 SQL 문이 공유될 수 있도록  REF 커서 오픈 시에 USING 구문을 사용한 예이다.

USING 절 다음에 바인드 변수에 해당하는 변수 값을 기술 하면된다.

 

REF커서만 오픈 하였으므로, 반환되는 REF커서를 입력 받아 데이터를  FETCH 하고 CLOSE 하는 또다른 프러시저를 작성해야한다.

 

 

CREATE OR REPLACE PROCEDURE CLOSE_REFCUR_PROC(P_REFCUR IN SYS_REFCURSOR)
    IS
    --커서 내용을 저장할 레코드 선언
    TYPE REC_EMP IS RECORD (
    ENAME EMP.ENAME%TYPE,
    JOB   EMP.JOB%TYPE
    );
    
    I_REF REC_EMP;
    
    BEGIN
        LOOP
            --넘겨받은 REF 커서의 내용을 FETCH 하여 변수에 저장함
            FETCH P_REFCUR INTO I_REF;
            
            EXIT WHEN P_REFCUR%NOTFOUND;
            
             DBMS_OUTPUT.PUT_LINE(I_REF.ENAME || ''|| I_REF.JOB);
            END LOOP;
            --커서 close
            CLOSE P_REFCUR;
            
        EXCEPTION
        WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('OUTHER ERROR= ' || '' ||SQLERRM(SQLCODE));
           
     END;

 

DECLARE 
--ref 커서 변수 선언
    I_REFCUR SYS_REFCURSOR;
    
    BEGIN
        FOR EMP_REC IN (SELECT ENAME FROM EMP)
        --ENAME 만 변경하면 프로시저 호출
         LOOP
                OPEN_REFCUR_PROC(EMP_REC.ENAME,I_REFCUR);
                 CLOSE_REFCUR_PROC(I_REFCUR);
                END LOOP;
                
            EXCEPTION
                WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('SQLERRORCODE = ' || SQLERRM(SQLCODE));
            
        END;
            /

 

 

SELECT SQL_ID,HASH_VALUE,LOADED_VERSIONS,executions,SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE '%USING%';

USING 절이 사용된 SQL의 실행 횟수(EXECUTIONS)이 15번 수행되었음을 알수 있다.

이는 SQL 이 공유되어 재사용되고 있음을 보여주는 것입니다. 따라서 REF커서가 반복 사용될 때는 반드시  USING 절을 추가하여  SQL 이 공유 처리될 수 있도록 해야합니다.