在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 ;
