目錄mysql雙游標嵌套循環mysql游標與嵌套循環游標嵌套遍歷測試總結mysql雙游標嵌套循環

系統要上線新功能，引入了BPMN流程引擎，引入該引擎后就會涉及到歷史數據處理問題，因為系統已經生成了上千條業務數據，由于不是靠BPMN引擎流轉的，所以需要將業務數據和流程引擎關聯起來，有多少條在執行的業務數據，就生成多少條流程實例，我們的流程引擎使用的是activiti流程引擎框架；

其中一塊的歷史數據處理是要查詢出符合條件的歷史數據，然后遍歷循環業務，業務數據中有個處理人的字段是多人的，要把這個多人的以逗號分隔開，有幾人再次循環幾次；

廢話不多說，直接上代碼：

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表：

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; -- 開啟游標2loop_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; -- 關閉游標2SET done = 0; -- important -- ------------------------------------------------------------------------------------ END LOOP loop_1; CLOSE cursor_user1; -- 關閉游標1END測試

user表數據：

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; -- 開啟游標2loop_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; -- 關閉游標2SET done = 0; -- important -- ------------------------------------------------------------------------------------ END LOOP loop_1; CLOSE cursor_user1; -- 關閉游標1SELECT result; END

執行結果：

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持好吧啦網。