mysql 存储过程嵌套

最新推荐文章于 2021-02-27 07:59:21 发布

转载最新推荐文章于 2021-02-27 07:59:21 发布 · 890 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/niejianbo/blog/833365

文章标签：

#数据库 #python

2019独角兽企业重金招聘Python工程师标准>>>

新建脚本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();

转载于:https://my.oschina.net/niejianbo/blog/833365