[오라클] CURSOR 개념과 사용법 OPEN CLOSE LOOP

CURSOR

• 커서는 ORACLE 서버에서 할당한 전용 메모리 영역에 대한 포인터이다.
  질의의 결과로 얻어진 여러 행이 저장된 메모리상에 위치한다.
  커서는 SELECT문의 결과 집합을 처리하는데 사용된다.

커서는 두가지로 나누어 진다.

• 묵시적 커서(암시적 커서) (Implicit Cursor)
• 명시적 커서

묵시적 커서(암시적 커서) (Implicit Cursor)

• 오라클 내부에서 SQL문장이 실행될 때 마다 자동으로 생성되어 실행되는 커서
• SQL문이 실행되는 순간 자동으로 OPEN과 CLOSE를 실행한다.

묵시적 커서의 속성

SQL%FOUND
: 해당 SQL문에 의해 반환된 총 행수가 1개 이상일 경우 TRUE (Boolean)

SQL%NOTFOUND
: 해당 SQL문에 의해 반환된 총 행수가 없을경우 TRUE (Boolean)

SQL%ROWCOUNT
: 해당 SQL문에 의해 반환된 총 행수, 가장 최근 수행된 SQL 문에 의해 영항을 받은 행의 갯수 (정수)

 

예제

	DECLARE
	    BEGIN
	    DELETE FROM emp WHERE DEPTNO = 10;
	    DBMS_OUTPUT.PUT_LINE('처리 건수 : ' || TO_CHAR(SQL%ROWCOUNT)|| '건');
	END;
    
-- 결과
-- 처리 건수 : 21건
-- PL/SQL 처리가 정상적으로 완료되었습니다.

명시적 커서

• 사용자가 직접 정의해서 사용하는 커서

명시적 커서 속성

커서이름%ROWCOUNT
: 현재까지 반환된 모든 행의 수를 출력

커서이름%FOUND
: FETCH한 데이터가 행을 반환하면 TRUE

커서이름%NOTFOUND
: FETCH한 데이터가 행을 반환하지 않으면 TRUE (LOOP 종료 시점을 찾는다)

커서이름%ISOPEN
: 커서가 OPEN 되어 있으면 TRUE

커서를 만드는 단계

1. 커서 선언 (DECLARE)
2. 커서 오픈 (OPEN)
3. 패치 단계에서 커서 사용 (FETCH)
4. 커서 클로즈 (CLOSE)

문법

-- 문법
DECLARE
	CURSOR [커서이름] IS [SELECT 구문];
BEGIN
	OPEN [커서이름];
	FETCH [커서이름] INTO [로컬변수];  -- FETCH cursor_name INTO var1, var2;
	CLOSE [커서이름];				 -- CLOSE cursor_name
END;

예제

-- 예제1
DECLARE
    ID_LIST SYS_REFCURSOR; -- 커서정의
    I_ID VARCHAR2(100);    -- 변수정의
BEGIN
    OPEN ID_LIST;
    FOR
        SELECT USER_ID FROM MY_USER WHERE 조건;
    LOOP -- 반복
    FETCH ID_LIST INTO I_ID;        -- 하나씩 변수에 넣기
    EXIT  WHEN ID_LIST%NOTFOUND;    -- 더이상 없으면 끝내기
        DMBS_OUTPUT.PUT_LINE(I_ID); -- 출력
    END LOOP;
    CLOSE ID_LIST;
END;


-- 예제 2
DECLARE
    CURSOR ID_LIST IS
    SELECT 'TEST' AS USER_ID
    FROM DUAL;
BEGIN
    FOR TEST_CURSOR IN ID_LIST
    LOOP
        DBMS_OUTPUT.putline(TEST_CURSOR.USER_ID);
    END LOOP;
END;

 

활용

명시적 커서 FOR LOOP

서브쿼리를 활용하여 CURSOR FOR LOOP를 사용하면 CURSOR 선언이 필요없어짐
커서의 OPEN, FETCH, CLOSE가 자동으로 발생하므로 따로 기술할 필요가 없고, 레코드 이름도 자동 선언됨.

 

예제

-- 예제 2를 변경한 것
DECLARE
BEGIN
    FOR ID_LIST IN
    (
        SELECT 'TEST' AS USER_ID
        FROM DUAL
    )
    LOOP
    DBMS_OUTPUT.putline(ID_LIST.USER_ID);
END LOOP;
END;

SQL OPEN FOR