DATABASE/SQL, PL-SQL

Cursor(커서) - 1

devstep88 2021. 2. 17. 23:06

SELECT문이건 DML문이건 쿼리가 실행되려면 DB 서버에 의해 파싱되어 실행 가능한 구조로 SGA(System Global Area)에 저장되어야 한다. 커서는 DB 서버의 SGA에 저장되어 있는 특정 쿼리를 실행하기 위한 정보를 저장하는 PGA(Program Global Area) 내부의 Private SQL Area에 대한 포인터로 클라이언트 메모리에 존재하는 자료구조이다.

사실은 파싱되어 SGA에 저장되어 있는 쿼리문에 관련된 데이터 구조인 Shared SQL Area도 커서라고 불린다.

여기서 말하는 커서는 클라이언트 메모리에 존재하는 자료구조이다.

 

PL/SQL에서 SELECT, INSERT, UPDATE, DELETE, MERGE와 같은 DML 쿼리나 CREATE/ALTER/DROP과 같은 DDL문을 실행하기 위해서는 반드시 커서를 생성한 후에 이를 사용하여 쿼리를 실행하는 구조로 되어 있다.

 

묵시적 커서와 명시적 커서

정적 SQL에서 커서의 유형은 두가지인데 묵시적 커서(implicit cursor)와 명시적 커서(explicit cursor)이다.

두 유형의 가장 큰 차이점은 묵시적 커서는 DECLARE절에 커서를 선언하지 않은 채로 커서를 사용하고 명시적 커서는 'CURSOR' 키워드를 사용하여 커서를 선언한 후에 사용한다는 것이다.

비교 항목 묵시적 커서 명시적 커서
커서 선언 없음 있음
사용 시 복잡도 단순 복잡
커서 제어 불가능 가능
유연성 낮음 높음
사용 가능한 SQL 유형 SELECT, INSERT, UPDATE, DELETE, MERGE SELECT
커서 속성 참조 SQL%커서속성 커서명%커서속성
전형적인 사용방법 즉시 실행 OPEN, FETCH, CLOSE

묵시적 커서는 명시적 커서에 비해 사용방법이 훨씬 단순하다. 그러나 묵시적 커서는 SQL문이 PL/SQL 프로그램에 나타나는 즉시 실행되므로 프로그램에서 이를 세밀하게 제어하는 것이 불가능하다. 명시적 커서에서는 커서를 OPEN한 후 여러 가지 방법으로 사용할 수 있고 사용자에 의한 최적화가 가능하다. 제어가 가능하다는 것은 유연성이 높다는 장점과 사용이 복잡하다는 단점이 동시에 존재한다는 것을 의미한다. 추가로, SELECT가 아닌 DML은 묵시적 커서에서만 사용 가능하다.

묵시적 커서

DECLARE
	v_name emp.ename%TYPE;
BEGIN
	--묵시적 커서
    SELECT ename
      INTO v_name
      FROM emp
     WHERE empno = 7788;
     
     DBMS_OUTPUT_PUT_LINE('ENAME = '||v_name);
END;

 

명시적 커서

DECLARE절에 CURSOR 선언을 가지는 커서로 SELECT문에 대해서만 사용 가능하다.

명시적 커서는 보통 한 개보다 많은 결과 로우를 반환할 때 사용된다.

DECLARE절에 선언된 명시적 커서는 전형적으로 두 가지 방식으로 사용된다.

  • OPEN문을 사용하여 커서를 열고 FETCH문을 사용하여 결과를 추출하고 CLOSE문을 사용하여 커서를 닫는다.
  • FOR LOOP문에서 사용한다.
DECLARE
	v_name emp.ename%TYPE;
    
	--명시적으로 커서 선언
    CURSOR ename_cursor IS
    SELECT ename
      FROM emp
     WHERE empno = 7788;
BEGIN
	-- 커서를 OPEN한다.
    OPEN ename_cursor;
    
    -- SELECT 결과를 FETCH한다.
    FETCH ename_cursor
     INTO v_name;
     
     DBMS_OUTPUT_PUT_LINE('ENAME = '||v_name);
     
     -- 커서를 CLOSE한다.
     CLOSE ename_cursor;
END;
-- LOOP문을 사용하여 여러 건을 FETCH
DECLARE
	v_empno NUMBER
	v_ename emp.ename%TYPE;
    
	--명시적 커서 선언
    CURSOR ename_cursor IS
    SELECT ename
      FROM emp
     ORDER BY empno;
BEGIN
	-- 커서를 OPEN한다.
    OPEN ename_cursor;
    
    -- SELECT 결과를 FETCH한다.
    LOOP
    	FETCH ename_cursor INTO v_empno, v_ename;
     	EXIT WHEN ename_cursor%NOTFOUND;
     DBMS_OUTPUT_PUT_LINE('EMPNO = '|| v_empno || ' ENAME = '||v_ename);
     
     -- 커서를 CLOSE한다.
     CLOSE ename_cursor;
END;
-- BULK COLLECT를 사용하여 여러 건을 한번에 FETCH
DECLARE
	TYPE empno_arr IS TABLE OF NUMBER;
	TYPE ename_arr IS TABLE OF emp.ename%TYPE;
    
    v_empno empno_arr;
    v_ename ename_arr;
    
	--명시적 커서 선언
    CURSOR ename_cursor IS
    SELECT empno, ename
      FROM emp;
BEGIN
	-- 커서를 OPEN한다.
    OPEN ename_cursor;
    
    -- SELECT 결과를 FETCH한다.
    	FETCH ename_cursor BULK COLLECT INTO v_empno, v_ename;
     	
     DBMS_OUTPUT_PUT_LINE('사원수 = '|| v_empno.COUNT);
     
     -- 커서를 CLOSE한다.
     CLOSE ename_cursor;
END;

커서 FOR LOOP

여러 건의 결과를 가지는 SELECT문의 처리 시 묵시적 커서와 명시적 커서에 공통적으로 사용할 수 있는 기법이 커서 FOR LOOP다. 묵시적 커서를 사용하는 경우가 일반적인데 별도의 커서 선언없이 커서 FOR LOOP를 사용할 수 있어 프로그램 작성이 간단해진다. 커서 FOR LOOP를 사용하면 쿼리 결과의 활용도 매우 단순해진다.

REM emp와 동일한 구조의 테이블 emp2 생성
CREATE TABLE emp2 AS SELECT * FROM emp WHERE ROWNUM = 0;

BEGIN
	FOR c IN (SELECT * FROM emp)  -- 커서 FOR LOOP를 사용하여 테이블 복사
    LOOP
    	INSERT INTO emp2 VALUES c;
    END LOOP;
END;