MySQL存储过程与存储函数
存储过程和存储函数是指在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和存储函数来执行已经定义好的SQL语句，以避免开发人员重复编写相同的SQL语句，增加开发的效率，减少客户端和服务端的数据传输。
创建存储过程和存储函数
创建存储过程
创建存储过程的语法如下：
CREATE PROCEDURE proc_name([proc_parameter[...]])
[characteristic] routine_body
其中，proc_name代表存储过程的名称；proc_charameter代表存储过程的参数列表；characteristic代表存储过程的特性；routine_body代表SQL代码的内容。proc_parameter参数由三部分组成，分别为输入或输出类型、参数名称和参数类型。其形式为[IN|OUT|INOUT]param_name type。其中IN表示输入参数；OUT表示输出参数；INOUT表示既可作为输入函数又可作为输出函数；param_type表示存储过程参数名称；type参数指存储过程的参数类型，该类型可以为MySQL中的任意数据类型。一个存储过程包含名称、参数列表、还可以包含很多SQL语句集。
注：characteristic部分的语法如下。
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
| 参数 | 说明 |
|---|---|
| sp_name | 存储过程或存储函数的名称 |
| characteristic | 指定存储函数的特性 |
| CONTRAINS SQL | 表示子程序包含SQL语句，但不包含读写数据的语句 |
| NO SQL | 表示子程序不包含SQL语句 |
| READS SQL DATA | 表示子程序中包含读数据的语句 |
| MODIFIES SQL DATA | 表示子程序中包含写数据的语句 |
| SQL SECURITY | 指明权限执行。DEFINER表示只有定义者才能执行；INVOKER表示调用者才能执行 |
| COMMENT ‘string’ | 注释信息 |
输入参数、输出参数、输入输出参数：
in 输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回
out 输出参数:该值可在存储过程内部被改变，并向外输出，一般在调用时使用全局变量，即在变量名前加一个“@”符号，此时这个变量就存储起来了
inout 输入输出参数，既能输入一个值又能传出来一个值
在MySQL中存储过程体以CREATE PROCECURE开始，后面紧跟存储过程的名称和参数。MySQL中的存储过程名称不区分大小写，且存储过程不能与MySQL中的内置函数同名。存储过程体的语句块以begin开始，以end结束，语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程体内部要以分号结束，所以在定义存储过程体之前都要新定义一个结束符号，并且应降低该字符在语句块中的出现概率。
下面我们先创建一个数据表，该数据表中只包含一个字段用于存储编程语言名称的字段，创建存储过程的语句如下。
delimiter //
create procedure proc_name(in parameter int)
begin
declare variable varchar(50);
if parameter = 1 then
set variable = 'MySQL';
elseif parameter = 2 then
set variable = 'Java';
else set variable = 'Python';
end if;
insert into tb values (variable);
end
//
在此例中，我们将parameter作为输入量，然后通过判断parameter的值来改变变量的值，最后使用INSERT INTO语句实现快速插入数据的操作。
MySQL中默认的语句结束符为分号，存储过程中的SQL语句需要使用分号结束.为了避免冲突，首先用“delimiter //”将MySQL的结束符设置为”//“，再用“delimiter ;”来将结束符设置成分号。
创建存储函数
创建存储函数的语法和创建存储过程的语法类似，语法如下：
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
其中sp_name为存储函数的名称，func_parameter为存储函数的参数列表，RETURNS type指定返回值的类型，characteristic指定存储函数的特性，routine_body代表SQL代码内容。
下面仅演示如何创建存储函数。
delimiter //
create function func(i int)
returns varchar(50)
begin
if i=1 then return 'Java';
elseif i=2 then return 'Python';
else return 'MySQL';
end if;
end;
//
若MySQL提示：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 需要在MySQL的配置文件中找到[mysqld]位置，在该行下面设置log-bin-trust-function-creators=1。
变量的应用
局部变量
局部变量以关键字DECLARE声明，语法如下：
DECLARE variable_name type [DEFAULT value]
其中variable_name表示局部变量名，type表示类型，DEFAULT value可选，表示为局部变量设置默认值。
如：
delimiter //
create procedure p1()
begin
declare i varchar(10) default 'outer';
begin
declare i varchar(10) default 'inner';
select i;
end;
select i;
end;
//
全局变量
全局变量比较于局部变量，只需要在前面加上一个“@”符号。
delimiter //
create procedure p2()
begin
set @a=1;
begin
set @a=2;
select @a;
end;
select @a;
end;
//
为变量赋值
MySQL中可以使用关键字DECLARE来声明变量，可以使用关键字SET为变量赋值，可以同时为多个变量赋值，各个变量的赋值语句用“,”隔开。
另外，MySQL还支持使用下面的语句结构为变量赋值：
SELECT col_name[,...] INTO var_name[,...] FROM table_name where condition;
其中col_name代表查询的字段名称；var_name代表变量的名称；table_name代表表的名称，如从stu表中查询name为‘张三’的记录，并将记录中的tel赋值为局部变量i，那么代码如下：
select tel into i from stu where name = '张三';
光标的应用
在MySQL中查询记录，其结果可能位多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。
光标的使用包括以下步骤：
声明光标
光标只能在存储过程中定义。声明光标使用关键字DECLARE，语法如下。
DECLARE cursor_name CURSOR FOR select_statement;
其中，cursor_name为光标的名称，光标使用和表是同样的规则；select_statement是一个SELECT语句，返回一行或多行数据，该语句也可以在存储过程中定义多个光标，但是必须保证光标的名称不能相同。
打开光标
在声明光标之后，若要使用光标需要先打开光标。打开光标的语法如下。
OPEN cursor_name;
使用光标
顺利打开光标后，可以使用FETCH...INTO语句读取数据，语法如下。
FETCH cursor_name INTO var_name[,var_name]...
关闭光标
使用CLOSE关键字关闭光标，语法如下。
CLOSE cursor_name;
现在举一个实例——更新表中信息：在员工表中将name字段由‘姓名’改成‘id-姓名’：
首先查看表中数据。
然后我们创建如下的存储过程。
delimiter //
create procedure proc()
begin
declare e_id int; # 存储字段id
declare e_name varchar(50); # 存储字段name
declare isDone int default 0; # 存储是否结束的标志
declare cur_proc cursor for select id as e_id,name as e_id from employees; # 声明光标
declare continue handler for not found set isDone = 1;
# 若光标没有成功查找到数据就将结束表示设为1
open cur_proc; # 开启光标
while not isDone do # 循环，若结束标志为0则继续循环
fetch cur_proc into e_id,e_name; # 使用光标，赋值
update employees set name = concat(e_id,'-',e_name) where id = e_id; # 连接
end while;
close cur_proc; # 关闭光标
end //
然后调用存储过程，最后再查看表的数据。
可以看到表的数据被改动了，说明达到目的了。
调用存储过程和存储函数
调用存储过程
存储过程的调用在前面的示例中已多次使用。使用CALL关键字调用存储过程，然后存储过程会被执行，然后将结果输出给返回值。语法如下。
CALL sp_name([parameter[,...]]);
其中sp_name代表存储过程的名称，parameter是参数，且参数不能省略。
MySQL中，若存储过程的参数是OUT或者INOUT作为输出参数使用时，我们一般在传递参数的时候将全局变量作为参数，如我们定义一个如下的存储过程： “create procedure proc(out i int) begin ... end //” 那么我们在调用的时候这样调用：“call proc(@a) //” 这样调用后就会在MySQL中生成这个全局变量@a，之后我们使用“select @a;” 或者其他的语句就可以使用这个值了。
如我们先定义一个如下的存储过程，然后调用此存储过程。调用存储过程的语句如下：
call count_of_student(@total) //
select @total //
可以看到@total的值为4，说明employees表中存在4条数据行。
调用存储函数
存储函数的调用和MySQL的内置函数基本相同。语法如下：
SELECT function_name([parameter[,...]]);
如我们定义如下的存储函数，然后调用此存储函数。调用存储函数的语法如下：
select search_name(1) //
查看存储过程和存储函数
SHOW STATUS语句
使用SHOW STATUS语句查看存储过程和存储函数的语法如下：
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']
如查看db数据库中所有的存储过程：
show procedure status;
查看所有以字母p开头的存储过程名：
show procedure status like 'p%';
SHOW CREATE语句
通过SHOW CREATE语句查看存储过程和存储状态的语法如下：
SHOW CREATE {PROCEDURE|FUNCTION} sp_name;
sp_name代表存储过程或者存储函数的名称。
如查询count_of_student的定义：
show create procedure count_of_student //
查询结果显示存储过程的定义、字符集等信息。
SHOW STATUS语句只能查看存储过程或存储函数所操作的数据库对象，如存储过程或存储函数的名称、类型、定义者、修改时间等信息，并不能查询存储过程或存储函数的具体定义。若要查看详细定义，可以使用SHOW CREATE语句。
修改存储过程和存储函数
使用ALTER关键字修改存储过程或存储函数的语法如下：
ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
| 参数 | 说明 |
|---|---|
| sp_name | 存储过程或存储函数的名称 |
| characteristic | 指定存储函数的特性 |
| CONTRAINS SQL | 表示子程序包含SQL语句，但不包含读写数据的语句 |
| NO SQL | 表示子程序不包含SQL语句 |
| READS SQL DATA | 表示子程序中包含读数据的语句 |
| MODIFIES SQL DATA | 表示子程序中包含写数据的语句 |
| SQL SECURITY | 指明权限执行。DEFINER表示只有定义者才能执行；INVOKER表示调用者才能执行 |
| COMMENT ‘string’ | 注释信息 |
删除存储过程和存储函数
使用DROP PROCEDURE来删除存储过程，使用DROP FUNCTION删除存储函数，语法如下：
DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name;