오라클 스토어드 프로시저 : 실무에서 활용하는 다양한 기능

앞선 글에서는 스토어드 프로시저의 기본 개념과 간단한 예시를 살펴보았습니다. 이번에는 실제 개발 환경에서 자주 사용되는 다양한 기능과 예시를 통해 스토어드 프로시저를 더욱 효과적으로 활용하는 방법을 알아보겠습니다.

1. 입력 및 출력 파라미터 활용(UPDATE)

CREATE OR REPLACE PROCEDURE proc_update_employee_salary(
    p_employee_id IN employees.employee_id%TYPE,
    p_salary IN NUMBER,
    p_result OUT VARCHAR2
)
IS
BEGIN
    UPDATE employees
    SET salary = p_salary
    WHERE employee_id = p_employee_id;

    IF SQL%ROWCOUNT > 0 THEN
        p_result := '성공';
    ELSE
        p_result := '실패';
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        p_result := '오류 발생: ' || SQLERRM;
END;
/



-- 프로시저 호출
DECLARE
  v_result VARCHAR2(100);
BEGIN
  proc_update_employee_salary(101, 50000, v_result);
  DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

• 입력 파라미터: p_employee_id, p_salary는 프로시저 호출 시 전달되는 값입니다.
• 출력 파라미터: p_result는 프로시저 실행 결과를 반환합니다.
• 예외 처리: EXCEPTION 블록을 사용하여 오류 발생 시 적절한 메시지를 반환합니다.

728x90

2. 커서 활용

CREATE OR REPLACE PROCEDURE proc_get_employees_by_dept(
    p_dept_no IN departments.department_id%TYPE,
    p_employee_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_employee_cursor FOR
    SELECT *
    FROM employees
    WHERE department_id = p_dept_no;
END;
/
 
-- 프로시저 호출

DECLARE
  v_emp_cursor SYS_REFCURSOR;
  v_emp_id employees.employee_id%TYPE;
  v_emp_name employees.first_name%TYPE;
BEGIN
  proc_get_employees_by_dept(10, v_emp_cursor); -- 부서 번호 10번인 사원 조회

  LOOP
    FETCH v_emp_cursor INTO v_emp_id, v_emp_name;
    EXIT WHEN v_emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('사원번호: ' || v_emp_id || ', 이름: ' || v_emp_name);
  END LOOP;

  CLOSE v_emp_cursor;
END;
/

• 커서: 결과 집합을 행 단위로 접근할 수 있도록 하는 객체입니다.
• REF CURSOR: 다양한 쿼리 결과를 하나의 커서 변수에 담아 처리할 수 있습니다.

 

 Json 형식으로 리턴하는 예:

DECLARE
  v_emp_cursor SYS_REFCURSOR;
  v_emp_id employees.employee_id%TYPE;
  v_emp_name employees.first_name%TYPE;
  v_json_str VARCHAR2(4000);
  
  FUNCTION escape_for_json(p_str IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p_str, '"', '\"'),
               '\', '\\'), '/', '\/'), '\b', '\b'), '\f', '\f'), '\n', '\n'), '\r', '\r'), '\t', '\t');
  END escape_for_json;

BEGIN
  proc_get_employees_by_dept(10, v_emp_cursor);

  v_json_str := '[';

  LOOP
    FETCH v_emp_cursor INTO v_emp_id, v_emp_name;
    EXIT WHEN v_emp_cursor%NOTFOUND;

    v_json_str := v_json_str || '{"employee_id": "' || escape_for_json(v_emp_id) || '", "first_name": "' || escape_for_json(v_emp_name) || '"},';
  END LOOP;

  -- 마지막 쉼표 제거
  v_json_str := SUBSTR(v_json_str, 1, LENGTH(v_json_str) - 1);

  v_json_str := v_json_str || ']';

  DBMS_OUTPUT.PUT_LINE(v_json_str);

  CLOSE v_emp_cursor;
END;
/

3. 트리거 활용

CREATE OR REPLACE TRIGGER trg_salary_update
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(-20001, '급여는 인상되어야 합니다.');
    END IF;
END;
/

• 트리거: 특정 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 PL/SQL 블록입니다.
• BEFORE UPDATE: salary 컬럼이 업데이트되기 전에 트리거가 실행됩니다.
• FOR EACH ROW: 업데이트되는 각 행에 대해 트리거가 실행됩니다.

4. 패키지 활용

CREATE OR REPLACE PACKAGE pkg_employee
IS
    PROCEDURE proc_update_salary(p_employee_id IN NUMBER, p_salary IN NUMBER);
    FUNCTION get_employee_name(p_employee_id IN NUMBER) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_employee
IS
    -- 프로시저 및 함수 구현
END pkg_employee;
/




-- 프로시저 호출
BEGIN
  pkg_employee.proc_update_salary(101, 60000);
END;
/

-- 함수 호출
DECLARE
  v_name VARCHAR2(100);
BEGIN
  v_name := pkg_employee.get_employee_name(101);
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

• 패키지: 관련된 프로시저, 함수, 변수 등을 하나의 단위로 묶어 관리하는 객체입니다.
• 정보 은닉: 패키지 내부의 구현을 외부에 노출하지 않고 인터페이스만 제공할 수 있습니다.

스토어드 프로시저 활용 시 주의 사항

• 성능: 인덱스를 적절히 활용하고, 불필요한 데이터베이스 접근을 최소화해야 합니다.
• 보안: SQL 인젝션과 같은 보안 취약점을 방지하기 위해 바인드 변수를 사용해야 합니다.
• 유지보수: 명확한 주석과 표준을 준수하여 코드 가독성을 높여야 합니다.
• 오류 처리: 예외 처리를 통해 시스템 오류를 방지하고 안정성을 확보해야 합니다.

 

728x90

스토어드 프로시저는 오라클 데이터베이스 개발에서 필수적인 요소입니다. 다양한 기능과 활용 사례를 통해 데이터베이스 관리 및 개발 생산성을 향상시킬 수 있습니다. 본 글에서 소개된 내용을 바탕으로 스토어드 프로시저를 효과적으로 활용하여 더욱 안정적이고 효율적인 데이터베이스 시스템을 구축하시기 바랍니다.