mysql雙游標(biāo)嵌套循環(huán)方式
更新時(shí)間：2023年07月06日 10:36:46 作者：tonglei111
這篇文章主要介紹了mysql雙游標(biāo)嵌套循環(huán)方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
mysql雙游標(biāo)嵌套循環(huán)
系統(tǒng)要上線新功能，引入了BPMN流程引擎，引入該引擎后就會(huì)涉及到歷史數(shù)據(jù)處理問(wèn)題，因?yàn)橄到y(tǒng)已經(jīng)生成了上千條業(yè)務(wù)數(shù)據(jù)，由于不是靠BPMN引擎流轉(zhuǎn)的，所以需要將業(yè)務(wù)數(shù)據(jù)和流程引擎關(guān)聯(lián)起來(lái)，有多少條在執(zhí)行的業(yè)務(wù)數(shù)據(jù)，就生成多少條流程實(shí)例，我們的流程引擎使用的是activiti流程引擎框架；
其中一塊的歷史數(shù)據(jù)處理是要查詢出符合條件的歷史數(shù)據(jù)，然后遍歷循環(huán)業(yè)務(wù)，業(yè)務(wù)數(shù)據(jù)中有個(gè)處理人的字段是多人的，要把這個(gè)多人的以逗號(hào)分隔開(kāi)，有幾人再次循環(huán)幾次；
廢話不多說(shuō)，直接上代碼：
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;
?? ?-- 創(chuàng)建游標(biāo)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;
?? ?-- 創(chuàng)建游標(biāo)2 ,這個(gè)游標(biāo)的創(chuàng)建使用了：將數(shù)據(jù)庫(kù)內(nèi)某個(gè)字段以逗號(hào)分隔的列，拆成一個(gè)數(shù)據(jù)列表，這樣游標(biāo)2就可以遍歷執(zhí)行這個(gè)數(shù)據(jù)集了
?? ?-- 其中語(yǔ)句：select (id-1) as id from rp_nonstandard_approve c order by id asc limit 21 ?是要查出一個(gè)從0開(kāi)始的遞增的連續(xù)數(shù)字，rp_nonstandard_approve 的主鍵id剛好是自增的主鍵，limit 21 是因?yàn)閍.approveHandlers字段最多存20個(gè)人，所以這里要大于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 ;
?? ?-- 游標(biāo)循環(huán)結(jié)束
?? ?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;
?? ?-- 開(kāi)啟游標(biāo)，調(diào)用開(kāi)啟游標(biāo)時(shí)才會(huì)執(zhí)行對(duì)應(yīng)游標(biāo)的查詢方法
?? ?open cursor_test1 ;
?? ?while @i<=requirementCount_ ?do
?? ??? ?-- 將游標(biāo)中的值賦值給申明的變量，順序要和游標(biāo)定義內(nèi)的一致；
?? ??? ?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_ ;
?? ??? ?-- 運(yùn)行時(shí)實(shí)例
?? ??? ?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);
?? ?-- 當(dāng)前非標(biāo)審批環(huán)節(jié)的處理人數(shù)
?? ?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;
?? ?-- 中間運(yùn)行時(shí)變量
?? ?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) ;
?? ?-- 主運(yùn)行是變量
?? ?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) ;
?? ?-- 歷史實(shí)例
?? ?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 ;
?? ??? ?-- 根據(jù)非標(biāo)處理人數(shù)循環(huán)插入執(zhí)行數(shù)據(jù)
?? ??? ?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);
?? ?-- 根據(jù)非標(biāo)處理人數(shù)循環(huán)插入任務(wù)數(shù)據(jù)
?? ?-- 任務(wù)數(shù)據(jù)
?? ?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);
?? ?-- 根據(jù)非標(biāo)處理人數(shù)循環(huán)插入任務(wù)?
?? ?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('數(shù)據(jù)處理?xiàng)l數(shù)：',result_ ) as resultdata;
end;注意事項(xiàng)：
游標(biāo)的腳本是在調(diào)用open時(shí)才會(huì)執(zhí)行，比如我的游標(biāo)cursor_test2 中有用到businessMainDataId 變量，但是在聲明游標(biāo)時(shí)這個(gè)變量是沒(méi)有值的，fetch cursor_test1 into startUserId_ ,businessMainDataId ,businessCode 方法執(zhí)行時(shí)才會(huì)把這個(gè)變量賦值，之后再執(zhí)行open cursor_test2 時(shí)，businessMainDataId 已經(jīng)有了值，這時(shí)就是根據(jù)此時(shí)的businessMainDataId 查詢的審批數(shù)據(jù)；
mysql游標(biāo)與嵌套循環(huán)
游標(biāo)嵌套遍歷
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`); -- 定于游標(biāo)1
DECLARE cursor_user2 CURSOR FOR(SELECT `name` FROM `user`); -- 定義游標(biāo)2
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 結(jié)束標(biāo)識(shí)
OPEN cursor_user1; -- 開(kāi)啟游標(biāo)1
loop_1: LOOP -- 外層循環(huán)
-- -------------------------------------------------------------------------------------
# 嘗試從游標(biāo)1指向的數(shù)據(jù)集合獲取數(shù)據(jù)，如果集合已經(jīng)遍歷完畢，done的值被設(shè)置為1。
FETCH cursor_user1 INTO user1;
IF done = 1 THEN LEAVE loop_1; END IF; -- 外層循環(huán)結(jié)束條件
OPEN cursor_user2; -- 開(kāi)啟游標(biāo)2
loop_2: LOOP -- 內(nèi)層循環(huán)
-- -----------------------------------------------------------------------------
# 嘗試從游標(biāo)2指向的數(shù)據(jù)集合獲取數(shù)據(jù)，如果集合已經(jīng)遍歷完畢，done的值被設(shè)置為1。
FETCH cursor_user2 INTO user2;
IF done = 1 THEN LEAVE loop_2; END IF; -- 內(nèi)層循環(huán)結(jié)束條件
# do something...
-- -----------------------------------------------------------------------------
END LOOP loop_2;
CLOSE cursor_user2; -- 關(guān)閉游標(biāo)2
SET done = 0; -- important
-- ------------------------------------------------------------------------------------
END LOOP loop_1;
CLOSE cursor_user1; -- 關(guān)閉游標(biāo)1
END測(cè)試
user表數(shù)據(jù)：
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`); -- 定于游標(biāo)1
DECLARE cursor_user2 CURSOR FOR(SELECT `name` FROM `user`); -- 定義游標(biāo)2
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 結(jié)束標(biāo)識(shí)
OPEN cursor_user1; -- 開(kāi)啟游標(biāo)1
loop_1: LOOP -- 外層循環(huán)
-- -------------------------------------------------------------------------------------
# 嘗試從游標(biāo)1指向的數(shù)據(jù)集合獲取數(shù)據(jù)，如果集合已經(jīng)遍歷完畢，done的值被設(shè)置為1。
FETCH cursor_user1 INTO user1;
IF done = 1 THEN LEAVE loop_1; END IF; -- 外層循環(huán)結(jié)束條件
OPEN cursor_user2; -- 開(kāi)啟游標(biāo)2
loop_2: LOOP -- 內(nèi)層循環(huán)
-- -----------------------------------------------------------------------------
# 嘗試從游標(biāo)2指向的數(shù)據(jù)集合獲取數(shù)據(jù)，如果集合已經(jīng)遍歷完畢，done的值被設(shè)置為1。
FETCH cursor_user2 INTO user2;
IF done = 1 THEN LEAVE loop_2; END IF; -- 內(nèi)層循環(huán)結(jié)束條件
# do something...
SET result = CONCAT(result,';',user1,'-',user2);
-- -----------------------------------------------------------------------------
END LOOP loop_2;
CLOSE cursor_user2; -- 關(guān)閉游標(biāo)2
SET done = 0; -- important
-- ------------------------------------------------------------------------------------
END LOOP loop_1;
CLOSE cursor_user1; -- 關(guān)閉游標(biāo)1
SELECT result;
END執(zhí)行結(jié)果：
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
您可能感興趣的文章:
相關(guān)文章
MySQL數(shù)據(jù)庫(kù)十大優(yōu)化技巧
WEB開(kāi)發(fā)者不光要解決程序的效率問(wèn)題，對(duì)數(shù)據(jù)庫(kù)的快速訪問(wèn)和相應(yīng)也是一個(gè)大問(wèn)題。希望本文能對(duì)大家掌握MySQL優(yōu)化技巧有所幫助。2011-03-03
避免MySQL中的隱式轉(zhuǎn)換的方法小結(jié)
在 MySQL 中,隱式轉(zhuǎn)換可能導(dǎo)致索引失效、結(jié)果不符合預(yù)期或性能問(wèn)題,以下是避免隱式轉(zhuǎn)換的具體方法,從表設(shè)計(jì)、查詢編寫(xiě)到配置優(yōu)化,逐步減少隱式轉(zhuǎn)換的發(fā)生,具體操作過(guò)程跟隨小編一起看看吧2025-04-04
mysql 常用數(shù)據(jù)庫(kù)語(yǔ)句 小練習(xí)
一個(gè)mysql小練習(xí) 建表 查詢 修改表 增加字段 刪除字段2009-07-07
只要公司有數(shù)據(jù)團(tuán)隊(duì)的那免不了讓這幫家伙把全公司的數(shù)據(jù)庫(kù)數(shù)據(jù)都摸一遍,但是要是直接把root用戶給了他們有點(diǎn)危險(xiǎn),于是只能給設(shè)權(quán)限,這篇文章主要給大家介紹了關(guān)于如何通過(guò)3個(gè)步驟為Mysql添加只讀賬號(hào)的相關(guān)資料,需要的朋友可以參考下2023-12-12
這篇文章主要介紹了MySQL 重寫(xiě)查詢語(yǔ)句的三種策略，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-05-05
mysql數(shù)據(jù)插入覆蓋和時(shí)間戳的問(wèn)題及解決
這篇文章主要介紹了mysql數(shù)據(jù)插入覆蓋和時(shí)間戳的問(wèn)題及解決，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-03-03
這篇文章主要給大家介紹了關(guān)于如何查看MySQL數(shù)據(jù)文件存放路徑的相關(guān)資料,文中通過(guò)圖文以及代碼示例介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用mysql具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-08-08
MySQL數(shù)據(jù)庫(kù)和Redis緩存一致性的更新策略
本文主要介紹了MySQL數(shù)據(jù)庫(kù)和Redis緩存一致性的更新策略問(wèn)題，文中有詳細(xì)的代碼示例，有需要的朋友可以參考一下2023-04-04
MySQL8.0.23版本的root密碼重置最優(yōu)解法
這篇文章主要介紹了MySQL8.0.23版本的root密碼重置最優(yōu)解法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
MySQL更改默認(rèn)字符集為utf-8的全過(guò)程
這篇文章主要介紹了MySQL更改默認(rèn)字符集為utf-8的全過(guò)程，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12