当前位置：首页 > 数据库 > Mysql > 详细页面

mysql双游标嵌套循环方式

时间：2023-10-29来源：系统城装机大师作者：佚名

mysql双游标嵌套循环

系统要上线新功能，引入了BPMN流程引擎，引入该引擎后就会涉及到历史数据处理问题，因为系统已经生成了上千条业务数据，由于不是靠BPMN引擎流转的，所以需要将业务数据和流程引擎关联起来，有多少条在执行的业务数据，就生成多少条流程实例，我们的流程引擎使用的是activiti流程引擎框架；

其中一块的历史数据处理是要查询出符合条件的历史数据，然后遍历循环业务，业务数据中有个处理人的字段是多人的，要把这个多人的以逗号分隔开，有几人再次循环几次；

废话不多说，直接上代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90 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(),'-','');
set @startUserId= startUserId_ ;
-- 运行时实例
insert into act_ru_execution (id_, rev_, proc_inst_id_, business_key_, parent_id_, proc_def_id_, super_exec_, root_proc_inst_id_, act_id_, is_active_, is_concurrent_, is_scope_, is_event_scope_, is_mi_root_, suspension_state_, cached_ent_state_, tenant_id_, name_, start_time_, start_user_id_, lock_time_, is_count_enable_, evt_subscr_count_, task_count_, job_count_, timer_job_count_, susp_job_count_, deadletter_job_count_, var_count_, id_link_count_ )
values (@mainProcessId,1,@mainProcessId,null,null,@procDefId,null,@mainProcessId,null,1,0,1,0,0,1,null,'',@processInstanceName ,sysdate(),@startUserId,null,0,0,0,0,0,0,0,0,0);
set @middleProcessId = replace(uuid(),'-','');
set @middleProcessActKey = 'nonStandardApprove';
insert into act_ru_execution (id_, rev_, proc_inst_id_, business_key_, parent_id_, proc_def_id_, super_exec_, root_proc_inst_id_, act_id_, is_active_, is_concurrent_, is_scope_, is_event_scope_, is_mi_root_, suspension_state_, cached_ent_state_, tenant_id_, name_, start_time_, start_user_id_, lock_time_, is_count_enable_, evt_subscr_count_, task_count_, job_count_, timer_job_count_, susp_job_count_, deadletter_job_count_, var_count_, id_link_count_ )
values (@middleProcessId,1,@mainProcessId,null,@mainProcessId,@procDefId,null,@mainProcessId,@middleProcessActKey,0,0,0,0,1,1,null,'',null ,sysdate(),null,null,0,0,0,0,0,0,0,0,0);
-- 当前非标审批环节的处理人数
select count(1) into assigineeCount_ from (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 ) g;
select h.approve_handlers into users from rp_nonstandard_approve h where h.status = 1 and h.requirementID=businessMainDataId;
-- 中间运行时变量
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'interger','nrOfActiveInstances',@middleProcessId,@mainProcessId,null,null,null,assigineeCount_ ,assigineeCount_ ,null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'interger','nrOfCompletedInstances',@middleProcessId,@mainProcessId,null,null,null,0,0,null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'interger','nrOfInstances',@middleProcessId,@mainProcessId,null,null,null,assigineeCount_ ,assigineeCount_ ,null) ;
-- 主运行是变量
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'long','requirementId',@mainProcessId,@mainProcessId,null,null,null,businessMainDataId,businessMainDataId,null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'json','users',@mainProcessId,@mainProcessId,null,null,null,null,concat('[',users,']'),null) ;
-- 历史实例
set @startActId = 'newRequirement';
insert into act_hi_procinst (id_, proc_inst_id_, business_key_, proc_def_id_, start_time_, end_time_, duration_, start_user_id_, start_act_id_, end_act_id_, super_process_instance_id_, delete_reason_,tenant_id_, name_) values(@mainProcessId,@mainProcessId,null,@procDefId,sysdate(),null,null,@startUserId,@startActId,null,null,null,'',@processInstanceName);
open cursor_test2 ;
set @j= 1;
while @j<=assigineeCount_ do
fetch cursor_test2 into runTaskAssignee ;
-- 根据非标处理人数循环插入执行数据
set @taskProcessId = replace(uuid(),'-','');
insert into act_ru_execution (id_, rev_, proc_inst_id_, business_key_, parent_id_, proc_def_id_, super_exec_, root_proc_inst_id_, act_id_, is_active_, is_concurrent_, is_scope_, is_event_scope_, is_mi_root_, suspension_state_, cached_ent_state_, tenant_id_, name_, start_time_, start_user_id_, lock_time_, is_count_enable_, evt_subscr_count_, task_count_, job_count_, timer_job_count_, susp_job_count_, deadletter_job_count_, var_count_, id_link_count_ )
values (@taskProcessId,1,@mainProcessId,null,@middleProcessId,@procDefId,null,@mainProcessId,@middleProcessActKey,1,0,0,0,0,1,null,'',null ,sysdate(),null,null,0,0,0,0,0,0,0,0,0);
-- 根据非标处理人数循环插入任务数据
-- 任务数据
set @runTaskId = replace(uuid(),'-','');
set @runTaskKey= 'nonStandardApprove';
set @runTaskName= '审批';
set @runTaskAssignee = runTaskAssignee;
insert into act_ru_task (id_,rev_,execution_id_,proc_inst_id_,proc_def_id_,name_,parent_task_id_,description_,task_def_key_,owner_,assignee_,delegation_,priority_,create_time_,due_date_category_,suspension_state_,tenant_id_,form_key_,claim_time_) values (@runTaskId,1,@taskProcessId,@mainProcessId,@procDefId,@runTaskName,NULL,NULL,@runTaskKey,NULL,@runTaskAssignee,NULL,50,sysdate(),null,null,1,'',null,null);
-- 根据非标处理人数循环插入任务
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'string','user',@taskProcessId,@mainProcessId,null,null,null,null,@runTaskAssignee,null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'interger','loopCounter',@taskProcessId,@mainProcessId,null,null,null,0,'0',null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'string','user',@taskProcessId,@mainProcessId,@runTaskId ,null,null,null,@runTaskAssignee,null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'interger','loopCounter',@taskProcessId,@mainProcessId,@runTaskId,null,null,0,'0',null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'interger','nrOfActiveInstances',@taskProcessId,@mainProcessId,@runTaskId,null,null,assigineeCount_ ,assigineeCount_ ,null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'interger','nrOfCompletedInstances',@taskProcessId,@mainProcessId,@runTaskId,null,null,0,'0',null) ;
insert into act_ru_variable (id_, rev_, type_, name_, execution_id_, proc_inst_id_, task_id_, bytearray_id_, double_, long_, text_, text2_) values (replace(uuid(),'-',''),1,'interger','nrOfInstances',@taskProcessId,@mainProcessId,@runTaskId,null,null,assigineeCount_ ,assigineeCount_ ,null) ;
set @j=@j+1;
end while;
close cursor_test2 ;
set @businessMainDataId = businessMainDataId;
set @businessMainDataTable = 't_original_requirement';
set @businessCode = businessCode ;
insert into rp_pe_business_instance (business_main_data_id,business_main_data_table_name,process_instance_id,version,status,create_by,create_time,update_by,update_by,update_time,is_business_running_process,business_code) values(@businessMainDataId,@businessMainDataTable,@mainProcessId,replace(uuid(),'-',''),1,null,sysdate(),null,sysdate(),1,@businessCode);
set @i=@i+1;
set result_ =result_ +1;
end while;
close cursor_test1 ;
select concat('数据处理条数：',result_ ) as resultdata;
end;

注意事项：

游标的脚本是在调用open时才会执行，比如我的游标cursor_test2 中有用到businessMainDataId 变量，但是在声明游标时这个变量是没有值的，fetch cursor_test1 into startUserId_ ,businessMainDataId ,businessCode 方法执行时才会把这个变量赋值，之后再执行open cursor_test2 时，businessMainDataId 已经有了值，这时就是根据此时的businessMainDataId 查询的审批数据；

mysql游标与嵌套循环

游标嵌套遍历

user表：

在这里插入图片描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE user1 VARCHAR(100);
DECLARE user2 VARCHAR(100);
DECLARE done INT DEFAULT 0;
DECLARE cursor_user1 CURSOR FOR(SELECT `name` FROM `user`); -- 定于游标1
DECLARE cursor_user2 CURSOR FOR(SELECT `name` FROM `user`); -- 定义游标2
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 结束标识
OPEN cursor_user1; -- 开启游标1
loop_1: LOOP -- 外层循环
-- -------------------------------------------------------------------------------------
# 尝试从游标1指向的数据集合获取数据，如果集合已经遍历完毕，done的值被设置为1。
FETCH cursor_user1 INTO user1;
IF done = 1 THEN LEAVE loop_1; END IF; -- 外层循环结束条件
OPEN cursor_user2; -- 开启游标2
loop_2: LOOP -- 内层循环
-- -----------------------------------------------------------------------------
# 尝试从游标2指向的数据集合获取数据，如果集合已经遍历完毕，done的值被设置为1。
FETCH cursor_user2 INTO user2;
IF done = 1 THEN LEAVE loop_2; END IF; -- 内层循环结束条件
# do something...
-- -----------------------------------------------------------------------------
END LOOP loop_2;
CLOSE cursor_user2; -- 关闭游标2
SET done = 0; -- important
-- ------------------------------------------------------------------------------------
END LOOP loop_1;
CLOSE cursor_user1; -- 关闭游标1
END

测试

user表数据：

在这里插入图片描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE result VARCHAR(2000) DEFAULT 'result';
DECLARE user1 VARCHAR(100);
DECLARE user2 VARCHAR(100);
DECLARE done INT DEFAULT 0;
DECLARE cursor_user1 CURSOR FOR(SELECT `name` FROM `user`); -- 定于游标1
DECLARE cursor_user2 CURSOR FOR(SELECT `name` FROM `user`); -- 定义游标2
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 结束标识
OPEN cursor_user1; -- 开启游标1
loop_1: LOOP -- 外层循环
-- -------------------------------------------------------------------------------------
# 尝试从游标1指向的数据集合获取数据，如果集合已经遍历完毕，done的值被设置为1。
FETCH cursor_user1 INTO user1;
IF done = 1 THEN LEAVE loop_1; END IF; -- 外层循环结束条件
OPEN cursor_user2; -- 开启游标2
loop_2: LOOP -- 内层循环
-- -----------------------------------------------------------------------------
# 尝试从游标2指向的数据集合获取数据，如果集合已经遍历完毕，done的值被设置为1。
FETCH cursor_user2 INTO user2;
IF done = 1 THEN LEAVE loop_2; END IF; -- 内层循环结束条件
# do something...
SET result = CONCAT(result,';',user1,'-',user2);
-- -----------------------------------------------------------------------------
END LOOP loop_2;
CLOSE cursor_user2; -- 关闭游标2
SET done = 0; -- important
-- ------------------------------------------------------------------------------------
END LOOP loop_1;
CLOSE cursor_user1; -- 关闭游标1
SELECT result;
END