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;（弃用：使用start transaction）23 -- 开启事务24 START TRANSACTION;25 -- 打开游标26 OPEN cur;27 read_loop:LOOP28 -- 提取游标里的数据29 FETCH cur INTO v_employee_id;30 -- 声明结束的时候31 IF done=1 or MSG=1 THEN32 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 then36 -- 根据员工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