2019-06-28 mysql 循环执行sql

最新推荐文章于 2024-07-08 02:51:24 发布

hq091117

最新推荐文章于 2024-07-08 02:51:24 发布

阅读量935

点赞数 1

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/hq091117/article/details/94010161

mysql 专栏收录该内容

29 篇文章

订阅专栏

本文介绍使用SQL存储过程进行批量数据插入的方法，通过定义循环和变量，实现对多个发票类型的数据自动填充，同时展示了如何创建和调用存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//定义结束符
delimiter $$
//如果已经有pre函数则删除
drop procedure  if exists pre; 
//创建pre函数
CREATE PROCEDURE pre ( ) BEGIN
//定义i
	DECLARE
		i INT;
	//起始值
	SET i = 1000;
	WHILE
            //循环
			i < 1095 DO
			INSERT INTO aa ( conn_id, invoice_type, invoice_type_name, invouce_max_amount, invoice_max_num, valid_num, used_num, lave_num, virtual_num, min_num, invoice_status )
		VALUES
			( i, '1', '机动车销售统一发票', '999999.99', '100', '100', '0', '100', '100', '5', '1' );
		INSERT t_invoice_stock_detail ( conn_id, invoice_type, invoice_type_name, invouce_max_amount, invoice_max_num, valid_num, used_num, lave_num, virtual_num, min_num, invoice_status )
		VALUES
			( i, '2', '增值税专用发票', '999999.99', '100', '100', '0', '100', '100', '5', '1' );
		INSERT t_invoice_stock_detail ( conn_id, invoice_type, invoice_type_name, invouce_max_amount, invoice_max_num, valid_num, used_num, lave_num, virtual_num, min_num, invoice_status )
		VALUES
			( i, '3', '增值税普通发票', '999999.99', '100', '100', '0', '100', '100', '5', '1' );
		INSERT t_invoice_stock_detail ( conn_id, invoice_type, invoice_type_name, invouce_max_amount, invoice_max_num, valid_num, used_num, lave_num, virtual_num, min_num, invoice_status )
		VALUES
			( i, '4', '增值税电子普通发票', '999999.99', '100', '100', '0', '100', '100', '5', '1' );
		INSERT t_invoice_stock_detail ( conn_id, invoice_type, invoice_type_name, invouce_max_amount, invoice_max_num, valid_num, used_num, lave_num, virtual_num, min_num, invoice_status )
		VALUES
			( i, '6', '二手车统一发票', '999999.99', '100', '100', '0', '100', '100', '5', '1' );
		
		SET i = i + 1;
		
	END WHILE;

END $$ CALL pre ( ); // 结束

DROP PROCEDURE idata;

delimiter;;
CREATE PROCEDURE idata ( ) BEGIN
	DECLARE
		i INT;
	
	SET i = 1;
	WHILE
			( i <= 800000 ) DO
			INSERT INTO t2 ( q, w, e, r, f )
		VALUES
			( i, i, i, i, i );
		
		SET i = i + 1;
		
	END WHILE;
	
END;;

delimiter;
CALL idata ( );