[PL/SQL] CURSOR example (open, fetch, exit when, close) (fetch, for) (with parameter)

PL/SQL에서 CURSOR는 데이터를 효율적으로 조회하고 처리할 수 있는 기능을 제공해요. 특히 데이터베이스에서 여러 행을 반복 처리할 때 커서 사용이 유용합니다. 커서 사용법을 잘 이해하면 더 효율적이고 깔끔한 코드를 작성할 수 있는데요, PL/SQL에서 커서를 사용하는 다양한 방법과 예제를 소개해보겠습니다. 😊

기본 커서, FOR 문을 이용한 커서, 파라미터화된 커서까지 다양한 방법을 통해 PL/SQL의 데이터 처리 방법에 대해 알아보아요.

커서(Cursor)란 무엇인가요?

커서는 SQL 쿼리의 결과를 행 단위로 처리할 수 있는 메커니즘입니다. 데이터를 가져와 처리해야 하는 로직이 있는 경우, 커서를 사용하면 한 번에 하나의 행을 가져와 필요한 작업을 수행할 수 있습니다.

이렇게 하면 대량의 데이터를 한꺼번에 로드하지 않아도 되므로 메모리 효율성이 크게 향상됩니다.

커서의 주요 구성 요소

커서는 다음과 같은 단계로 구성됩니다.

선언(DECLARE): 커서를 정의하는 단계입니다. 어떤 데이터를 가져올지 쿼리를 작성합니다.
열기(OPEN): 커서를 열어 데이터를 가져오기 준비합니다.
가져오기(FETCH): 한 번에 하나의 행을 가져옵니다.
닫기(CLOSE): 커서를 닫아 메모리를 해제합니다.

커서 사용 예제

아래 예제는 커서를 사용하여 직원 정보를 출력하는 기본적인 구조입니다.

커서 열고 가져오기(OPEN, FETCH, CLOSE)

먼저 커서를 선언하고, 열고, 데이터를 하나씩 가져오는 방식입니다.

DECLARE
   r_emp   emp%ROWTYPE;

   CURSOR emp_cur
   IS
      SELECT empno, ename FROM emp;
BEGIN
   OPEN emp_cur;

   LOOP
      FETCH emp_cur
      INTO r_emp.empno, r_emp.ename;

      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (r_emp.empno || ' ' || r_emp.ename);
   END LOOP;

   CLOSE emp_cur;
END;
/

위 코드에서는 emp_cur라는 커서를 선언하여 emp 테이블에서 직원 번호(empno)와 이름(ename)을 선택합니다. 커서를 열고(OPEN), 반복문(LOOP)을 통해 하나씩 데이터를 가져와 출력합니다.

FETCH를 사용할 때, 더 이상 가져올 데이터가 없을 경우 EXIT WHEN emp_cur%NOTFOUND 조건을 통해 루프를 빠져나오게 됩니다. 마지막에는 CLOSE를 통해 커서를 닫아줍니다.

커서 동적 쿼리

커서를 동적쿼리로 열어보겠습니다.

OPEN cur_name for sql;
LOOP
FETCH cur_name INTO row_type
EXIT WHEN cur_name%NOTFOUND;
ENDLOOP;
CLOSE cur_name;

FOR 문을 활용한 커서 처리

PL/SQL에서는 FOR 루프를 사용해 커서를 더 간단하게 처리할 수 있습니다. FETCH와 EXIT 문을 명시적으로 작성하지 않아도 자동으로 행을 가져오고 반복을 종료해 줍니다.

DECLARE
   CURSOR emp_cur
   IS
      SELECT empno, ename FROM emp;
BEGIN
   FOR emp_rec IN emp_cur
   LOOP
      DBMS_OUTPUT.put_line (emp_rec.empno || ' ' || emp_rec.ename);
   END LOOP;
END;
/

위 예제는 더 간결한 코드로, 커서를 FOR 문 안에서 자동으로 열고, 데이터가 없을 때 자동으로 닫힙니다. 따라서 FETCH, OPEN, CLOSE를 직접 쓸 필요가 없습니다.

아, 그리고 FOR 반복문과 커서를 함께 사용할 때는 CLOSE를 명시적으로 호출할 필요가 없어요. FOR 반복문이 자동으로 커서를 OPEN하고, 데이터 FETCH 작업을 진행한 뒤 반복문이 끝나면 커서를 CLOSE 하기 때문이에요.

FOR 반복문 자체가 커서의 시작부터 끝까지의 모든 관리를 자동으로 처리하니까, 별도의 CLOSE 호출을 하지 않아도 됩니다.

파라미터를 사용하는 커서

특정 조건에 따라 데이터를 필터링해야 할 때 파라미터화된 커서를 사용할 수 있습니다. 아래는 파라미터를 사용하는 커서 예제입니다.

SET SERVEROUTPUT ON

DECLARE
   CURSOR emp_cur (p_ename emp.ename%TYPE)
   IS
      SELECT empno, ename
        FROM emp
       WHERE ename = p_ename;
BEGIN
   FOR emp_rec IN emp_cur ('KING')
   LOOP
      DBMS_OUTPUT.PUT_LINE (emp_rec.empno || ' ' || emp_rec.ename);
   END LOOP;
END;
/

위 코드에서는 emp_cur 커서가 p_ename이라는 파라미터를 받고, ename 컬럼이 KING인 직원만 선택합니다. FOR 루프에서 커서를 실행할 때 emp_cur('KING') 형식으로 파라미터 값을 지정해 줍니다. 펑션이나 프로시저를 구성할 때에 파라미터를 지정하는 방향으로 만들어볼 수도 있겠지요?

Implicit Cursor (암시적, 묵시적, 암묵적)

BEGIN
   FOR emp_rec IN (SELECT * FROM emp)
   LOOP
      DBMS_OUTPUT.PUT_LINE (emp_rec.empno || ' ' || emp_rec.ename);
   END LOOP;
END;
/

동적 커서 사용하기 (커서와 동적 쿼리)

다음은 Oracle의 emp 테이블을 기준으로 동적 커서 예제를 만들어 보았습니다. 앞에서 설명한 명시적, 암시적 커서는 정적인 쿼리를 구성하는데요, 여기에서 소개하는 동적쿼리는 조건에 따라 유동적으로 쿼리를 구성할 수 있는 장점이 있습니다. 대신 쿼리에서 오류가 발생하더라도 문자열로 구성이 되기에 컴파일 단계에서 오류 여부를 확인할 수 없는 단점이 있어요. 그만큼 문장이 길어지는 경우, 유지보수 시 어려움이 발생할 수 있습니다.

DECLARE
    -- 커서와 변수 선언
    employee_cursor SYS_REFCURSOR;  -- REF CURSOR 타입의 커서 정의
    query_text VARCHAR2(1000);  -- 동적 쿼리 문자열 변수
    
    -- 매개변수 선언 (emp 테이블의 필드를 기준으로 설정)
    input_empno NUMBER := 7839;  -- 조회할 사원 번호 (예: 7839)
    input_deptno NUMBER := 10;   -- 조회할 부서 번호 (예: 10)

    -- 커서에서 가져올 데이터를 저장할 변수들
    fetched_empno NUMBER;
    fetched_ename VARCHAR2(100);
    fetched_job VARCHAR2(10);
    fetched_salary NUMBER;

BEGIN
    -- 동적 쿼리 정의
    query_text := 'SELECT EMPNO, ENAME, JOB, SAL 
                   FROM emp 
                   WHERE EMPNO = :empno
                   AND DEPTNO = :deptno';

    -- 커서를 열고 동적 쿼리에 매개변수를 바인딩
    OPEN employee_cursor FOR query_text
        USING input_empno, input_deptno;

    -- 커서에서 데이터를 하나씩 가져오기
    LOOP
        FETCH employee_cursor INTO fetched_empno, fetched_ename, fetched_job, fetched_salary;
        EXIT WHEN employee_cursor%NOTFOUND;
        
        -- 가져온 데이터를 출력 (또는 필요한 작업을 수행)
        DBMS_OUTPUT.PUT_LINE('사원번호: ' || fetched_empno);
        DBMS_OUTPUT.PUT_LINE('이름: ' || fetched_ename);
        DBMS_OUTPUT.PUT_LINE('직업: ' || fetched_job);
        DBMS_OUTPUT.PUT_LINE('급여: ' || fetched_salary);
    END LOOP;

    -- 커서 닫기
    CLOSE employee_cursor;

EXCEPTION
    WHEN OTHERS THEN
        -- 예외 처리 (오류 발생 시 출력 및 커서 닫기)
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        IF employee_cursor%ISOPEN THEN
            CLOSE employee_cursor;
        END IF;
END;
/

코드 설명

커서 선언: SYS_REFCURSOR를 사용하여 참조 커서를 정의합니다.
동적 쿼리 작성: l_v_TEMP_QRY에 SQL 쿼리를 문자열로 작성합니다. 여기서 :EMPNO와 :DEPTNO는 바인딩될 자리 표시자입니다.
커서 열기: OPEN ... FOR ... USING 구문을 사용하여 커서를 열고 매개변수를 바인딩합니다.
데이터 가져오기: LOOP와 FETCH를 사용하여 각 행을 하나씩 가져오며, %NOTFOUND를 통해 더 이상 가져올 데이터가 없을 때 루프를 종료합니다.
예외 처리: EXCEPTION 블록에서 오류가 발생할 경우 메시지를 출력하고, 커서가 열려 있다면 닫습니다.