Oracle PL/SQL 3 - 커서

정미·2023년 1월 27일
0

정의

Oracle 서버에서 할당한 전용 메모리 영역에 대한 포인터

  • 질의의 결과로 얻어진 여러 행이 저장된 메모리 상의 위치
  • SELECT문 결과 집합을 처리하는 데 사용됨.

종류

암시적 커서 Implicit Cursor

오라클이나 PL/SQL 실행 메커니즘에 의해 처리되는 SQL 문의 결과에 대한 익명의 주소

특징

  • 오라클 서버에서 SQL 문을 처리하기 위해 내부적으로 생성, 관리한다.
  • 오라클 DB에서 실행되는 모든 SQL문은 암시적 커서가 생성되며, 커서 속성을 사용할 수 있다.
    - 대상: 모든 DML, PL/SQL SELECT문
    - SQL 커서 속성을 사용하면 SQL의 결과를 테스트할 수 있다.
  • SQL 문이 실행되는 순간 자동으로 OPEN과 CLOSE를 실행한다.

속성

  1. SQL%FOUND
    • 반환된 행이 1개 이상일 경우 TRUE
  2. SQL%NOTFOUND
    • 반환된 행이 없을 경우 TRUE
  3. SQL%ISOOPEN
    • 암시적 커서가 열려 있는지의 여부 검색
    • PL/SQL은 실행 후에 바로 암시적 커서를 닫기 때문에 항상 FALSE
  4. SQL%ROWCOUNT
    • 반환된 총 행의 개수
    • 가장 최근 수행된 SQL 문에 의해 영향을 받은 행의 개수

예시

SET SERVEROUTPUT ON;
/

BEGIN
	DELETE FROM EMPLOYEE
	WHERE DEPT_NO = 10;

	DBMS_OUTPUT.PUT_LINE('처리 건수: ' || TO_CHAR(SQL%ROWCOUNT) || '건')
END;
/

/* 결과
처리 건수: NN건
PL/SQL 처리가 정상적으로 완료되었습니다.
*/
  • SET SERVEROUTPUT ON;
    - 기본적으로 PL/SQL은 결과를 보여주지 않는다.
    - 결과를 출력하고 싶을 때의 설정

명시적 커서 Explicit cursor

프로그래머에 의해 선언되고 이름을 가진 커서

속성

  1. CURSOR_NAME%FOUND
    • FETCH한 데이터가 행을 반환할 경우 TRUE
  2. CURSOR_NAME%NOTFOUND
    • FETCH한 데이터가 행을 반환하지 않을 경우 TRUE
    • LOOP 종료 시점 찾을 때 사용
  3. CURSOR_NAME%ISOOPEN
    • 커서가 열려 있을 경우 TRUE
  4. CURSOR_NAME%ROWCOUNT
    • 현재까지 반환된 총 행의 개수

문법

DECLARE
	CURSOR CURSOR_NAME IS SELECT;
BEGIN
	OPEN CURSOR_NAME;
	FETCH CURSOR_NAME INTO VARIABLE1, VARIABLE2, ..;
	CLOSE CURSOR_NAME;
END;
  1. DECLARE
    • 이름이 있는 SQL 영역을 생성
  2. OPEN
    • 커서 열기
    • 결과 행 집합 식별
    • 커서 안의 검색이 실행된다.
    • 데이터행을 추출하지 못해도 에러가 발생하지 않는다.
  3. FETCH
    • 커서 패치
    • 현재 데이터 행을 OUTPUT 변수에 반환한다.
    • 현재 행이 없을 때까지 수행 가능
    • 커서의 SELECT 문의 컬럼과 OUTPUT 변수의 수, 데이터 타입이 동일해야 한다.
    • 커서는 한 줄씩 데이터를 패치한다.
  4. CLOSE
    • 커서 닫기
    • 결과 행 집합 해제
    • 사용을 마친 커서는 반드시 닫아주어야 한다.
    • 필요 시 커서를 다시 열 수 있다.
    • 커서를 닫은 상태에서 FETCH 불가능

예시

  1. 선언부에서 커서 정의 및 할당
SET SERVEROUTPUT ON;

DECLARE
	CURSOR EMPLOYEE_CURSOR
	IS
	SELECT * FROM EMPLOYEE WHERE DEPT_NOT = 10;

	V_EMPLOYEE EMPLOYEE%ROWTYPE;  -- 변수
BEGIN
	OPEN EMPLOYEE_CURSOR;

	LOOP
	FETCH EMPLOYEE_CURSOR INTO V_EMPLOYEE;  -- 하나씩 변수에 넣기
	EXIT WHEN EMPLOYEE_CURSOR%NOTFOUND;  -- 더 이상 없으면 끝
		DBMS_OUTPUT.PUT_LINE(V_EMPLOYEE.EMPLOYEE_NO || ' ' || V_EMPLOYEE.NAME);
	END LOOP;
	
	CLOSE EMPLOYEE_CURSOR;
END;
  1. 선언부에서 커서 정의만, 실행부의 FOR문으로 할당
SET SERVEROUTPUT ON;

DECLARE
	ID_LIST SYS_REFCURSOR;  -- 커서 정의
	I_ID VARCHAR2(100);  -- 변수
BEGIN
	OPEN ID_LIST;

	FOR
		SELECT USER_ID FROM USERS WHERE condition;

	LOOP
	FETCH ID_LIST INTO I_ID;
	EXIT WHEN ID_LIST%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(I_ID);
	END LOOP;
	
	CLOSE ID_LIST;
END;
  1. LOOP로 커서 FOREACH 접근
SET SERVEROUTPUT ON;

DECLARE
	CURSOR ID_LIST
	IS
	SELECT 'JM' AS USER_ID FROM DUAL;
BEGIN
	FOR
		TEST_CURSOR IN ID_LIST
	LOOP
		DBMS_OUTPUT.PUT_LINE(TEST_CURSOR.USER_ID);
	END LOOP;
END;

EXPLICIT CURSOR FOR LOOP

  • 서브쿼리를 사용하는 방식
  • CURSOR를 선언할 필요가 없다.
  • FOR LOOP가 자동적으로 커서를 OPEN, 행이 없을 때까지 FETCH, CLOSE 해준다.
  • ROWTYPE 변수 선언 필요 x
    - 암시적으로 선언된다.
    - FOR LOOP 안에서만 사용 가능하다.
  • 내부적으로 처리되는 데이터 양, I/O 측면에서 훨씬 효율적이다.
-- 예시 3번을 명시적 커서 FOR LOOP 방식으로 변경
SET SERVEROUTPUT ON;

BEGIN
	FOR ID_LIST IN
		(SELECT 'JM' AS USER_ID FROM DUAL)
	LOOP
		DBMS_OUTPUT.PUT_LINE(ID_LIST.USER_ID);
	END LOOP;
END;

참조

0개의 댓글