1. 커서
- Oracle 서버에서 할당한 전용 메모리 영역에 대한 포인터
- SQL문을 처리한 결과 집합을 가리키는 포인터
- 질의 결과로 얻어진 여러 행이 저장된 메모리 상의 위치
- Oracle 서버 프로세스 내부의 Private SQL Area 라는 메모리 영역에 결과 집합이 저장됨
- Private SQL Area : 특정 쿼리에 대한 결과를 저장하고 캐싱하는 역할
- 한 세션 안에서 같은 쿼리를 반복 호출하면 저장되어 있는 결과 집합을 반환 > 자원 낭비 최소화
- SELECT문의 결과 집합을 처리하는데 사용
1-1. 암시적 커서(Implict Cursor)
- 내부에서 자동으로 생성되어 사용
- Oracle DB에서 실행되는 모든 SQL문장은 암시적 커서가 생성되며, 커서 속성을 사용 가능
- Oracle 서버에서 SQL문을 처리하기 위해 내부적으로 생성 및 관리함
- 모든 DML과 PL/SQL SELECT문에 대해 선언됨
- PL/SQL 블록 안에서 실행하는 SQL 문장 실행 시 자동으로 만들어져 생성
- SQL문이 실행되는 순간 자동으로 OPEN, CLOSE를 실행
- SQL 커서 속성을 사용하면 SQL문의 결과를 테스트할 수 있음
1-1-1. 암시적 커서 속성
SQL%FOUND
: 해당 SQL문에 의해 반환된 행수가 1개 이상이면 TRUE
SQL%NOTFOUND
: 해당 SQL문에 의해 반환된 행수가 없으면 TRUE
SQL%ISOPEN
: 암시적 커서가 열려있는지 여부 확인, 항상 FALSE (PL/SQL은 실행 후 바로 묵시적 커서를 닫기 때문)
SQL%ROWCOUNT
: 해당 SQL문에 의해 반환된 총 행수, 가장 최근 수행된 SQL문에 의해 영향 받은 행수
1-1-2. 암시적 커서 예시
DECLARE
v_user_num NUMBER := 8;
v_cnt NUMBER;
BEGIN
DELETE FROM USER_INFO WHERE USER_NUM > v_user_num;
dbms_output.put_line('DELETE 건수 : ' || SQL%ROWCOUNT);
IF SQL%FOUND THEN dbms_output.put_line('패치 로우 수 1개 이상');
ELSE dbms_output.put_line('패치 로우 수 0개');
END IF;
IF SQL%ISOPEN THEN dbms_output.put_line('커서 열림');
ELSE dbms_output.put_line('커서 닫힘');
END IF;
END;
/
1-2. 명시적 커서(Explict Cursor)
- 사용자가 직접 정의해서 사용하는 커서
- 결과 데이터 집합을 ROW 별로 참조해 작업해야 할 때 유용함
- PL/SQL에서 SELECT문 사용 시 INTO를 함께 사용해야 하며, 항상 단일행만 리턴 받아야 함
- 커서 사용 시 SQL 처리 결과 집합을 가져와 복수행에 대한 작업도 가능
1-2-1. 명시적 커서 속성
%FOUND
: FETCH한 데이터가 행을 반환하면 TRUE
%NOTFOUND
: FETCH한 데이터가 행을 반환하지 않으면 TRUE (LOOP 종료할 시점을 찾음)
%ISOPEN
: 커서가 OPEN되어 있으면 TRUE
%ROWCOUNT
: 현재까지 반환된 모든 행의 수
1-2-2. 명시적 커서 문법
- 커서 열기
OPEN
- 결과 행 집합을 식별
- 커서 안의 검색이 실행되며 아무런 데이터 행을 추출하지 못해도 에러가 발생하지 않음
- 커서 패치
FETCH
- 현재 행을 변수에 로드(현재 행이 없을 때까지 수행할 수 있음)
- 현재 데이터 행을 OUTPUT 변수에 반환
- 커서의 SELECT문의 컬럼과 OUTPUT 변수의 타입, 개수가 동일해야 함
- 커서는 한 라인씩 데이터를 FETCH함
- 문법 :
FETCH cursor_name INTO variable1, variable2;
- 커서 닫기
CLOSE
- 결과 행 집합을 해제
- 사용을 마친 커서는 반드시 닫아주어야 함
- 필요 시 커서를 다시 열 수 있음
- 커서를 닫은 상태에서 FETCH 불가능
- 문법 :
CLOSE cursor_name;
DECLARE
DECLARE
CURSOR [커서명] IS [SELECT 구문];
BEGIN
OPEN [커서명];
FETCH [커서명] INTO [로컬변수];
CLOSE [커서명];
END;
/
1-2-3. 명시적 커서 예시
DECLARE
v_num NUMBER := 5;
CURSOR userCursor
IS
SELECT * FROM USER_INFO
WHERE USER_NUM < v_num;
userData USER_INFO%ROWTYPE;
BEGIN
OPEN userCursor;
LOOP
FETCH userCursor INTO userData;
EXIT WHEN userCursor%NOTFOUND;
dbms_output.put_line(userData.USER_NUM || ' ' || userData.USER_NM);
END LOOP;
CLOSE userCursor;
END;
/
- 위와 같이 커서를 선언하여 사용하는 방법, 아래와 같이 CURSOR FOR LOOP를 사용하는 방법이 있음
- 서브 쿼리를 활용하여
CURSOR FOR LOOP
사용 > CURSOR를 선언하지 않아도 됨
FOR LOOP
가 자동으로 커서를 OPEN, CLOSE 해줌
- 행이 없을 때까지 FETCH 또한 자동으로 가능
- ROWTYPE에 해당하는 변수를 따로 DECLARE할 필요가 없음 (암시적으로 선언되기 때문)
DECLARE
CURSOR name_list IS
SELECT USER_NM FROM USER_INFO;
BEGIN
FOR name_rec IN name_list
LOOP
dbms_output.put_line(name_rec.USER_NM);
END LOOP;
END;
/
DECLARE
BEGIN
FOR name_list IN
( SELECT USER_NM FROM USER_INFO )
LOOP
dbms_output.put_line(name_list.USER_NM);
END LOOP;
END;
/
- CURSOR FOR LOOP은 내부적으로 처리되는 데이터의 양, I/O 측면에서 보다 효율적이므로 권장됨
1-3. 커서 변수
- 변수의 특징이 있는 커서
- 한 개 이상의 쿼리를 연결해 사용할 수 있음 (재사용)
- 커서 변수를 함수나 프로시저의 매개변수로 전달 가능
- 커서 속성 사용 (v_cursor%FOUND, ...)
- 커서는 블록 안에서만 사용 가능하며, 한 번 선언 후 변경 불가
- 블록이 사라질 때 커서도 사라지며, 다른 블록에서 사용 불가
- 커서 변수로 커서 선언하면, 다른 블록에서도 사용 가능하며 재사용이 가능해짐
1-3-1. 커서 변수 선언
RETURN [반환 타입]
: 반환하는 결과 집합 %ROWTYPE
- 커서가 반환하는 타입은 한 개 이상의 컬럼이 있는 레코드 타입
- 반환 타입이 있으면 강한 커서 타입 / 생략하면 약한 커서 타입
- SYS_REFCURSOR : 오라클 빌트인 커서 타입
- 별도로 커서 타입 선언할 필요 없음 (약한 커서 타입)
TYPE [커서 타입명] IS REF CURSOR [RETURN 반환 타입];
[커서 변수명] [커서 타입명];
[커서 타입명] SYS_REFCURSOR;
DECLARE
TYPE userCurType IS REF CURSOR RETURN USER_INFO%ROWTYPE;
testCursor SYS_REFCURSOR;
BEGIN
(생략)
END
;
/
1-3-2. 커서 변수 사용
OPEN [커서 변수명] FOR SELECT ~ ;
- 'USER_INFO' 테이블의 레코드 타입으로 커서를 받아 컬럼을 모두 레코드에 담고, 레코드 타입 변수로 안의 속성에 접근하여 값을 출력
DECLARE
TYPE userCurType IS REF CURSOR RETURN USER_INFO%ROWTYPE;
userCur userCurType;
userData USER_INFO%ROWTYPE;
BEGIN
OPEN userCur FOR SELECT * FROM USER_INFO WHERE USER_NUM < 5;
LOOP
FETCH userCur INTO userData;
dbms_output.put_line(userData.USER_NM || userData.USER_BIRTH);
EXIT WHEN userCur%NOTFOUND;
END LOOP;
END;
/
DECLARE
TYPE userCurType IS REF CURSOR;
userCur userCurType;
userData USER_INFO%ROWTYPE;
BEGIN
DECLARE
userCur SYS_REFCURSOR;
userData USER_INFO%ROWTYPE;
BEGIN
1-4. 커서 표현식
- SELECT문에서 컬럼 형태로 커서를 사용하는 것
CURSOR(서브 쿼리)
형태로 사용
- 아래와 같이 다중행을 리턴하는 서브 쿼리로 인해 오류 발생 시, CURSOR를 이용해 모든 행에 대한 결과 확인 가능
SELECT
USER_NUM
, USER_NM
, (
SELECT STORE_NM FROM STORE_INFO WHERE OWNER_NUM = USER_NUM
) AS STORE_NM
FROM USER_INFO;
SELECT
...
, CURSOR (
SELECT STORE_NM FROM STORE_INFO WHERE OWNER_NUM = USER_NUM
) AS STORE_NM
...
FROM USER_INFO;
1-4-4. 다중행 출력
- 다중행 서브쿼리를 가리키는 커서를 변수에 담아 각각의 행으로 출력
DECLARE
CURSOR user_cursor IS
SELECT
USER_NM
, CURSOR (SELECT STORE_NM FROM STORE_INFO WHERE OWNER_NUM = USER_NUM) AS STORE_NM
FROM USER_INFO;
v_user_name USER_INFO.USER_NM%TYPE;
store_name_cursor SYS_REFCURSOR;
v_store_name STORE_INFO.STORE_NM%TYPE;
BEGIN
OPEN user_cursor;
LOOP
FETCH user_cursor INTO v_user_name, store_name_cursor;
EXIT WHEN user_cursor%NOTFOUND;
dbms_output.put_line('회원명 : ' || v_user_name);
LOOP
FETCH store_name_cursor INTO v_store_name;
EXIT WHEN store_name_cursor%NOTFOUND;
dbms_output.put_line(' 상점명 : ' || v_store_name);
END LOOP;
END LOOP;
END;
/