MYSQL（存储过程）

小二店

已于 2024-08-11 18:56:32 修改

阅读量977

点赞数 7

文章标签： mysql 数据库

于 2024-08-11 18:46:24 首次发布

本文链接：https://blog.csdn.net/2301_81784283/article/details/141108024

版权

1.介绍

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

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

特点：

封装，复用

可以接收参数，也可以返回数据

减少网络交互，效率提升

2.基本语法

1.存储过程创建

create procedure 存储过程名称 ([参数列表])
begin

  SQL 语句;

end;

2.存储过程调用

存储过程名称 ([参数]);

3.存储过程查看

/*查询指定数据库的存储过程及状态信息*/
select * from information_schema.routines where routine_schema = ‘xxx’; 

/*查询某个存储过程的定义*/
show create procedure 存储过程名称;

4.存储过程删除

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

注意:在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter 指定SQL语句结束符；

3.系统变量

系统变量是MySQL服务器提供的，不是用户定义的，属于服务器层面的，分为全局变量（global），会话变量（session）

1.查看系统变量

/*查看所有系统变量（variables）*/
show [session|global] variables;

/*--可以通过like模糊匹配方式查找变量*/
show [session|global] variables like ‘…’; 

/* 查看指定变量的值*/
select @@[session|global] 系统变量名;

2.设置系统变量

/*方式一*/
set [session|global] 系统变量名=值;

/*方式二*/
set @@[session|global] 系统变量名=值;

4.用户自定义变量

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

1.赋值

/*方式一*/
set @var_name =值;

/*方式二*/
set @var_name:=值;

/*方式三*/
select @var_name=值;

/*方式四*/
select 字段名 into @var_name from 表名;

2.使用

select @var_name;

注意：

用户自定义变量无需对其申明或初始化，只不过获取到的值为NULL;

5.局部变量

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

1.声明

declear 变量名称 类型[default=0];
/*变量类型就是数据库字段：int bigint char varchar date time等*/

2.赋值

set 变量名 =值;

set 变量名：=值;

select 字段名 into 变量名 from 表名;

6.if条件判断

if 条件1 then
   代码块1
elseif 条件二 then
   代码块二
else
   代码块三
end if;

7.存储过程参数

类型	含义	备注
in	该参数作为输入，也就是调用时传入值	默认
out	该参数作为输出，就是该参数可以作为返回值
inout	既可以作为输入参数，也可以作为输出参数

用法：

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

end;

8.case语句

1.语法一

相当于switch

case case_value   
when when_value1 then statement_list1    
[ when when_value2 then statement_list2] ...  
[else statement_list]  
end case;

2.语法二

相当于if elseif

case   
when search_condition1 then statement_list1   
[when search_condition2 then statement_list2] ...   
[else statement_list]   
end case;

9.whlie循环

while循环是具有条件的循环控制语句。当满足条件后，在执行循环体中的SQL语句。具体语法为:

--先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
    sql逻辑...
end while;

10.repeate循环

repeate 是有条件的控制语句，当满足条件时退出循环，具体语法为：

--先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
    sql逻辑...
    until 条件
end repeat;

11.loop循环

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

[label:] loop

      sql逻辑...

end loop [label];

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

演示：

create procedure p9(in n int)
begin
  declare total int default 0;

  sum:loop

    if n<=0 then
       leave sum;
    end if;

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

  select total;
end;

12.游标cursor

游标（sursor）被定义为一种数据类型，用于存储查询结果集的数据。在存储过程中，可以使用游标对结果集进行循环处理。游标的使用包括声明、open、fetch和close四个步骤。

其语法如下：

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

/*打开游标*/
open 游标名称;

/*获取游标标记*/
fetch 游标名称 into 变量[,变量];

/*关闭游标*/
close 游标名称;

演示：

13.条件处理程序

条件处理程序（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代码的简写

14.存储函数

存储函数是必需具有返回值的存储过程，存储的参数列表只能是in类型的，具体语法如下：

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

begin
    -- SQL语句;
end;

characteristic说明:
    deterministic：相同的输入输出产生相同的结果
    no sql：不包含SQL语句
    reads sql data ：包含读取数据的语句，不包含写入数据的语句。

代码演示：

create function fun1(n int)
    returns 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;