MySql、PostgreSql、SqlServer三种数据库的造数存储过程实例

时间：2023-05-28 15:48:20

主要实例：把临时表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 ''

) 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存储过程',

                    ''

                ) ;

            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,

        '',

        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 ''+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;

相关文章