MySQL 存储过程空结果集错误Error 1329 No data 的异常处理

在MySQL的存储过程中，当查询到空结果集时会产生下面报错
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 ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2140546/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2140546/