delimiter // drop procedure if exists procedure_test; create procedure procedure_test() begin declare count_ int default 0; declare requirementCount_ int default 1; declare assigineeCount_ int default 1; declare result_ int default 0; declare startUserId_ int ; declare runTaskAssignee int ; declare businessMainDataId int ; declare businessCode varchar (10); declare users varchar (250); declare process_serial_number int default 0; -- 创建游标1 declare cursor_test1 cursor for select o.commituserId,o.id,o.code from rp_original_requirement o where o.status = 1 and o.currentNode = 16; -- 创建游标2 ,这个游标的创建使用了:将数据库内某个字段以逗号分隔的列,拆成一个数据列表,这样游标2就可以遍历执行这个数据集了 -- 其中语句:select (id-1) as id from rp_nonstandard_approve c order by id asc limit 21 是要查出一个从0开始的递增的连续数字,rp_nonstandard_approve 的主键id刚好是自增的主键,limit 21 是因为a.approveHandlers字段最多存20个人,所以这里要大于20; declare cursor_test2 cursor for select substring_index(substring_index(a.approveHandlers, ',' ,d.id+1), ',' ,-1) as assignee from rp_nonstandard_approve a join ( select (id-1) as id from rp_nonstandard_approve c order by id asc limit 21 ) d on d.id<(length(a.approveHandlers)-length( replace (a.approveHandlers, ',' , '' ))+1) where a.status = 1 and a.requirementID=businessMainDataId ; -- 游标循环结束 declare exit handler for not found close cursor_test1 ; select count (1) into requirementCount_ from rp_original_requirement o where o.status = 1 and o.currentNode = 16; select p.id_ into @procDefId from act_re_prodef p left join act_re_deployment ard on p.deployment_id_= ard.id_ where p.key_= 'nonstandardProcess' order by deploy_time_ desc limit 1; set @i=1; -- 开启游标,调用开启游标时才会执行对应游标的查询方法 open cursor_test1 ; while @i<=requirementCount_ do -- 将游标中的值赋值给申明的变量,顺序要和游标定义内的一致; fetch cursor_test1 into startUserId_ ,businessMainDataId ,businessCode ; set process_serial_number = process_serial_number + 1; set @processInstanceName = concat( 'NSP' ,date_formart(now(), '%Y%m%d%H%i%s' ),lpad(process_serial_number,6,0)); set @mainProcessId=repalce(uuid(), '-' , ''
|