OracleSQL - PL/SQL 커서(CURSOR)

오라클에서는 하나의 레코드가 아닌 여러 레코드로 구성된 작업 영역에서 SQL 문을 실행하고, 그 과정에서 발생한 정보를 저장하기 위해 커서(CURSOR)를 사용하며, 커서에는 암시적인 커서와 명시적인 커서가 있다. 

 

암시적 커서는 모든 SQL 문에 존재하며 SQL 문 실행 시 오직 하나의 행(ROW)만 출력하게 된다. 그러나 SQL문을 실행한 결과물(RESULT SET)이 여러 행(ROW)으로 구성된 경우 커서(CURSOR)를 명시적으로 선언해야 여러 행(ROW)을 다룰 수 있다. 커서를 이용하여 테이블 내에 여러 행에 접근 가능한 방법은 마치 JAVA에서 FOR EACH 구문과 비슷한 느낌이 든다. 

 

기본적인 형식 및 구조는 다음과 같다.

 

-- 커서 생성
CURSOR 커서이름
IS
SELECT 컬럼명1, 컬럼명2, ...
FROM 테이블이름;

-- 커서 오픈
OPEN 커서이름;

-- 커서 오픈 시 쏟아져나오는 데이터들을 반복문으로 처리
LOOP
     -- 한 행씩 받아다가 처리하는 행위 
     FETCH 커서이름 INTO 컬럼명1, 컬럼명2, ...;

     -- 커서에서 더 이상 데이터가 쏟아져 나오지 않는 상태 체크 (반복문 종료를 위함)
     EXIT WHEN 커서이름%NOTFOUND;

END LOOP;


-- 커서 클로즈
CLOSE 커서이름;

 

예제

이제 예제를 통해 커서를 어떻게 사용할 수 있는지 살펴보고 커서가 가지는 장점은 무엇인지 살펴보도록 하겠다.

 

사원들의 정보를 담고 있는 TBL_INSA 테이블이 있다고 가정하자. 이 테이블에서는 사원 번호, 이름, 전화번호, 직급, 연봉 등의 데이터가 들어간다. 

 

PL/SQL을 사용하여 특정 사원(단일 행) 데이터(이름, 전화번호)에 접근하는 방법은 다음과 같다.

 

-- 1001번 사원의 이름, 전화번호를 검색

DECLARE
     V_NAME     TBL_INSA.NAME%TYPE;
     V_TEL         TBL_INSA.TEL%TYPE;
BEGIN
     SELECT NAME, TEL INTO V_NAME, V_TEL
     FROM TBL_INSA
     WHERE NUM = 1001;

     DBMS_OUTPUT.PUT_LINE(V_NAME || ' -- ' || V_TEL);
END;
--==>> 홍길동 -- 011-2356-4528

하지만 여러 행(여러 사원)을 접근하려고 시도하면 어떻게 될까?

 

-- 이전 예와 비슷한 조건으로 다중 행 접근 
DECLARE
     V_NAME     TBL_INSA.NAME%TYPE;
     V_TEL         TBL_INSA.TEL%TYPE;
BEGIN
     SELECT NAME, TEL INTO V_NAME, V_TEL
     FROM TBL_INSA;

     DBMS_OUTPUT.PUT_LINE(V_NAME || ' -- ' || V_TEL);
END;

--==>>  에러 발생
--          (ORA-01422: exact fetch returns more than requested number of rows)

여러 행을 한꺼번에 처리하지 못하기 때문에 오라클에서 에러가 발생한다. 이 문제는 다음과 같이 반복문을 사용하면 해결이 가능하다. 

 

-- 다중 행 접근 - 반복문 활용 (커서 X)
DECLARE 
    V_NAME      TBL_INSA.NAME%TYPE;
    V_TEL          TBL_INSA.TEL%TYPE;

    V_NUM        TBL_INSA.NUM%TYPE := 1001;
BEGIN
    LOOP
          SELECT NAME, TEL INTO V_NAME, V_TEL
          FROM TBL_INSA
          WHERE NUM = V_NUM;

          DBMS_OUTPUT.PUT_LINE(V_NAME || ' -- ' || V_TEL);
          V_NUM := V_NUM + 1;
          
          EXIT WHEN V_NUM >= 1061;
    END LOOP;
END;

커서를 사용하지 않은 상태에서 이 반복문 작업은 여러 문제가 있다. 첫번째로는 시작과 끝을 테이블 데이터를 조회하여 미리 알아야 한다. TBL_INSA 테이블에서 시작이 1001이고 끝이 1061인 것을 사용자가 미리 확인한 후 작성해야 한다.

 

두번째 문제점은 반복문 내에서 사원번호(NUM)를 1씩 증가하면서 검색한다는 것이다. 만약 사원번호를 5씩 증가하면서 추가시켜줬거나 중간에 삭제된 사원들이 있는 경우, 1씩 증가하면서 처리하기 때문에 사원번호가 존재하지 않는 데이터를 검색하는 비효율적인 동작을 수행하게 될것이다.  

 

이제 커서를 사용하여 TBL_INSA의 여러 행을 접근해보도록 하자.

 

-- 다중 행 접근 - 반복문 활용 (커서 이용 후)
DECLARE 
     V_NAME    TBL_INSA.NAME%TYPE;
     V_TEL        TBL_INSA.TEL%TYPE;

     -- 커서 이용을 위한 커서변수 선언(-> 커서 정의)
     CURSOR CUR_INSA_SELECT
     IS
     SELECT NAME, TEL
     FROM TBL_INSA;

BEGIN 
     -- 커서 오픈
     OPEN CUR_INSA_SELECT;

     LOOP
         -- 한 행 씩 받아다가 처리
         FETCH CUR_INSA_SELECT INTO V_NAME, V_TEL;

         -- 커서에서 더 이상 데이터가 쏟아져 나오지 않는 상태면 반복문 종료
         EXIT WHEN CUR_INSA_SELECT%NOTFOUND;

         -- 출력
          DBMS_OUTPUT.PUT_LINE(V_NAME || ' -- ' || V_TEL);

     END LOOP; 

     -- 커서 클로즈
     CLOSE CUR_INSA_SELECT;
END;

커서를 사용하여 생긴 이점은 이제 짐작해볼 수 있을 것이다. 우선 앞서 봤던 NUM을 몰라도 모든 데이터를 출력할 수 있다. 그리고 NUM을 1씩 증가하지 않더라도 모든 데이터를 가져올 수 있다.