② DBMS_SQL.TO_REFCURSOR 함수

DBMS_SQL 패키지로 SELECT문을 실행해 결과를 받아오기 위해서는 SELECT 리스트에 해당하는 각 컬럼을 정의(DEFINE_COLUMN)한 뒤 루프를 돌며 패치하면서 이전에 정의했던 컬럼값을 변수에 할당(COLUMN_VALUE)하는 과정을 거쳐야 했다. 그런데 11g부터 제공된 TO_REFCURSOR 함수를 사용하면 이런 과정을 줄일 수 있다.

TO_REFCURSOR 함수는 파싱되고 실행된 커서를 약한 커서 타입으로 변환하는 함수로, 변환된 약한 커서 타입을 커서 변수로 받을 수 있다. 한마디로 SELECT한 결과를 커서 변수로 받을 수 있다는 말이다. 커서 변수로 결과를 받으면 이전처럼 컬럼 정의, 루프를 돌며 패치, 정의한 컬럼 값을 변수에 넣는 일련의 과정을 생략할 수 있다. 그저 결과가 담긴 커서 변수의 값을 추출해 내기만 하면 된다.

    DBMS_SQL.TO_REFCURSOR(
           cursor_number IN OUT INTEGER)
    RETURN SYS_REFCURSOR;

• cursor_number: 변환할 문장의 커서 ID

• 반환 값: 오라클에서 제공하는 SYS_REFCURSOR 타입

그럼 예제를 통해 이 함수의 동작 방식을 살펴 보자. 기본 활용 편에서 소개했던 사원번호, 사원명을 추출했던 예제를 그대로 사용하되, 이번에는 DBMS_SQ.TO_REFCURSOR 함수를 사용함으로써 이전 예제와 비교해 어떤 점이 달라졌는지 알아 보자.

입력

    DECLARE
      --출력용 변수 선언
      vc_cur       SYS_REFCURSOR;               --커서 변수
      va_emp_id    DBMS_SQL.NUMBER_TABLE;       --사번을 담을 컬렉션 변수
      va_emp_name  DBMS_SQL.VARCHAR2_TABLE;     --사원명을 담을 컬렉션 변수

      vs_sql VARCHAR2(1000);

      -- 바인드 변수 선언과 값 설정
      vs_job      employees.job_id%TYPE := 'SA_REP';
      vn_sal      employees.salary%TYPE := 9000;
      vn_manager  employees.manager_id%TYPE := 148;

      -- DBMS_SQL 패키지 관련 변수
      vn_cur_id   NUMBER := DBMS_SQL.OPEN_CURSOR(); -- 1. 커서를 연다
      vn_return   NUMBER;
    BEGIN
      -- SQL문을 변수에 담는다(바인드 변수 앞에 :를 붙인다)
      vs_sql := 'SELECT employee_id, emp_name
                   FROM employees
                  WHERE job_id = :a
                    AND salary < :b
                    AND manager_id = :c ';

      -- 2. 파싱
      DBMS_SQL.PARSE (vn_cur_id, vs_sql, DBMS_SQL.NATIVE);

      -- 3. 바인드 변수 연결(WHERE 절에 사용한 변수가 3개 이므로 각 변수별로 총 3회 호출)
      DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':a', vs_job);
      DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':b', vn_sal);
      DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':c', vn_manager);

      -- 4. 쿼리 실행
      vn_return := DBMS_SQL.EXECUTE (vn_cur_id);

      -- 5. DBMS_SQL.TO_REFCURSOR를 사용해 커서로 변환
      vc_cur := DBMS_SQL.TO_REFCURSOR (vn_cur_id);

      -- 6. 변환한 커서를 사용해 결과를 패치하고 결과 출력
      FETCH vc_cur BULK COLLECT INTO va_emp_id, va_emp_name;

      FOR i IN 1 .. va_emp_id.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(va_emp_id(i) || ' - ' || va_emp_name(i));
      END LOOP;

      -- 7. 커서 닫기( vn_cur_id가 아니라 변환된 vc_cur 커서를 닫는다)
      CLOSE vc_cur;
    END;

결과

    171 - William Smith
    172 - Elizabeth Bates
    173 - Sundita Kumar

언뜻 봐도 기존에 비해 코드가 줄어 들었음을 알 수 있을 것이다. 컬럼 정의, 패치, 값 할당 작업을 일일이 할 필요 없이 TO_REFCURSOR 함수를 사용해 동적 SQL문에 대한 커서를 커서 변수로 할당한 뒤, BULK COLLECT INTO 절을 사용해 선택한 결과를 컬렉션 변수에 담기만 하면 된다. 마지막 FOR문은 단순히 값을 출력하려고 넣은 것일 뿐이므로 이 반복문을 굳이 사용할 필요는 없다.

한 가지 주의할 점은 맨 마지막에 커서를 닫는 부분에서, 기존에는 DBMS_SQL.CLOSE_CURSOR 프로시저를 사용해 OPEN_CURSOR 함수가 반환한 커서(vn_cur_id)를 닫았지만, 이번 경우에는 이미 커서 변수로 변환(vc_cur)을 했으므로, 닫아야 할 커서는 vn_cur_id가 아니라 vc_cur이라는 점을 잊지 않도록 하자.