526互联

mysql新增插入的时候如何写存储过程

发布时间 2023-11-01 20:22:36作者: 小陈子博客

sql初始化脚本

create table ACT_RESULT_LOG
(
    onlineseqid VARCHAR(32),
    businessid  VARCHAR(32),
    becifno     VARCHAR(32),
    ivisresult  VARCHAR(32),
    createdby   VARCHAR(32),
    createddate DATE,
    updateby    VARCHAR(32),
    updateddate DATE,
    risklevel   VARCHAR(32)
);

新增存储过程

## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_procedure;
delimiter;;
CREATE PROCEDURE insert_procedure ()
BEGIN
    # 定义循环值
    DECLARE i INT DEFAULT 1;
    # 开启事务
    START TRANSACTION;
    # 开始循环插入
    WHILE ( i <= 1000000 ) DO
            INSERT INTO `ACT_RESULT_LOG`(`onlineseqid`,`businessid`, `becifno`, `ivisresult`, `createdby`, `createddate`, `updateby`, `updateddate`, `risklevel`) VALUES (CONCAT('ON00000',i), CEIL(RAND() * 100), CEIL(RAND() * 100), CONCAT('笔记本电脑',i), MOD(i, 4)+1, NOW(), MOD(i, 4)+1, NOW(),CONCAT('笔记本电脑',i));
            SET i = i + 1;
        END WHILE;
END;;
delimiter;

调用存储过程插入数据

CALL insert_procedure ();

参考链接

https://blog.csdn.net/weixin_44606481/article/details/133426905
https://xkzzz.com/post/132973.html
https://blog.csdn.net/qq_44300280/article/details/128580960