【测试】主流数据库存储过程编写样例（Oracle、MySQL、SQL Server）

标签：COMMENT 20 COLUMN 样例 column Server DEFAULT SQL NULL

这个...做测试其实有时候还是需要先弄点数据才好针对某些功能进行测试的（相信做过开发的都应该深有体会）。一般像我这种老油条都推荐使用存储过程来做的初始化数据，一来脚本不会骗人，二来可以通过另一种方式验证逻辑关系。下面将整理了三个主流数据库（Oracle、MySQL和SQL Server）的“单表造数”的存储过程写法，“伸手党”请自取。

1. ORACLE

1.1 创建测试表

CREATE TABLE "YZH2_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 "YZH2_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
  )
);

1.2 预设游标10000（必要时）

alter system set open_cursors = 10000;

1.3 模拟新增10万条数据

DECLARE I number := 0; 
BEGIN 
  FOR I IN 0..100000 LOOP 
    INSERT INTO YZH2_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;

2. SQL SERVER

2.1 创建测试表

CREATE TABLE dbo.yzh2_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

2.2 模拟新增3万条数据

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

INSERT INTO dbo.yzh2_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;

3. MySQL

3.1 创建测试表

CREATE TABLE `yzh2_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;

3.2 模拟新增10万条数据

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

    INSERT INTO yzh2_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;;

3.3 存储过程调用

CALL test_insert();

标签：COMMENT,20,COLUMN,样例,column,Server,DEFAULT,SQL,NULL
From： https://blog.51cto.com/u_15761576/6165551