赞
踩
在做压力测试或系统预演前部分表或许会需要大量的数据来验证,这个时候又不想写代码,数据库的存储过程不失为一种好的选择。
这篇文章主要是记录一下关于MySQL、Oracle和MS SQL三大关系型数据库的存储过程批量插入写法。
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 ) );
这个表几乎将所有字段类型都写上了,后面的例子也是同样的方式,除了部分较为特殊的数据类型外,其他数据类型都将在一个表中列出。
alter system set open_cursors = 10000;
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;
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
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;
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;
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;;
CALL test_insert();
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。