[불친절한 SQL 프로그래밍] CURSOR 표현식

♣ CURSOR(커서)란? 쿼리문에 의해서 반환되는 결과값들을 저장하는 메모리 공간.

SELECT 문을 통해 결과값들이 나올 때 이 결과들은 메모리 공간에 저장하게 되는데 이때 이 메모리 공간을 '커서'라고 한다.

♣ FETCH란? 커서에서 원하는 결과값을 추출하는 것.

SELECT의 결과가 하나의 행(단일 튜플)일 경우는 INTO 절을 이용해 변수에 저장할 수 있지만, 결과가 복수행(복수 튜플)일 경우는 INTO절로 이를 처리할 수가 없다. 이렇게 복수 행의 결과를 행 단위로 처리하기 위해서 사용하는 것이 CURSOR이다.

CURSOR 표현식은 서브 쿼리의 결과를 중첩 커서(nested cursor)로 반환한다. 중첩 커서는 PL/SQL의 REF CURSOR와 동일한 유형이다. 

 

스칼라 서브 쿼리의 결과가 중첩 커서를 통해 다중 열, 다중 행으로 반환된다.

 

예제를 위해 다음과 같이 테이블을 생성한다. 순서(seq)대로 금액(val)이나 비율(rat)을 할인하는 데이터가 입력된 테이블이다.

DROP TABLE t1 PURGE;
CREATE TABLE t1 (seq NUMBER, val NUMBER, rat NUMBER);

INSERT INTO t1 VALUES (1, 2000, NULL);
INSERT INTO t1 VALUES (2, NULL, 0.1);
INSERT INTO t1 VALUES (3, 3000, NULL);
INSERT INTO t1 VALUES (4, NULL, 0.2);
COMMIT;

다음과 같이 금액(i_val)을 할인하는 함수를 생성하자. 두 번째 매개변수가 SYS_REFCURSOR타입이다.

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

※ CURSOR FOR LOOP은 내부적으로 처리되는 데이터의 양, I/O 측면에서 훨씬 효율적이기 때문에, 가급적 이를 사용하는 것이 좋다.

 

다음은 사용자 함수를 사용한 쿼리다. CURSOR 표현식으로 인수를 입력했다. 20000의 할인된 금액이 반환되는 것을 확인할 수 있다.