[PL/SQL] 암시적 커서, 명시적 커서 / 커서 변수와 커서 표현식 / 다중행 서브쿼리 활용 예시

EUN JY·2022년 8월 25일
PL/SQLoracle
1

Database

목록 보기
12/21

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;
/
/* 결과
DELETE 건수 : 1
패치 로우 수 1개 이상
커서 닫힘 */

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
    • 명명된 SQL 영역을 생성
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; -- 더이상 없으면 EXIT
        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할 필요가 없음 (암시적으로 선언되기 때문)
-- for문에 레코드 선언(name_rec)
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;
/

-- 명시적 커서 FOR LOOP
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. 커서 변수 사용

  • CLOSE는 직접할 필요가 없음
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 문
    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
-- SYS_REFCURSOR
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;
/* ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다. */

SELECT
    ...
    , CURSOR (
        SELECT STORE_NM FROM STORE_INFO WHERE OWNER_NUM = USER_NUM
    ) AS STORE_NM
    ...
FROM USER_INFO;
/* 결과
서강준	{<STORE_NM=교보문고 광화문점>,}
송강	{}
로운	{<STORE_NM=신세계 영등포점>,<STORE_NM=63빌딩 아쿠아리움>,} */

1-4-4. 다중행 출력

  • 다중행 서브쿼리를 가리키는 커서를 변수에 담아 각각의 행으로 출력
    • JAVA의 MAP 컬렉션과 유사한 형태
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;
/
/* 결과
회원명 : 차은우
  상점명 : 카페노티드 제주애월
회원명 : 서강준
  상점명 : 런던베이글뮤지엄 도산점
회원명 : 강태오
  상점명 : 카페 레이어드 연남점
회원명 : 지창욱
회원명 : 이준혁
  상점명 : 카페 쿠운
  상점명 : 태시트 TACIT
  상점명 : 귤꽃다락
회원명 : 임시완
*/
profile
EUN JY
개린이
이전 포스트

[PL/SQL] 시스템 예외(Exception) 처리 / 사용자 정의 예외 처리

다음 포스트

[Oracle] 트랜잭션과 Commit, Rollback, Savepoint

0개의 댓글