mysql存储过程套嵌_mysql 存储过程嵌套

新建脚本update_mysql.sql

DELIMITER //

DROP PROCEDURE IF EXISTS updateOldData; //如果之前调用过以下 过程函数，需要将其drop

CREATE PROCEDURE updateOldData()

BEGIN

DECLARE

primary_agent_id BIGINT;

DECLARE

secondary_agent_id BIGINT;

DECLARE

primary_agent_signed INT DEFAULT 0;

DECLARE

primary_agent_sign_time DATETIME DEFAULT NULL;

DECLARE done int;

DECLARE

cur1 CURSOR FOR SELECT     //声明第一个游标

d.id,

d.signed,

d.sign_time

FROM

oms_agent d

WHERE

d.super_id in ( 1001, 1002, 1003);

DECLARE

cur2 CURSOR FOR SELECT    //声明第二个游标

b.SECONDARY_AGENT_ID

FROM

agent_relation b

WHERE

b.PRIMARY_AGENT_ID = primary_agent_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

open cur1;

out_loop:LOOP

FETCH cur1 INTO primary_agent_id,primary_agent_signed,primary_agent_sign_time;

IF done = 1 THEN

LEAVE out_loop;

END IF;

open cur2;

inner_loop:LOOP

FETCH cur2 INTO secondary_agent_id;

IF done = 1 THEN

LEAVE inner_loop;

END IF;

UPDATE

oms_agent

SET

signed = primary_agent_signed,sign_time= primary_agent_sign_time

WHERE

id = secondary_agent_id;

end LOOP inner_loop;

CLOSE cur2;  //关闭游标

SET done=0;

END LOOP out_loop;

CLOSE cur1;  //关闭游标

END

在服务器数据库中调用此脚本，source /path/update_mysql.sql

调用储存过程函数 call updateOldData();