MySQL事务处理
时间：2021-07-01 10:21:17
帮助过：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
MySQL事务处理
标签：
