PL/SQL 기본_3 - 커서

PL/SQL의 꽃이라 불리는 "커서"에 대해서 알아보도록 하자.

 

커서(CURSOR)의 정의

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

 

커서(CURSOR)의 종류

• 암시적 커서(Implicit Cursor)
  • 정의
    • 오라클 DB에서 실행되는 모든 SQL 문장은 암시적 커서가 생성되며, 커서 속성을 사용할 수 있다.
    • 모든 DML과 PL/SQL SELECT문에 대해 선언
    • 암시적 커서는 오라클이나 PL/SQL 실행 메커니즘에 의해 처리되는 SQL 문장이 처리되는 곳에 대한 익명의 주소
    • Oracle 서버에서 SQL문을 처리하기 위해 내부적으로 생성하고 관리
    • 암시적 커서는 SQL문이 실행되는 순간 자동으로 OPEN, CLOSE 실행
    • SQL 커서 속성을 사용하면 SQL문 결과 테스트 가능
  • 속성
    • SQL%FOUND : 해당 SQL문에 의해 반환된 총 행수가 1개 이상일 경우 TRUE(BOOLEAN)
    • SQL%NOTFOUND : 해당 SQL문에 의해 반환된 총 행수가 없을 경우 TRUE(BOOLEAN)
    • SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색(PL/SQL은 실행 후 바로 CLOSE)
    • SQL%ROWCOUNT : 해당 SQL문에 의해 반환된 총 행수, 가장 최근 수행된 SQL문에 의해 영향을 받은 행의 갯수(정수)
  • EX)

	SET SERVEROUTPUT ON;
	DECLARE
	    BEGIN
	    DELETE FROM emp WHERE DEPTNO = 10;
	    DBMS_OUTPUT.PUT_LINE('처리 건수 : ' || TO_CHAR(SQL%ROWCOUNT)|| '건');
	    END;

-- 결과

처리 건수 : 21건

PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

• 명시적 커서(Explicit Cursor)
  • 정의
    • 프로그래머에 의해 선언되어 이름이 있는 커서
  • 속성
    • %ROWCOUNT : 현재까지 반환된 모든 행의 수를 출력
    • %FOUND : FETCH한 데이터가 행을 반환하면 TRUE
    • %NOTFOUND : FETCH한 데이터가 행을 반환하지 않으면 TRUE(LOOP를 종료할 시점을 찾을 때 사용)
    • %ISOPEN : 커서가 OPEN 되어 있으면 TRUE
  • 문법
    • DECLARE를 통해서 명명된 SQL 영역 생성
    • OPEN을 이용하여 결과 행 집합 식별
    • FETCH를 통해 현재 행을 변수에 로드(현재 행이 없을 때까지 수행 가능)
    • CLOSE를 통해 결과 행 집합 해제

	DECLARE
		CURSOR [커서이름] IS [SELECT 구문];
	BEGIN
		OPEN [커서이름];
		FETCH [커서이름] INTO [로컬변수];
		CLOSE [커서이름];
	END;

 

• OPEN(커서 열기)
  • OPEN 문을 사용하여 커서를 연다.
  • 커서 안의 검색이 실행되며 아무런 데이터 행을 추출하지 못해도 에러가 발생하지 않는다.
• FETCH(커서 페치)
  • 현재 데이터 행을 OUTPUT 변수에 반환
  • 커서의 SELECT 문의 컬럼 수와 OUTPUT 변수의 수가 동일해야 한다.
  • 커서 컬럼의 변수 타입과 OUTPUT 변수의 데이터 타입 역시 동일해야 한다.
  • 커서는 한 라인씩 데이터를 FETCH
  • 문법 : FETCH cursor_name INTO variable1, variable2;
• CLOSE(커서 닫기)
  • 사용을 마친 커서는 반드시 닫아준다.
  • 필요시 커서는 다시 열 수 있다.
  • 커서를 닫은 상태에서 FETCH는 불가능하다.
  • 문법 : CLOSE cursor_name;
• EX 1) 커서 사용 예제 1

	SET SERVEROUTPUT ON;
	DECLARE
	    CURSOR emp_cur -- 커서정의
	    IS
	    SELECT * FROM emp WHERE DEPTNO = 10;
	    emp_rec emp%ROWTYPE; -- 변수정의
	BEGIN
	    OPEN emp_cur;
	    LOOP -- 반복
	    FETCH emp_cur INTO emp_rec; -- 하나씩 변수에 넣기
	    EXIT  WHEN emp_cur%NOTFOUND; -- 더이상 없으면 끝내기
	        DMBS_OUTPUT.PUT_LINE(emp_rec.empno || ' ' || emp_rec.name); -- 출력
	    END LOOP;
	    CLOSE emp_cur;
	END;

 

• EX 2) 커서 사용 예제 2

	SET SERVEROUTPUT ON;
	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;

 

• EX 3) 커서 사용 예제 3

	SET SERVEROUTPUT ON;
	DECLARE
	    CURSOR ID_LIST IS
	    SELECT 'GOD' 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를 선언할 필요도 없어진다.
  • 명시적 커서 FOR LOOP를 사용하면 FOR LOOP가 자동적으로 커서를 OPEN, 행이 없을 때까지 FETCH하고, CLOSE
  • ROWTYPE에 해당하는 변수를 따로 DECLARE할 필요가 없다. - 암시적으로 선언되기 때문
  • 이 암시적 카운터는 FOR LOOP에서만 사용 가능
  • 이 커서의 데이터 타입(컬럽 데이터 타입의 집합)도 %ROWTYPE 앞으로 이용 가능
• EX 3) 커서 사용 예제 3을 "명시적 커서 FOR LOOP" 방식으로 변경한 예

	SET SERVEROUTPUT ON;
	DECLARE
	BEGIN
	    FOR ID_LIST IN 
	    (
	        SELECT 'GOD' AS USER_ID FROM DUAL
	    )
	    LOOP
	    DBMS_OUTPUT.putline(ID_LIST.USER_ID);
	END LOOP;
	END;

 

※ CURSOR FOR LOOP은 내부적으로 처리되는 데이터의 양, I/O 측면에서 훨씬 효율적, 가급적 사용 권장