MySQL学习（17）：SQL编程：存储过程

1.什么是存储过程

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

存储过程的特点：

（1）封装，复用

（2）可以接收参数，可以返回数据

2.存储过程语法

2.1创建

create procedure 存储过程名([参数列表])
#小括号必须要有，参数列表可以没有
begin
SQL语句
end;

2.2调用

call 存储过程名([参数列表]);

2.3查询

（1）查询指定数据库的存储过程及状态信息

select * from information_schema.routines where routine_schema='数据库名';

（2）查询某个存储过程的定义

show create procedure 存储过程名;

查询结果出现的root是创建存储过程的用户

2.4删除

drop procedure 存储过程名;

2.5结束符号问题

在存储过程的SQL语句部分中，需要分号切分语句，而分号是mysql识别一条命令完结的标识，所以会造成使用分号后，SQL语句直接执行，无法创建存储过程。

要解决此问题，可以通过delimiter命令更改结束符，来规避此问题：

delimiter $$
#属于这条命令后，mysql所有语句的结束符就由分号变为$$了
#$$不是固定的，可以自行设置结束符

使用实例如下图：

创建完存储过程后，还可以再用delimiter命令把结束标志改回分号

3.存储过程变量

3.1系统变量

系统变量是mysql服务器提供的，分为全局变量（global）、会话变量（session）

（1）查看系统变量

show [global] variables;
#查看所有变量
#不写global是检索当前会话的变量，写global是检索mysql全局的变量

show [global] variables like '匹配内容';
#通过like模糊匹配查找变量，_匹配单字符，%匹配多字符

select @@[global] 系统变量名;
#查看指定变量

（2）设置系统变量

set [global] 系统变量名=值；
或
set @@[global]系统变量名=值；

变量值设置完后（无论是全局还是会话），只要重启数据库，系统变量名就都会恢复成默认值。如果想永久更改，需要在配置文件/etc/my.cnf中配置

3.2用户变量

用户可以自定义变量，不需提前声明。

#赋值语句
set @变量名=值;
或
select @变量名=值;

select 字段名 into @变量名 from 表名；
#直接把表内字段的数据作为变量的值

select @变量名;
#查看变量的值

用户变量的使用只限于当前会话

3.3局部变量

局部变量需要declare声明，其范围是在一个存储过程的begin到end之间

（1）声明：

declare 变量名 变量类型[default 默认值];
#在声明的时候可以直接设置一个默认值
#变量类型就是int、char、varchar等等

（2）赋值：

跟用户变量的赋值方式相同

#赋值语句
set @变量名=值;
或
select @变量名=值;

select 字段名 into @变量名 from 表名；
#直接把表内字段的数据作为变量的值

4.存储过程参数与流程控制语句

4.1参数

存储过程的参数是在创建时设置的，作为输入、输出的对象使用。

存储过程的参数有以下3种：

使用方法如下：

（1）实例1

调用及显示方法如下：

*@result是一个未经声明的用户变量，只是用来接收存储过程结果的

图中灰色字段是软件工具自动提示出来的，不需要敲

（2）实例2

该例中，需要设置一个同时负责传入数据与传出数据的用户变量@score

4.2if else语句

if判断是在begin与end间使用

条件1成立，执行第一个then后的语句，否则判断条件2；条件2成立，执行第二个then后的语句，否则判断条件3......

end if是固定的结尾标识

4.3case语句

（1）语法一

case 值
when 值1 then 语句1
when 值2 then 语句2
[else 语句3]
end else;
#case后的值是值1就执行语句1，是值2就执行语句2

（2）语法二

case
when 条件表达式1 then 语句1
when 条件表达式2 then 语句2
[else 语句3]
end else;
#条件表达式1为true就执行语句1，条件表达式2为true就执行语句2，否则执行语句3

4.4while循环语句

实例如下：

4.5repeat循环语句

与while语句的区别是：while是符合条件才进行循环，repeat是满足条件才跳出循环

使用repeat写上面的while实例如下：

4.6loop循环语句

loop可实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以实现简单的死循环。

一个普通的loop循环如下：

[自定义开始标志:]loop
SQL语句
end loop[自定义结束标志];
#开始标志后的冒号不能少

loop循环本身是没有退出条件，需要自行添加退出语句：

（1）leave：配合循环使用，退出循环。

（2）iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

使用实例如下（还是前例）：

4.7游标cursor

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

游标的使用包括游标游标(CURSOR)的声明、OPEN、FETCH和 CLOSE，其语法如下：

DECLARE 游标名称 CURSOR FOR 查询语句;
#声明游标

OPEN 游标名称;
#打开游标

FETCH 游标名称 INTO 变量,变量...;
#获取游标记录

CLOSE 游标名称;
#关闭游标

使用实例如下：

上述程序虽然可以顺利执行，但存在一个while语句死循环的问题，要解决这个问题，可以使用handler（条件处理程序）

4.8条件处理程序handler

handler可以用来定义在流程执行中遇到问题时的处理步骤。分为继续、停止2种。其语法如下：

declare continue handler for 状态1,状态2... SQL语句;
#当出现语句中的状态时，继续执行当前程序

declare exit handler for 状态1,状态2... SQL语句;
#当出现语句中的状态时，停止执行当前程序

语句中的状态有4种：

（1）sqlstate 状态码

状态码比如02000，代表没得到数据

（2）sqlwaring：所有以01开头的状态码

（3）not found：所有以02开头的状态码

（4）sqlexception：01、02开头以外的其他状态码

使用handler解决前述while语句死循环的方法如下：