Oracle PL/SQL 入門案例實戰：員工薪資調整與異動日誌

案例需求

兩表：EMP (員工)、DEPT (部門)。需求：

1. 建表並各載入 5 筆測試資料
2. 技術部員工薪資漲 20%
3. 建立薪資異動日誌追蹤
4. 建立測試套件

實作步驟

1. 建表與載入資料 (以程序封裝)

CREATE TABLE EMP (EMP_ID NUMBER(5), EMP_NAME VARCHAR2(20), EMP_SALARY NUMBER(4));
CREATE TABLE DEPT (DEPT_ID NUMBER(3), DEPT_NAME VARCHAR2(20), EMP_ID NUMBER(5));

CREATE OR REPLACE PROCEDURE INS_TABLE_EMP(P_EMP_ID NUMBER, P_EMP_NAME VARCHAR2, P_EMP_SALARY NUMBER) AS
  V_EMP_ID NUMBER := P_EMP_ID;
  V_EMP_NAME VARCHAR2(20) := P_EMP_NAME;
  V_EMP_SALARY NUMBER := P_EMP_SALARY;
BEGIN
  INSERT INTO EMP VALUES (V_EMP_ID, V_EMP_NAME, V_EMP_SALARY);
END;

CREATE OR REPLACE PROCEDURE INS_TABLE_DEPT(P_DEPT_ID NUMBER, P_DEPT_NAME VARCHAR2, P_EMP_ID NUMBER) AS
  V_DEPT_ID NUMBER := P_DEPT_ID;
  V_DEPT_NAME VARCHAR2(20) := P_DEPT_NAME;
  V_EMP_ID NUMBER := P_EMP_ID;
BEGIN
  INSERT INTO DEPT VALUES (V_DEPT_ID, V_DEPT_NAME, V_EMP_ID);
END;

BEGIN
  INS_TABLE_EMP(10000,'員工1',4000);
  INS_TABLE_EMP(10001,'員工2',2300);
  INS_TABLE_EMP(10002,'員工3',3500);
  INS_TABLE_EMP(10003,'員工4',3500);
  INS_TABLE_EMP(10004,'員工5',3500);

  INS_TABLE_DEPT(111,'技術部',10000);
  INS_TABLE_DEPT(111,'技術部',10001);
  INS_TABLE_DEPT(111,'技術部',10002);
  INS_TABLE_DEPT(112,'業務部',10003);
  INS_TABLE_DEPT(113,'人資部',10004);
END;

2. 複合查詢更新薪資

UPDATE EMP
SET EMP_SALARY = EMP_SALARY * 1.2
WHERE EMP_ID IN (SELECT EMP_ID FROM DEPT WHERE DEPT_NAME = '技術部');

3. 觸發器記錄薪資異動日誌

CREATE TABLE SALARY_LOG (
  EMP_ID NUMBER, OLD_SALARY NUMBER, NEW_SALARY NUMBER,
  CHANGE_DATE DATE DEFAULT SYSDATE, CHANGE_USER VARCHAR2(30) DEFAULT USER
);

CREATE OR REPLACE TRIGGER TRG_SALARY_LOG
BEFORE UPDATE OF EMP_SALARY ON EMP
FOR EACH ROW
WHEN (OLD.EMP_SALARY != NEW.EMP_SALARY)
BEGIN
  INSERT INTO SALARY_LOG (EMP_ID, OLD_SALARY, NEW_SALARY)
  VALUES (:OLD.EMP_ID, :OLD.EMP_SALARY, :NEW.EMP_SALARY);
END;

4. 建立測試套件

CREATE OR REPLACE PACKAGE TEST_PKG AS
  PROCEDURE RUN_TEST;
END;

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS
  PROCEDURE RUN_TEST AS
  BEGIN
    -- 呼叫更新、驗證日誌等測試邏輯
    NULL;
  END;
END;