프로시저

프로시저(Procedure)

특정 업무를 처리하는 일련의 작업을 프로그램으로 저장하여 사용하는 객체

함수와 달리 값을 반환하지 않아도 됩니다.

함수 vs 프로시저 비교

구분	함수(Function)	프로시저(Procedure)
반환값	반드시 하나의 값을 반환	반환값이 없거나 여러 개 가능
실행 방법	SELECT 문에서 직접 호출 가능	EXECUTE 문으로 호출
주 용도	계산 결과 반환	복잡한 비즈니스 로직 처리
DML 사용	제한적	자유롭게 사용 가능

프로시저 생성

•

파라미터 없는 프로시저

CREATE OR REPLACE PROCEDURE 프로시저명
  IS | AS
      선언부
  BEGIN
      실행부
  EXCEPTION
      예외 처리부
  END [프로시저명];
SQL
복사

•

파라미터 있는 프로시저

CREATE OR REPLACE PROCEDURE 프로시저명
  (
      파라미터1 [IN / OUT / IN OUT] 데이터타입 [ := 값 | DEFAULT 기본값 ],
      파라미터2 [IN / OUT / IN OUT] 데이터타입 [ := 값 | DEFAULT 기본값 ],
      ...
  )
  IS | AS
      선언부
  BEGIN
      실행부
  EXCEPTION
      예외 처리부
  END [프로시저명];
SQL
복사

파라미터 모드

모드	설명	데이터 흐름	기본값 설정
IN	입력 전용 파라미터	호출부 → 프로시저	가능
OUT	출력 전용 파라미터	프로시저 → 호출부	불가능
IN OUT	입출력 파라미터	양방향 (호출부 프로시저)	가능

IN 모드 (입력 파라미터)

•

프로시저로 값을 전달만 하는 파라미터입니다.

•

프로시저 내부에서 값을 변경할 수 없습니다 (읽기 전용).

•

기본값을 설정할 수 있으며, 생략 시 기본 모드입니다.

•

리터럴, 변수, 수식 등을 전달할 수 있습니다.

OUT 모드 (출력 파라미터)

•

프로시저에서 호출부로 값을 반환하는 파라미터입니다.

•

프로시저 내부에서 값을 할당해야 합니다.

•

호출 시 반드시 변수를 전달해야 합니다 (리터럴 불가).

•

기본값을 설정할 수 없습니다.

•

프로시저 시작 시 자동으로 NULL로 초기화됩니다.

IN OUT 모드 (입출력 파라미터)

•

프로시저로 값을 전달하고, 다시 결과를 받아오는 파라미터입니다.

•

프로시저 내부에서 값을 읽고 수정할 수 있습니다.

•

호출 시 반드시 변수를 전달해야 합니다.

•

기본값을 설정할 수 있습니다.

•

값을 누적하거나 변환하는 작업에 유용합니다.

프로시저 생성 예시

•

파라미터 없는 프로시저

•

파라미터 있는 프로시저

•

파라미터 없는 프로시저

CREATE OR REPLACE PROCEDURE update_all_salaries
IS
    v_count NUMBER := 0;
BEGIN
    UPDATE employees
    SET salary = salary * 1.05;  -- 전 직원 5% 인상
    
    v_count := SQL%ROWCOUNT;  -- 영향받은 행 수
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(v_count || '명의 급여가 인상되었습니다.');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
END update_all_salaries;
/
SQL
복사

•

파라미터 있는 프로시저

파라미터 모드에는 IN, OUT, IN OUT 세 가지가 있습니다.

CREATE OR REPLACE PROCEDURE get_emp_info
(
    p_emp_id    IN  NUMBER,           -- 입력 파라미터
    p_emp_name  OUT VARCHAR2,         -- 출력 파라미터
    p_salary    OUT NUMBER,           -- 출력 파라미터
    p_dept_name OUT VARCHAR2          -- 출력 파라미터
)
IS
BEGIN
    SELECT e.emp_name, 
           e.salary,
           d.dept_title
    INTO p_emp_name, p_salary, p_dept_name
    FROM employees e
    LEFT JOIN departments d ON e.dept_code = d.dept_id
    WHERE e.emp_id = p_emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_emp_name := '데이터 없음';
        p_salary := 0;
        p_dept_name := '부서 없음';
END get_emp_info;
/
SQL
복사

프로시저 생성 (기본값 설정)

CREATE OR REPLACE PROCEDURE increase_salary
(
    p_emp_id    IN NUMBER,
    p_rate      IN NUMBER := 0.1,      -- 기본값 10%
    p_bonus     IN NUMBER DEFAULT 0    -- 기본값 0
)
IS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_rate),
        bonus = p_bonus
    WHERE emp_id = p_emp_id;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('급여가 인상되었습니다.');
END increase_salary;
/
SQL
복사

프로시저 실행

방법 1: EXECUTE 문 사용

•

파라미터가 없가나 IN 파라미터만 있는 경우

EXECUTE 프로시저명;

EXECUTE 프로시저명(인자1, 인자2, ...);

EXECUTE 프로시저명(파라미터1 => 인자1, 파라미터2 => 인자2, ...);
SQL
복사

•

예시

SET SERVEROUTPUT ON;

-- 파라미터 없는 프로시저
EXECUTE update_all_salaries;

-- 파라미터 있는 프로시저 (위치 기반)
EXECUTE increase_salary(100, 0.15, 0.2);

-- 파라미터 있는 프로시저 (이름 기반)
EXECUTE increase_salary(p_emp_id => 100, p_rate => 0.15);
SQL
복사

방법 2: PL/SQL 블록 내에서 실행

•

OUT 파라미터가 있는 경우

DECLARE
    변수1 데이터타입;
    변수2 데이터타입;
    ...
BEGIN
    프로시저명(IN파라미터, OUT변수1, OUT변수2, ...);
    
    -- 반환된 값 사용
    DBMS_OUTPUT.PUT_LINE(변수1);
    DBMS_OUTPUT.PUT_LINE(변수2);
END;
/
SQL
복사

•

예시

DECLARE
    v_name VARCHAR2(50);
    v_sal NUMBER;
    v_dept VARCHAR2(50);
BEGIN
    get_emp_info(100, v_name, v_sal, v_dept);
    
    DBMS_OUTPUT.PUT_LINE('이름: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('급여: ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('부서: ' || v_dept);
END;
/
SQL
복사

프로시저 삭제

DROP PROCEDURE 프로시저명;
SQL
복사

•

예시

DROP PROCEDURE update_all_salaries;
SQL
복사

실전 예제

예제 1: 부서별 급여 통계 프로시저

CREATE OR REPLACE PROCEDURE get_dept_salary_stats
(
    p_dept_code IN VARCHAR2,
    p_avg_sal   OUT NUMBER,
    p_max_sal   OUT NUMBER,
    p_min_sal   OUT NUMBER,
    p_emp_count OUT NUMBER
)
IS
BEGIN
    SELECT AVG(salary), MAX(salary), MIN(salary), COUNT(*)
    INTO p_avg_sal, p_max_sal, p_min_sal, p_emp_count
    FROM employees
    WHERE dept_code = p_dept_code;
    
    DBMS_OUTPUT.PUT_LINE('=== 부서 급여 통계 ===');
    DBMS_OUTPUT.PUT_LINE('평균 급여: ' || TO_CHAR(p_avg_sal, '999,999,999'));
    DBMS_OUTPUT.PUT_LINE('최고 급여: ' || TO_CHAR(p_max_sal, '999,999,999'));
    DBMS_OUTPUT.PUT_LINE('최저 급여: ' || TO_CHAR(p_min_sal, '999,999,999'));
    DBMS_OUTPUT.PUT_LINE('인원 수: ' || p_emp_count || '명');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 부서에 직원이 없습니다.');
END get_dept_salary_stats;
/
SQL
복사

예제 2: 직원 정보 일괄 업데이트 프로시저

CREATE OR REPLACE PROCEDURE batch_update_employees
(
    p_dept_code IN VARCHAR2,
    p_salary_rate IN NUMBER DEFAULT 1.0,
    p_bonus_rate IN NUMBER DEFAULT 0
)
IS
    v_count NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary * p_salary_rate,
        bonus = bonus + p_bonus_rate
    WHERE dept_code = p_dept_code;
    
    v_count := SQL%ROWCOUNT;
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(v_count || '명의 직원 정보가 업데이트되었습니다.');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        RAISE;
END batch_update_employees;
/
SQL
복사

프로시저 활용 팁

•

복잡한 비즈니스 로직은 프로시저로 캡슐화하여 재사용성을 높이세요

•

트랜잭션 처리가 필요한 작업은 프로시저에서 COMMIT/ROLLBACK을 명시하세요

•

예외 처리를 반드시 포함하여 안정성을 확보하세요

•

OUT 파라미터를 활용하여 여러 값을 반환할 수 있습니다

주요 차이점 정리

함수 (Function)

•

 SELECT 문에서 사용 가능

•

 단일 값 반환 필수

•

 계산, 변환 작업에 적합

•

 DML 사용 제한적

•

 트랜잭션 제어 불가

프로시저 (Procedure)

•

 복잡한 비즈니스 로직 처리

•

 여러 값 반환 가능 (OUT)

•

 DML 자유롭게 사용

•

 트랜잭션 제어 가능

•

 SELECT 문에서 직접 호출 불가