MySQL由浅入深探究存储过程

MySQL
313
0
0
2023-06-26
like模糊匹配查询慢解决之道——MySQL全文索
上一篇
MySQL参数调优实例探究讲解
下一篇
目录
  • 一、存储过程的介绍
  • 二、存储过程的基本语法
  • 三、变量
  • （1）系统变量
  • （2）用户自定义变量
  • （3）局部变量
  • 四、存储过程的语法详解
  • （1）if判断
  • （2）参数
  • （3）条件判断case语句
  • （4）while循环语句
  • （5）repeat循环语句
  • （6）loop循环语句
  • （7）cursor游标

一、存储过程的介绍

存储过程是事先经经过编译并存储在数据库中的一段SQL语句的集合，调用存储

过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于高效数据处理

的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用，你可以将

它和C语言中的函数类比，注意是类比而不是相同。

特点：封装，复用，可以接受参数，也可以返回数据，减少网络交互，效率提升

二、存储过程的基本语法

 创建存储过程：
 create procedure 存储过程名称(参数列表)
 begin
  -SQL语句
 end;
 调用存储过程：
 call 存储过程名称（参数）
 查看在哪个数据库下的存储过程的语句：
 select *from information_schema.routines where routine_schema='数据库名';
 查看某个存储过程的定义，也就是创建存储过程的语句
 show create procedure 存储过程名称; 
 删除存储过程：
 drop procedure if exists 存储过程名称:

举例：

#使用class_first数据库
use class_first;
# 开始创建存储过程
create procedure p()
begin
    select *from s;
end;
create procedure p()
begin
    select *from p;
end;
# 调用其中一个存储过程p
call p();
# 查看当前数据库存在的存储过程
select *from information_schema.ROUTINES where routine_schema='class_first';
# 查看某一个创建某一个存储过程的语句，假如查看的是存储过程p
show create procedure p;

三、变量

（1）系统变量

系统变量是MySQL服务器提供，不是用户自定义的，属于服务器层面，分为全局变量(global)和会话变量(session)，会话变量指的是在当前控制台的变量，假如修改了话变量，但是重新打开了另外一个控制台，查看时会发现并未修改。

查看系统变量
 show [session/global] variables;      查看所有系统变量
 show [session/global] variables like '...'; 可以通过like模糊匹配方式查找变量
 select @@[session/global].系统变量名    查看指定变量的值
设置系统变量
 set [session/global] 系统变量名=值;
 set @@[session/global]系统变量=值;
show session variables;
show session variables like 'auto%';
set session autocommit=;
关闭了当前会话的自动提交，但是其他会话并未关闭

全局变量的修改在MySQL服务器重新启动后还是会回到初始值，想要永久修改的话，要修改MySQL的部分配置文件。

（2）用户自定义变量

用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用"@变量名"即可，假如这个时候并未赋值，那么得到的值就是NULL，其作用域为当前连接。

赋值
  set @变量名=值;
  set @变量名:=值;
  select @变量名:=值；
  从表格查询将查询的数据赋值给变量
  select 字段名 into @变量名 from 表名; 
 使用变量
  select @变量名;
select @s;#并未给s赋值，得到的是NULL


set @ss:=;
select @io:='opop';
select @ss,@io;

（3）局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要declare声明，可以作存储过程

内的局部变量和输入参数，局部变量的范围是在其内声明的begin...end块。

声明：
 declare 变量名 变量类型 （如果有默认值则 default...）
 变量类型：int,bigint,char,varchar,dae,time
 赋值
  set 变量名=值
  set 变量名:=值
  select 字段名 into 变量名 from 表名...；
create procedure p()
begin
    declare st int default;
    declare sss int;
    select  count(*) into sss from s;
    select sss;
end;
call p();

四、存储过程的语法详解

（1）if判断

1:if判断
 if 条件 then
 ...
 end if
2:if...elseif判断
 if 条件 then
 ...
 elseif 条件2 then
 ...
 end if
3:if...else判断
if 条件 then
...
else 
...
end if

（2）参数

参数：
  in  该类参数作为输入，也就是需要调用时传入值（什么也没有是默认是in参数）
  out  该类参数作为输出，也就是该参数可以作为返回值
  inout 既可以作为输入参数，也可以作为输出参数
用法：
 create procedure 存储过程名称([in/out/inout]参数名 参数类型)
 begin
  SQL语句
 end;

举个例子，输入成绩，得到成绩的等级

create procedure p(in score int,out result varchar(10))
begin
     if score>=&&score<=100 then
         set result:='优秀';
    elseif score>=&&score<=100 then
         set result:='及格';
    elseif score>=&&score<=100 then
         set result:='不及格';
    else
         set result:='输入的参数是非法参数';
     end if;
end;
call p(819,@ioio);//这里第二个返回的参数是用户自定义的变量，记得要用@哦
select @ioio;

第二个例子是关于inout的使用

create procedure p(inout result int)
begin
     set result:=result*.5;
end;
set @:=100;
call p(@9);
select @;

（3）条件判断case语句

case
 when 条件表达式1 then
  ...
 when 条件表达式2 then
  ...
 ...
 else
  ...
end case;

需求：一月到三月是第一季度，每三个月是一个季度，现在输入一个月份，判断是第几季度。

create procedure p(in res int,out ul varchar(10))
begin
     case
         when res>=&&res<=3 then
           set ul:='第一季度';
        when res>=&&res<=6 then
           set ul:='第二季度';
        when res>=&&res<=9 then
           set ul:='第三季度';
        when res>=&&res<=12 then
           set ul:='第四季度';
        else
           set ul:='你输入的是非法参数';
        end case;
end;
call p(-1,@res);
select  @res;

（4）while循环语句

如果条件是true就继续下去循环知道为false
while 条件 do
 SQL语句
end while;

需求：求1到n的和：

create procedure p(in n int)
begin
    declare sum int default;
    declare i int default;
    while i<=n do
        set sum:=sum+i;
        set i:=i+;
        end while;
    select sum;
end;
call p(100);

（5）repeat循环语句

repeat和while循环不一样，while循环满足条件继续循环，而repeat循环满足条件则跳出循环。

repeat 
 SQL逻辑
 until 条件
end repeat：

如：求1到n的和

create procedure p(in n int)
begin
    declare sum int default;
    declare i int default;
    repeat
        set sum:=sum+i;
        set i=i+;
    until i>n
        end repeat;
    select sum;
end;
call p(10);

（6）loop循环语句

loop可以配合一下两个语句实现简单的退出循环
leave：退出当前的循环
iterate：结束本次循环，直接进行下一次的循环
语法：
 循环名称:loop
   循环体
  end loop;

求1到n之间的和（使用loop）

create procedure p(in n int)
begin
    declare sum int default;
    declare i int default;
    su:loop
        if i>n then
            leave su;
        end if;
        set sum:=sum+i;
        set i:=i+;
    end loop;
    select sum;
end;
call p(100);

求1到n之间偶数的和

create procedure p(in n int)
begin
     declare sum int default;
     declare i int default;
     su:loop
         set i:=i+;
        if i%=1 then
          iterate su;
        end if;
        if i>n then
             leave su;
         end if;
        set sum:=sum+i;
     end loop;
     select sum;
end;
call p(10);

（7）cursor游标

游标是用来莻查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环

的处理。游标的使用包括游标的声明，open，fetch和close。也就是说游标可以歌剧

自己想要的条件得到一个筛选过的结果集。其用法分别如下：

1：声明游标
 declare 游标名称 cursor for 查询语句;
2：打开游标
 open 游标名称
3：获取游标记录
 fetch 游标名称 into 变量,[变量];
4:关闭游标
 close 游标名

再具体举例之前还得说一下条件处理处理程序，为什么要说呢？在获取游标记录时我们使用循环来获取，直到游标中的数据获取完了，但要怎么判断获取结束，这时候就需要条件处理程序了。

条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相对应的处理步骤。
语法：
 declare 行为 handler for 状态码 +sql逻辑语句
行为：
 continue 继续执行当前程序
 exit 终止执行当前程序
状态码
 如02000之类
 sqlwarning sql警告，所有以01开头的代码简写
 not found 未找到数据，所以以02开头
 sqlexception 没有被sqlwarning和not found捕获的代码简写

具体我们来举个例子

这里我创建了一张表，现在我要将年龄小于自定义输入的值再重新放入一个表格中（如年龄小于20岁）：

create table sp(
    age int,
    name varchar()
);
insert into sp values (,'李四'),
(,'张三'),
(,'王二麻子'),
(,'赵云'),
(,'查类'),
(,'谢逊'),
(,'李白'),
(,'杜甫'),
(,'韩信');


create procedure p(in uage int)
begin
    declare usname varchar();
    declare u_age int;
    declare u_cursor cursor for select name,age from sp where age<uage;
    declare exit handler for not found close u_cursor;
    drop table if exists stu;
    create table stu(
        u_name varchar(),
        u_age int
    );
    open u_cursor;
    while true do
        fetch u_cursor into usname,u_age;
        insert into stu(u_name, u_age) values(usname,u_age);
        end while;
    close u_cursor;
end;
call p(20);

同时数据库中也出现了stu表

like模糊匹配查询慢解决之道——MySQL全文索
上一篇
MySQL参数调优实例探究讲解
下一篇