mysql存储过程

背景

工作中有个需求需要用 sql 补录数据，很久没有写存贮过程了，重新学习了一波。现在记录下。

假设场景：

学校里有了新的政策，所有学生都需要办理学生卡，为了鼓励学生办卡，办卡时会赠送10块钱到卡里。

学生卡记录学生姓名，学生id，余额，创建时间字段。

实现目标：

把学生信息查出来，并向学生卡数据表里插入数据。

完整代码

数据准备

create table testdb.student_card
(
    id           int auto_increment comment '自增id'
        primary key,
    student_name varchar(32)       null comment '学生姓名',
    student_id   int               null comment '学生id',
    money        decimal default 0 not null comment '余额',
    create_time  datetime          null comment '创建时间'
)
    comment '学生卡';


create table testdb.students
(
    id          int auto_increment comment '主键id'
        primary key,
    country     varchar(32)  null comment '国家',
    body_height int          not null comment '身高（cm）',
    name        varchar(48)  null comment '名字',
    sex         varchar(2)   not null comment '性别',
    addr        varchar(128) null comment '住址',
    weight      int          null comment '体重（kg）'
)
    comment '学生表';

INSERT INTO testdb.students (id, country, body_height, name, sex, addr, weight) VALUES (1, '中国', 163, '张云绮', '男', '安溪镇南朝大街114号', 52);
INSERT INTO testdb.students (id, country, body_height, name, sex, addr, weight) VALUES (2, '中国', 152, '万雨', '女', '安乐村葫芦南路33号', 42);
INSERT INTO testdb.students (id, country, body_height, name, sex, addr, weight) VALUES (3, '美国', 172, 'petter', '男', '南河别墅社区118号', 62);

存储过程

create procedure my_procedure()
begin

    -- 定义变量
    declare init_money int default 10;
    declare stu_id int default 0;
    declare stu_country varchar(128) default '';
    declare stu_name varchar(128) default '';
    declare current_data date default null;

    -- 定义变量（循环结束标记，sql异常标记）
    declare loop_done boolean default false;
    declare sql_err boolean default false;
    -- 定义游标
    declare student_list cursor for select id,country,name from students;
    -- 定义 迭代 结束后  赋值变量
    declare continue handler for not found set loop_done=true;
    -- 定义 执行sql 异常 赋值变量
    declare continue handler for sqlexception set sql_err=true;
    -- 开启事务
    start transaction ;
    -- 开启游标
    open student_list;
    -- 预编译 sql
    prepare sql_insert from 'insert into student_card (student_name, student_id, money,create_time) values (?,?,?,?) ';

    -- 开始循环
    loop_tag:loop

        -- 迭代游标  赋值到变量
        fetch student_list into stu_id,stu_country,stu_name;
        -- 迭代 完毕 删除预编译，离开循环
        if loop_done then
            drop prepare sql_insert;
            leave loop_tag;
        end if;

        -- 参数赋值
        select now() into current_data;
        set @stu_name = stu_name,@stu_id = stu_id,@init_money = init_money,@create_time = current_data;
        -- 打印参数
        select 'execute_params'as 'logs', @stu_name as '学生名字', @stu_id as '学生id', @init_money as '金额',@create_time as '创建时间';
        -- 执行语句 （参数 必须为 @xxx 形式）
        execute sql_insert using @stu_name, @stu_id, @init_money,@create_time;

    -- 循环结束
    end loop loop_tag ;
    -- 关闭游标
    close student_list;

    -- 通过 sql 执行状态来 回滚 或 提交事务
    if sql_err then
        rollback ;
        -- 打印 执行结果
        select 'rollback' as 'execute_result';
    else
        commit ;
        -- 打印 执行结果
        select 'success' as 'execute_result';
    end if;

end;

常用语句

创建存储过程

create procedure my_procedure()
begin
	xxxx ....
end;


-- 带参数的 存储过程
create procedure procedure(in paramA varchar(2) ,out paramB int,inout paramC datetime)

删除存储过程

DROP PROCEDURE IF EXISTS my_procedure;

执行存储过程

call my_procedure();
-- 传参
call my_procedure(paramA，paramB，paramC);

定义变量

declare paramA int;
declare paramB varchar(20) default 'paramB';
declare paramC datetime default '2021-03-21 15:23:24';

变量赋值

set paramA = 10;
set @paramB = expr...;
select id into paramA from students where name = 'petter' limit 1;

流程控制语句

-- if else
create procedure test(in aaa int)
    begin
        if aaa<=10 then
        	select '<=10' as 'out';
        elseif aaa>10 and aaa<=20 then
            select '> 10 and <= 20' as 'out';
        elseif aaa>20 then
            select '>20' as 'out';
        end if;
    end;
-- while do 
create procedure test(in aaa int)
    begin
        while aaa>10 do
            select aaa;
            set aaa = aaa-1;
            end while;
    end;
-- while do 标记 跳出循环
CREATE PROCEDURE test ()
BEGIN
    DECLARE i INT default 0;
    outer_label: BEGIN  #设置一个标记并设置开始
        while i<10 DO
                SET i = i + 1;
                IF i > 2 THEN
                    select '跳出循环' column1;
                    LEAVE  outer_label;  #满足条件，终止循环，跳转到end outer_label标记
                ELSE
                    SELECT i as column1;
                END IF;
            end while;
    END outer_label;  #设置标记并结束
END;
-- loop 循环
CREATE PROCEDURE test (in a int)
BEGIN
    -- 开始循环
    loop_tag:loop
        set a = a+1;
        
        if a=3 then
            -- 跳出循环
            leave loop_tag;
        end if;
        select a 'current_a';
        -- 循环结束
    end loop loop_tag ;
END;
-- repeat 循环
CREATE PROCEDURE test (in a int)
begin
    repeat -- 循环开始
    select a; -- 输出结果
    set a=a+1;
    until a=3 end repeat; -- 满足条件循环结束
end;

事件处理

declare continue/exit handler for xxx....   do ....

-- 定义 not found 时 设置变量值  并  继续/退出
declare continue/exit handler for not found set loop_done=true;
-- 定义 sql 异常 时 设置变量值  并  继续/退出
declare continue/exit handler for sqlexception set sql_err=true;

预编译

-- 预编译
PREPARE stmt_name FROM preparable_stmt
-- 执行 using 传参 （参数必须时 @xxx）
EXECUTE stmt_name
 [USING @var_name [, @var_name] ...] -
-- 删除 预编译
{DEALLOCATE | DROP} PREPARE stmt_name

游标

-- 定义游标
DECLARE cursor_name CURSOR FOR SELECT_statement;
-- 打开游标
OPEN cursor_name;
-- 遍历数据并赋值到 变量列表
FETCH cursor_name INTO variable_name_list;
-- 关闭游标
CLOSE cursor_name;

注意事项

• 游标的 定义。必须放在变量定义之后。

• 注意循环退出条件，不要造成死循环。

• 不同的循环配合不同的退出条件，循环体的执行次数略有差异，实际使用时要注意（上生产时 要做充分的测试）