【Mysql实战】存储过程详解

快速入门

创建存储过程

CREATE PROCEDURE p1()

BEGIN
	-- SQL语句
	SELECT count(*) FROM user;
END;

调用存储过程

CALL p1();

查看存储过程

SHOW CREATE PROCEDURE 存储过程名称

删除存储过程

DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ;

入参出参

• IN：该类参数作为输入，也就是需要调用时传入值

• OUT：该类参数作为输出，也就是该参数可以作为返回值

• INOUT：既可以作为输入参数，也可以作为输出参数

创建存储过程

create procedure p4(in score int,out result varchar(12))

BEGIN

	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';

	end if;

END;

执行存储过程

call p4(90,@result);

select @result;

构造数据

工具表

create table user
(
    id          bigint unsigned auto_increment comment 'ID'
        primary key,
    code        varchar(64) default '' not null comment '用户编码',
    name        varchar(64) default '' not null comment '用户信息',
    create_time datetime               null comment '创建时间'
) comment '用户表';

存储过程

CREATE PROCEDURE `make_data`()
BEGIN
    DECLARE userId varchar(100);
    DECLARE userName varchar(100);
    DECLARE taskType INTEGER;
    declare taskTypeName varchar(20);
    declare createTime datetime;
    declare startTime datetime;
    declare endTime datetime;
    declare finishTime datetime;
    declare enumVal INTEGER;
    declare enumName varchar(20);
    declare qrcode varchar(20);
    declare num INTEGER;
    declare startIndex INTEGER;
    declare uid INTEGER;
    set createTime = now();
    set startTime = date_sub(now(), interval floor(rand() * 24) hour);
    set endTime = date_add(now(), interval floor(rand() * 24) hour);
    set finishTime = date_add(now(), interval floor(rand() * 12) hour);
    START TRANSACTION;
    SELECT id, name FROM user ORDER BY RAND() LIMIT 1 into userId,userName;
    set taskType = floor(rand() * 10);
    set enumVal = floor(rand() * 3);
    if (taskType < 3) then
        set taskTypeName = 'aaa';
    elseif (taskType < 7) then
        set taskTypeName = 'bbb';
    elseif (taskType < 10) then
        set taskTypeName = 'ccc';
    end if;
    case enumVal
        when 0 then set enumName = 'AA';
        when 1 then set enumName = 'BB';
        when 2 then set enumName = 'CC';
        end case;
    set qrcode = (SELECT LPAD(now(), 10, 0) AS TIME);
    set num = (select count(1) from user where name = qrcode);
    if (num = 0) THEN
        INSERT INTO user (name, code, create_time) VALUES (enumVal, qrcode, now());
        select max(id) from user into uid;
        -- 插入关联表数据
    ELSE
        SET startIndex = startIndex + 1;
    END IF;
    select userId,
           userName,
           taskType,
           taskTypeName,
           createTime,
           startTime,
           endTime,
           finishTime;
END;

• 通过使用if函数和rand(),taskTypeName可以随机从aaa，bbb，ccc中获取；
• 通过使用case函数和rand()，enumVal的取值也是随机的。
• num存储执行mysql语句的执行结果，可以根据查询到的结果判断是否需要新增数据操作。
• uid存储了插入user表的主键，可以进行插入关联表的数据
• 使用SELECT id, name FROM user ORDER BY RAND() LIMIT 1，可以随机查询到user表的任意一条数据。
• endTime和finishTime是随机的时间段，endTime在当前时间的0-24小时内随机，finishTime在当前时间的0-12小时内随机。

批量插入数据

工具表

-- auto-generated definition
create table app_task
(
    id           bigint auto_increment comment '主键'
        primary key,
    user_id      int               null comment '当前执行人',
    user_name    varchar(24)       null comment '当前执行人名字',
    arrival_time datetime          null comment '到达时间',
    start_time   datetime          null comment '开始时间',
    finish_time  datetime          null comment '完成时间',
    expect_time  datetime          null comment '预计时间',
    create_time  datetime          null comment '创建时间',
    is_delete    tinyint default 0 null comment '0-生效，1-失效'
)
    comment '任务表';

存储过程

CREATE PROCEDURE `batch_insert_data`(
    IN count INTEGER, -- record参数用来传需要插入数据的条数
    IN dt timestamp -- dt参数用来传入时间戳，开始插入的第一条数据的时间（格式为：'2023-05-14 14:31:44'）
)
BEGIN
    DECLARE number INTEGER; -- 声明一个number，用来控制循环的次数
    set number = 1; -- 将number的值赋值为1，代表循环从1开始
    START TRANSACTION;
    WHILE number <= count
        DO
            -- 使用while进行循环，满足条件进入循环
            select date_add(dt, interval 1 second) into dt;
            -- 使用date_add()函数将时间进行转换为秒数，并赋值给dt参数
-- 插入数据
            INSERT INTO app_task -- 库名.表名
-- 由于id是自动增长，不需要额外赋值，所以需要将剩余的其他字段全部列出进行一一对应赋值插入
            (user_id,
             user_name,
             arrival_time,
             start_time,
             finish_time,
             expect_time,
             create_time,
             is_delete)
            VALUES (FLOOR(RAND() * 10),
                    2,
                    now(),
                    DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),
                    DATE_ADD(CURDATE(), INTERVAL 23 HOUR),
                    dt,
                    now(),
                    0);
-- number参数进行自增
            set number = number + 1;
-- dt参数进行自增
            set dt = date_add(dt, interval 1 second);
        end WHILE;
    COMMIT;
END;

使用游标

create procedure use_cursor()
begin

    declare u_name varchar(200);
    declare u_code varchar(200);
    declare u_cursor cursor for select name, code from user where id > 1;
    -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
    declare exit handler for SQLSTATE '02000' close u_cursor;
    create table if not exists tb_user
    (
        id     int primary key auto_increment,
        u_name varchar(200),
        u_code varchar(200)
    );
    open u_cursor;
    while true
        do
            fetch u_cursor into u_name,u_code;

            select u_code, u_name;
            insert into tb_user values (null, u_name, u_name);
        end while;
    -- 关闭游标
    close u_cursor;
end;

• 游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE
• 游标的使用是为了获取游标指向的数据集。

批量给表添加字段

create procedure batch_add_column(
in database_name varchar(60),
in table_name_match varchar(60)
)

BEGIN

-- 临时变量 TABLE NAME
DECLARE T_NAME VARCHAR(256) DEFAULT NULL;
DECLARE T_SQL VARCHAR(256) DEFAULT NULL;

-- 游标相关 CURSOR TABLE_NAMES
DECLARE C_ALL_TABLE CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = database_name AND table_name REGEXP table_name_match;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET T_NAME = NULL;

OPEN C_ALL_TABLE;
    FETCH C_ALL_TABLE INTO T_NAME;
    WHILE(T_NAME IS NOT NULL) DO
        SET @T_SQL = CONCAT('ALTER TABLE ', T_NAME, ' ADD COLUMN CREATE_TIME datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT \'创建时间\'');
        PREPARE statm FROM @T_SQL;
        EXECUTE statm;
        FETCH C_ALL_TABLE INTO T_NAME;
    END WHILE;
CLOSE C_ALL_TABLE;

END;