在MySQL的存储过程中,当查询到空结果集时会产生下面报错
Error 1329 No data – zero rows fetched, selected, or processed
解决方法:
在存储过程中,添加异常处理
注意代码中的橙色部分的异常代码
-
delimiter $$
- CREATE PROCEDURE PROC_ADDSubscribers_diff()
- BEGIN
- DECLARE done INT DEFAULT FALSE;
- DECLARE Var_IMSI_NODE2 varchar(16);
- DECLARE Var_MSISDN_NODE2 varchar(19);
- DECLARE Var_IMEI_NODE2 varchar(16);
- DECLARE Var_Timestamp_NODE2 bigint(32);
- DECLARE Var_IMSI_NODE1 varchar(16);
- DECLARE Var_MSISDN_NODE1 varchar(19);
- DECLARE Var_IMEI_NODE1 varchar(16);
- DECLARE Var_Timestamp_NODE1 bigint(32);
- DECLARE Var_sqlcode INT DEFAULT 0;
- DECLARE cur1 CURSOR FOR select IMSI, MSISDN, IMEI, Timestamp from ADDSubscribers_node2;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
- OPEN cur1;
- read_loop: LOOP
- FETCH cur1 INTO Var_IMSI_NODE2, Var_MSISDN_NODE2, Var_IMEI_NODE2, Var_Timestamp_NODE2;
- IF done IS TRUE THEN
- LEAVE read_loop;
- END IF;
- IF done IS FALSE THEN
- IF (Var_IMSI_NODE2 is not null) THEN
- BEGIN
- DECLARE no_data CONDITION FOR 1329;
- DECLARE CONTINUE HANDLER FOR no_data
- BEGIN
- SET Var_sqlcode=2000;
- END;
- select Timestamp, MSISDN, IMEI INTO Var_Timestamp_NODE1, Var_MSISDN_NODE1, Var_IMEI_NODE1 from dmcdbMTNGH.ADDSubscribers where IMSI = Var_IMSI_NODE2;
- IF Var_sqlcode = 2000 THEN
- start transaction;
- INSERT INTO ADDSubscribers_diff SELECT * FROM ADDSubscribers_node2 WHERE IMSI = Var_IMSI_NODE2;
- commit;
- ELSEIF Var_sqlcode = 0 THEN
- IF Var_Timestamp_NODE1 >= Var_Timestamp_NODE2 THEN
- select concat(‘The data on node01 is newer!’) as Info;
- ELSE
- IF (Var_MSISDN_NODE1 <> Var_MSISDN_NODE2) || (Var_IMEI_NODE1 <> Var_IMEI_NODE2) THEN
- start transaction;
- INSERT INTO ADDSubscribers_diff SELECT * FROM ADDSubscribers_node2 WHERE IMSI = Var_IMSI_NODE2;
- commit;
- END IF;
- END IF;
- END IF;
- END;
- END IF;
- END IF;
- END LOOP;
- CLOSE cur1;
- select concat(‘The job’,‘ is ‘,‘finished!’) as Info;
- END$$
- delimiter ;