mysql借助存储过程使用游标遍历并修改数据
分三步执行:1、删除已存在的存储过程,2、创建存储过程,3、执行存储过程。
DROP PROCEDURE IF EXISTS flow_menu;
CREATE PROCEDURE flow_menu()
BEGIN
DECLARE s int DEFAULT 0;
DECLARE F_GUID1 varchar(400);
DECLARE TASK_USER1 varchar(60);
DECLARE OBJ_GUID1 varchar(60);
DECLARE OP_TIME1 decimal(30,0);
DECLARE FLOW_ID1 varchar(100);
DECLARE MDL_ID1 varchar(30);
DECLARE flow_task CURSOR FOR select F_GUID,TASK_USER,OBJ_GUID,OP_TIME ,FLOW_ID,MDL_ID from FLOW_TASK2USER_LIST where F_GUID like '%S_3%' and F_GUID like '%10002002%' and FLOW_ID = 'JCSK_BHGL_FLOW' GROUP BY OBJ_GUID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
open flow_task;
fetch flow_taskinto F_GUID1,TASK_USER1,OBJ_GUID1,OP_TIME1,FLOW_ID1,MDL_ID1;
while s<>1 do
delete from FLOW_TASK2USER_LIST where OBJ_GUID1 = OBJ_GUID and TASK_USER1 = TASK_USER;
INSERT INTO FLOW_TASK2USER_LIST (F_GUID,TASK_USER,OBJ_GUID,OP_TIME ,FLOW_ID,MDL_ID) VALUES (F_GUID1,'username',OBJ_GUID1 ,OP_TIME1 ,FLOW_ID1 ,MDL_ID1);
fetch flow_task into F_GUID1,TASK_USER1,OBJ_GUID1,OP_TIME1,FLOW_ID1,MDL_ID1;
end while;
close flow_task;
END;
call flow_menu();