從薪資最低的員工開始,每人漲薪 10%,但總薪資不得超過 60,000。輸出漲薪人數與漲薪後總薪資。
SET SERVEROUTPUT ON;
DECLARE
-- 游標:按薪資遞增排序
CURSOR c_emp IS
SELECT empno, sal FROM emp ORDER BY sal;
v_empno emp.empno%TYPE;
v_sal emp.sal%TYPE;
v_total_sal NUMBER := 0; -- 當前總薪資
v_cnt NUMBER := 0; -- 漲薪人數
v_limit CONSTANT NUMBER := 60000; -- 上限
BEGIN
-- 初始化總薪資
SELECT SUM(sal) INTO v_total_sal FROM emp;
OPEN c_emp;
LOOP
EXIT WHEN v_total_sal > v_limit; -- 已超限,停止
FETCH c_emp INTO v_empno, v_sal;
EXIT WHEN c_emp%NOTFOUND; -- 無更多員工
-- 預估漲薪後總薪資
IF v_total_sal + v_sal * 0.1 > v_limit THEN
EXIT; -- 即將超限,停止
END IF;
-- 執行漲薪
UPDATE emp SET sal = sal * 1.1 WHERE empno = v_empno;
v_total_sal := v_total_sal + v_sal * 0.1;
v_cnt := v_cnt + 1;
END LOOP;
CLOSE c_emp;
COMMIT;
DBMS_OUTPUT.PUT_LINE('漲薪後總薪資: ' || v_total_sal);
DBMS_OUTPUT.PUT_LINE('漲薪人數: ' || v_cnt);
END;
/
ORDER BY sal ASC 確保從最低薪開始漲。UPDATE 前先判斷 v_total_sal + v_sal*0.1 > limit,避免超限後再回滾。v_total_sal 即時累加漲幅,不需重新查表。EXIT WHEN v_total_sal > v_limit 處理初始總薪資已超限的極端情況。MERGE INTO emp t
USING (
SELECT empno, sal,
SUM(sal) OVER (ORDER BY sal ROWS UNBOUNDED PRECEDING) AS running_total
FROM emp
) s ON (t.empno = s.empno)
WHEN MATCHED THEN
UPDATE SET sal = sal * 1.1
WHERE s.running_total + sal*0.1 <= 60000; -- 需配合窗口函數邏輯調整
SAVEPOINT、錯誤處理、稽核日誌。