[PL/SQL] 커서(CURSOR) (3) : 커서 변수

 [PL/SQL] 커서(CURSOR) (1) : 명시적 커서와 묵시적 커서

 [PL/SQL] 커서(CURSOR) (2) : 커서와 FOR문

 [PL/SQL] 커서(CURSOR) (3) : 커서변수

커서 변수

특징

- 한 개 이상의 쿼리를 연결해 사용할 수 있다.

  하나의 커서 변수를 선언해 쿼리를 연결해서 사용한 뒤, 같은 커서 변수를 또 다른 쿼리에 연결해서 사용할 수 있다.

- 변수처럼 커서 변수를 함수나 프로시저의 매개변수로 전달할 수 있다.

- 커서 속성을 사용할 수 있다.

사용방법

1. 커서 변수 선언하기

TYPE 커서_타입명 IS REF CURSOR [ RETURN 반환 타입 ];
커서_변수명 커서_타입명;

반환타입은 해당 커서가 반환하는 결과 집합을 나타내며 '%ROWTYPE' 속성으로 정의한다.

RETURN 반환 타입은 생략가능하며, 생략시 약한커서타입, 생략하지않으면 강한커서타입이 된다.

오라클 빌트인 커서 타입인 SYS_REFCURSOR 사용한다. RETURN을 쓰지 않으므로 당연히 약한 커서 타입이 된다.

TEST_CURSOR SYS_REFCURSOR;

2. 커서 변수 사용하기

OPEN 커서변수명 FOR SELECT 문;

선언한 커서 타입이 약한 커서 타입이면 커서 결과 집합의 종류가 다양해진다.

만약 강한 커서 타입을 선언한다면 아래와 같이 선언하는데,

TYPE TEST_CURTYPE IS REF CURSOR RETURN DEPARTMENTS%ROWTYPE;
TEST_CURVAR TEST_CURTYPE;

OPEN TEST_CURVAR FOR SELECT  *
                     FROM    DEPARTMENTS;

위 처럼 커서 변수를 사용할 수 있지만 아래 처럼 커서 타입과 다르게는 사용할 수 없다.

OPEN TEST_CURVAR FOR SELECT  DEPARTMENT_ID
                     FROM    DEPARTMENTS;

    

그런데 약한 커서 타입을 선언하면 위 두 방식 모두 사용할 수 있다.

TYPE TEST_CURTYPE IS REF CURSOR;
TEST_CURVAR TETS_CURTYPE;

OPEN TEST_CURVAR FOR SELECT  *
                     FROM    DEPARTMENTS;

OPEN TEST_CURVAR FOR SELECT  DEPARTMENT_ID
                     FROM    DEPARTMENTS;

3. 커서 변수에서 결과 집합 가져오기

FETCH 커서변수명 INTO 변수1, 변수2, ...;

FETCH 커서변수명 INTO 레코드명;

예제

DECLARE
    VS_EMP_NAME     EMPLOYEES.EMP_NAME%TYPE;
    
    TYPE    EMP_DEP_CURTYPE IS REF CURSOR;
    EMP_DEP_CURVAR EMP_DEP_CURTYPE;
BEGIN
    OPEN    EMP_DEP_CURVAR  FOR SELECT  EMP_NAME
                                FROM    EMPLOYEES
                                WHERE   DEPARTMENT_ID = 90;
    
    LOOP
        FETCH   EMP_DEP_CURVAR INTO    VS_EMP_NAME;
        
        EXIT    WHEN EMP_DEP_CURVAR%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(VS_EMP_NAME);
    END LOOP;
END;

SYS_REFCURSOR 를 이용한 예제

DECLARE
    VS_EMP_NAME     EMPLOYEES.EMP_NAME%TYPE;
    
    EMP_DEP_CURVAR SYS_REFCURSOR;
BEGIN
    OPEN    EMP_DEP_CURVAR  FOR SELECT  EMP_NAME
                                FROM    EMPLOYEES
                                WHERE   DEPARTMENT_ID = 90;
    
    LOOP
        FETCH   EMP_DEP_CURVAR INTO    VS_EMP_NAME;
        
        EXIT    WHEN EMP_DEP_CURVAR%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(VS_EMP_NAME);
    END LOOP;
END;

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

DECLARE
    VS_EMP_NAME     EMPLOYEES.EMP_NAME%TYPE;
    -- 커서 변수 선언
    EMP_DEP_CURVAR  SYS_REFCURSOR;
    
    -- 프로시저 정의 매개변수는 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;
        
        P_CURVAR := C_TEMP_CURVAR;
    END;
BEGIN
    -- 프로시저 호출
    TEST_CURSOR_ARGU(EMP_DEP_CURVAR);
    
    -- 전달받은 매개변수를 출력
    LOOP
        FETCH   EMP_DEP_CURVAR INTO VS_EMP_NAME;
        
        EXIT    WHEN EMP_DEP_CURVAR%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(VS_EMP_NAME);
    END LOOP;
END;

 [PL/SQL] 커서(CURSOR) (1) : 명시적 커서와 묵시적 커서

 [PL/SQL] 커서(CURSOR) (2) : 커서와 FOR문

 [PL/SQL] 커서(CURSOR) (3) : 커서변수