MySQL transaction processing

1 -- --------------------------------------------------------------------------------2 --Routine DDL3 --note:comments before and after the routine body is not being stored by the server4 -- --------------------------------------------------------------------------------5 DELIMITER $$6 7 CREATEDefiner=' Hap_dev ' @ '%`PROCEDURE' Cpm_project_delete ' (p_project_idinteger)8 BEGIN9 DECLAREv_employee_idINT;Ten DECLAREV_projectsINT; One --Traverse data End Flag A DECLAREDoneINT DEFAULT 0; - --transaction error Mark bit - DECLAREMSGINT DEFAULT 0; the --Cursors - DECLARECurCURSOR  for SELECTemployee_id fromCpm_emp_registerationwhereproject_id=p_project_id; - --binding the end flag to a cursor - DECLARE CONTINUEHANDLER for  notFOUNDSETDone= 1; + --bind an error flag - DECLARE CONTINUEHANDLER forSQLEXCEPTIONSETMSG= 1; + --Turn off transaction auto-commit A SETAutocommit= 0; at --Open Transaction -STARTTRANSACTION; - --Open Cursor - OPENcur; - Read_loop:loop - --extracting data from a cursor in FETCHCur intov_employee_id; - --at the end of the statement to IFDone=1 orMSG=1  Then + LEAVE Read_loop; - END IF; the SELECT COUNT(project_id) intoV_projects fromCpm_emp_registerationWHEREemployee_id=v_employee_id; * IFV_projects= 1  Then $ --Delete employee data based on employee IDPanax Notoginseng DELETE  fromCpm_emp_registerationWHEREemployee_id=v_employee_id; - DELETE  fromCpm_employeesWHEREemployee_id=v_employee_id; the DELETE  fromCpm_emp_certificatesWHEREemployee_id=v_employee_id; + ELSE  A DELETE  fromCpm_emp_registerationWHEREemployee_id=v_employee_id andproject_id=p_project_id; the END IF; + ENDLOOP; - --Close Cursors $ CLOSEcur; $ --Delete Engineering data based on Project ID - DELETE  fromCpm_projectsWHEREproject_id=p_project_id; - DELETE  fromCpm_projects_duty_companiesWHEREproject_id=p_project_id; the --determines whether a transaction is passed uniformly, commits it, or rolls back - IFMSG= 1  Then ROLLBACK;Wuyi ELSE COMMIT; the END IF; - END

MySQL transaction processing