PL/SQL 기초 07강 — 프로시저·함수·패키지·트리거 기초

🎯 학습 목표

CREATE PROCEDURE와 CREATE FUNCTION으로 재사용 가능한 서브프로그램을 만든다.
IN/OUT 파라미터의 차이를 이해한다.
패키지(Package)와 트리거(Trigger)의 개념과 기본 구조를 안다.

📖 저장 서브프로그램 vs 익명 블록

익명 블록          : 이름 없음, DB에 저장 안 됨, 매번 컴파일, 일회성 실행
저장 프로시저/함수  : 이름 있음, DB에 컴파일해서 저장, 어디서든 호출 가능, 재사용

프로시저와 함수는 한 번 만들어 두면 애플리케이션·다른 PL/SQL·SQL*Plus에서 자유롭게 호출할 수 있습니다.

💻 CREATE PROCEDURE — 저장 프로시저

-- 급여 인상 프로시저: 사원번호와 인상 금액을 받아 처리
CREATE OR REPLACE PROCEDURE raise_salary(
  p_empno   IN  emp.empno%TYPE,    -- IN : 입력 전용 (기본값)
  p_amount  IN  NUMBER,
  p_new_sal OUT emp.sal%TYPE       -- OUT: 결과를 호출자에게 반환
) AS
BEGIN
  UPDATE emp SET sal = sal + p_amount WHERE empno = p_empno;

  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20001, '사원을 찾을 수 없습니다: ' || p_empno);
  END IF;

  SELECT sal INTO p_new_sal FROM emp WHERE empno = p_empno;
  COMMIT;
END raise_salary;
/

-- 호출 방법
DECLARE
  v_new_sal emp.sal%TYPE;
BEGIN
  raise_salary(7369, 300, v_new_sal);
  DBMS_OUTPUT.PUT_LINE('변경 후 급여: ' || v_new_sal);
END;
/

IN: 호출자가 전달하는 입력 값(기본값). 서브프로그램 안에서 변경 불가.
OUT: 서브프로그램이 결과를 넘겨주는 값. 호출 전에는 NULL.
IN OUT: 입력과 출력 모두 사용.

💻 CREATE FUNCTION — 저장 함수

-- 세후 급여를 계산해서 반환하는 함수
CREATE OR REPLACE FUNCTION calc_net_sal(
  p_empno IN emp.empno%TYPE
) RETURN NUMBER
AS
  v_sal  emp.sal%TYPE;
  v_comm emp.comm%TYPE;
BEGIN
  SELECT sal, NVL(comm, 0) INTO v_sal, v_comm
    FROM emp WHERE empno = p_empno;
  RETURN ROUND((v_sal + v_comm) * 0.9, 2);   -- 10% 세금 공제
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END calc_net_sal;
/

-- SQL에서 직접 호출 가능 (함수의 큰 장점)
SELECT ename, sal, calc_net_sal(empno) AS net_sal
  FROM emp
 WHERE deptno = 20;

함수는 반드시 RETURN 값;으로 값을 반환해야 하며, DML(INSERT/UPDATE/DELETE)·COMMIT·ROLLBACK을 포함하면 SQL에서 호출할 때 제약이 생깁니다. 순수 조회·계산 함수를 권장합니다.

📖 패키지(Package) 개요

패키지는 관련된 프로시저·함수·변수·커서·예외를 하나로 묶은 그룹입니다. 스펙(Specification)은 공개 인터페이스를, 바디(Body)는 구현을 담습니다.

-- 패키지 스펙 (공개 인터페이스)
CREATE OR REPLACE PACKAGE emp_mgr AS
  PROCEDURE hire(p_name VARCHAR2, p_deptno NUMBER);
  FUNCTION  get_count(p_deptno NUMBER) RETURN NUMBER;
END emp_mgr;
/

-- 패키지 바디 (구현)
CREATE OR REPLACE PACKAGE BODY emp_mgr AS
  PROCEDURE hire(p_name VARCHAR2, p_deptno NUMBER) IS
  BEGIN
    INSERT INTO emp(ename, deptno, hiredate) VALUES(p_name, p_deptno, SYSDATE);
    COMMIT;
  END hire;

  FUNCTION get_count(p_deptno NUMBER) RETURN NUMBER IS
    v_cnt NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_cnt FROM emp WHERE deptno = p_deptno;
    RETURN v_cnt;
  END get_count;
END emp_mgr;
/

-- 호출: 패키지명.서브프로그램명
BEGIN
  emp_mgr.hire('김철수', 20);
  DBMS_OUTPUT.PUT_LINE('20부서 인원: ' || emp_mgr.get_count(20));
END;
/

📖 트리거(Trigger) 개요

트리거는 테이블에 DML이 발생할 때 자동으로 실행되는 PL/SQL 코드입니다. 감사 로그·자동 계산·참조 무결성 보완 등에 씁니다.

-- emp 테이블의 sal 컬럼이 UPDATE될 때 변경 이력을 기록
CREATE OR REPLACE TRIGGER trg_emp_sal_audit
  AFTER UPDATE OF sal ON emp        -- sal 컬럼 UPDATE 후 실행
  FOR EACH ROW                      -- 행마다 실행
BEGIN
  INSERT INTO emp_sal_log(empno, old_sal, new_sal, changed_at)
    VALUES(:OLD.empno, :OLD.sal, :NEW.sal, SYSDATE);
END trg_emp_sal_audit;
/

:OLD: DML 이전 행의 값 (UPDATE·DELETE에서 유효).
:NEW: DML 이후 행의 값 (INSERT·UPDATE에서 유효).
BEFORE 트리거: DML 실행 전에 동작 — 값 검증·변환에 적합.
AFTER 트리거: DML 실행 후에 동작 — 로깅·연계 처리에 적합.

⚠️ 주의사항

트리거 안에서 트리거를 유발하는 DML을 하면 순환 트리거가 발생할 수 있습니다. 설계 시 주의하세요.
함수를 SQL에서 호출할 때 안에 COMMIT이 있으면 ORA-14552 오류가 납니다. SQL에서 쓸 함수는 DML을 포함하지 않도록 설계하세요.
패키지 바디를 수정하면 스펙은 재컴파일하지 않아도 됩니다. 스펙을 변경하면 바디·종속 객체가 모두 무효화(INVALID)됩니다.

🧭 마무리 & 다음 단계

이 강좌 한 문장 요약: “PL/SQL은 SQL에 절차적 흐름(조건·반복·예외)을 더해 복잡한 업무 로직을 DB 안에서 효율적으로 처리하는 Oracle 전용 언어입니다.”
더 깊게 배울 주제: 벌크 처리(BULK COLLECT INTO, FORALL), 동적 SQL(EXECUTE IMMEDIATE), 파이프라인 함수, 오브젝트 타입, 컴파일러 최적화 힌트.
관련 강좌: Oracle 카테고리의 다른 글과 함께 보면 좋습니다.