Oracle PL/SQL 實戰入門：從建表、加薪邏輯、觸發器日誌到測試包開發

結合「員工/部門」雙表案例，完整演示 PL/SQL 核心能力：DDL 建表、存儲過程批量寫入、複合查詢更新、觸發器審計、Package 測試框架封裝。

案例需求與知識點對應

1. 建立 emp/dept 表並寫入 5 筆資料 → 基礎 SQL 與存儲過程。
2. 「技術部」員工薪資漲 20% → 複合查詢（子查詢/關聯更新）。
3. 薪資變動審計日誌 → BEFORE INSERT/UPDATE/DELETE 觸發器，捕捉 :OLD/:NEW。
4. 建立測試套件 → Package 封裝（規格/主體/初始化），採「中間表記錄變數」法。

核心代碼實現要點

1. 參數化存儲過程寫入測試資料

CREATE OR REPLACE PROCEDURE ins_table_emp(p_emp_id NUMBER, p_emp_name VARCHAR2, p_emp_salary NUMBER) AS
BEGIN
  INSERT INTO emp VALUES (p_emp_id, p_emp_name, p_emp_salary);
END;

調用區塊批量插入 5 筆員工與部門資料。

2. 彈性加薪存儲過程

CREATE OR REPLACE PROCEDURE add_salary(p_dept_name VARCHAR2) AS
BEGIN
  UPDATE emp
  SET emp_salary = emp_salary * 1.2
  WHERE emp_id IN (SELECT emp_id FROM emp, dept WHERE emp.emp_id = dept.emp_id AND dept.dept_name = p_dept_name);
END;

以部門名稱為參數，提升複用性。

3. 觸發器審計薪資變動

CREATE OR REPLACE TRIGGER print_salary_change
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
DECLARE
  salary_balance NUMBER;
BEGIN
  salary_balance := :NEW.salary - :OLD.salary;
  DBMS_OUTPUT.PUT_LINE('Old: ' || :OLD.salary);
  DBMS_OUTPUT.PUT_LINE('New: ' || :NEW.salary);
  DBMS_OUTPUT.PUT_LINE('Diff: ' || TO_CHAR(salary_balance));
END;

:OLD/:NEW 為觸發器特有偽記錄，觸發器宣告區需 DECLARE。

4. 測試 Package：Debug 封裝

CREATE OR REPLACE PACKAGE debug AS
  PROCEDURE debug(p_desc VARCHAR2, p_val VARCHAR2);
  PROCEDURE reset;
  v_line_no NUMBER;
END debug;

CREATE OR REPLACE PACKAGE BODY debug AS
  PROCEDURE debug(p_desc VARCHAR2, p_val VARCHAR2) IS
  BEGIN
    INSERT INTO debug_table VALUES (v_line_no, p_desc, p_val);
    v_line_no := v_line_no + 1;
  END;
  PROCEDURE reset IS BEGIN v_line_no := 1; DELETE FROM debug_table; END;
BEGIN reset; END debug;  -- 初始化

Package 概念類似 OO 類別，封裝變數與程序，提升模組化與效能。測試三法：DBMS_OUTPUT、中間表記錄、異常捕獲；本案採中間表法。