Oracle PL/SQL 入門實戰：從建表、資料初始化、複合查詢更新到觸發器審計與套件測試

案例需求

• 兩張表：`emp` (員工)、`dept` (部門)。
• 建表、各寫入 5 筆測試資料。
• 「技術部」員工薪水漲 20%。
• 建立薪水變動審計日誌。
• 建立測試套件。

1. 建表

CREATE TABLE emp (
    emp_id     NUMBER(5) PRIMARY KEY,
    emp_name   VARCHAR2(20),
    emp_salary NUMBER(4)
);

CREATE TABLE dept (
    dept_id   NUMBER(3) PRIMARY KEY,
    dept_name VARCHAR2(20),
    emp_id    NUMBER(5) REFERENCES emp(emp_id)
);

2. 資料初始化：參數化預存程序

CREATE OR REPLACE PROCEDURE ins_emp (
    p_id NUMBER, p_name VARCHAR2, p_sal NUMBER
) AS BEGIN
    INSERT INTO emp VALUES (p_id, p_name, p_sal);
END;
/

CREATE OR REPLACE PROCEDURE ins_dept (
    p_did NUMBER, p_dname VARCHAR2, p_eid NUMBER
) AS BEGIN
    INSERT INTO dept VALUES (p_did, p_dname, p_eid);
END;
/

BEGIN
    ins_emp(10000, '張三', 4000);
    ins_emp(10001, '李四', 2300);
    ins_emp(10002, '王五', 3500);
    ins_emp(10003, '趙六', 3500);
    ins_emp(10004, '錢七', 3500);

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

3. 複合查詢更新：指定部門加薪

CREATE OR REPLACE PROCEDURE raise_salary_by_dept (p_dept_name VARCHAR2) AS
BEGIN
    UPDATE emp
    SET emp_salary = emp_salary * 1.2
    WHERE emp_id IN (
        SELECT e.emp_id
        FROM emp e JOIN dept d ON e.emp_id = d.emp_id
        WHERE d.dept_name = p_dept_name
    );
END;
/

EXEC raise_salary_by_dept('技術部');

4. 觸發器：薪水變動審計日誌

CREATE TABLE salary_audit (
    audit_id    NUMBER GENERATED ALWAYS AS IDENTITY,
    emp_id      NUMBER(5),
    old_salary  NUMBER(4),
    new_salary  NUMBER(4),
    change_date DATE DEFAULT SYSDATE,
    changed_by  VARCHAR2(30) DEFAULT USER
);

CREATE OR REPLACE TRIGGER trg_emp_salary_audit
BEFORE UPDATE OF emp_salary ON emp
FOR EACH ROW
BEGIN
    IF :OLD.emp_salary != :NEW.emp_salary THEN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary)
        VALUES (:OLD.emp_id, :OLD.emp_salary, :NEW.emp_salary);
    END IF;
END;
/

5. 測試套件

CREATE OR REPLACE PACKAGE pkg_emp_test AS
    PROCEDURE test_raise_salary;
    PROCEDURE test_audit_log;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp_test AS
    PROCEDURE test_raise_salary IS
        v_sal_before NUMBER;
        v_sal_after  NUMBER;
    BEGIN
        SELECT emp_salary INTO v_sal_before FROM emp WHERE emp_id = 10000;
        raise_salary_by_dept('技術部');
        SELECT emp_salary INTO v_sal_after FROM emp WHERE emp_id = 10000;
        ASSERT(v_sal_after = v_sal_before * 1.2, '薪水應漲 20%');
    END;

    PROCEDURE test_audit_log IS
        v_cnt NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_cnt FROM salary_audit WHERE emp_id = 10000;
        ASSERT(v_cnt = 1, '應產生一筆審計記錄');
    END;
END;
/

關鍵知識點總結

• %TYPE / %ROWTYPE：錨定資料型別，結構變更自動適應。
• 隱性游標屬性：`SQL%ROWCOUNT`、`SQL%FOUND`、`SQL%NOTFOUND` 判斷 DML 影響。
• 觸發器時機：`BEFORE` 適合驗證/審計，`AFTER` 適合後續動作（如發送通知）。
• 套件：規格與主體分離、封裝相關程序/函數/游標/型別、支援多載、狀態保持（Session 級）。
• 單元測試：`utPLSQL` / `Oracle SQL Developer Unit Testing` 可自動化。