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

80 阅读 0 评论 53 点赞

我是靠谱客的博主丰富中心，最近开发中收集的这篇文章主要介绍mysql中通过存储过程和游标来实现循环插入数据，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

需求：根据条件从一个表（后称为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();