複雜業務刪除常涉及多表級聯、條件判斷、循環處理。本文提供生產級存儲過程事務範本：START TRANSACTION 顯式開啟、游標遍歷關聯數據、SQLEXCEPTION 統一捕獲、MSG 標記位決策提交/回滾、資源釋放保證，可直接套用至級聯刪除、批量狀態變更等場景。
DELIMITER $$
CREATE PROCEDURE `cpm_project_delete`(IN p_project_id INT)
BEGIN
-- 1. 變量聲明
DECLARE v_employee_id INT;
DECLARE v_projects INT;
DECLARE done INT DEFAULT 0; -- 游標結束標記
DECLARE msg INT DEFAULT 0; -- 異常標記位 (0=正常, 1=異常)
-- 2. 定義游標：查詢該項目下所有員工
DECLARE cur CURSOR FOR
SELECT employee_id FROM cpm_emp_registration WHERE project_id = p_project_id;
-- 3. 綁定條件處理器
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 游標結束
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET msg = 1; -- 任意 SQL 異常
-- 4. 顯式開啟事務（禁用 autocommit）
START TRANSACTION;
-- 5. 打開游標並循環
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_employee_id;
IF done = 1 OR msg = 1 THEN
LEAVE read_loop;
END IF;
-- 6. 業務邏輯：檢查員工是否僅屬於此項目
SELECT COUNT(project_id) INTO v_projects
FROM cpm_emp_registration WHERE employee_id = v_employee_id;
IF v_projects = 1 THEN
-- 僅屬於此項目 → 級聯刪除員工主檔與證書
DELETE FROM cpm_emp_registration WHERE employee_id = v_employee_id;
DELETE FROM cpm_employees WHERE employee_id = v_employee_id;
DELETE FROM cpm_emp_certificates WHERE employee_id = v_employee_id;
ELSE
-- 屬於多個項目 → 僅刪除關聯記錄
DELETE FROM cpm_emp_registration WHERE employee_id = v_employee_id AND project_id = p_project_id;
END IF;
END LOOP;
-- 7. 關閉游標
CLOSE cur;
-- 8. 刪除項目主檔與責任公司關聯
DELETE FROM cpm_projects WHERE project_id = p_project_id;
DELETE FROM cpm_projects_duty_companies WHERE project_id = p_project_id;
-- 9. 統一提交/回滾決策
IF msg = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$
DELIMITER ;
| 模式 | 代碼體現 | 作用 |
|---|---|---|
| 顯式事務邊界 | START TRANSACTION / COMMIT/ROLLBACK | 避免自動提交導致部分成功、部分失敗 |
| 標記位決策 | DECLARE msg INT DEFAULT 0 + HANDLER SET msg=1 + IF msg=1 ROLLBACK | 在不支持 UNDO HANDLER 的 MySQL 中實現異常回滾 |
| 游標資源管理 | OPEN → FETCH → CLOSE 完整三部曲 | 防止游標洩漏導致「Too many open cursors」 |
| 雙重退出條件 | IF done=1 OR msg=1 THEN LEAVE | 正常遍歷結束與異常中斷同一退出路徑 |
| 條件分支業務 | IF v_projects=1 THEN ... ELSE ... END IF | 單項目員工級聯刪除 vs 多項目員工僅解除關聯 |
-- 1. 保存點：大循環中每 N 次設置保存點，異常時回滾到最近保存點而非整個事務
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET msg = 1;
ROLLBACK TO SAVEPOINT sp_batch; -- 需在循環前 SAVEPOINT sp_batch
END;
-- 2. 死鎖重試（應用層更合適，存儲過程內難實現指數退避）
-- 建議：應用層捕獲 1213 錯誤碼，指數退避重試 CALL
-- 3. 審計日誌：關鍵操作寫入 audit_log 表
INSERT INTO audit_log (op_type, target_id, operator, ts)
VALUES ('DELETE_PROJECT', p_project_id, CURRENT_USER(), NOW());
DECLARE 必須置於 BEGIN 後、所有語句前，順序：變量 → 游標 → 條件處理器。SELECT 不能包含 INTO 子句，FETCH ... INTO 才賦值。CONTINUE HANDLER FOR SQLEXCEPTION 捕獲所有錯誤，若需區分錯誤碼，改用 DECLARE CONTINUE HANDLER FOR 1213 SET deadlock_flag=1 等具體碼。DDL（CREATE/DROP/ALTER），會隱式提交打破原子性。SELECT @msg; 或通過 OUT 參數返回 msg。這個「事務 + 游標 + 標記位 + 條件分支」範本，是 MySQL 存儲過程處理複雜寫入業務的標準化骨架。建議團隊將其封裝為代碼片段，Code Review 時重點檢查：事務邊界是否清晰、異常路徑是否覆蓋、資源是否釋放、鎖持有時間是否可控。
