효과적인 오라클 커서 사용법과 예제로 배우는 PL/SQL

1. 오라클 커서의 개념과 사용법

오라클 커서는 SQL문을 실행한 결과로 반환된 데이터를 처리하기 위한 포인터 형태의 객체입니다. 커서는 SQL문의 실행 결과를 가리키고, 이를 통해 데이터에 접근하고 조작할 수 있습니다.

커서는 크게 암시적 커서와 명시적 커서로 나뉘며, 명시적 커서는 PL/SQL 블록 내에서 개발자가 직접 정의하여 사용할 수 있습니다. 명시적 커서는 커서 변수를 선언하고, OPEN, FETCH, CLOSE 등의 커서 동작을 수행하여 데이터를 조회하고 처리할 수 있습니다.

커서 변수를 선언하고 초기화한 뒤, OPEN으로 커서를 열고 FETCH로 데이터를 한 번에 하나씩 가져올 수 있습니다. FETCH로 데이터를 가져오다가 더 이상 데이터가 없으면 커서를 닫아야 합니다. 또한 커서에서 반환된 데이터는 반드시 처리되어야 하며, 처리가 완료된 후에는 CLOSE를 통해 커서를 닫아야 합니다.

커서 변수는 데이터를 저장하기 위한 자료구조로 사용되며, 커서를 통해 데이터에 접근하여 조작할 수 있습니다. 커서를 사용하면 복잡한 데이터 처리 작업을 수행할 수 있으며, 필요한 데이터만 추출하여 필요한 로직을 적용할 수 있습니다.

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name
    FROM employees
    WHERE department_id = 10; -- 예시로 부서 ID가 10인 직원들을 조회하는 커서 선언

  emp_rec emp_cursor%ROWTYPE; -- 커서의 결과를 담을 변수 선언

BEGIN
  OPEN emp_cursor; -- 커서 열기

  LOOP
    FETCH emp_cursor INTO emp_rec; -- 데이터 한 건씩 가져오기
    EXIT WHEN emp_cursor%NOTFOUND; -- 데이터가 없을 때 반복문 종료

    -- 가져온 데이터를 활용한 로직 구현
    DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ' - ' || emp_rec.last_name || ', ' || emp_rec.first_name);
  END LOOP;

  CLOSE emp_cursor; -- 커서 닫기
END;

위의 예제는 employees 테이블에서 부서 ID가 10인 직원들을 조회하는 커서를 선언하고, 커서를 통해 가져온 데이터를 출력하는 예제입니다. 이를 통해 오라클 커서의 개념과 사용법을 이해할 수 있습니다.

2. PL/SQL에서의 오라클 커서 활용 방법

PL/SQL은 오라클의 프로시저 언어로, 오라클 데이터베이스에서 데이터를 조회하고 처리하는데 사용됩니다. PL/SQL에서 오라클 커서를 활용하여 데이터를 조회하고 조작하는 방법에 대해 알아보겠습니다.

2.1. 커서 변수 선언

PL/SQL에서 커서 변수를 선언하기 위해서는 CURSOR 키워드를 사용합니다. 커서 변수는 SELECT 문의 결과를 담아두는 역할을 합니다. 예시 코드를 통해 커서 변수를 선언하는 방법을 살펴보겠습니다.

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name
    FROM employees
    WHERE department_id = 10; -- 예시로 부서 ID가 10인 직원들을 조회하는 커서 선언
BEGIN
  -- 커서를 사용한 로직 구현
  NULL;
END;

2.2. 커서 열기

커서 변수를 선언하고 초기화한 후에는 OPEN을 사용하여 커서를 열어야 합니다. 커서를 열면 커서가 조회할 결과 집합을 가리키게 되고, 데이터를 한 건씩 가져올 수 있는 상태가 됩니다.

OPEN emp_cursor; -- 커서 열기

2.3. 데이터 조회 및 처리

커서를 열면 데이터를 한 건씩 가져오기 위해 FETCH를 사용합니다. FETCH를 통해 데이터를 가져오다가 더 이상 가져올 데이터가 없으면 반복문을 종료해야 합니다. 가져온 데이터를 활용하여 필요한 로직을 구현할 수 있습니다.

LOOP
  FETCH emp_cursor INTO emp_rec; -- 데이터 한 건씩 가져오기
  EXIT WHEN emp_cursor%NOTFOUND; -- 데이터가 없을 때 반복문 종료

  -- 가져온 데이터를 활용한 로직 구현
  DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ' - ' || emp_rec.last_name || ', ' || emp_rec.first_name);
END LOOP;

2.4. 커서 닫기

데이터를 처리한 후에는 CLOSE를 사용하여 커서를 닫아야 합니다. 커서를 닫으면 결과 집합이 반환되고, 더 이상 커서를 통해 데이터를 조회할 수 없는 상태가 됩니다. 커서를 닫지 않으면 오류가 발생할 수 있습니다.

CLOSE emp_cursor; -- 커서 닫기

이렇게 PL/SQL에서 오라클 커서를 활용하여 데이터를 조회하고 처리할 수 있습니다. 커서를 사용하면 복잡한 데이터 처리 작업을 효율적으로 수행할 수 있으며, 필요한 데이터만 추출하여 로직을 적용할 수 있습니다.

3. 실제 예제를 통한 오라클 커서의 활용 문제 해결

다음은 실제 예제를 통해 오라클 커서의 활용을 통해 문제를 해결하는 과정을 설명하겠습니다. 예제에서는 employees 테이블에서 부서 ID에 따라 직원들의 연봉 합계를 계산하는 문제를 다룰 것입니다.

3.1. 문제 정의

직원 테이블에는 employees 테이블이 있고, 이 테이블에는 직원들의 정보가 저장되어 있습니다. 문제는 부서 ID별 직원들의 연봉 합계를 구하는 것입니다.

3.2. 해결 방법

해결하기 위해 다음과 같은 절차를 따릅니다.

커서 변수를 선언하고 SELECT문을 작성합니다.
커서를 열고 데이터를 조회합니다.
데이터를 가져와 연봉을 더하여 변수에 누적합니다.
데이터가 모두 조회될 때까지 반복합니다.
커서를 닫고 누적된 연봉 결과를 출력합니다.

예시 코드를 통해 문제를 해결하는 과정을 살펴보겠습니다.

DECLARE
  CURSOR salary_cursor IS
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id; -- 부서 ID별 연봉 합계를 조회하는 커서 선언

  salary_rec salary_cursor%ROWTYPE; -- 커서의 결과를 담을 변수 선언

  total_salary NUMBER := 0; -- 누적된 연봉을 저장할 변수 선언

BEGIN
  OPEN salary_cursor; -- 커서 열기

  LOOP
    FETCH salary_cursor INTO salary_rec; -- 데이터 한 건씩 가져오기
    EXIT WHEN salary_cursor%NOTFOUND; -- 데이터가 없을 때 반복문 종료

    -- 가져온 데이터를 활용하여 연봉 누적
    total_salary := total_salary + salary_rec.total_salary;
  END LOOP;

  CLOSE salary_cursor; -- 커서 닫기

  -- 연봉 결과 출력
  DBMS_OUTPUT.PUT_LINE('Total Salary: ' || total_salary);
END;

위의 예제는 employees 테이블에서 부서 ID별 직원들의 연봉 합계를 계산하는 문제를 해결하는 코드입니다. 커서를 사용하여 데이터를 조회하고 연봉을 누적하는 과정을 반복한 뒤, 최종적으로 연봉 합계를 출력합니다.

이를 통해 오라클 커서의 활용을 통해 실제 문제를 해결하는 방법을 알아보았습니다. 커서를 사용하면 데이터를 효율적으로 조회하고 처리할 수 있으며, 복잡한 문제를 간결하게 해결할 수 있습니다.

소소한 일상과 경제 정보를 드림