MySql、PostgreSql、SqlServer三种数据库的造数存储过程实例
主要实例：把临时表tmp_table数据插入到目标表target_table
一、MySql造数存储过程实例
mysql造数
-- 第一步，创建临时表 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table ( `id` VARCHAR (64) NOT NULL, `second_id` VARCHAR (64) NOT NULL, `total_amount` DOUBLE (18, 2) DEFAULT NULL, `total_day` INT (11) DEFAULT NULL, `create_time` datetime NOT NULL, `edit_time` datetime DEFAULT NULL, `editor` VARCHAR (50) DEFAULT NULL, `status` INT (11) NOT NULL DEFAULT '0' ) DEFAULT CHARSET = utf8mb4; -- 第二步，使用前先清空临时表 TRUNCATE TABLE tmp_table; -- 第三步，定义存储过程
-- 生成5-10位随机正整数ceiling(5 - 1 + rand() *(10 -(5 - 1)))
-- 生成5-7位随机正整数 FLOOR(5 + RAND() *(7 + 1 - 5))
#定义存储过程 delimiter ##num_limit 要插入数据的数量,rand_limit 最大随机的数值 DROP PROCEDURE IF EXISTS insert_test_val ;
CREATE PROCEDURE insert_test_val (IN num_limit INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE id VARCHAR (64) DEFAULT 1; DECLARE second_id VARCHAR (64) DEFAULT 1; WHILE i <= num_limit DO INSERT INTO testdb.tmp_table ( `id`, `second_id`, `total_amount`, `total_day`, `create_time`, `edit_time`, `editor`, `status` ) VALUES ( id, second_id, ceiling(5 - 1 + rand() *(10 -(5 - 1))), FLOOR(5 + RAND() *(7 + 1 - 5)), SYSDATE(), SYSDATE(), 'mysql存储过程', '0' ) ; SET id = id + 1 ; SET second_id = second_id + 1 ; SET i = i + 1 ; END WHILE ; END -- 第四步，调用存储过程，插入1万条数据 #调用存储过程 CALL insert_test_val (10000) ; -- 第五步，可跳过 #查看临时表（检查存储过程是否生成数据） SELECT * FROM tmp_table ; -- 第六步，插入到目标表 INSERT INTO `testdb`.`target_table` (`id`,`second_id`,`total_amount`,`total_day`,`create_time`,`edit_time`,`editor`,`status`) SELECT * FROM tmp_table ; -- 第七步，删除存储过程生成的数据（若不需要删除则跳过） DELETE FROM target_table WHERE id = second_id ; -- 第八步，删除存储过程（若不需要删除则跳过） DROP PROCEDURE IF EXISTS insert_test_val ;
二、PostgreSql造数存储过程实例
postgresql造数
-- 第一步，创建临时表 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table2 ( "encryptionphone_text" VARCHAR (32) COLLATE "default", "user_id" int4, "target_code" VARCHAR (32) COLLATE "default", "target_time" TIMESTAMP (6), "creator" VARCHAR (128) COLLATE "default", "create_time" TIMESTAMP (6) DEFAULT now(), "editor" VARCHAR (128) COLLATE "default", "edit_time" TIMESTAMP (6) DEFAULT now(), "status" bool DEFAULT TRUE ); -- 第二步，使用前先清空临时表 TRUNCATE TABLE tmp_table2; -- 第三步，定义存储过程，num_limit 要插入数据的数量 DROP FUNCTION IF EXISTS insert_test2(); CREATE OR REPLACE FUNCTION insert_test2(IN num_limit INT) RETURNS void AS $$ DECLARE i INT DEFAULT 1; DECLARE encryptionphone_text INT DEFAULT 1; DECLARE initDate INT DEFAULT 1565332582; DECLARE initDateStr timestamp; BEGIN WHILE i <= num_limit LOOP initDateStr := to_timestamp(initDate+60*60*24*1+encryptionphone_text%(60*60*24*17)); INSERT INTO tmp_table2 ( "encryptionphone_text", "user_id", "target_code", "target_time", "creator", "create_time", "editor", "edit_time", "status" ) VALUES ( '1a2b3c4d5e6f7g8h9i' || CAST (@encryptionphone_text AS VARCHAR (32)), i, '9876543210', initDateStr, 'insert_test2函数所造', now(), NULL, now(), 't' ); encryptionphone_text := encryptionphone_text + 1; i := i + 1; END LOOP; END; $$ LANGUAGE plpgsql; -- 第四步，调用存储过程，插入1万条数据 SELECT insert_test2(10000); -- 第五步，查询临时表（可跳过） select * from tmp_table2; -- 第六步，插入到目标表 INSERT INTO target_table("encryptionphone_text", "user_id", "target_code", "target_time", "creator", "create_time", "editor", "edit_time", "status") SELECT * from tmp_table2; -- 第七步，检查插入数据是否正确 SELECT count(1) from target_table; -- 第八步，删除存储过程（若不需要删除则跳过） DROP FUNCTION insert_test2(IN num_limit INT);
除了上面格式的存储过程，可在转化数据后再插入数据，例子如下：
-- 第一步，创建临时表 CREATE TEMPORARY TABLE if not exists tmp_table ("phone_md5" varchar(32) COLLATE "default"); -- 第二步，使用前先清空临时表 TRUNCATE TABLE tmp_table; -- 第三步，定义存储过程，num_limit 要插入数据的数量 DROP FUNCTION IF EXISTS insert_test1(); CREATE OR REPLACE FUNCTION insert_test1(IN num_limit INT) RETURNS void AS $$ DECLARE i INT DEFAULT 1390000000; BEGIN WHILE i <= num_limit LOOP INSERT INTO tmp_table("phone_md5") VALUES (i); i := i + 1; END LOOP; END; $$ LANGUAGE plpgsql; -- 第四步，调用存储过程，插入1万条数据 SELECT insert_test1(1390010000); -- 第五步，查询临时表（可跳过） select * from tmp_table; -- 第六步，插入到目标表 INSERT INTO encryptionphone_list("phone_md5") SELECT md5(phone_md5) from tmp_table; -- 第七步，检查插入数据是否正确 SELECT count(1) from encryptionphone_list; -- 第八步，删除存储过程 DROP FUNCTION insert_test1(IN num_limit INT);
三、SqlServer造数存储过程实例
sqlserver造数
-- 第一步，删掉##aa缓存表 drop table ##aa; -- 第二步，定义缓存表##aa if OBJECT_ID('tempdb..##aa') is not null drop table ##aa CREATE TABLE ##aa( [phone] varchar(32) NULL , [name] varchar(32) NULL , [CreateDate] datetime NULL DEFAULT (getdate()) , [EditDate] datetime NULL DEFAULT (getdate()) ) -- 第三步，清空缓存表 TRUNCATE TABLE ##aa; -- 第四步，定义存储过程，并执行 DECLARE @i INT DECLARE @s VARCHAR(20) declare @phone int SET @i=1 set @phone=10000000 WHILE @i<=10000000 BEGIN SELECT @s=CAST(@i as VARCHAR(20)) insert into ##aa select '131'+CAST(@phone as VARCHAR(20)), '简单存储生成'+i, GETDATE(), GETDATE() set @phone=@phone+1 set @i=@i+1 end -- 第五步，把缓存表数据插入到目标表 insert into target_table( [phone], [name], [CreateDate], [EditDate]) select * from ##aa; -- 第六步，检查目标表数据 select * from target_table;
