MYSQL创建存储过程，遍历查询表数据，将满足条件的数据插入另一张表中

71 阅读 0 评论 47 点赞

我是靠谱客的博主清脆歌曲，最近开发中收集的这篇文章主要介绍MYSQL创建存储过程，遍历查询表数据，将满足条件的数据插入另一张表中，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

存储过程实现要求：

1、遍历 t_qlrinfo表数据 ,获取bsm,qxdm,fwqbm,ywh,qlbsm；

2、将数据与t_tb_dyxx关联，若存在满足条件的数据，则将 t_qlrinfo表的bsm插入T_TMP_QLRXX_BSM表中。

----------------------------------------------------------------------------------------------

存储过程编写如下：

DELIMITER // # 定义//为一句sql的结束标志，取消;的所代表的意义
drop procedure if exists proc_insert;
create procedure proc_insert()
begin
DECLARE v_qxdm INT; # 声明变量
DECLARE v_ywh VARCHAR(64);
DECLARE v_fwqbm int ;
DECLARE v_djbsm BIGINT ;
   DECLARE v_bsm BIGINT ;
   DECLARE rows int ;
   declare done BOOLEAN default 0;
   # 下面定义一个游标来记录sql查询的结果
   declare s_list cursor for select bsm,qxdm,fwqbm,ywh,qlbsm from BDCZZ.t_qlrinfo;
   # 为下面循环建立一个退出标志，当游标遍历完后将done的值设置为1
   declare continue handler for not found set done=1;
   open s_list; # 打开游标
       read_loop:LOOP
fetch s_list into v_bsm, v_qxdm,v_fwqbm, v_ywh,v_djbsm ; # 将游标中的值赋给定义好的变量
       select count(1) into rows from bdczztb.t_tb_dyxx b where v_qxdm=b.qxdm_num and v_fwqbm=b.fwqbm and v_ywh=b.ywh and v_djbsm=b.djbsm_num ;
       IF done THEN # 当为1时退出，为0时继续
           LEAVE read_loop;
       END IF;
       IF rows>0 then
                   INSERT INTO BDCZZTB.T_TMP_QLRXX_BSM(BSM_DELETE) values(v_bsm);
                   commit;
    END IF;
   END LOOP;
close s_list; # 关闭游标
end//
DELIMITER;

---------------------------------------------------

-- 调用存储过程
call proc_insert();

-----------------------------------------------------------------------------------------------------------

如果我还要记录查询到第几行插入的数据，和插入时间，则新加上 XH 和时间：

DELIMITER //
drop procedure if exists proc_insert;
create procedure proc_insert()
begin
DECLARE v_qxdm INT;
DECLARE v_ywh VARCHAR(64);
DECLARE v_fwqbm int ;
DECLARE v_djbsm BIGINT ;
   DECLARE v_bsm BIGINT ;
   DECLARE rows int ;
   declare xh int default 0;
   declare done BOOLEAN default 0;
   declare s_list cursor for select bsm,qxdm,fwqbm,ywh,qlbsm from BDCZZ.t_qlrinfo;
   declare continue handler for not found set done=1;
   open s_list;
       read_loop:LOOP
fetch s_list into v_bsm, v_qxdm,v_fwqbm, v_ywh,v_djbsm ;
       set xh := xh+1 ;
       select count(1) into rows from bdczztb.t_tb_dyxx b where v_qxdm=b.qxdm_num and v_fwqbm=b.fwqbm and v_ywh=b.ywh and v_djbsm=b.djbsm_num ;
       IF done THEN
           LEAVE read_loop;
       END IF;
       IF rows>0 then
                   INSERT INTO BDCZZTB.T_TMP_QLRXX_BSM(BSM_DELETE,xh,gxsj) values(v_bsm, xh ,sysdate());
                   commit;
    END IF;
   END LOOP;
close s_list;
end//
DELIMITER;