MySQL进阶第五天——存储过程与存储函数
存储过程简介
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
优点
- 通过把处理封装在容易使用的单元中，简化复杂的操作；
- 简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码；
- 通常存储过程有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中。 但是，MySQL 实现的存储过程略有不同。MySQL 存储过程按需编译。在编译存储过程之后，MySQL 将其放入缓存中。MySQL 为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询；
- 存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的 SQL 语句，而只用发送存储过程的名称和参数；
- 存储的程序对任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能；
- 存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。
缺点
- 如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则 CPU 使用率也会增加，因为 MySQL 数据库最初的设计侧重于高效的查询，不利于逻辑运算；
- 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难；
- 很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL 不提供调试存储过程的功能；
- 开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。
MySQL 中的存储过程
创建与调用过程
创建存储过程
-- 创建存储过程
create procedure mypro(in a int,in b int,out sum int)
begin
set sum = a+b;
end;
调用存储过程
call mypro(1,2,@s);-- 调用存储过程
select @s;-- 显示过程输出结果
存储过程语法解析
create procedure用来创建过程；mypro用来定义过程名称；(in a int,in b int,out sum int)表示过程的参数，其中in表示输入参数，out表示输出参数。类似于 Java 定义方法时的形参和返回值；begin与end表示过程主体的开始和结束，相当于 Java 定义方法的一对大括号；call用来调用过程，@s是用来接收过程输出参数的变量
存储过程的参数
MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型：
- IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）；
- OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）；
- INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。
存储过程根据参数可分为四种类别：
-
没有参数的过程；
-
只有输入参数的过程；
-
只有输出参数的过程；
-
包含输入和输出参数的过程。
变量
MySQL 中的存储过程类似 java 中的方法。
既然如此，在存储过程中也同样可以使用变量。java 中的局部变量作用域是变量所在的方法，而 MySQL 中的局部变量作用域是所在的存储过程。
变量定义
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
-
declare用于声明变量；
-
variable_name表示变量名称；
-
datatype为 MySQL 的数据类型；
-
default用于声明默认值;
变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
在存储过程中使用变量，代码如下所示
use schooldb;-- 使用 schooldb 数据库
-- 创建过程
create procedure mypro1()
begin
declare name varchar(20);
set name = '丘处机';
select * from studentinfo where studentname = name;
end;
-- 调用过程
call mypro1();
流程控制语句
if 条件语句
IF 语句包含多个条件判断，根据结果为 TRUE、FALSE执行语句，与编程语言中的 if、else if、else 语法类似。
定义存储过程，输入一个整数，使用 if 语句判断是正数还是负数，代码如下所示：
-- 创建过程
create procedure mypro2(in num int)
begin
if num<0 then -- 条件开始
select '负数';
elseif num=0 then
select '不是正数也不是负数';
else
select '正数';
end if;-- 条件结束
end;
-- 调用过程
call mypro2(-1);
case 条件语句
case是另一个条件判断的语句，类似于编程语言中的 choose、when语法。MySQL 中的 case语句有两种语法
定义存储过程，输入一个整数，使用 case 语句判断是正数还是负数，代码如下所示：
-- 创建过程
create procedure mypro3(in num int)
begin
case -- 条件开始
when num<0 then select '负数';
when num=0 then select '不是正数也不是负数';
else select '正数';
end case; -- 条件结束
end;
-- 调用过程
call mypro3(1);
定义存储过程，输入一个整数，使用 case 语句判断是 1 还是 2，代码如下所示：
-- 创建过程
create procedure mypro4(in num int)
begin
case num -- 条件开始
when 1 then select '数值是 1';
when 2 then select '数值是 2';
else select '不是 1 也不是 2';
end case; -- 条件结束
end;
-- 调用过程
call mypro4(3);
两种 case 语法都可以实现条件判断，但第一种适合范围值判断，而第二种适合确定值判断。
while 循环语句
while语句的用法和 java中的 while循环类似。
定义存储过程，使用 while 循环输出 1 到 10 的累加和，代码如下所示：\
-- 创建过程
create procedure mypro5(out sum int)
begin
declare num int default 0;
set sum = 0;
while num<10 do -- 循环开始
set num = num+1;
set sum = sum+num;
end while; -- 循环结束
end;
-- 调用过程
call mypro5(@sum);
-- 查询变量值
select @sum;
repeat 循环语句
repeat语句的用法和 java中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是 repeat表达
式值为 false时才执行循环操作，直到表达式值为 true停止。
定义存储过程，使用 repeat 循环输出 1 到 10 的累加和，代码如下所示：
-- 创建过程
create procedure mypro6(out sum int)
begin
declare num int default 0;
set sum = 0;
repeat-- 循环开始
set num = num+1;
set sum = sum+num;
until num>=10
end repeat; -- 循环结束
end;
-- 调用过程
call mypro6(@sum);
-- 查询变量值
select @sum;
loop 循环语句
循环语句，用来重复执行某些语句。
执行过程中可使用 leave语句或 iterate 跳出循环，也可以嵌套 IF等判断语句。
- leave语句效果相当于 java 中的 break，用来终止循环；
- iterate语句效果相当于 java 中的 continue，用来结束本次循环操作，进入下一次循环。
定义存储过程，使用 loop 循环输出 1 到 10 的累加和，代码如下所示：
-- 创建过程
create procedure mypro7(out sum int)
begin
declare num int default 0;
set sum = 0;
loop_sum:loop-- 循环开始
set num = num+1;
set sum = sum+num;
if num>=10 then
leave loop_sum;
end if;
end loop loop_sum; -- 循环结束
end;
-- 调用过程
call mypro7(@sum);
-- 查询变量值
select @sum;
代码中的 loop_sum 相当于给循环贴个标签，方便多重循环时灵活操作。
存储过程的管理
存储过程的管理主要包括：显示过程、显示过程源码、删除过程。
显示存储过程
SHOW PROCEDURE STATUS;
显示特定数据库的存储过程
SHOW PROCEDURE status where db = 'schooldb';
显示特定模式的存储过程，要求显示名称中包含“my”的存储过程
SHOW PROCEDURE status where name like '%my%';
显示存储过程“mypro1”的源码
SHOW CREATE PROCEDURE mypro1;
删除存储过程“mypro1”
drop PROCEDURE mypro1;
存储函数
存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作。
在 MySQL 中，使用CREATE FUNCTION语句来创建存储函数，其语法形式如下：
CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body
sp_name 参数：表示存储函数的名称；
func_parameter：表示存储函数的参数列表；
RETURNS type：指定返回值的类型；
characteristic 参数：指定存储函数的特性，该参数的取值与存储过程是一样的；
routine_body 参数：表示 SQL 代码的内容，可以用 BEGIN…END 来标示 SQL 代码的开始和结束。
注意：在具体创建函数时，函数名不能与已经存在的函数名重名。除了上述要求外，推荐函数名命名（标识符）为 function_xxx 或者 func_xxx。
func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
[IN | OUT | INOUT] param_name type;
- IN 表示输入参数，OUT 表示输出参数，INOUT 表示既可以输入也可以输出；
- param_name 参数是存储函数的参数名称；
- type 参数指定存储函数的参数类型，该类型可以是 MySQL 数据库的任意数据类型。
例子
使用 CREATE FUNCTION 创建查询 tb_student 表中某个学生姓名的函数，SQL 语句和执行过程如下：
mysql> DELIMITER //
-> CREATE FUNCTION func_student(id INT(11))
-> RETURNS VARCHAR(20)
-> COMMENT '查询某个学生的姓名'
-> BEGIN
-> RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
-> END //
Query OK, 0 rows affected (0.10 sec)
mysql> DELIMITER ;
上述代码中：
1、创建了 func_student 函数，
2、该函数拥有一个类型为 INT(11) 的参数 id，
3、返回值为 VARCHAR(20) 类型。
4、SELECT 语句从 tb_student 表中查询 id 字段值等于所传入参数 id 值的记录，同时返回该条记录的 name 字段值。
创建函数与创建存储过程一样，需要通过命令 DELIMITER // 将 SQL 语句的结束符由“;”修改为“//”，最后通过命令 DELIMITER ; 将结束符号修改成 SQL 语句中默认的结束符号。
如果在存储函数中的 RETURN 语句返回一个类型不同于函数的 RETURNS 子句中指定类型的值，返回值将被强制为恰当的类型。比如，如果一个函数返回一个 ENUM 或 SET 值，但是 RETURN 语句返回一个整数，对于 SET 成员集的相应的 ENUM 成员，从函数返回的值是字符串。
查看存储函数的语法如下
SHOW FUNCTION STATUS LIKE 存储函数名;
SHOW CREATE FUNCTION 存储函数名;
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储函数名;
可以发现，操作存储函数和操作存储过程不同的是将 PROCEDURE 替换成了 FUNCTION。
修改存储函数的语法如下
ALTER FUNCTION 存储函数名 [ 特征 ... ]
删除存储过程的语法如下
DROP FUNCTION [ IF EXISTS ] <函数名>