Oracle | 프로시저 / REF CURSOR

1. 프로시저의 사용 이유

프로시저는 반복되는 작업을 자동화하고 복잡한 로직을 캡슐화할 수 있게 도와줍니다. 프로시저는 함수와 비슷하지만 값을 반환하지 않고 특정 작업을 수행하는 데 중점을 둡니다.

주요 이유:

• 중복 코드 제거: 비슷한 쿼리를 여러 곳에서 반복적으로 사용해야 할 때, 이를 프로시저로 묶으면 한 번만 작성하고 필요할 때마다 재사용할 수 있습니다.
• 복잡한 로직 관리: 여러 개의 SQL 쿼리나 조건문을 하나의 프로시저로 묶어 두면 코드가 더 깔끔하고 유지보수가 쉬워집니다.
• 권한 관리: 데이터베이스 사용자에게 직접 쿼리를 실행할 수 있는 권한을 주지 않고, 대신 프로시저를 실행하게 할 수 있습니다. 이렇게 하면 보안이 강화됩니다.

예를 들어, 특정 테이블에서 데이터를 조회하는 쿼리가 여러 곳에서 반복되면, 이를 프로시저로 만들어 한 번만 정의하고 재사용할 수 있습니다.

2. REF CURSOR의 사용 이유

REF CURSOR는 SQL 쿼리의 결과 집합(즉, 여러 행)을 동적으로 반환할 수 있도록 해주는 포인터입니다. REF CURSOR를 사용하면 SQL 쿼리의 결과를 한 번에 여러 행을 반환할 수 있습니다.

주요 이유:

• 동적 쿼리 결과 반환: REF CURSOR는 쿼리의 결과를 동적으로 반환할 수 있습니다. 예를 들어, 어떤 조건에 따라 조회할 데이터를 동적으로 선택하고, 그 결과를 클라이언트로 넘겨줄 때 유용합니다.
• 여러 결과 반환: 하나의 쿼리에서 여러 행의 결과를 반환할 수 있습니다. REF CURSOR를 사용하면 여러 행을 순차적으로 처리할 수 있기 때문에, 결과가 한 번에 너무 많아서 처리하기 힘든 경우에도 유용합니다.
• 유연성: REF CURSOR를 사용하면 한 쿼리로 여러 종류의 데이터를 처리할 수 있습니다. 예를 들어, 하나의 프로시저에서 부서별로 데이터를 조회하고, 다른 프로시저에서 직무별로 데이터를 조회하는 등의 다양한 방식으로 결과를 처리할 수 있습니다.

---

프로시저 + REF CURSOR을 함께 사용하는 이유

• 복잡한 데이터 조회 처리: 데이터를 조회하는 로직이 복잡하거나, 특정 조건에 따라 다르게 데이터를 조회할 때, 프로시저와 REF CURSOR를 함께 사용하면 유연하고 효율적으로 데이터를 처리할 수 있습니다.
• 결과를 클라이언트에 넘기기: 프로시저를 사용하여 쿼리를 실행하고, 그 결과를 REF CURSOR로 클라이언트 애플리케이션에 넘겨주면, 클라이언트는 그 결과를 순차적으로 처리할 수 있습니다.
• 재사용성: 여러 쿼리의 실행 결과를 하나의 프로시저에서 처리하고, REF CURSOR로 결과를 전달하면, 다른 부분에서 반복적으로 쿼리를 작성하지 않고 프로시저를 재사용할 수 있습니다.

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,     -- 사원 ID
    first_name VARCHAR2(50),            -- 이름
    last_name VARCHAR2(50),             -- 성
    department_id NUMBER,               -- 부서 ID
    job_id VARCHAR2(10)                 -- 직무 ID
);

-- 샘플 데이터 삽입
INSERT INTO employees (employee_id, first_name, last_name, department_id, job_id)
VALUES (1, 'John', 'Doe', 10, 'IT_PROG');

INSERT INTO employees (employee_id, first_name, last_name, department_id, job_id)
VALUES (2, 'Jane', 'Smith', 20, 'HR_REP');

INSERT INTO employees (employee_id, first_name, last_name, department_id, job_id)
VALUES (3, 'Michael', 'Johnson', 10, 'IT_PROG');

INSERT INTO employees (employee_id, first_name, last_name, department_id, job_id)
VALUES (4, 'Emily', 'Davis', 30, 'SA_REP');

-- 데이터 확인
SELECT * FROM employees;

 

샘플 데이터:

1	John	Doe	10	IT_PROG
2	Jane	Smith	20	HR_REP
3	Michael	Johnson	10	IT_PROG
4	Emily	Davis	30	SA_REP

 

CREATE OR REPLACE PROCEDURE get_employees_by_condition (
    p_column_name IN VARCHAR2,       -- 조회할 컬럼 이름 (예: 'department_id', 'job_id')
    p_value IN VARCHAR2,             -- 조건 값 (예: '10' 또는 'IT_PROG')
    o_cursor OUT SYS_REFCURSOR       -- 결과를 반환할 커서
) IS
BEGIN
    -- 동적 SQL을 사용하여, 주어진 컬럼에 대해 조건을 만족하는 데이터를 반환
    OPEN o_cursor FOR
        'SELECT * FROM employees WHERE ' || p_column_name || ' = :param'
        USING p_value;               -- p_value를 :param에 바인딩
END;
/

 

department_id가 10인 사원들 조회

DECLARE
    my_cursor SYS_REFCURSOR;  -- 결과를 받을 커서 변수
    emp_id employees.employee_id%TYPE;
    emp_name employees.first_name%TYPE;
BEGIN
    -- 'department_id'가 10인 사원들을 조회
    get_employees_by_condition('department_id', '10', my_cursor);

    -- 커서에서 데이터를 하나씩 가져오기
    LOOP
        FETCH my_cursor INTO emp_id, emp_name;
        EXIT WHEN my_cursor%NOTFOUND;  -- 더 이상 데이터가 없으면 종료
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Name: ' || emp_name);
    END LOOP;

    CLOSE my_cursor;  -- 커서 닫기
END;
/

Employee ID: 1, Name: John
Employee ID: 3, Name: Michael

 

 

job_id가 'IT_PROG'인 사원들 조회

DECLARE
    my_cursor SYS_REFCURSOR;  -- 결과를 받을 커서 변수
    emp_id employees.employee_id%TYPE;
    emp_name employees.first_name%TYPE;
BEGIN
    -- 'job_id'가 'IT_PROG'인 사원들을 조회
    get_employees_by_condition('job_id', 'IT_PROG', my_cursor);

    -- 커서에서 데이터를 하나씩 가져오기
    LOOP
        FETCH my_cursor INTO emp_id, emp_name;
        EXIT WHEN my_cursor%NOTFOUND;  -- 더 이상 데이터가 없으면 종료
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Name: ' || emp_name);
    END LOOP;

    CLOSE my_cursor;  -- 커서 닫기
END;
/

Employee ID: 1, Name: John
Employee ID: 3, Name: Michael