쿼리를 제외한 커서 헤더 부분만 선언하는 형태

커서 헤더만 선언부에 명시하면 해당 커서의 쿼리는 패키지 본문에서 작성해야 한다. 따라서 커서 구현부인 쿼리를 외부에 숨길 수 있다. 또한 헤더 부분만 선언할 때는 커서가 반환, 패치하는 데이터를 가리키는 RETURN절을 명시해야 한다. 즉 구현부의 쿼리 결과로 반환되는 컬럼의 타입을 RETURN절과 함께 명시해야 한다는 말이다. RETURN절은 두 가지 형태로 사용할 수 있는데, %ROWTYPE을 사용하는 것과 사용자가 직접 정의한 레코드 타입을 명시하는 것이다.

먼저 %ROWTYPE형 커서를 선언해 보자.

입력

    CREATE OR REPLACE PACKAGE ch12_cur_pkg IS
    ...
    ...
    -- ROWTYPE형 커서 헤더 선언
      CURSOR pc_depname_cur ( dep_id IN departments.department_id%TYPE )
        RETURN departments%ROWTYPE;

    END ch12_cur_pkg;

결과

    PACKAGE CH12_CUR_PKG이(가) 컴파일되었습니다.

이제 커서에 대한 쿼리를 패키지 본문에서 작성해 보자.

입력

    CREATE OR REPLACE PACKAGE BODY ch12_cur_pkg IS
      -- ROWTYPE형 커서 본문
      CURSOR pc_depname_cur ( dep_id IN departments.department_id%TYPE )
        RETURN departments%ROWTYPE
      IS
        SELECT *
          FROM departments
         WHERE department_id = dep_id;

    END ch12_cur_pkg;

결과

     PACKAGE BODY CH12_CUR_PKG이(가) 컴파일되었습니다.

커서에 대한 쿼리는 패키지 본문에 있으므로 외부에는 감춰져 있다. 하지만 이런 종류의 커서 역시 사용법은 동일하다.

입력

    BEGIN
      FOR rec IN ch12_cur_pkg.pc_depname_cur(30)
      LOOP
        DBMS_OUTPUT.PUT_LINE(rec.department_id || ' - ' || rec.department_name);
      END LOOP;
    END;

결과

    30 - 구매/생산부

이번에는 레코드를 직접 정의한 뒤, 이 레코드 타입을 반환하는 커서를 선언해 보자. 레코드 역시 패키지에서 선언해 사용 가능하며, 그 기본적인 성질은 변수와 동일하다.

입력

    CREATE OR REPLACE PACKAGE ch12_cur_pkg IS
    ...
    ...

      -- 사용자 정의 레코드 타입
      TYPE emp_dep_rt IS RECORD (
           emp_id employees.employee_id%TYPE,
           emp_name employees.emp_name%TYPE,
           job_title jobs.job_title%TYPE );

      -- 사용자 정의 레코드를 반환하는 커서
      CURSOR pc_empdep2_cur ( p_job_id IN jobs.job_id%TYPE )
        RETURN emp_dep_rt;

    END ch12_cur_pkg;

결과

    PACKAGE CH12_CUR_PKG이(가) 컴파일되었습니다.

emp_dep_rt 라는 레코드 타입을 선언한 뒤, 다시 이 레코드 타입을 반환하는 커서를 선언했다. 이제 커서에 대한 쿼리를 패키지 본문에서 작성해야 한다.

입력

    CREATE OR REPLACE PACKAGE BODY ch12_cur_pkg IS
    ...
    ...

    -- 사용자 정의 레코드를 반환하는 커서
      CURSOR pc_empdep2_cur ( p_job_id IN jobs.job_id%TYPE )
        RETURN emp_dep_rt
    IS
      SELECT a.employee_id, a.emp_name, b.job_title
        FROM employees a,
             jobs b
       WHERE a.job_id = p_job_id
         AND a.job_id = b.job_id;

    END ch12_cur_pkg;

결과

    PACKAGE BODY CH12_CUR_PKG이(가) 컴파일되었습니다.

이제 위에서 만든 커서를 사용하는 익명 블록을 만들어 보자.

입력

    BEGIN
      FOR rec IN ch12_cur_pkg.pc_empdep2_cur('FI_ACCOUNT')
      LOOP
        DBMS_OUTPUT.PUT_LINE(rec.emp_id || ' - ' || rec.emp_name || ' - ' || rec.job_title );
      END LOOP;
    END;

결과

    109 - Daniel Faviet - Accountant
    110 - John Chen - Accountant
    111 - Ismael Sciarra - Accountant
    112 - Jose Manuel Urman - Accountant
    113 - Luis Popp ? Accountant