mysql中通过存储过程和游标来实现循环插入数据

已于 2022-02-14 17:00:40 修改
阅读量3.2k 收藏 35
点赞数 5
文章标签： SQL 存储过程 数据迁移 游标 批量插入
于 2021-09-09 11:43:38 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cxyrfg/article/details/120197780
版权

需求：根据条件从一个表（后称为A）中查询符合条件的结果集，根据表A的结果集循环插入到另一个表（后称为B）中，

解决SQL：

drop procedure if exists handle_data; # 如果存在名字为handle_data的procedure则删除
delimiter // # 定义//为一句sql的结束标志，取消;的所代表的意义
#声明存储过程handle_data（）
CREATE PROCEDURE handle_data ( ) 
  # 存储过程开始
  BEGIN
  # 定义一个变量，while循环时使用
  DECLARE deviceCode VARCHAR( 255 );
  # 为下面while循环建立一个退出标志，
  DECLARE flag INT DEFAULT 0;
  # 定义一个游标来记录sql查询的结果
  DECLARE deviceCodeList CURSOR FOR ( SELECT device_code FROM t_device WHERE DEVICE_TYPE_CODE IN ("SmokeDetector","HeatDetector","HydrantButton","ManualCallButton"));
  # 当游标遍历完后将flag的值设置为1
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
  # 打开游标
  OPEN deviceCodeList;
  # 将游标中的值赋给定义好的变量，实现for循环的要点
  FETCH deviceCodeList INTO deviceCode;
  # 当flag不等于1时，会一直循环
  WHILE flag != 1 DO
  
	  # 执行需要循环处理的SQL
	  # 插入数据到另一个表
      INSERT INTO `t_device_expand_property` (`DEVICE_CODE`,`PROPERTY_ID`,`PROPERTY_VALUE`) values(deviceCode,27,"27"),(deviceCode,21,"21");
	  # 游标往后移，注：不能漏
      FETCH deviceCodeList INTO deviceCode;
    end while;
  CLOSE deviceCodeList; # 关闭游标
  # 存储过程结束
  end //
    
delimiter ;
call handle_data();

注：1.变量定义要在CURSOR 之前；2.DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1要在打开游标之前；3.循环过程中定要执行FETCH xxx INTO xxx ，使游标后移；

简单表演示：
提供数据的表：

drop table if exists t_device;
CREATE TABLE `t_device` (
  `ID` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `DEVICE_NAME` varchar(128) DEFAULT NULL COMMENT '设备名称',
  `device_alias` varchar(128) DEFAULT NULL COMMENT '设备别名',
  `DEVICE_CODE` varchar(128) NOT NULL COMMENT '资产唯一编号',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `DEVICE_CODE` (`DEVICE_CODE`)
) ENGINE=InnoDB AUTO_INCREMENT=3718 DEFAULT CHARSET=utf8 COMMENT='设备表';

INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (65, 'B3-364向东', 'B3-364向东', '5361bd9b1a6c435fa14ef3bf2404d8ef');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (66, 'B3-363向北', 'B3-363向北', '6516f1c522dc493099bad595b9795d9a');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (67, 'B3-269向北', 'B3-269向北', '61edb76574c9496ba550603806b49474');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (68, 'B3-247向北', 'B3-247向北', '2be4c022ed594abfba88ba6b0d027113');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (69, 'B3-247向北', 'B3-247向北', '8a067b67cff846c0a85b1dc88013db8e');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (70, 'B3-232向西', 'B3-232向西', 'b2f867f886fd4fb49c943a4135a91d03');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (71, 'B3-218向北', 'B3-218向北', 'f3fc66ee3d614df49171a11b249396b4');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (72, 'B3-209向南', 'B3-209向南', 'f4417cac5ebd468bad801430a9a55633');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (73, 'B3-206向南', 'B3-206向南', '4896a7a107db4efe90160303751a74bd');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (74, 'B3-198向西', 'B3-198向西', '46f6094a8cf54612a0e7f0937cdf59c0');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (75, 'B3-190向北', 'B3-190向北', 'ae78c2dbdec54a6da9748c3552512fde');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (76, 'B3-189向西', 'B3-189向西', '0da288ebd66f46e38cfcdd44260a7112');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (77, 'B3-180向西', 'B3-180向西', 'aaf59b3542fe49d7ae44700a8cbadbf9');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (78, 'B3169向南', 'B3169向南', '369b8c58f7d8444dafa45da6a5b09680');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (79, 'B3-159向北', 'B3-159向北', 'f6076ad7086a489e999cc729d49b5208');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (80, 'B3-151向东', 'B3-151向东', 'e37b615095b24a9b905bf5d0e8a4d634');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (81, 'B3-110向东', 'B3-110向东', 'eba5c92b869d4a16ac111c5885f75f4c');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (82, 'B3-098向东', 'B3-098向东', '4760537860204bbc97e38170e2b7ccda');
INSERT INTO `t_device`(`ID`, `DEVICE_NAME`, `device_alias`, `DEVICE_CODE`) VALUES (83, 'B3-017向东', 'B3-017向东', '350de6edaf5b4785915c0fe1eaccc818');

在这里插入图片描述

需要插入数据表：

drop table if exists t_device_property;
CREATE TABLE `t_device_property` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `DEVICE_CODE` varchar(32) NOT NULL COMMENT '设备编码',
  `PROPERTY_ID` varchar(32) NOT NULL COMMENT '属性ID',
  `PROPERTY_VALUE` varchar(255) DEFAULT NULL COMMENT '属性值',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=192 DEFAULT CHARSET=utf8 COMMENT='设备属性值表';

在这里插入图片描述

存储过程：

drop procedure if exists handle_data; # 如果存在名字为handle_data的procedure则删除
delimiter // # 定义//为一句sql的结束标志，取消;的所代表的意义
#声明存储过程handle_data（）
CREATE PROCEDURE handle_data ( ) 
  # 存储过程开始
  BEGIN
  # 定义一个变量，while循环时使用
  DECLARE deviceCode VARCHAR( 255 );
  # 为下面while循环建立一个退出标志，
  DECLARE flag INT DEFAULT 0;
  # 定义一个游标来记录sql查询的结果
  DECLARE deviceCodeList CURSOR FOR ( SELECT device_code FROM t_device WHERE id>80);
  # 当游标遍历完后将flag的值设置为1
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
  # 打开游标
  OPEN deviceCodeList;
  # 将游标中的值赋给定义好的变量，实现for循环的要点
  FETCH deviceCodeList INTO deviceCode;
  # 当flag不等于1时，会一直循环
  WHILE flag != 1 DO
  
	  # 执行需要循环处理的SQL
	  # 插入数据到另一个表
      INSERT INTO `t_device_property` (`DEVICE_CODE`,`PROPERTY_ID`,`PROPERTY_VALUE`) values(deviceCode,27,"27");
	  # 游标往后移，注：不能漏
      FETCH deviceCodeList INTO deviceCode;
    end while;
  CLOSE deviceCodeList; # 关闭游标
  # 存储过程结束
  end //
    
delimiter ;
call handle_data();

在这里插入图片描述
其他存储过程示例：
表：

CREATE TABLE `t_exhibition_rel_floor` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `exhibition_code` varchar(64) NOT NULL COMMENT ' 展会编码',
  `floor` int(2) NOT NULL COMMENT '所在楼层',
  `passenger_flow_count` int(8) unsigned NOT NULL DEFAULT '0' COMMENT '客流摄像头统计人流数',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_exhibition_code_floor` (`exhibition_code`,`floor`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='展会楼层表';

在这里插入图片描述
存储过程：

drop procedure if exists handle_data; # 如果存在名字为handle_data的procedure则删除
delimiter // # 定义//为一句sql的结束标志，取消;的所代表的意义
#声明存储过程
create PROCEDURE handle_data()
	BEGIN
		 # 定义一个变量，while循环时存储展会编码
		DECLARE tmp_exhibition_code VARCHAR(64);
		 # 定义一个变量，while循环时存储楼层
		DECLARE tmp_floor int(2) DEFAULT 3;
		 # 定义一个变量，while循环时存储创建时间
		DECLARE tmp_create_time datetime DEFAULT NULL;
		 # 定义一个变量，while循环时存储更新时间
		DECLARE tmp_update_time datetime DEFAULT NULL;
		# 为下面while循环建立一个退出标志，
		DECLARE flag INT DEFAULT 0;
		# 定义一个游标tmp_index来记录sql查询的结果
		DECLARE tmp_index CURSOR FOR (SELECT t.exhibition_code,t1.floor,t.create_time,t.update_time FROM t_exhibition t inner JOIN t_exhibitor t1 on t.exhibition_code = t1.exhibition_code where floor IN (3,4,5) GROUP BY t.exhibition_code,t1.floor ORDER BY t.create_time asc);
		# 当游标遍历完后将flag的值设置为1
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
		# 打开游标
		OPEN tmp_index;
		# 将游标中的值赋给定义好的变量，实现for循环的要点
		FETCH tmp_index INTO tmp_exhibition_code,tmp_floor,tmp_create_time,tmp_update_time;
		# 当flag不等于1时，会一直循环
		WHILE flag != 1 DO
		
			# 执行需要循环处理的SQL
			# 插入数据到另一个表
			INSERT INTO `t_exhibition_rel_floor` (`exhibition_code`,`floor`,`passenger_flow_count`,create_time,update_time) values(tmp_exhibition_code,tmp_floor,0,tmp_create_time,tmp_update_time);
			# 游标往后移，注：不能漏
				FETCH tmp_index INTO tmp_exhibition_code,tmp_floor,tmp_create_time,tmp_update_time;
			end while;
		CLOSE tmp_index; # 关闭游标
		# 存储过程结束
		end //
    
delimiter ;
# 调用存储过程
call handle_data();

在这里插入图片描述

cxyrfg
关注
Mysql 存储过程，开启游标循环后，重复循环插入多次数据。
wangliuqi123的博客
11-22 5613
今天在写存储过程，给其他表插入数据时，发现，我游标取出来的数据明明只有一条，但是在循环过程中，却发现插入了两条数据。下面是代码 BEGIN #Routine body goes here... DECLARE event_content1 VARCHAR(255); DECLARE event_id1 INT; DECLARE event_name VARCHAR(255); DEC...
MySQL存储过程使用游标循环数据列表
jiaweiok123的博客
02-26 5959
本篇文章主要讲解，我的一个案例，使用存储过程和游标循环数据列表，并且做一些操作，比如保存一些数据，修改一些数据； 1、需求？ MySQL使用存储过程循环数据列表？ 2、先描述下MySQL有哪些循环的语法 while循环 CREATE PROCEDURE proc_while () BEGIN DECLARE num INT ; SET num = 0 ; ...
mysql 存储过程 循环
weixin_30929011的博客
03-05 84
网摘DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_pament_TrainFee`() BEGIN /*局部变量的定义 declare*/ declare strYear int; declare strEnrollID int; declare feesum int; declare stop int defaul...
mysql使用存储过程循环插入数据
yingjiell的专栏
11-22 759
<br />创建一个存储过程，名字为autoInsert<br />CREATE PROCEDURE `autoInsert`()<br />BEGIN<br />    DECLARE i int default 10000;<br />    WHILE(i < 20000) DO<br />        insert into app.agent (account_id, firstname,lastname,password,enabled,sip_user,admin_password) val
mysql游标遍历-记录
最新发布
m0_62681080的博客
08-16 424
3.2 DECLARE cur CURSOR FOR: 声明一个游标 cur，用于遍历查询 SELECT id, order_date FROM orders WHERE order_status = ‘pending’ 返回的结果集。: 声明一个名为 done 的整数变量，初始值为 0。read_loop 是循环的标识符，用于退出循环。FETCH: 从游标中获取一行数据，并将其存储在相应的变量中。OPEN: 打开游标，使其可以开始遍历结果集。CLOSE: 关闭游标，释放与其相关的资源。
存储过程(循环插入数据)
09-24
使用for loop向表中批量插入数据，推荐使用plsql执行，Dbvisualizer语句执行会出错
mysql 游标循环_mysql存储过程 游标 循环使用介绍
weixin_32491255的博客
01-18 1066
今天分享下自己对于Mysql存储过程的认识与了解,这里主要说说大家常用的游标加循环的嵌套使用Mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于Mysql存储过程的认识与了解。一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里主要说说大家常用的游标加循环的嵌套使用。首先先介绍循环的分类：(1)WHILE ... END WHILE(2)L...
mysql存储过程 循环 游标
weixin_41521294的博客
07-30 127
MySQL存储过程循环游标 作为一名经验丰富的开发者，我很高兴能帮助你了解如何在MySQL中使用存储过程、循环和游标。以下是实现这一功能的步骤和代码示例。 步骤 以下是实现MySQL存储过程循环游标的步骤： 步骤 描述 1 创建存储过程 2 定义游标 3 使用循环遍历游标 4 处理循环中的逻辑 ...
mysql 存储过程循环 游标
weixin_36491755的博客
07-22 195
MySQL 存储过程中的循环与游标使用 在数据库编程中，循环和游标是实现复杂逻辑和批量操作的重要工具。MySQL 作为一种流行的关系型数据库管理系统，提供了丰富的存储过程功能，其中就包括了对循环和游标的支持。本文将介绍如何在 MySQL 存储过程中使用循环和游标。 循环 在 MySQL 中，可以使用 WHILE 循环来执行...
存储过程循环插入
qq_37361360的博客
10-11 1100
delimiter $$ create procedure pre() begin declare i int; set i=1; while i<100 do INSERT INTO test (name) VALUES ('m1602809'); set i=i+1; end while; end $$ call pre();
Mysql存储过程循环内嵌套使用游标示例代码
09-10
总结来说，这个示例展示了如何在MySQL存储过程中使用游标进行循环处理，并在循环内部再次嵌套游标以实现更精细的数据操作。这种技术在处理大量数据或执行复杂逻辑时非常有用，尤其是在需要逐行检查和处理数据的情况...
mysql复杂存储过程实例（游标、临时表、循环、递归）
08-18
本资源结合实例实现一个复杂的存储过程，存储过程中有用到游标、临时表、循环、递归等知识，sql文件附有实例数据表创建的sql语句。
mysql存储过程 游标 循环使用介绍
09-11
总结来说，MySQL存储过程中的游标和循环机制提供了强大的数据处理能力。通过游标，我们可以逐行处理数据，结合循环结构，能够灵活地实现复杂的数据操作逻辑。在实际应用中，这些工具常用于批量插入、更新、计算统计...
mysql存储过程循环 游标 输出结果_mysql存储过程 游标 查询结果循环
weixin_39610488的博客
01-21 566
Mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于Mysql存储过程的认识与了解。一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里主要说说大家常用的游标加循环的嵌套使用。首先先介绍循环的分类：(1)WHILE ... END WHILE(2)LOOP ... END LOOP(3)REPEAT ... END REPEAT(4)GOT...
mysql游标遍历循环 插入数据
LonelySnow的博客
11-20 3669
BEGIN DECLARE no_more_record INT DEFAULT 0; DECLARE insertColumn VARCHAR(18); DECLARE cur_record CURSOR FOR SELECT insertParam FROM testTable; DECLARE CONTINUE HANDLER FOR NOT FOUND SET ...
MySQL - 存储过程游标循环插入数据
你今天真好看呀
02-09 1279
【代码】MySQL - 存储过程游标循环插入数据。
mySql 存储过程循环插入数据
Damily的博客
05-10 402
delimiter createprocedureproinsert()begindeclareiint;seti=1;whilei<100doinsertintocesdamilyvalues(i,concat(′damily′,i));seti=i+1;endwhile;end create procedure pro_insert() begin declare i int; set i=1; while i<100 do insert into ces_damily values(i,
mysql存储过程 例子 游标cursor 循环嵌套 事物
热门推荐
光头的专栏
05-28 1万+
昨天要mysql中写存储过程发现网上有关于cursor循环且套的资料很少,现在 发出本人写的例子,希望能对大家有帮助.[斜体字 必须出现]  DELIMITER $$USE `db`$$DROP PROCEDURE IF EXISTS `test`$$ CREATE DEFINER=`root`@`%` PROCEDURE `test`()BEGIN  
存储过程循环添加数据
tatagg的专栏
10-13 676
ALTER PROCEDURE dbo.StoredProcedure2     /*      循环添加20000条数据    */AS    /* SET NOCOUNT ON */    --SET IDENTITY_INSERT testPROC ONdeclare @i intset @i=1while @ibegin    ins
mysql存储过程 游标循环
07-28
当需要对一个结果集进行循环处理时，可以使用游标（cursor）来实现。下面是一个示例，展示了如何在MySQL存储过程中使用游标循环： ```sql DELIMITER // CREATE PROCEDURE process_data() BEGIN -- 声明变量来存储结果集的字段值 DECLARE done INT DEFAULT FALSE; DECLARE col1 VARCHAR(255); DECLARE col2 INT; -- 声明游标 DECLARE cur CURSOR FOR SELECT column1, column2 FROM your_table; -- 定义异常处理程序 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 打开游标 OPEN cur; -- 循环处理结果集 read_loop: LOOP -- 从游标中读取下一行数据 FETCH cur INTO col1, col2; -- 如果已经读取完所有行，则退出循环 IF done THEN LEAVE read_loop; END IF; -- 在这里进行你的处理逻辑，可以使用 col1 和 col2 来访问字段值 -- 示例：打印字段值 SELECT col1, col2; END LOOP; -- 关闭游标 CLOSE cur; END // DELIMITER ; ``` 在上面的示例中，首先声明了一些变量来存储结果集的字段值。然后，声明了一个游标，它通过SELECT语句从指定的表中获取数据。接下来，定义了一个异常处理程序，用于在读取完所有行后设置`done`变量为TRUE。然后打开游标，并使用一个循环（`LOOP`）来逐行读取结果集中的数据。在循环中，可以使用游标的FETCH语句将字段值赋给声明的变量。在每次循环中，可以根据需要对字段值进行处理。最后，关闭游标。 你可以根据你的具体需求修改上述示例中的代码。希望这能帮助到你！
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值