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

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

最終存儲(chǔ)過(guò)程的寫(xiě)法如下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 /*測(cè)試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 /*測(cè)試1條生產(chǎn)bom */?? ?);?? ?#定義生產(chǎn)bom的sop游標(biāo)?? ?/*DECLARE bomSopCursor CURSOR FOR (?? ?);*/?? ?#解決沒(méi)有查到數(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,'')<>''/*測(cè)試1條數(shù)據(jù) AND pbr_id=86673*/;?? ?SET @creationDate=NOW();?? ?TRUNCATE TABLE a;?? ?##打開(kāi)生產(chǎn)bom游標(biāo)?? ?OPEN productionBomCursor;?? ??? ?/* 生產(chǎn)bom循環(huán)開(kāi)始 */?? ??? ? 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)?? ??? ? ? ?#打開(kāi)生產(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 ;

剛開(kāi)始寫(xiě)完執(zhí)行時(shí)，

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

后面加了如下語(yǔ)句就正常了：

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

由于存儲(chǔ)過(guò)程調(diào)試不太方便，其中a表是用來(lái)調(diào)試用的臨時(shí)表，調(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存儲(chǔ)過(guò)程循環(huán)中使用游標(biāo)方法及遇到的問(wèn)題

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

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

循環(huán)

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

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

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

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

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

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

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

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

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

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

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

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

總結(jié)

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