目錄mysql存儲過程多層游標(biāo)循環(huán)嵌套的寫法最近有遇到一個需求最終存儲過程的寫法如下mysql存儲過程循環(huán)中使用游標(biāo)方法及遇到的問題循環(huán)循環(huán)無法正常退出問題總結(jié)mysql存儲過程多層游標(biāo)循環(huán)嵌套的寫法最近有遇到一個需求

將有3級主從關(guān)聯(lián)的表數(shù)據(jù)同步到另外一個有3級主從關(guān)聯(lián)的表中，同步數(shù)據(jù)的表是第3級子表，而關(guān)聯(lián)關(guān)系在第1、2級主表中，由于考慮到到內(nèi)網(wǎng)數(shù)據(jù)庫可能沒法直接訪問的限制，所以沒有用代碼來實(shí)現(xiàn)，而是直接用存儲過程來實(shí)現(xiàn)

最終存儲過程的寫法如下DELIMITER $$USE `myDb`$$DROP PROCEDURE IF EXISTS `syncBomSopFromRouteSop`$$CREATE PROCEDURE `syncBomSopFromRouteSop`(IN tenantId BIGINT(11))BEGIN?? ?#工藝路線ID?? ?DECLARE crId BIGINT(11);?? ?#生產(chǎn)bomID?? ?DECLARE pbrId BIGINT(11);?? ?#工序檔案ID?? ?DECLARE processId VARCHAR(50);?? ?#生產(chǎn)bom工序Id?? ?DECLARE bomProcessId BIGINT(11);?? ?#生產(chǎn)bom數(shù)量?? ?DECLARE productionBomNum INT(5);?? ?#生產(chǎn)bom工序數(shù)量?? ?DECLARE productionBomProcessNum INT(5);?? ?DECLARE i INT(4) DEFAULT 1;?? ?#DECLARE j INT(4) DEFAULT 1;?? ?#定義生產(chǎn)bom游標(biāo)?? ?DECLARE productionBomCursor CURSOR FOR (?? ??? ?SELECT cr_id,pbr_id FROM jgmes_modeling_production_bom_route WHERE tenant_id=tenantId AND delete_flag=0 AND IFNULL(cr_id,'')<>'' ORDER BY cr_id,pbr_id /*測試1條數(shù)據(jù)AND pbr_id=86673*/??? ?);?? ?#定義生產(chǎn)bom工序游標(biāo)?? ?DECLARE bomProcessCursor CURSOR FOR (?? ??? ?SELECT pbp_id bomProcessId,p.p_id processId?? ??? ?FROM jgmes_modeling_production_bom_process pbp??? ??? ?LEFT JOIN jgmes_modeling_process p ON p.p_id=pbp.p_id?? ??? ?LEFT JOIN jgmes_modeling_production_bom_route pbr ON pbr.pbr_id=pbp.pbr_id?? ??? ?WHERE pbp.tenant_id=tenantId AND pbp.delete_flag=0 AND??? ??? ?pbr.tenant_id=tenantId AND pbr.delete_flag=0 AND pbr.pbr_id=pbrId /*測試1條生產(chǎn)bom */?? ?);?? ?#定義生產(chǎn)bom的sop游標(biāo)?? ?/*DECLARE bomSopCursor CURSOR FOR (?? ?);*/?? ?#解決沒有查到數(shù)據(jù)報(bào)：“DECLARE CONTINUE HANDLER FOR NOT FOUND SET @IS_FOUND=0;”的異常的處理方法?? ?DECLARE CONTINUE HANDLER FOR NOT FOUND SET @IS_FOUND=0;?? ?SELECT COUNT(*) INTO productionBomNum FROM jgmes_modeling_production_bom_route WHERE tenant_id=tenantId AND delete_flag=0 AND IFNULL(cr_id,'')<>''/*測試1條數(shù)據(jù) AND pbr_id=86673*/;?? ?SET @creationDate=NOW();?? ?TRUNCATE TABLE a;?? ?##打開生產(chǎn)bom游標(biāo)?? ?OPEN productionBomCursor;?? ??? ?/* 生產(chǎn)bom循環(huán)開始 */?? ??? ? FETCH productionBomCursor INTO crId,pbrId;?? ??? ? WHILE i<=productionBomNum DO?? ??? ? ? ?SELECT COUNT(*) INTO productionBomProcessNum FROM jgmes_modeling_production_bom_process WHERE tenant_id=tenantId AND delete_flag=0 AND pbr_id=pbrId;?? ??? ? ? ?#第2層循環(huán)?? ??? ? ? ?#打開生產(chǎn)bom工序游標(biāo)?? ??? ? ? ?OPEN bomProcessCursor;?? ??? ??? ?FETCH bomProcessCursor INTO bomProcessId,processId;?? ??? ??? ?SET @j=1; ??? ??? ??? ?WHILE @j<=productionBomProcessNum DO?? ??? ??? ?INSERT INTO a (seqNum, remark)VALUES(CONCAT(i,'_',@j,'_',bomProcessId), 'i-j-bomProcessId');?? ??? ??? ??? ?#INSERT INTO a (seqNum, remark)VALUES(CONCAT(crId,'_',processId), 'crProcessId');?? ??? ??? ??? ?#刪除生產(chǎn)bom工序下面的sop表記錄?? ??? ??? ??? ?UPDATE jgmes_modeling_production_sop SET delete_flag=1 WHERE tenant_id=tenantId AND delete_flag=0 AND pbp_id=bomProcessId;?? ??? ??? ??? ?#將生產(chǎn)bom工序?qū)?yīng)工藝路線工序下面的sop文件復(fù)制插入?? ??? ??? ??? ?INSERT INTO jgmes_modeling_production_sop (?? ??? ??? ??? ? ?pbr_id,?? ??? ??? ??? ? ?pbp_id,?? ??? ??? ??? ? ?sn,?? ??? ??? ??? ? ?file_name,?? ??? ??? ??? ? ?file_path,?? ??? ??? ??? ? ?file_type,?? ??? ??? ??? ? ?file_length,?? ??? ??? ??? ? ?file_version_num,?? ??? ??? ??? ? ?tenant_id,?? ??? ??? ??? ? ?creation_date,?? ??? ??? ??? ? ?created_by,?? ??? ??? ??? ? ?last_update_date,?? ??? ??? ??? ? ?last_updated_by,?? ??? ??? ??? ? ?last_update_login,?? ??? ??? ??? ? ?delete_flag,?? ??? ??? ??? ? ?version_num?? ??? ??? ??? ?)?? ??? ??? ??? ?SELECT?? ??? ??? ??? ? ?pbrId,?? ??? ??? ??? ? ?bomProcessId,?? ??? ??? ??? ? ?sn,?? ??? ??? ??? ? ?file_name,?? ??? ??? ??? ? ?file_path,?? ??? ??? ??? ? ?file_type,?? ??? ??? ??? ? ?file_length,?? ??? ??? ??? ? ?file_version_num,?? ??? ??? ??? ? ?sop.tenant_id,?? ??? ??? ??? ? ?@creationDate,?? ??? ??? ??? ? ?-1,?? ??? ??? ??? ? ?sop.last_update_date,?? ??? ??? ??? ? ?sop.last_updated_by,?? ??? ??? ??? ? ?sop.last_update_login,?? ??? ??? ??? ? ?sop.delete_flag,?? ??? ??? ??? ? ?sop.version_num?? ??? ??? ??? ?FROM?? ??? ??? ??? ? ?jgmes_modeling_crafts_sop ?sop?? ??? ??? ??? ?LEFT JOIN jgmes_modeling_crafts_process cp ON cp.cp_id=sop.cp_id?? ??? ??? ??? ?LEFT JOIN jgmes_modeling_process p ON p.p_id=cp.p_id?? ??? ??? ??? ?WHERE sop.tenant_id=tenantId AND sop.delete_flag=0 AND cp.cr_id=crId AND cp.p_id=processId;?? ??? ??? ??? ?#游標(biāo)下移?? ??? ??? ??? ?FETCH bomProcessCursor INTO bomProcessId,processId;?? ??? ??? ??? ?SET @j=@j+1;?? ??? ??? ?END WHILE;?? ??? ? ? ?CLOSE bomProcessCursor;?? ??? ? ? ?#游標(biāo)下移?? ??? ? ? ?FETCH productionBomCursor INTO crId,pbrId;?? ??? ? ? ?SET i=i+1;?? ??? ? END WHILE;?? ??? ?/*生產(chǎn)bom循環(huán)結(jié)束*/?? ?##關(guān)閉游標(biāo)?? ?CLOSE productionBomCursor;?? ??? ?#返回新同步的數(shù)據(jù)?? ?SELECT * FROM jgmes_modeling_production_sop WHERE tenant_id=tenantId AND delete_flag=0 AND creation_date=@creationDate AND created_by=-1;END$$DELIMITER ;

剛開始寫完執(zhí)行時，

報(bào)了一個“DECLARE CONTINUE HANDLER FOR NOT FOUND SET @IS_FOUND=0;”的錯誤

后面加了如下語句就正常了：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET @IS_FOUND=0;

由于存儲過程調(diào)試不太方便，其中a表是用來調(diào)試用的臨時表，調(diào)試完成后可以刪除

CREATE TABLE `a` (? `id` bigint(11) NOT NULL AUTO_INCREMENT,? `seqNum` varchar(50) DEFAULT NULL,? `remark` varchar(100) DEFAULT NULL,? PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10722 DEFAULT CHARSET=utf8mb4mysql存儲過程循環(huán)中使用游標(biāo)方法及遇到的問題

Oracle開發(fā)9年，頻繁使用存儲過程。現(xiàn)轉(zhuǎn)到mysql，使用存儲過程還是不太習(xí)慣，語法差別比較大，而且沒有深入去了解過mysql的存儲過程語法。

現(xiàn)在公司有部分?jǐn)?shù)據(jù)需要通過存儲過程處理，只能硬著頭皮上了。

循環(huán)

與oracle 用法基本一樣，使用loop或者while

首先定義一個游標(biāo)

DECLARE rs CURSOR FOR?? SELECT?? ??? ?contract_id?? ?FROM?? ??? ?data_sale_contract_delay?? ?WHERE FLAG IS NULL or FLAG= '';

在游標(biāo)定義后面要定義一個如果游標(biāo)移動到最后一行數(shù)據(jù)后再次移動后處理的語句.

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;

這個語句的含義是在fetch 不到內(nèi)容時,將變量done 賦值為1.用來做跳出循環(huán)判斷用.

此處與Oracle不同,Oracle會更簡單,直接exit when v_cur%notfound;就可以跳出循環(huán).v_cur是游標(biāo)的名字.

接下來打開游標(biāo),遍歷:

OPEN rs;FETCH NEXT FROM rs INTO v_contract_id;WHILE (done<>1) DO?... ? ?-- 處理邏輯省略? FETCH NEXT FROM rs INTO v_contract_id;-- 處理完成后直接再從游標(biāo)讀取下一條數(shù)據(jù),如果已是最后一條數(shù)據(jù),再fetch時會出發(fā)上面的 CONTINUE HANDLER,將變量done 值改成1END WHILE;CLOSE rs;循環(huán)無法正常退出問題

由于while中使用了select XX into XX from XXX的這種賦值語句在 select 的時候，沒查到數(shù)據(jù)，導(dǎo)致出發(fā)handler將done修改為1，然后光標(biāo)就會跳出循環(huán)。

或者多層循環(huán)嵌套時內(nèi)循環(huán)出現(xiàn)將done修改為1的情況。

以上這兩種情況，都是因?yàn)镃ONTINUE HANDLER把done更新造成的，如果遇到此類問題，需要在每次可能出現(xiàn)done被改變的地方處理完邏輯后增加set done=0;把變量值改一下，這樣就可以繼續(xù)執(zhí)行了。

總結(jié)

這些僅為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持好吧啦網(wǎng)。