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)
);

2. 建立資料載入程序

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 ins_table_emp;
/

CREATE OR REPLACE PROCEDURE ins_table_dept (
    p_dept_id   NUMBER,
    p_dept_name VARCHAR2,
    p_emp_id    NUMBER
) AS
BEGIN
    INSERT INTO dept VALUES (p_dept_id, p_dept_name, p_emp_id);
END ins_table_dept;
/

BEGIN
    ins_table_emp(10000, '王大明', 4000);
    ins_table_emp(10001, '李小美', 2300);
    ins_table_emp(10002, '張建國', 3500);
    ins_table_emp(10003, '陳雅婷', 3500);
    ins_table_emp(10004, '林志強', 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;
/

3. 薪資調整邏輯 (複合查詢)

UPDATE emp e
SET emp_salary = emp_salary * 1.2
WHERE emp_id IN (
    SELECT emp_id FROM dept WHERE dept_name = '技術部'
);

4. 建立薪資變動日誌表與觸發器

CREATE TABLE salary_log (
    log_id      NUMBER GENERATED ALWAYS AS IDENTITY,
    emp_id      NUMBER,
    old_salary  NUMBER,
    new_salary  NUMBER,
    change_date DATE DEFAULT SYSDATE,
    changed_by  VARCHAR2(30) DEFAULT USER
);

CREATE OR REPLACE TRIGGER trg_salary_change
AFTER 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;
/

5. 開發測試套件

CREATE OR REPLACE PACKAGE test_pkg AS
    PROCEDURE test_salary_increase;
    PROCEDURE test_log_record;
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS
    PROCEDURE test_salary_increase IS
        v_sal_before NUMBER;
        v_sal_after  NUMBER;
    BEGIN
        SELECT emp_salary INTO v_sal_before FROM emp WHERE emp_id = 10000;
        -- 執行調薪邏輯 (此處略)
        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_log_record IS
        v_cnt NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_cnt FROM salary_log WHERE emp_id = 10000;
        ASSERT(v_cnt = 1, '日誌未正確記錄');
    END;
END test_pkg;
/

註：ASSERT 為示意性程序，實際可用 DBMS_ASSERT 或自訂例外處理。

結語

本案例串聯 DML、複合查詢、觸發器、套件開發與單元測試，展示 PL/SQL 解決完整業務問題的能力。實務上再加入交易控制、錯誤日誌、效能基準測試，即可落地生產環境。

• 首頁
• 上一頁
• 1
• 2
• 3
• 下一頁
• 尾頁
• 共3頁