Oracle PL/SQL 프로시저[커서]

명시적 커서를 패키지에서 선언하고 사용할수 있는데 변수나 상수와 마찬가지로 패키지 내에서 선언한 커서는 세션이 살아 있는 동안 유지된다. 변수는 값이 유지되지만, 커서는 그 상태까지 유지되는 특징이 있다.

 

패키지 선언부에 커서 전체를 선언하는 형태

CREATE OR REPLACE PACKAGE EMP_CUR
  IS
  CURSOR PC_EMPDEP_CUR(DEP_ID IN DEPARTMENTS.DEPARTMENT_ID%TYPE)
  IS
         SELECT E.EMPLOYEE_ID , A.EMP_NAME ,B.DEPARTMENT_ID
         FROM EMPLOYEES E, DEPARTMENTS B
         WHERE E.DEPARTMENT_ID = DEP_ID
         AND E.DEPARTMENT_ID = B.DEPARTMENT_ID;
     END EMP_CUR;

BEGIN 
  FOR REC IN EMP_CUR.PC_EMPDEP_CUR(30)
    LOOP
      DBMS_OUTPUT.PUT_LINE(REC.EMP_NAME || '-' || REC.DEPARTMENT_NAME);
      END LOOP;
      END;   
      

변수처럼 패키지 선언부에 선언하면 공용이 되어 '패키지명.커서명' 형태로 참조할수 있다.

 

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

커서 헤더만 선언부에 명시하면 해당 커서의 쿼리는 패키지 본문에서 작성해야 한다. 따라서 커서 구현부인 쿼리를 외부에 숨길수가 있다. 또한 헤더 부분만 선언할 때는 커서가 반환, 패치하는 데이터를 가리키는 RETURN절을 명시해야한다.

즉 구현부의 쿼리 결과로 반환되는 컬럼의 타입을 RETURN 절과 함께 명시해야 한다는 말이다. RETURN 절은 두가지 형태로 사용할수 있는데 %ROWTYPE을 사용하는것과 사용자가 직접 정의한 레코드 타입을 명시하는것이다.

 

 

CREATE OR REPLACE PACKAGE EMP_CUR
  IS
  .......
     
     --ROW 형태의 커서선언
         CURSOR PC_DEPNAME_CUR(DEP_ID IN DEPARTMENTS.DEPARTMENT_ID%TYPE)
         RETURN DEPARTMENTS%ROWTYPE;

커서에 대한 쿼리를 패키지 본문에 작성

 CREATE OR REPLACE PACKAGE EMP_CUR IS
  --ROWTYPE형 커서본문
  CURSOR PC_DEPNAME_CUR(DEP_ID IN DEPARTMENTS.DEPARTMENT_ID%TYPE)
  RETURN DEPARTMENTS%TYPE
  
  IS
  SELECT * FROM DEPARTMENTS
  WHERE DEPARTMENT_ID = DEP_ID;
         

 

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

 

 

CREATE OR REPLACE PACKAGE EMP_CUR IS
....
....
       TYPE EMP_DEP IS RECORD (
       EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE,
       EMP_NAME EMPLOYEES.EMP_NAME%TYPE,
       JOB_TITLE JOBS.JOB_TITLE%TYPE);
       
       CURSOR PC_EMP_CUR (P_JOB_ID IN JOBS.JOB_ID%TYPE)
       RETURN EMP_DEP_RT;
      

EMP_DEP 라는 레코드를 선어한뒤 다시 이 레코드 타입을 반환하는 커서를 선언했다. 

 

CREATE OT REPLACE PACKAGE EMP_CUR IS
...
...
 CURSOR PC_EMP_CUR (P_JOB_ID IN JOBS.JOB_ID%TYPE)
       RETURN EMP_DEP_RT
      
       IS SELECT E.EMPLOYEE_ID, E.EMP_NAME, B.JOB_TITLE
       FROM EMPLOYEES E, JOBS B
       WHERE E.JOB_ID = P_JOB_ID
       AND E.JOB_ID = B.JOB_ID
                    

 커서에 대한 쿼리를 패키지 본문에 작성했다.