快捷導(dǎo)航

mysql存儲(chǔ)過(guò)程多層游標(biāo)循環(huán)嵌套的寫法分享

更新時(shí)間：2023年07月06日 10:16:42 作者：LT_1029

這篇文章主要介紹了mysql存儲(chǔ)過(guò)程多層游標(biāo)循環(huán)嵌套的寫法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

mysql存儲(chǔ)過(guò)程多層游標(biāo)循環(huán)嵌套的寫法

最近有遇到一個(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ù)可能沒法直接訪問(wèn)的限制，所以沒有用代碼來(lái)實(shí)現(xiàn)，而是直接用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)

最終存儲(chǔ)過(guò)程的寫法如下

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 (
?? ?);*/
?? ?#解決沒有查到數(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;
?? ?##打開生產(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í)行時(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=utf8mb4

mysql存儲(chǔ)過(guò)程循環(huán)中使用游標(biāo)方法及遇到的問(wèn)題

Oracle開發(fā)9年，頻繁使用存儲(chǔ)過(guò)程。現(xiàn)轉(zhuǎn)到mysql，使用存儲(chǔ)過(guò)程還是不太習(xí)慣，語(yǔ)法差別比較大，而且沒有深入去了解過(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)打開游標(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 值改成1
END WHILE;
CLOSE rs;