存储过程与存储函数
【应知部分】：
1.存储过程与存储函数概述
存储过程与存储函数是MySQL自5.0版本之后开始支持的过程式数据库对象。它们作为数据库存储的重要功能，可以提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
2. 存储过程的概念
概念：
存储过程是一组为了完成某特定功能的SQL语句集，其实质上就是一段存放在数据库中的代码，它可以由声明式语句（如CREATE、UPDATE和SELECT等语句）和过程式语句（如IF-THEN-ELSE控制结构语句）组成。这组语句经过编译后会存储在数据库中，用户只需通过指定存储过程的名字并给定参数（如果该存储过程带有参数），即可随时调用处理它，而不必重新编译，提高数据库操作语句的执行效率。
优点：
（1）增强SQL语言的功能与灵活性。
（2）良好的封装性。
（3）高性能。
（4）可减少网络流量。
（5）保证数据库的安全性和数据的完整性。
3. 存储过程体的作用
在存储过程体中可以使用各种SQL语句与过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。
构造存储过程体的语法元素：
（1）局部变量
（2）SET语句
（3）SELECT…INTO语句
（4）流程控制语句
（5）游标
4. 存储函数的概念
在MySQL中，存在一种与存储过程十分相似的过程式数据库对象——存储函数。它与存储过程一样，都是由SQL语句和过程式语句组成的代码片段，并且可以被应用程序和其他SQL语句调用。
存储函数与存储过程的区别：
（1）存储函数不能拥有输出参数。存储过程拥有输出参数。
（2）可以直接对存储函数调用，不需要使用CALL语句，存储过程调用需要。
（3）存储函数中必须包含RETURN语句，此语句不允许出现在存储过程中。
【应会部分】：
一、 创建存储过程
语法：CREATE PROCEDURE sp_name([proc_parameter[,…]])
[characteristic…]routine_body
其中，proc_parameter的格式为：
[IN|OUT|INOUT]param_name type
type的格式为：
Any valid MySQL data type characteristic的格式为： COMMENT ‘string’ |LANGUAGE SQL |[NOT]DETERMINISTIC |{ CONTAINS SQL|NO SQL|READES SQL DATA|MODIFIES SQL DATA} |SQL SECURITY{DEFINER|INVOKER}
routine_body的格式为：
Valid SQL routine statement
主要语法说明：（详细说明见P141）
★sp_name：存储过程的名称，默认在当前数据库下创建。定义名字应该避免与MySQL内置函数相同。
★proc_parameter：存储过程参数列表。参数取名不要与数据表的列名相同。
★characteristic：存储过程的某些特征设定。
★routine_body：存储过程的主体部分，也称为存储过程体，其包含了在过程调用的时候必须执行的SQL语句。这个部分以BEGIN开始，以关键字END结束。
★DELIMITER命令的语法格式：
DELIMITER $$(略)
例题：
1）将MySQL结束符修改为两个感叹号“$$”
修改：mysql> delimiter $$
还原：mysql> delimiter ;
2）在数据库db_school中创建一个存储过程，用于实现给定表tb_student中一个学生的学号即可修改表tb_student中该学生的性别为一个指定的性别(使用该存储过程修改学生表中的学号、性别)。
mysql> use db_school; mysql> delimiter $$ mysql> create procedure sp_update_sex(in sno int,in ssex nchar(2)) begin update tb_student set sex=ssex where studentno=sno; end $$ mysql> delimiter ;
注意：“ssex nchar(2)” 在Linux系统下的Mysql数据库管理系统中，由于字符集问题导致乱码的情况时有发生，大多是因为兼容性造成的，我们这里使用的是Unicode编码，能够表示全世界所有的字节。使用存储过程插入或更新的字符有可能出现乱码问题，为保证不出现乱码我们这里采用Unicode编码，即nchar或nvarchar。
查看存储过程状态你就会明白：
mysql>show procedure status\G *************************** 1. row *************************** Db: db_school Name: sp_update_sex Type: PROCEDURE Definer: root@localhost Modified: 2018-05-19 21:38:52 Created: 2018-05-19 21:38:52 Security_type: DEFINER Comment: mysql>character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
或查看指定的数据库“存储过程”。
show create procedure sp_update_sex\G *************************** 1. row *************************** Procedure: sp_update_sex sql_mode: Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_update_sex`(in sno int,in ssex nchar(1)) begin update tb_student set sex=ssex where studentno=sno; end character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
另外需要注意的是：如果你不是使用root用户创建的数据库存储过程，一定要去查看它是否有create routine权限。否则储存过程是无法创建的。
查看方法：以用户’root’@’localhost’为例
mysql> select Create_routine_priv from mysql.user -> where user='root' and host='localhost'; +-----------------------------------+ | Create_routine_priv | +-----------------------------------+ | Y | +------------------------------------+ 1 row in set (0.00 sec)
‘Y’说明拥有该权限。
【调用】储存过程怎样使用，我们这里使用以上创建的储存过程来验证。
mysql> select studentNo,sex -> from tb_student -> where studentNo='2013110101'; +------------------+---------+ | studentNo | sex | +------------------+---------+ | 2013110101 | 男 | +------------------+---------+
1 row in set (0.00 sec)这里我们使用存储过程把学号为“2013110101”的同学的性别更新为“女”。
mysql> call sp_update_sex('2013110101',n'女');
验证：
mysql> select studentNo,sex from tb_student where studentNo='2013110101'; +-------------------+----------+ | studentNo | sex | +-------------------+----------+ | 2013110101 | 女 | +-------------------+----------+ 1 row in set (0.00 sec)
验证成功！
授予普通用户拥有“存储过程或函数”的相关权限：（复习授权）
|
User表中的列 |
权限名称 |
权限的范围 |
|
Alter_routine_priv |
ALTER ROUTINE |
修改存储过程和函数 |
|
Create_routine_priv |
CREATE ROUTINE |
创建存储过程和函数 |
|
Execute_priv |
EXECUTE |
执行存储过程和函数 |
mysql> use mysql mysql> grant ALTER ROUTINE,CREATE ROUTINE,EXECUTE on `db_school`.* to 'bob'@'localhost' identified by '123456';
或者：
mysql> use mysql mysql> create user 'bob1'@'localhosat' identified by '123456'; mysql>update user set Create_routine_priv='Y', Alter_routine_priv='Y', Execute_priv:='Y' where user='bob1' and host='localhost';
其它权限如果需要也必须开启！
二、存储过程
1）局部变量
作用：在存储过程体中可以设置局部变量，用来存储存储过程体中的临时结果。在mysql5.5中可以使用DECLARE语句来声明局部变量并同时还可以对该局部变量赋予一个初始值。
其语法格式为：
DECLARE var_name[ ,…] type [DEFAULT value]
语法说明：
var_name：用于指定局部变量的名称
type：用于声明局部变量的数据类型
DEFAULT子句：用于为局部变量指定一个默认值。若没有指定默认为空。
注意：
（1）局部变量只能在存储过程体的BEGIN…END语句块中声明。
（2）局部变量必须在存储过程体的开头处声明。
（3）局部变量的作用范围仅限于声明它的BEGIN…END语句块，其它语句块中的语句不可以使用它。
（4）局部变量不同于用户变量，两者的区别是：局部变量声明时，在其前面没有使用“@”符号，并且它只能声明它的BEGIN…END语句块中的语句所使用；用户变量在声明时，会在前面使用“@”符号，同时声明的用户变量存在于整个会话之中。
2）SET语句
在MySQL5.5中可以使用SET语句为局部变量赋值，其语法格式是：
SET var_name=expr[,var_name=expr];
例题：使用存储过程计算两个值的和
mysql>use db_school #存储过程是数据库对象，因此必须选择一个数据库 mysql> delimiter $$ mysql>create procedure sp_sum(in a int,in b int) begin declare c int; set c=a+b; select c; end $$ mysql> delimiter ; mysql> call sp_sum(5,6); +---- -------+ | c | +-----------+ | 11 | +-----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
3）SELECT …INTO语句
在MySQL中，可以使用SELECT…INTO语句把选定列的值直接存储到局部变量中，其语法格式是：
SELECT col_name[,…]INTO var_name[,…]table_expr
语法说明如下：
★col_name:用于指定列名。
★var_name:用于指定要赋值的变量名。
★table_expr：表示使用SELECT语句中的FROM子句及后面的语法部分。
注意：存储过程体中的SELECT…INTO语句返回的结果集只能有一行数据。
例题：创建一个存储过程，用于得到某个学生的籍贯。
mysql> delimiter $$ mysql> create procedure sp_native1(in sno int) begin declare na nchar(20); select native into na from tb_student where studentno=sno; select na; end$$ mysql> delimiter ;
调用：
mysql> call sp_native1 ('2013110201'); +----------------+ | na | +----------------+ | 内蒙古 | +----------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
例题：创建一个存储过程，用于得到某个指定学生的籍贯。
mysql> delimiter $$ mysql> create procedure sp_native2(inout sno int) begin declare na nchar(20); select native into na from tb_student where studentno=sno; select na; end$$ mysql> delimiter ;
注意：调用前这里必须先赋值。
mysql> set @sno=2013110101; mysql> call sp_native2 (@sno); +-------------+ | na | +-------------+ | 山西 | +-------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)
【理解】：参数类型in、out、inout的区别！具体内容请参阅P141。
4）流程控制语句
在MySQL 5.5中，可以在存储过程体中使用以下两类用于控制语句流程的过程式SQL语句。
（1）条件判断语句
常用的条件判断语句有IF-THEN-ELSE语句和CASE语句。其中，IF-THEN-ELSE语句可以根据不同的条件执行不同的操作，其语法格式为： IF search_condition THEN statement_list [ELESEIF search_condition THEN statement_list]… [ELSE statement_list] END IF
语法及使用说明如下：
★search_condition：用于指定判断条件。
★statement_list：用于表示包含了一条或多条的SQL语句。
★只有当判断search_condition为真时，才会执行相应的SQL语句。
★IF-THEN-ELSE语句不同于系统函数IF（）。
CASE语句在存储过程中的使用具有两种语法格式，分别是： CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]… [ELSE statement_list] END CASE 或 CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] [ELSE statement_list] END CASE
语法说明如下：
★第一种语法格式中的case_value用于指定要判断的值或表达式，随后紧跟的是一系列WHEN-THEN语句块。其中，第一个WHEN-THEN语句块中的参数when_value用于指定要与case_value进行比较的值。倘若比较的值为真，则执行对应的statement_list中的SQL语句。如若每一个WHEN-THEN语句块中的参数when_value都不能与case_value相匹配，则会执行ELSE子句中指定的语句。该CASE语句最终会以关键字END CASE作为结束。
★第二种语法格式中的关键字CASE后面没有指定参数，而是在WHEN-THEN语句块中使用search_condition指定一个比较表达式。若表达式为真，则会执行对应的关键字THEN后面的语句。与第一种语法格式相比，这种语法格式能够实现更为复杂的条件判断，而且使用起来会更方便。
例题：新建存储过程用于实现插入不同的数据（1）。
use db_school Create table table1(variable1 char(10)); DELIMITER $$ CREATE PROCEDURE proc1 (IN parameter1 INTEGER) BEGIN DECLARE variable1 CHAR(10); IF parameter1 = 17 THEN SET variable1 = 'birds'; ELSE SET variable1 = 'beasts'; END IF; INSERT INTO table1 VALUES (variable1); END $$ DELIMITER ;
验证：
set @parameter1=18;
call proc1(@parameter1);
select * from table1;
例题：新建存储过程用于实现插入不同的数据（2）
Create table t(s1 int); DELIMITER $$ CREATE PROCEDURE proc2(IN parameter int) begin declare var int; set var=parameter+1; if var=0 then insert into t values(17); end if; if parameter=0 then update t set s1=s1+1; else update t set s1=s1+2; end if; end $$ DELIMITER ;
验证：
Set @parameter=-1; Call proc2(@parameter); Select * from t;
Set @parameter=0; Call proc2(@parameter); Select * from t;
例题：新建存储过程用于实现插入不同的数据（3）CASE语句
Create table t1(s1 int); DELIMITER $$ CREATE PROCEDURE proc3(in parameter int) begin declare var int; set var=parameter+1; case var when 0 then insert into t1 values(17); when 1 then insert into t1 values(18); else insert into t1 values(19); end case; end $$ DELIMITER ;
验证
set @parameter=0; call proc3(@parameter); select * from t1;
或者
DELIMITER $$ CREATE PROCEDURE proc4(in parameter int) begin declare var int; set var=parameter+1; case when var=0 then insert into t1 values(30); when var>0 then insert into t1 values(40); when var<0 then insert into t1 values(50); else insert into t1 values(60); end case; end $$ DELIMITER ;
验证：
set @parameter=-1; call proc3(@parameter); select * from t1;
（2）循环语句
常用的循环语句有WHILE语句、REPEAT语句和LOOP语句。
WHILE语句语法格式如下：
while条件 do
--循环体
End while
例题：新建存储过程用于实现插入不同的数据（4）-WHILE语句。
DELIMITER $$ CREATE PROCEDURE proc5() begin declare var int; set var=0; while var<6 do insert into t values(var); set var=var+1; end while; end $$ DELIMITER ;
验证：
delete from t; call proc5; select * from t;
REPEAT语句语法格式如下：
repeat
--循环体
until循环条件
endrepeat;
注：它在执行操作后检查结果，而while则是执行前进行检查。
例题：新建存储过程用于实现插入不同的数据（5）-REPEAT语句。
DELIMITER $$ CREATE PROCEDURE proc6() begin declare v int; set v=0; repeat insert into t values(v); set v=v+1; until v>=5 end repeat; end $$ DELIMITER ;
验证：
delete from t; call proc6; select * from t;
LOOP语句：
注：loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环；
例题：新建存储过程用于实现插入不同的数据（6）-LOOP语句。
DELIMITER $$ CREATE PROCEDURE proc7() begin declare v int; set v=0; LOOP_LABLE:loop insert into t values(v); set v=v+1; if v >=5 then leave LOOP_LABLE; end if; end loop; end $$ DELIMITER ;
验证：
delete from t; call proc7; select * from t;
LABLES:标号：
标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步;
ITERATE(迭代)语句：
通过引用复合语句的标号,来从新开始复合语句。
例题：新建存储过程用于实现插入不同的数据（7）- ITERATE(迭代)语句。
DELIMITER $$ CREATE PROCEDURE proc10() begin declare v int; set v=0; LOOP_LABLE:loop if v=3 then set v=v+1; ITERATE LOOP_LABLE; end if; insert into t values(v); set v=v+1; if v>=5 then leave LOOP_LABLE; end if; end loop; end $$ DELIMITER ;
验证：
delete from t; call proc10; select * from t;
MySQL存储过程的查询:
（1） select name from mysql.proc where db=’数据库名’; （2） show procedure status where db=’数据库名’; （3） SHOW CREATE PROCEDURE 数据库.存储过程名 \G
5）游标
游标（cursor）就是游动的标识，通俗的这么说，一条sql取出对应n条结果资源的接口/句柄，就是游标，沿着游标可以一次取出一行；
使用场景：
1.只能用于存储过程或存储函数中，不能单独在查询操作中使用；
2.在存储过程或存储函数中可以定义多个游标，但是一个BEGIN...END
语句块中每一个游标的名字必须是唯一的；
3.游标不是一条SELECT语句，是被SELECT语句检索出来的结果集；
游标使用步骤：
1）声明游标：使用游标前，必须申明（定义）它，定义要使用的SELECT语句；
DECLARE 游标名称 CURSOR FOR SELECT语句（返回一行或多行的数据）；
2）打开游标：使用游标前，打开游标
OPEN 游标名称；
3）读取数据：对于填有数据的游标，可根据需要取出数据；
FETCH 游标名 INTO 变量名1，...变量名n;
注：FETCH语句是将游标指向的一行数据赋给一些变量，这些变量的数目必须等于声明游标SELECT子句中选择列的数目，游标相当于一个指针，指向当前的一行数据；
- 关闭游标：CLOSE 游标名称
例题：在数据库db_school中创建一个存储过程，用于计算表tb_student中数据行的行数。
USE db_school; Delimiter $$ Create procedure sp_sumrow(in rows int) Begin DECLARE sno CHAR; DECLARE FOUND BOOLEAN DEFAULT TRUE; DECLARE cur CURSOR FOR SELECT studentno from tb_student; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FOUND=FALSE; SET rows=0; OPEN cur; FETCH cur INTO sno; WHILE FOUND DO SET rows=rows+1; FETCH cur INTO sno; END WHILE; CLOSE cur; Select @rows; End $$ Delimiter ;
验证：
Call sp_sumrow(@rows);
6）删除存储过程
mysql>drop procedure sp_sumrow;
三、存储函数
create function 存储函数名(参数 数据类型)
RETURES 数据类型
存储函数体
Return values 返回数值给存储函数体
语法:
CREATE FUNCTION sp_name([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body Return 存储函数语法： Create function 函数([函数参数[,….]]) Returns 返回类型 Begin If( Return (返回的数据) Else Return(返回的数据) end if; end;
例1：在数据库db_school中创建一个存储函数，要求该函数根据给定的学号返回学生的姓名；
-- 创建存储函数
DELIMITER $$ CREATE FUNCTION f_studentname(id INT ) RETURNS CHAR(80) Begin RETURN (SELECT Studentname FROM tb_student WHERE studentno=id ); END $$ DELIMITER ; Select f_studentname(‘2013110101’);
例2：在数据库db_school中出创建一个存储函数，要求该函数根据给定的学号返回学生的性别，
如果数据库中没有给定的学号，则返回‘没有该学生’;
DELIMITER $$ CREATE FUNCTION search_sex1(sno char(10)) Returns char(2) deterministic Begin Declare ssex char(2); Select sex INTO ssex from tb_student where studentno=sno; IF SSEX IS NULL THEN RETURN(SELECT '没有该学生'); ELSE IF SSEX='女' THEN Return(Select '女'); ELSE Return(Select '男'); END IF; END IF; end $$ Select search_sex1(‘2013110101’)$$
查看存储过程和函数
存储过程和函数创建以后，可以查看存储过程和函数的状态和定义。
通过SHOW STATUS语句来查看存储过程和函数的状态，也可以通过SHOW CREATE语句来查看存储过程和函数的定义。
通过查询information_schema数据库下的Routines表来查看存储过程和函数的信息
1、SHOW STATUS语句查看存储过程和函数的状态
MySQL中可以通过SHOW STATUS语句查看存储过程和函数的状态。其基本语法形式如下：
SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ' pattern ' ] ;
其中，PROCEDURE参数表示查询存储过程；FUNCTION参数表示查询存储函数；
LIKE ' pattern '参数用来匹配存储过程或函数的名称。
SHOW FUNCTION STATUS LIKE '% search_sex1%';
