MySQL事務處理，mysql事務
1 -- --------------------------------------------------------------------------------
2 -- Routine DDL
3 -- Note: comments before and after the routine body will not be stored by the server
4 -- --------------------------------------------------------------------------------
5 DELIMITER $$
6
7 CREATE DEFINER=`hap_dev`@`%` PROCEDURE `cpm_project_delete`(p_project_id integer)
8 BEGIN
9 DECLARE v_employee_id INT;
10 DECLARE v_projects INT;
11 -- 遍歷數據結束標志
12 DECLARE done INT DEFAULT 0;
13 -- 事務出錯標記位
14 DECLARE MSG INT DEFAULT 0;
15 -- 游標
16 DECLARE cur CURSOR FOR SELECT employee_id FROM cpm_emp_registeration where project_id = p_project_id;
17 -- 將結束標志綁定到游標
18 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
19 -- 將出錯標志綁定
20 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET MSG = 1;
21 -- 關閉事務自動提交
22 -- SET AUTOCOMMIT = 0;（棄用）
23 -- 開啟事務
24 START TRANSACTION;
25 -- 打開游標
26 OPEN cur;
27 read_loop:LOOP
28 -- 提取游標裡的數據
29 FETCH cur INTO v_employee_id;
30 -- 聲明結束的時候
31 IF done=1 or MSG=1 THEN
32 LEAVE read_loop;
33 END IF;
34 SELECT COUNT(project_id) into v_projects FROM cpm_emp_registeration WHERE employee_id = v_employee_id;
35 IF v_projects = 1 then
36 -- 根據員工id刪除員工數據
37 DELETE FROM cpm_emp_registeration WHERE employee_id = v_employee_id;
38 DELETE FROM cpm_employees WHERE employee_id = v_employee_id;
39 DELETE FROM cpm_emp_certificates WHERE employee_id = v_employee_id;
40 ELSE
41 DELETE FROM cpm_emp_registeration WHERE employee_id = v_employee_id and project_id = p_project_id;
42 END IF;
43 END LOOP;
44 -- 關閉游標
45 CLOSE cur;
46 -- 根據工程id刪除工程數據
47 DELETE FROM cpm_projects WHERE project_id = p_project_id;
48 DELETE FROM cpm_projects_duty_companies WHERE project_id = p_project_id;
49 -- 判斷事務是否一致通過，是則提交，否則回滾
50 IF MSG = 1 THEN ROLLBACK;
51 ELSE COMMIT;
52 END IF;
53 END
