Oracle PL/SQL 實戰案例：員工薪資調整與異動追蹤

案例需求

1. 建立 EMP（員工）、DEPT（部門）表並填入測試資料
2. 將「技術部」員工薪資上調 20%
3. 建立薪資異動日誌表與 Trigger 自動記錄
4. 撰寫測試 Package 驗證邏輯

1. 建立表結構與測試資料

CREATE TABLE dept (
  deptno NUMBER(2) PRIMARY KEY,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);

CREATE TABLE emp (
  empno    NUMBER(4) PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2) REFERENCES dept(deptno)
);

-- 插入 5 筆部門與 5 筆員工資料（略）

2. 薪資調整 Procedure

CREATE OR REPLACE PROCEDURE raise_sal_by_dept(p_dname VARCHAR2, p_pct NUMBER) AS
BEGIN
  UPDATE emp e
  SET sal = sal * (1 + p_pct/100)
  WHERE e.deptno = (SELECT deptno FROM dept WHERE dname = p_dname);
  
  DBMS_OUTPUT.PUT_LINE('已調整 ' || SQL%ROWCOUNT || ' 位員工薪資');
END;
/

3. 薪資異動日誌與 Trigger

CREATE TABLE sal_history (
  empno       NUMBER(4),
  old_sal     NUMBER(7,2),
  new_sal     NUMBER(7,2),
  change_date DATE DEFAULT SYSDATE,
  changed_by  VARCHAR2(30) DEFAULT USER
);

CREATE OR REPLACE TRIGGER trg_emp_sal_audit
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (OLD.sal != NEW.sal)
BEGIN
  INSERT INTO sal_history (empno, old_sal, new_sal)
  VALUES (:OLD.empno, :OLD.sal, :NEW.sal);
END;
/

4. 測試 Package

CREATE OR REPLACE PACKAGE pkg_test AS
  PROCEDURE setup_data;
  PROCEDURE test_raise_sal;
  PROCEDURE verify_results;
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test AS
  -- 使用暫存表記錄中間結果
  PROCEDURE setup_data AS
  BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE emp';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE dept';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE sal_history';
    -- 插入固定測試資料...
  END;

  PROCEDURE test_raise_sal AS
  BEGIN
    raise_sal_by_dept('技術部', 20);
  END;

  PROCEDURE verify_results AS
  BEGIN
    -- 查詢 emp 薪資與 sal_history 筆數，輸出驗證訊息
    FOR r IN (SELECT ename, sal FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname='技術部')) LOOP
      DBMS_OUTPUT.PUT_LINE(r.ename || ' 新薪資: ' || r.sal);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('日誌筆數: ' || (SELECT COUNT(*) FROM sal_history));
  END;
END pkg_test;
/

5. 執行測試

SET SERVEROUTPUT ON
EXEC pkg_test.setup_data;
EXEC pkg_test.test_raise_sal;
EXEC pkg_test.verify_results;

PL/SQL 測試三大法

1. DBMS_OUTPUT.PUT_LINE 印出中間變數
2. 插入暫存表記錄關鍵步驟結果，事後查詢分析
3. 區塊內 BEGIN ... EXCEPTION WHEN OTHERS THEN ... END; 捕獲異常

小結

本案例串聯 DDL、DML、Procedure、Trigger、Package，展示 PL/SQL 核心功能整合應用。掌握此流程，即可獨立開發資料庫端業務邏輯與稽核機制。