【岁月留声（测试）】关于MySQL、Oracle、MS SQL的存储过程批量插入数据写法

在做压力测试或系统预演前部分表或许会需要大量的数据来验证，这个时候又不想写代码，数据库的存储过程不失为一种好的选择。

这篇文章主要是记录一下关于MySQL、Oracle和MS SQL三大关系型数据库的存储过程批量插入写法。

Oracle

创建测试表

CREATE TABLE "P_ORACLE" (
    "VARCHAR2_COLUMN" VARCHAR2(20) NOT NULL ENABLE,
	"NUMBER_COLUMN" NUMBER,
	"DATE_COLUMN" DATE,
	"CLOB_COLUMN" CLOB,
	"BLOB_COLUMN" BLOB,
	"BINARY_DOUBLE_COLUMN" BINARY_DOUBLE,
	"BINARY_FLOAT_COLUMN" BINARY_FLOAT,
	"CHAR_COLUMN" CHAR(1),
	"CHAR_VARYING_COLUMN" VARCHAR2(20),
	"DEC_COLUMN" NUMBER(*, 0),
	"DECIMAL_COLUMN" NUMBER(*, 0),
	"DOUBLE_PRECISION_COLUMN" FLOAT(126),
	"CHARACTER_COLUMN" CHAR(1),
	"CHARACTER_VARYING_COLUMN" VARCHAR2(20),
	"FLOAT_COLUMN" FLOAT(126),
	"INT_COLUMN" NUMBER(*, 0),
	"INTEGER_COLUMN" NUMBER(*, 0),
	"NATIONAL_CHAR_COLUMN" NCHAR(1),
	"NATIONAL_CHAR_VARYING_COLUMN" NVARCHAR2(20),
	"NATIONAL_CHARACTER_COLUMN" NCHAR(1),
	"NATIONAL_CHARACTER_VARY_COLUMN" NVARCHAR2(20),
	"NCHAR_COLUMN" NCHAR(1),
	"NCHAR_VARYING_COLUMN" NVARCHAR2(20),
	"NCLOB_COLUMN" NCLOB,
	"NUMERIC_COLUMN" NUMBER(*, 0),
	"NVARCHAR2_COLUMN" NVARCHAR2(20),
	"RAW_COLUMN" RAW(20),
	"REAL_COLUMN" FLOAT(63),
	"SMALLINT_COLUMN" NUMBER(*, 0),
	"TIMESTAMP_COLUMN" TIMESTAMP (6),
	"VARCHAR_COLUMN" VARCHAR2(20),
	CONSTRAINT "P_ORACLE_PK" PRIMARY KEY ("VARCHAR2_COLUMN") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(
		INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
	) TABLESPACE "YZH" ENABLE
) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(
	INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
) TABLESPACE "YZH" LOB ("CLOB_COLUMN") STORE AS BASICFILE (
	TABLESPACE "YZH" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(
		INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
	)
) LOB ("BLOB_COLUMN") STORE AS BASICFILE (
	TABLESPACE "YZH" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(
		INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
	)
) LOB ("NCLOB_COLUMN") STORE AS BASICFILE (
	TABLESPACE "YZH" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(
		INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
	)
);

这个表几乎将所有字段类型都写上了，后面的例子也是同样的方式，除了部分较为特殊的数据类型外，其他数据类型都将在一个表中列出。

创建游标为10000（必要时）

alter system set open_cursors = 10000;

创建存储过程并模拟1万条数据插入

DECLARE I number := 0; 
BEGIN 
	FOR I IN 0..10000 LOOP 
		INSERT INTO P_ORACLE (
    		VARCHAR2_COLUMN, NUMBER_COLUMN, BINARY_DOUBLE_COLUMN,
			BINARY_FLOAT_COLUMN, CHAR_VARYING_COLUMN,
			DEC_COLUMN, DECIMAL_COLUMN, DOUBLE_PRECISION_COLUMN,
			CHARACTER_VARYING_COLUMN, FLOAT_COLUMN,
			INT_COLUMN, INTEGER_COLUMN, NATIONAL_CHAR_VARYING_COLUMN,
			NATIONAL_CHARACTER_VARY_COLUMN,
			NCHAR_VARYING_COLUMN, NUMERIC_COLUMN,
			NVARCHAR2_COLUMN, REAL_COLUMN, SMALLINT_COLUMN,
			VARCHAR_COLUMN
		)
		VALUES (I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I,I, I, I, I); 
	END LOOP; 
	COMMIT; 
END;

MS SQL

创建测试表

CREATE TABLE dbo.p_ms_checker (
	BIGINT_COLUMN bigint NULL DEFAULT (NULL),
	BINARY_COLUMN binary(64) NULL DEFAULT (NULL),
	BIT_COLUMN bit NULL DEFAULT (NULL),
	CHAR_COLUMN char(64) NULL DEFAULT (NULL),
	CHAR_MAX_COLUMN char(128) NULL DEFAULT (NULL),
	DATE_COLUMN date NULL DEFAULT (NULL),
	DATETIME_COLUMN datetime NULL DEFAULT (NULL),
	DECIMAL_COLUMN decimal(18,0) NULL DEFAULT (NULL),
	FLOAT_COLUMN real NULL DEFAULT (NULL),
	INT_COLUMN int NULL DEFAULT (NULL),
	MONEY_COLUMN money NULL DEFAULT (NULL),
	NCHAR_COLUMN nchar(64) NULL DEFAULT (NULL),
	NCHAR_MAX_COLUMN nchar(128) NULL DEFAULT (NULL),
	NTEXT_COLUMN ntext NULL DEFAULT (NULL),
	NUMERIC_COLUMN numeric(18,0) NULL DEFAULT (NULL),
	NVARCHAR_COLUMN nvarchar(64) NULL DEFAULT (NULL),
	NVARCHAR_MAX_COLUMN nvarchar(128) NULL DEFAULT (NULL),
	REAL_COLUMN real NULL DEFAULT (NULL),
	SMALLINT_COLUMN smallint NULL DEFAULT (NULL),
	SMALLMONEY_COLUMN smallmoney NULL DEFAULT (NULL),
	TEXT_COLUMN text NULL DEFAULT (NULL),
	TINYINT_COLUMN tinyint NULL DEFAULT (NULL),
	VARBINARY_COLUMN varbinary(64) NULL DEFAULT (NULL),
	VARCHAR_COLUMN varchar(64) NULL DEFAULT (NULL),
	VARCHAR_MAX_COLUMN varchar(128) NULL DEFAULT (NULL),
	XML_COLUMN xml NULL DEFAULT (NULL)
);
GO

创建存储过程模拟新增1万条数据

DECLARE @i INT
SET
    @i = 1 
WHILE @i <=10000 BEGIN

INSERT INTO dbo.p_ms_checker (
		BIGINT_COLUMN, BINARY_COLUMN, BIT_COLUMN,
		CHAR_COLUMN, CHAR_MAX_COLUMN, DATE_COLUMN,
		DATETIME_COLUMN, DECIMAL_COLUMN,
		FLOAT_COLUMN, INT_COLUMN, MONEY_COLUMN,
		NCHAR_COLUMN, NCHAR_MAX_COLUMN,
		NTEXT_COLUMN, NUMERIC_COLUMN, NVARCHAR_COLUMN,
		NVARCHAR_MAX_COLUMN, REAL_COLUMN,
		SMALLINT_COLUMN, SMALLMONEY_COLUMN,
		TEXT_COLUMN, TINYINT_COLUMN, VARBINARY_COLUMN,
		VARCHAR_COLUMN, VARCHAR_MAX_COLUMN,
		XML_COLUMN
	)
VALUES
	(
		@i,1,@i,@i,@i,GETDATE(),GETDATE(),@i,@i,@i,@i,@i,@i,STR(@i),
		@i,STR(@i),STR(@i),@i,@i,@i,STR(@i),NULL,1,@i,@i,STR(@i)
	)

SET @i = @i + 1 
END;

MySQL

创建测试表

CREATE TABLE `p_sync` (
  `bigint_column` bigint(10) NOT NULL COMMENT '1',
  `bit_column` bit(10) NOT NULL COMMENT '2',
  `blob_column` blob NOT NULL COMMENT '3',
  `char_column` char(64) NOT NULL COMMENT '4',
  `date_column` date NOT NULL COMMENT '5',
  `datetime_column` datetime NOT NULL COMMENT '6',
  `decimal_column` decimal(10,9) NOT NULL COMMENT '7',
  `double_column` double NOT NULL COMMENT '8',
  `float_column` float NOT NULL COMMENT '10',
  `int_column` int(11) NOT NULL COMMENT '11',
  `longblob_column` longblob NOT NULL COMMENT '12',
  `longtext_column` longtext NOT NULL COMMENT '13',
  `mediumblob_column` mediumblob NOT NULL COMMENT '14',
  `mediumint_column` mediumint(9) NOT NULL COMMENT '15',
  `mediumtext_column` mediumtext NOT NULL COMMENT '16',
  `smallint_column` smallint(6) NOT NULL COMMENT '18',
  `text_column` text NOT NULL COMMENT '19',
  `time_column` time NOT NULL COMMENT '20',
  `timestamp_column` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '21',
  `tinyint_column` tinyint(10) NOT NULL COMMENT '22',
  `tinytext_column` tinytext NOT NULL COMMENT '23',
  `varchar_column` varchar(32) NOT NULL COMMENT '24',
  PRIMARY KEY (`bigint_column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建存储并模拟1万条数据插入

DROP PROCEDURE if EXISTS test_insert;
DELIMITER ;;
CREATE PROCEDURE test_insert()
	BEGIN
	DECLARE y BIGINT DEFAULT 0;
	WHILE y<10000
	DO

		INSERT INTO p_sync (
		    bit_column, blob_column, char_column,
			date_column, datetime_column, decimal_column,
			double_column, float_column, int_column,
			longblob_column, longtext_column,
			mediumblob_column, mediumint_column,
			mediumtext_column, smallint_column,
			text_column, time_column, timestamp_column,
			tinyint_column, tinytext_column,
			varchar_column)
		VALUES(
			y, y, y, 
			now(), now(), y, 
			y, y, y, 
			y, y, 
			y, y, 
			y, y, 
			y,now(), now(), 
			y, y, 
			y
		);

		SET y=y+1;
	END WHILE ;
	commit;
END;;