MySQL之存储过程

MySQL存储过程

线上程序有时候出现问题导致数据错误的时候，如果比较着急，我们可以写一个存储来快速修复这块的数据，（开发中最常见的方式）

存储过程相对与Java开发来说，可以并不是太好维护以及阅读的，所以不建议在程序中去调用存储过程做一些业务操作，

准备数据

-- 如果有test库就删除test库
drop database if exists test;
-- 创建test库
create database test;
-- 如果有t_user进行删除
drop table if exists t_user;
create table t_user(
    id int not null primary key comment '编号',
    age smallint not null comment '年龄',
    name varchar(20) not null comment '姓名'
)collate = utf8mb4_bin charset = utf8mb4 engine = InnoDB;

存储过程

概念

一组预编译好的sql语句集合，理解为批处理语句

好处

• 提高代码的重用性
• 简化操作
• 减少编译次数并且减少和数据库服务器连接的次数，提高效率

创建存储过程

create procedure 存储过程的名字（[参数模式] 参数名 参数类型）
begin 	
	存储过程体
end

参数模式：

• in: 参数可以作为输入，也就是参数需要调用方传入值
• out：参数可以作为输出，也就是该参数可以作为返回值
• inout：该参数可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，也可以作为返回值，

参数模式默认为in；

一个存储过程可以有多个输入，多个输出，多个输入输出参数。

调用参数过程

call 存储过程名称（参数列表）

删除存储过程

drop procedure [if exists] 存储过程名称;

删除过程只能一个一个删除，不能批量删除

修改存储过程

存储过程不能修改，如果说涉及到，可以先删除，在重建

查看存储过程

show create procedure 存储过程名称；

示例：

示例1：空参列表

创建存储过程

-- 设置结束符号$
delimiter $
-- 创建存储过程
create procedure pro1()
    begin
        insert into t_user values (1,18,'张三');
        insert into t_user values (2,20,'李四');
    end $

delimiter用来设置结束符，当mysql执行脚本的时候，会遇到结束符的时候，此处会用到delimiter

调取存储过程：

call pro1();

验证结果：

select id, age, name from t_user;

存储过程调用成功，t_user表成功插入2条数据

示例2：带in参数的存储过程

创建存储过程：

-- 设置结束符号$
delimiter $
-- 创建存储过程
create procedure pro2(id int,age int,in  name varchar(20))
    begin
        insert into t_user values (id,age,name);
    end $

调用存储过程：

-- 创建三个自定义变量
select @id:=3,@age:=23,@name:='wangwu';
-- 调用存储过程
call pro2(@id,@age,@name);

验证结果;

示例3：带out参数的存储过程

聚合函数查询
聚合函数又名组函数，常用来对表中的数据进行统计和技术，
   常用的聚合函数
  - count:表示求指定的列的总数
  - max：表示求指定的最大值
  - min：表示求指定的最小值
  - sum：表示求指定列的和
  - avg：表示求指定列的平均值

-- 设置结束符$
delimiter $
-- 创建存储过程
create procedure pro3(out t_user_count int ,out max_age int )
    begin
        select count(*),max(age) into t_user_count,max_age from t_user;
    end $

调用存储过程

-- 调用存储过程
call p4(@t_user_count,@max_age);

验证结果：

select @t_user_count,@max_age;

·

示例4：带out参数的存储过程

创建存储过程

-- 设置结束符$
delimiter $
-- 创建存储过程
create procedure pro3(id int,age int ,in name varchar(20),
                        out t_user_count int,out max_age int )
    begin
        insert into t_user values (id,age,name);
        select count(*),max(age) into t_user_count,max_age from t_user;
    end $

调用存储过程

-- 创建三个自定义变量
select @id:=4,@age:=23,@name:='sunba';
-- 调用存储过程
call pro3(@id,@age,@name,@t_user_count,@max_age);

验证结果：

select @t_user_count,@max_age;

示例5：带inout参数的存储过程

创建存储过程：

-- 设置结束符$
delimiter $
-- 创建存储过程pro4
create procedure pro4(inout a int ,inout b int)
    begin
        set a = a * 2;
        select b * 2 into b;
    end $

调用存储过程：

-- 创建2个自定义变量
set @a = 10,@b = 20;
-- 调用存储过程
call pro4(@a,@b);

验证结果：

select @a,@b;
+----|----+
| @a | @b |
+----|----+
| 20 | 40 |
+----|----+

查看存储过程

show create procedure pro4;

定义一个流程控制语句if else

if语句包含多个条件判断，根据结果为true，false执行语句，与编程语言中的if else if语法类似

定义存储过程

create procedure pro1(in day int)
begin
    if day = 0 then
        select '星期天';
        elseif day = 1 then
        select '星期一';
        elseif day = 2 then
        select '星期二';
        elseif day = 3 then
        select '星期三';
        elseif day = 4 then
        select '星期四';
        elseif day = 5 then
        select '星期五';
        elseif day = 6 then
        select '星期六';
    end if;
end;

调用存储过程

call pro1(5);

结果：

定义一个条件控制语句case

定义存储过程

create procedure pro2(in num int)
begin
    case -- 条件开始
        when  num<0 then
        select '负数';
        when num >0 then
        select '正数';
        else
        select '不是正数也不是负数';
        end case ;-- 条件结束
end;

调用存储过程

call pro2(1);

结果：

定义一个循环语句 while

定义存储过程

create procedure pro3(in num int ,out sum int)
begin
    set sum = 0;
    while num<10 do -- 开始循环
        set num = num + 1;
    set sum = sum +num;
    end while ; -- 结束循环
end;

调用存储过程

-- 调用函数
call pro3(0,@sum);
-- 查询函数
select @sum;

结果：