MySQL存储过程

目录

介绍

特点

创建

调用

查看

删除

变量

查看系统变量

设置系统变量

注意：

用户定义变量

赋值

使用

局部变量

声明

赋值

if

参数

用法：

case

while

repeat

loop

游标

条件处理程序

介绍

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

特点

封账，复用
可以接收参数，也可以返回数据
减少网络交互，效率提升

创建

create procedure 存储过程名称([参数列表])
begin
        -- sql语句
end;

调用

call 名称([参数]);

查看

select * from information_schema.routines where routine_schema = 'xxx'  --  查询指定数据库的存储过程及状态信息
show create procedure 存储过程名称； 查询某个存储过程定义

删除

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

注意：在命令行中，执行创建存储过程的sql ,需要通过关键字delimiter指定sql语句的结束符。

变量

系统变量是mysql服务器提供，不是用户定义的，属于服务器层面。分为全局变量(global)、会话变量(session).

查看系统变量

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

设置系统变量

set [session | global] 系统变量名 = 值；
set [session | global] 系统变量名 = 值；

注意：

如果没有指定session/global,默认是session，会话变量。
mysql服务重新启动之后，所设置的全局参数会失效，想要不失效，可以再/etc/my.cnf中配置

用户定义变量

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

赋值

set @var_name= expr [,@var_name = expr]...;
set @var_name :=expr[,@var_name :=expr]...;
​
select @var_name := expr[,@var_name :=expr]....;
select 字段名 into @var_name from 表名；

使用

select @var_name;

注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为null.

局部变量

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

声明

declare 变量名变量类型 [default.....];
变量类型就是数据库字段类型：int、bigint、char、varchar、date、time等。
declare stu_count int default 0;

赋值

set 变量名 = 值；
set 变量名: = 值；
select 字段名 into 变量名 from 表名...;

if

语法：
if 条件1 then
        .....
elself 条件2 then     ... 可选      
         .....
else                  ... 可选
        .....    
end if;
 

参数

用法：

create procedure 存储过程名称 ([in/out/inout 参数名 参数类型])
begin
        -- sql语句
end;

case

语法一
Case case_value
        when when_value1 then statement_list1
        [when when_value2 then statement_list2]....
        [else statement_list]
end case;

语法二
 case
    when search_condition1 then statement_list1
    [when search_condition1 then statement_list1]....
    [else statement_list]
 end case;

while

while 循环是有条件的循环控制语句。满足条件后，在执行循环体中的sql语句。具体语法为：
#先判定条件如果条件为true,则执行逻辑，否则，不执行逻辑
while 条件 do
    sql 逻辑....
end while; -- 每一个这里都有结束标志

repeat

repeat是有条件的循环控制语句，当满足条件的时候退出循环。
# 先执行一次逻辑，然后判断逻辑是否满足，如果满足，则退出，如果不满足，则继续下一次循环
repeat
        sql 逻辑
        until 条件
end repeat;

loop

loop实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合一下两个语句使用：
    leave:配合循环使用，退出循环。
    iterate: 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
    sql逻辑....
end loop [end_label];
leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环

游标

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

声明游标
declare 游标名称 cursor for 查询语句； // 变量要定义在游标前面，异常要定义在后面
打开游标
open 游标名称；
获取游标记录
fetch 游标名称 into 变量[,变量];
关闭游标
close 游标名称；

条件处理程序

条件处理程序 (handler) 可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

create procedure p6(in uage int) -- 这两个变量的名字还不能起一样的
begin
​
    declare uname varchar(200);
    declare usex varchar(200);
    declare u_cursor cursor for select name,gender from user where age <= uage;
    declare exit handler for sqlstate '02000' close u_cursor;
    -- declare exit handler for not found close u_cursor; 这两句都是可以的，主要用来结束循环的
    drop table if exists tb_user;
    create table tb_user(
      id int primary key auto_increment,
      name varchar(100),
      gender varchar(100)
    );
​
    open u_cursor;
​
    while true do
            fetch u_cursor into uname,usex;
            insert into tb_user value (null,uname,usex);
        end while;
​
    close u_cursor;
end;