Shulou(Shulou.com)06/01 Report--

In the stored procedure of MySQL, the following error occurs when an empty result set is queried

Error 1329 No data-zero rows fetched, selected, or processed

Solution:

In the stored procedure, add exception handling

Note the exception code in the orange part of the code

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 newest') 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', 'finishedstones') As Info

END$$

Delimiter

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.