MySQL存储过程

1.什么是存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程就是数据库SQL语言层面的代码封装和重用。

2.存储过程的基本语法

2.1、创建存储过程

create procedure procedure_name([参数列表])
begin

    --sql语句

end;

2.2、调用存储过程

call procedure_name([参数]);

2.3、查看存储过程

select * from information_schema.routines where routine_schema='XXX';
--查询指定数据库的存储过程及状态信息
--XXX:数据库名称

2.4、删除存储过程

drop procedure [if exists] procedure_name;

3.存储过程变量

3.1、系统变量

系统变量是MySQL服务器提供的，分为全局变量（global）和会话变量（session），默认是session；MySQL服务重启后，所设置的全局参数会失效，可以在/ect/my.cnf中配置，以防止失效。

--查看系统变量
show [session|global] variables;  --查看所有系统变量
show [session|global] variables like ...; --可以通过like模糊匹配查询
select @@[session|global] 系统变量名; --查看指定变量的值


--设置系统变量
set [session|global] 系统变量名=值;
set @@[session|global] 系统变量名=值;

3.2、用户自定义变量

 用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用‘@变量名’使用就可以，其作用域为当前连接。

tip:@变量->用户自定义变量；@@变量->系统变量

1.赋值
set @var_name =expr[,@var_name =expr]...;
set @var_name =expr[,@var_name :=expr]...;
select @var_name :=expr[,@var_name :=expr]...;
select col_name into @var_name from tb1;

2.使用
select @var_name;

3.3、局部变量

根据需要定义在局部生效的变量，访问之前，需要declare声明，可以作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin ... end 块。

1.声明
declare 变量名 变量类型[default ...];

2.赋值
set 变量名 =值;
set 变量名 :=值;
select col into 变量名 from tb1;

4.常用语法

4.1、if条件判断

语法：

if 条件1 then
    ...
elseif 条件2 then      --可选
    ...
else                   --可选
    ...
end if;

例子：

create procedure p1()
begin
    declare score int default 68;
    declare result varchar(18);
    
    if score>85 then
        set result := '优秀';
    elseif score>=60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    select result;

end;

4.2、存储过程的参数

in（默认）：该类参数作为输入，需要调用时传入值

out：该类参数作为输出，该参数可以作为返回值

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

语法：

create procedure procedure_name([in/out/inout 参数名 参数类型])
begin

    --sql语句

end;

例子：

create procedure p2(in score int,out result varchar(18))
begin
    declare result varchar(18);
    
    if score>85 then
        set result := '优秀';
    elseif score>=60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

4.3、case语法

--语法1  判断case_value值，决定执行哪个分支
case case_value
        when when_value1 then statement_list1
        [when when_value2 then statement_list2]...
        [else statement_list]
end case;

--语法2 条件表达式写在when之后
case 
        when search_condition1 then statement_list1
        [when search_condition2 then statement_list2]...
        [else statement_list]
end case;

例子：

create procedure p3(in month int, out result varchar(18))
begin
    declare result varchar(18);
    case
        when month >= 1 and month <=3 then
            set result :='第一季度';
        when month >= 4 and month <=6 then
            set result :='第二季度';
        when month >= 7 and month <=9 then
            set result :='第三季度';
        when month >= 10 and month <=12 then
            set result :='第四季度';
        else 
            set result :='非法参数';
    end case;
end;

4.4、while语法

while循环是有条件的循环控制语句，满足条件后，再执行循环体中的SQL语句。

while condition do

    sql逻辑

end while;

--先判定条件，如果条件为true，则执行逻辑

例子：

--从0累加到n
create procedure p4(in n int,out total int)
begin 
    daclare total int defailt 0;
    while n>0 do
        set total := total+n;
        set n := n-1;
    end while;
end;

4.5、repeat

repeat是有条件的循环控制语句，当满足条件的时候退出循环，语法：

repeat
    
    SQL逻辑

    until 条件
end repeat;

例子：

--从0累加到n
create procedure p5(in n int,out total int)
begin 
    daclare total int defailt 0;
    repeat
        set total := total+n;
        set n := n-1;
    until n<=0
    end repeat;
end;

4.6、loop

loop实现简单循环，如果不在SQL逻辑中增加退出循环的条件，就是一个死循环。loop可以配合两个语句使用：

leave:配合循环使用，退出循环；

iterate：必须用在循环中，作用是跳出当前循环剩下的语句，直接进入下一次循环；

[begin_label:]loop

sql逻辑

end loop[end_label]


leave lable;   --退出指定标记的循环体
iterate label;  --直接进入下一次循环

例子：

--从0累加到n
create procedure p6(in n int,out total int)
begin 
    daclare total int defailt 0;
    sum:loop
        if n<=0 then 
            leave sum;
        end if;
        set total := total + n;
        set n := n - 1;
    end loop sum;
end;

--偶数累加到n
create procedure p7(in n int,out total int)
begin 
    daclare total int defailt 0;
    sum:loop
        if n<=0 then 
            leave sum;
        end if;

        if n%2=1 then 
            set n := n - 1;
            iterate sum;
        end if;

        set total := total + n;
        set n := n - 1;
    end loop sum;
end;

4.7、游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中，可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch和close，其语法如下：

--声明游标
declare 游标名称 cursor for 查询语句;

--打开游标
open 游标名称;

--获取游标记录
fetch 游标名称 into 变量[,变量];

--关闭游标
close 游标名称;

例子：

create procedure p7(in id int)
begin 
    declare uid int;                    --定义接收参数，参数定义要在游标定义之前
    declare uname varchar(100);         
    declare u_cursor cursor for select id,name from user where id>=1;    --定义游标
    declare exit handler for SQLSTATE '02000' close u_cursor;  --声明条件处理程序

    drop table if exists tt;
    create table if not exist tt(       --定义新表tt
        tid int primary key auto_increment,
        id int ,
        name varchar(100)
    );

    open u_cursor;                    --打开游标
    while true do                       --死循环
        fetch u_cursor into uid,uname;        --取出游标的值，注意要按顺序
        insert into tt values(null,uid,uname); --将值插入新表tt
    end while;
    
    close u_cursor;                    --关闭游标
end;

4.8、条件处理程序（handler）

条件处理程序（handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤，语法：

declare handler_action handler for condition_value[,condition_value] ... statement;

handler_action
    continue:继续执行当前程序
    exit:终止执行当前程序
condition_value
    SQLSTATE sqlstate_value:状态码，如02000
    SQLWARNING：所有以01开头的SQLSTATE代码的简写
    NOT FOUND：所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

4.9、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型，语法：

create function 存储函数名称([参数列表])
return type [characteristic...]
begin 

        --sql语句
        return ...;
end;




charactermistic说明：
determinstic:相同输入参数总是产生相同的结果
no sql:不包含sql语句
reads sql data:包含读取数据的语句，但不包含写入数据的语句

例子：

create function fun1(n int)
return int deterministic
begin
    declare total int default 0;
    while n>0 do
        set total := total +n;
        set n := n-1;
    end while;
    return total;
end;

调用函数：

select fun1(50);  --50只是例子，可以填任意int值