④ 커서 변수를 매개변수로 전달하기

커서 변수도 일종의 변수이므로, 사용자 정의 함수나 프로시저의 매개변수로 전달할 수 있다. 매개변수를 전달해서 받아 사용해야 하므로, 전달하는 쪽이나 받는 쪽이나 모두 같은 커서 타입의 변수를 사용해야 한다. 커서 변수를 매개변수로 전달하는 예제를 살펴 볼 텐데 그 처리 로직은 다음과 같다.

Ⓐ 먼저 SYS_REFCURSOR 타입 커서 변수를 생성하고, Ⓑ 커서 변수를 매개변수로 받는 프로시저를 만들 것이다. 이 프로시저의 매개변수는 당연히 SYS_REFCURSOR 타입으로 IN OUT 매개변수로 만들어 프로시저에서 커서를 오픈, 정의한 뒤, Ⓒ 패치한 결과를 매개변수에 할당하고 마지막으로 Ⓓ 프로시저를 호출하는 부분에서 전달해 받은 매개변수를 LOOP문을 사용해 이 매개변수에 담겨 있는 값을 출력해 볼 것이다. 실제 커서에 대한 쿼리는 앞에서 사용했던 90번 부서에 속한 사원이름을 출력하는 쿼리를 사용할 것이다. 예제소스는 다음과 같다.

입력

    DECLARE
      -- Ⓐ SYS_REFCURSOR 타입의 커서 변수 선언
      emp_dep_curvar SYS_REFCURSOR;

      -- 사원명을 받아오기 위해 변수를 선언한다.
      vs_emp_name employees.emp_name%TYPE;

      -- Ⓑ 커서 변수를 매개변수로 받는 프로시저, 매개변수는 SYS_REFCURSOR 타입의 IN OUT형
      PROCEDURE test_cursor_argu ( p_curvar IN OUT SYS_REFCURSOR)
      IS
        c_temp_curvar SYS_REFCURSOR;
      BEGIN
        -- 커서를 오픈
        OPEN c_temp_curvar FOR
        SELECT emp_name
         FROM employees
        WHERE department_id = 90;

        -- Ⓒ 오픈한 커서를 IN OUT 매개변수에 다시 할당
        p_curvar := c_temp_curvar;
      END;

    BEGIN
      -- 프로시저 호출
      test_cursor_argu (emp_dep_curvar);
      -- 프로시저 호출 후 emp_dep_curvar 변수에는 이 프로시저의 c_temp_curvar 결과가 담겨 있음

       -- Ⓒ 전달해서 받은 매개변수를 LOOP문을 사용해 결과를 출력
      LOOP
        -- 커서 변수를 사용해 결과 집합을 vs_emp_name 변수에 할당
        FETCH emp_dep_curvar INTO vs_emp_name;
        -- 더 이상 패치된 참조 로우가 없으면 LOOP 탈출(커서 변수를 이용한 커서 속성 참조)
        EXIT WHEN emp_dep_curvar%NOTFOUND;
        -- 사원명을 출력
        DBMS_OUTPUT.PUT_LINE(vs_emp_name);

      END LOOP;

    END;

결과

    Steven King
    Neena Kochhar
    Lex De Haan