溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

MySQL 存儲過程空結(jié)果集錯誤Error 1329 No data 的異常處理

發(fā)布時間：2020-08-08 19:23:11 來源：ITPUB博客 閱讀：1100 作者：feelpurple 欄目：MySQL數(shù)據(jù)庫
在MySQL的存儲過程中，當(dāng)查詢到空結(jié)果集時會產(chǎn)生下面報錯
Error 1329 No data - zero rows fetched, selected, or processed

解決方法：
在存儲過程中，添加異常處理
注意代碼中的橙色部分的異常代碼


  1. delimiter $$
  2. CREATE PROCEDURE PROC_ADDSubscribers_diff()
  3. BEGIN
  4. DECLARE done INT DEFAULT FALSE;
  5. DECLARE Var_IMSI_NODE2 varchar(16);
  6. DECLARE Var_MSISDN_NODE2 varchar(19);
  7. DECLARE Var_IMEI_NODE2 varchar(16);
  8. DECLARE Var_Timestamp_NODE2 bigint(32);
  9. DECLARE Var_IMSI_NODE1 varchar(16);
  10. DECLARE Var_MSISDN_NODE1 varchar(19);
  11. DECLARE Var_IMEI_NODE1 varchar(16);
  12. DECLARE Var_Timestamp_NODE1 bigint(32);
  13. DECLARE Var_sqlcode INT DEFAULT 0;
  14. DECLARE cur1 CURSOR FOR select IMSI, MSISDN, IMEI, Timestamp from ADDSubscribers_node2;
  15. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  16.   OPEN cur1;
  17.   read_loop: LOOP
  18.   FETCH cur1 INTO Var_IMSI_NODE2, Var_MSISDN_NODE2, Var_IMEI_NODE2, Var_Timestamp_NODE2;
  19.     IF done IS TRUE THEN
  20.     LEAVE read_loop;
  21.     END IF;
  22.   IF done IS FALSE THEN
  23.         IF (Var_IMSI_NODE2 is not null) THEN
  24.         BEGIN
  25.         DECLARE no_data CONDITION FOR 1329;
  26.         DECLARE CONTINUE HANDLER FOR no_data
  27.         BEGIN
  28.         SET Var_sqlcode=2000;
  29.         END;
  30.         select Timestamp, MSISDN, IMEI INTO Var_Timestamp_NODE1, Var_MSISDN_NODE1, Var_IMEI_NODE1 from dmcdbMTNGH.ADDSubscribers where IMSI = Var_IMSI_NODE2;
  31.             IF Var_sqlcode = 2000 THEN
  32.                 start transaction;
  33.                 INSERT INTO ADDSubscribers_diff SELECT * FROM ADDSubscribers_node2 WHERE IMSI = Var_IMSI_NODE2;
  34.                 commit;
  35.             ELSEIF Var_sqlcode = 0 THEN
  36.                 IF Var_Timestamp_NODE1 >= Var_Timestamp_NODE2 THEN
  37.                     select concat('The data on node01 is newer!') as Info;
  38.                 ELSE
  39.                     IF (Var_MSISDN_NODE1 <> Var_MSISDN_NODE2) || (Var_IMEI_NODE1 <> Var_IMEI_NODE2) THEN
  40.                     start transaction;
  41.                     INSERT INTO ADDSubscribers_diff SELECT * FROM ADDSubscribers_node2 WHERE IMSI = Var_IMSI_NODE2;
  42.                     commit;
  43.                     END IF;
  44.                 END IF;
  45.             END IF;
  46.         END;
  47.         END IF;
  48.   END IF;
  49.   END LOOP;
  50.   CLOSE cur1;
  51.   select concat('The job',' is ','finished!') as Info;
  52. END$$
  53. delimiter ;

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手