30_ 저장 프로시저(Stored Procedure)

1. 저장 프로시저란?

저장 프로시저는 여러 SQL 문을 하나로 묶어서 데이터베이스에 저장해두는 객체다.
일종의 데이터베이스 내부 함수 같은 개념으로,
한 번 만들어두면 여러 곳에서 반복적으로 사용할 수 있다.

즉,

• 복잡한 SQL 로직을 단순한 호출 한 줄로 대체할 수 있고
• 업무 로직을 모듈화할 수 있으며
• SQL 실행 속도도 향상된다.

 

2. 주요 특징

특징                       설명

재사용성	한 번 작성된 프로시저는 여러 번 호출 가능
모듈화	복잡한 로직을 분리해 관리하기 쉬움
보안성	테이블 직접 접근을 제한하고 프로시저를 통해 접근하도록 설정 가능
성능 향상	SQL 문이 미리 컴파일되어 저장되므로 실행 속도가 빠름

 

 

3. 기본 문법

CREATE [OR REPLACE] PROCEDURE 프로시저명
    (매개변수1 [IN|OUT|IN OUT] 데이터타입,
     매개변수2 [IN|OUT|IN OUT] 데이터타입,
     ...)
IS|AS
    -- 선언부
    변수 선언
BEGIN
    -- 실행부
    SQL문 또는 PL/SQL문
EXCEPTION
    -- 예외 처리부
    예외 처리문
END;
/

 

• IS 또는 AS 키워드는 선언부 시작을 의미한다.
• BEGIN ~ END 블록 안에 실행할 SQL 문을 작성한다.
• EXCEPTION 블록에서는 오류를 처리한다.

 

4. 매개변수 모드

모드                             설명

IN	외부에서 값을 전달받는 용도 (기본값)
OUT	프로시저 내부에서 계산된 값을 반환할 때 사용
IN OUT	값을 전달받고, 수정된 값을 다시 반환 가능

 

5. 프로시저 생성 예제

CREATE OR REPLACE PROCEDURE update_salary
    (p_emp_id IN NUMBER,
     p_raise_percent IN NUMBER)
IS
BEGIN
    UPDATE employees
    SET salary = salary + (salary * p_raise_percent/100)
    WHERE employee_id = p_emp_id;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, '급여 수정 중 오류 발생');
END;
/

설명

• p_emp_id: 사원 번호
• p_raise_percent: 인상 비율
• 급여를 수정하고 커밋한다.
• 예외가 발생하면 롤백 후 사용자 정의 오류 메시지를 출력한다.

 

6. 프로시저 실행 방법

-- 방법 1: EXECUTE 명령어
EXECUTE update_salary(100, 10);

-- 방법 2: 익명 블록에서 호출
BEGIN
    update_salary(100, 10);
END;
/

 

 

7. 프로시저 관리

작업                                   명령

프로시저 목록 조회	USER_PROCEDURES, ALL_PROCEDURES, DBA_PROCEDURES
소스 코드 조회	USER_SOURCE, ALL_SOURCE, DBA_SOURCE
프로시저 삭제	DROP PROCEDURE 프로시저명;

 

8. 사용 시 주의사항

• 예외 처리를 반드시 작성해야 한다.
• 트랜잭션(COMMIT, ROLLBACK)을 명확히 구분해야 한다.
• 불필요한 데이터 접근을 막기 위해 권한을 적절히 설정해야 한다.
• 복잡한 연산은 가능하면 프로시저 내부에서 효율적으로 처리할 것.

 

 

9. 실습 예제 — 부서별 평균 급여 계산

CREATE OR REPLACE PROCEDURE calc_dept_avg_salary
    (p_dept_id IN NUMBER,
     p_avg_salary OUT NUMBER)
IS
BEGIN
    SELECT AVG(salary)
    INTO p_avg_salary
    FROM employees
    WHERE department_id = p_dept_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_avg_salary := 0;
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20002, '평균 급여 계산 중 오류 발생');
END;
/

호출 예시

DECLARE
    v_dept_id NUMBER := 50;
    v_avg_salary NUMBER;
BEGIN
    calc_dept_avg_salary(v_dept_id, v_avg_salary);
    DBMS_OUTPUT.PUT_LINE('부서 ID ' || v_dept_id || '의 평균 급여: ' || v_avg_salary);
END;
/

 

10. 또 다른 예제 — 사원 정보 조회 프로시저

CREATE OR REPLACE PROCEDURE get_employee_info (
    p_emp_id IN hr.employees.employee_id%TYPE
) IS
    v_first_name hr.employees.first_name%TYPE;
    v_last_name  hr.employees.last_name%TYPE;
    v_salary     hr.employees.salary%TYPE;
BEGIN
    SELECT first_name, last_name, salary
    INTO v_first_name, v_last_name, v_salary
    FROM hr.employees
    WHERE employee_id = p_emp_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_emp_id);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_first_name || ' ' || v_last_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with ID ' || p_emp_id);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END get_employee_info;
/