mysql存储过程模板

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_test`(IN `nodeCode` varchar(100),IN `id` varchar(36))
BEGIN
DECLARE taskId varchar(100) DEFAULT '';
DECLARE spUserCode varchar(255) DEFAULT '';
DECLARE Done INT DEFAULT 0;

DECLARE test_CURSOR CURSOR FOR
select DISTINCT rh.task_id_,assignee_ from act_ru_variable rv inner join act_hi_actinst rh
on rv.PROC_INST_ID_=rh.PROC_INST_ID_ and rv.execution_id_=rh.execution_id_
where rh.**=id
and rv.name_='assignee' and rh.act_id_=`nodeCode`;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN test_CURSOR;
FETCH NEXT FROM test_CURSOR into taskId,spUserCode;
REPEAT
IF NOT Done THEN

create temporary table if not exists tmpTable
(
id varchar(60),
type varchar(30)
) ENGINE = MEMORY;

/*删除数据*/
INSERT into tmpTable (id ,type ) select
logid ,'workflowlog' from test

DELETE from test where LogID in (
SELECT id from tmpTable where type='workflowlog'
);

END IF;

FETCH NEXT FROM test_CURSOR INTO taskId,spUserCode;
UNTIL Done END REPEAT;
CLOSE test_CURSOR;

#销毁内存表
DROP TABLE IF EXISTS tmpTable;

END