MySQL的存储过程

存储过程

MySQL5.0版本开始支持存储过程

简单地说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法

存储过程就是数据库SQL语言层面的代码封装于重用

特性

有输入输出函数，可以声明变量，有if/else，case，while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

函数的普遍特性：模块化、封装、代码复用

速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤

创建格式

调用格式

call 储存名;

例：

变量

局部变量

局部变量：用户自定义，在begin/end块中有效

default：默认值

例

MySQL中可以使用SELECT...INTO语句为变量赋值，语法如下

select 字段名 [...] into 变量名 [,...] from 表名 where 查询条件

该语句会将查询到的结果赋值给变量

注:将查询结果赋值给变量时，该查询语句返回的结果只能是单行单列

也可以用SET语句为变量赋值，语法如下

ste 变量名 = 值;

用户变量

用户变量：用户自定义，当前会话（连接）有效。类比java的成员变量

例

注：用户变量可以在存储过程中创建，也可以在存储过程外创建

系统变量

介绍

·系统变量又分为全局变量与会话变量

·全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

·会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。

·也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。

·全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。

·有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的（不可修改），对于那些可以更改的系统变量，我们可以利用set语句进行更改。

全局变量

由系统提供，在整个数据库有效

例

会话变量

由系统提供，当前会话（连接）有效

例

参数传递

in

in表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内

例

out

out表示从存储过程内部传值给调用者

例

inout

inout表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

注：inout只能传入参数，若在存储过程中对参数进行了修改，则在存储过程外修改会保留

流程控制

if

IF语句包含多个条件判断，根据结果为TRUE.FALSE执行语句

其中，search_condition为一个条件，成立则执行后面的语句statement_list

注：

1.每个then语句后面都要带分号，包括else语句和end if语句后面也要加分号

2.if语句从上到下判断，如果前面满足了条件，那么即使后面也有条件满足也不会执行后面的语句

CASE

CASE是另一个条件判断语句

其中，语法一与switch类似，case_value一般为一个变量，其值如果与when_value相同，就执行后面的代码

语法二与if类似，when后面的条件成立，则执行then语句

注：每个then语句包括endcase后面需要加分号

循环

循环是一段在程序中只出现一次，但可能会连续运行多次的代码

循环中的代码会运行特定的次数，或者是运行到特定条件成立时结束循环

循环有三种：while，repeat，loop

跳转关键字

leave类似于break，跳出并结束当前所在循环

iterate类似于continue，结束本次循环，继续下次循环

跳转关键字的使用：

跳转关键字 标签;

例

while

先进行条件判断在执行代码

格式

注：标签可以加可以不加，且名字可以随便写，但如果要使用跳转关键字，则必须加标签

repeat

先执行一次代码，在判断条件

格式

loop

通过if语句来进行条件判断并结束循环

格式

游标

游标（cursor）是用来存储查询结果集的数据类型，在储存过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE。

光标中存储了一个结果集，即一张表，光标默认指在第0行，每次取值先往下一行再取值

格式

cursor_name为光标的名称

select_statement为一个结果集，一般为select语句

取值语法中，fetch...into...会将本次取到的一行数据输入到into后面的字段中，因此，后面的字段必须和光标中结果集的字段数量，类型相同

例

异常处理-HANDLER句柄

MySQL存储过程提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现

格式

DECLARE 处理异常后程序如何执行 HANDLER

FOR 触发条件 异常触发后执行的代码

处理异常后程序如何执行

continue:继续执行剩余代码

exit:直接终止程序
undo:MySQL不支持，不使用

触发条件

1.条件码：每个异常有自己的异常码，可以直接填异常吗

2.条件名

SQLWARING：SQL有警告

NOT FOUND：未发现数据，用于没有找到数据的情况下，如使用循环来为游标取值时

SQLEXCEPTION：SQL出现异常

定义HANDLER句柄后，一旦出现符合触发条件的异常时，就会执行代码，并根据设定来决定是继续执行还是终止

存储函数

再MySQL中，创建存储函数使用creat function关键字

格式：

creat function 函数名 ([参数名 参数类型 [,...]])

returns 返回值类型

[存储函数的特性]

begin

SQL代码;

return 返回值;

end;

删除格式:

drop function [if exists] 函数名

注：MySQL8之后创建存储函数时可能会报一个函数不被信任的错误，可以修改一个变量来允许创建函数权限信任

set global log_bin_trust_function_creators = TRUE;

调用存储函数

select 函数名()

注：需要带括号