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

將有3級主從關聯的表數據同步到另外一個有3級主從關聯的表中，同步數據的表是第3級子表，而關聯關系在第1、2級主表中，由于考慮到到內網數據庫可能沒法直接訪問的限制，所以沒有用代碼來實現，而是直接用存儲過程來實現

最終存儲過程的寫法如下DELIMITER $$USE `myDb`$$DROP PROCEDURE IF EXISTS `syncBomSopFromRouteSop`$$CREATE PROCEDURE `syncBomSopFromRouteSop`(IN tenantId BIGINT(11))BEGIN?? ?#工藝路線ID?? ?DECLARE crId BIGINT(11);?? ?#生產bomID?? ?DECLARE pbrId BIGINT(11);?? ?#工序檔案ID?? ?DECLARE processId VARCHAR(50);?? ?#生產bom工序Id?? ?DECLARE bomProcessId BIGINT(11);?? ?#生產bom數量?? ?DECLARE productionBomNum INT(5);?? ?#生產bom工序數量?? ?DECLARE productionBomProcessNum INT(5);?? ?DECLARE i INT(4) DEFAULT 1;?? ?#DECLARE j INT(4) DEFAULT 1;?? ?#定義生產bom游標?? ?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條數據AND pbr_id=86673*/??? ?);?? ?#定義生產bom工序游標?? ?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條生產bom */?? ?);?? ?#定義生產bom的sop游標?? ?/*DECLARE bomSopCursor CURSOR FOR (?? ?);*/?? ?#解決沒有查到數據報：“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條數據 AND pbr_id=86673*/;?? ?SET @creationDate=NOW();?? ?TRUNCATE TABLE a;?? ?##打開生產bom游標?? ?OPEN productionBomCursor;?? ??? ?/* 生產bom循環開始 */?? ??? ? 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層循環?? ??? ? ? ?#打開生產bom工序游標?? ??? ? ? ?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');?? ??? ??? ??? ?#刪除生產bom工序下面的sop表記錄?? ??? ??? ??? ?UPDATE jgmes_modeling_production_sop SET delete_flag=1 WHERE tenant_id=tenantId AND delete_flag=0 AND pbp_id=bomProcessId;?? ??? ??? ??? ?#將生產bom工序對應工藝路線工序下面的sop文件復制插入?? ??? ??? ??? ?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;?? ??? ??? ??? ?#游標下移?? ??? ??? ??? ?FETCH bomProcessCursor INTO bomProcessId,processId;?? ??? ??? ??? ?SET @j=@j+1;?? ??? ??? ?END WHILE;?? ??? ? ? ?CLOSE bomProcessCursor;?? ??? ? ? ?#游標下移?? ??? ? ? ?FETCH productionBomCursor INTO crId,pbrId;?? ??? ? ? ?SET i=i+1;?? ??? ? END WHILE;?? ??? ?/*生產bom循環結束*/?? ?##關閉游標?? ?CLOSE productionBomCursor;?? ??? ?#返回新同步的數據?? ?SELECT * FROM jgmes_modeling_production_sop WHERE tenant_id=tenantId AND delete_flag=0 AND creation_date=@creationDate AND created_by=-1;END$$DELIMITER ;

剛開始寫完執行時，

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

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

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

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

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存儲過程循環中使用游標方法及遇到的問題

Oracle開發9年，頻繁使用存儲過程。現轉到mysql，使用存儲過程還是不太習慣，語法差別比較大，而且沒有深入去了解過mysql的存儲過程語法。

現在公司有部分數據需要通過存儲過程處理，只能硬著頭皮上了。

循環

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

首先定義一個游標

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

在游標定義后面要定義一個如果游標移動到最后一行數據后再次移動后處理的語句.

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

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

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

接下來打開游標,遍歷:

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

由于while中使用了select XX into XX from XXX的這種賦值語句在 select 的時候，沒查到數據，導致出發handler將done修改為1，然后光標就會跳出循環。

或者多層循環嵌套時內循環出現將done修改為1的情況。

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

總結

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