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

发布时间：2022-04-12 06:23:22 所属栏目：MySql教程来源：互联网

导读：在MySQL的存储过程中，当查询到空结果集时会产生下面报错 Error 1329 No data - zero rows fetched, selected, or processed 解决方法：在存储过程中，添加异常处理注意代码中的橙色部分的异常代码 delimiter $$ CREATE PROCEDURE PROC_ADDSubscribers_d

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

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

数据库中间件ProxySQL	zabbix 一次悲惨的分区
mysql如何实现数据备份	建立数据表主键外键