0820_Oracle[PL/SQL] : CURSOR 커서

커서 CURSOR ?

 

- 하나의 레코드가 아닌 여러 레코드로 구성된 작업영역에서 SQL문을 실행하고 그 과정에 생긴 정보를 저장하기 위해서 CURSOR를 사용한다.

- 오라클 서버에 의해 실행되는 모든 SQL문은 연관된 각각의 커서를 소유하고 있다.

 

주기억장치에 저장된 공간이 커서이고, 그 커서에서 하나씩 꺼내오는 것이 FETCH이다.

 

-- 명시적 커서

DECLARE
    vname emp.name%TYPE;
    vsal emp.sal%TYPE;
    
    -- 1. 커서 선언
    CURSOR cur_emp IS SELECT name, sal FROM emp;
BEGIN
    -- 2. 커서 OPEN
    OPEN cur_emp;
    
    LOOP
        -- 3. FETCH - 하나 레코드를 가져옴
        FETCH cur_emp INTO vname, vsal;
        EXIT WHEN cur_emp%NOTFOUND; -- 이거 없으면 에러. 
        -- 가져올 자료가 없으면 LOOP를 빠져 나가라.
        
        DBMS_OUTPUT.PUT_LINE(vname || ' : ' || vsal);
    END LOOP;
    -- 커서 CLOSE
    CLOSE cur_emp;
END;
/

 

-- 암시적 커서

DECLARE
    vempNo emp.empNo%TYPE;
    vName emp.name%TYPE;
    vSal emp.sal%TYPE;
    vCount NUMBER;
BEGIN
    vempNo := '1001';
    SELECT name, sal INTO vname, vsal FROM emp WHERE empNo = vempNo;
        -- 레코드가 없거나 2개 이상 존재하면 오류
    vCount := SQL%ROWCOUNT; -- 해당 SQL 문에 영향을 받은 행의 수 
    
    DBMS_OUTPUT.PUT_LINE(vCount || ' : ' || '개 존재');
END;
/

 

-- CURSOR FOR LOOP : 자동 OPNE, 자동 FETCH, 자동 CLOSE

DECLARE 
    CURSOR cur_emp IS SELECT name, sal FROM emp;
BEGIN
    FOR rec IN cur_emp LOOP 
        DBMS_OUTPUT.PUT_LINE(rec.name || ' : ' || rec.sal);
    END LOOP;
END;
/

 

커서 변수 ( CURSOR VARIABLE) 

- 명시적 커서와 암시적 커서는 정적이므로, 커서가 만들어지는 시점에서 쿼리문이 정의된다. 따라서 런타임까지는 커서에 이용할 쿼리를 정의할 수 없기 때문에 REF CURSOR 와 커서변수를 제공하여, 런타임 시에 커서의 쿼리를 정의하고 결과 셋을 처리할 수 있도록 하고 있다.

- 커서 타입의 변수는 프로시저 등의 매개 변수로 사용할 수 있다.

- REF CURSOR 커서 타입 

　　강한 커서 타입 : 쿼리의 결과 셋을 구성할 리턴 타입을 %ROWTYPE 속성으로 정의한다.

　　약한 커서 타입 : 리턴 타입 정의를 포함하지 않고, 어떤 결과 셋(set)을 얻는 경우에도 이용할 수 있다.

- REF CURSOR type 정의

　　형식

　　　TYPE type_name IS REF CURSOR [ RETRUN return_type ]

　　커서 타입 정의 예

　　　TYPE refcur IS REF CURSOR ; -- 약한 커서 타입

　　　TYPE refcur_emp IS REF CURSOR RETURN emp%ROWTYPE ; -- 강한 커서 타입

　　커서 변수 사용

　　1) 커서 변수 선언 : 커서변수 커서타입명;

　　2) 커서 변수 사용 : OPEN 커서변수 FOR SELECT 문 ;

 

-- SYS_REFCURSOR 예

CREATE OR REPLACE PROCEDURE pEmpSelectList
(
    pResult OUT SYS_REFCURSOR 
)
IS
BEGIN
    OPEN pResult FOR
        SELECT name, sal, bonus FROM emp;
END;
/

 

-- 확인용 프로시저

CREATE OR REPLACE PROCEDURE pEmpSelectResult
IS
    vName emp.name%TYPE;
    vSal emp.sal%TYPE;
    vBonus emp.bonus%TYPE;
    vResult SYS_REFCURSOR;
BEGIN
    pEmpSelectList( vResult );
    LOOP 
        FETCH vResult INTO vName, vSal, vBonus;
        EXIT WHEN vResult%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(vName || ' : ' || vSal || ' : ' || vBonus);
    END LOOP;
END;
/

 

-- 결과 확인

EXEC pEmpSelectResult;