8.存储过程实验
8.1存储过程定义
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。
8.2存储过程的优点
1) 封装性
通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。
2) 可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
3) 可减少网络流量
由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
4) 高性能
当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能。
5) 提高数据库的安全性和数据的完整性
存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。
6) 使数据独立
数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。
8.3创建存储过程
语法格式如下：
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>
语法说明如下：
1) 过程名
存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
2) 过程参数
存储过程的参数列表。其中，< 参数名 >为参数名，< 类型 >为参数的类型（可以是任何有效的 MySQL 数据类）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。
MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。
需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。
3) 过程体
存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。
在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。
注意：在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。
为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下：
DELIMITER $$语法说明如下：
- $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
- 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。
在 MySQL 命令行客户端输入如下 SQL 语句。
mysql > DELIMITER ??成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。
若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：
mysql > DELIMITER ;注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。
8.3.1创建存储过程例子
mysql> delimiter $$
mysql> create procedure show_employeeInfo()
-> begin
-> select *from employee;
-> end$$8.4查看存储过程结构：
show create procedure show_employeeInfo \G;
8.5删除存储过程：
DROP PROCEDURE [ IF EXISTS ] <过程名>语法说明如下：
①过程名：指定要删除的存储过程的名称。
②IF EXISTS：指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。
注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。
8.6修改存储过程的语法格式如下：
ALTER PROCEDURE 存储过程名 [ 特征 ... ]特征指定了存储过程的特性，可能的取值有：
①CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
②NO SQL 表示子程序中不包含 SQL 语句。
③READS SQL DATA 表示子程序中包含读数据的语句。
④MODIFIES SQL DATA 表示子程序中包含写数据的语句。
⑤SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
⑥DEFINER 表示只有定义者自己才能够执行。
⑦INVOKER 表示调用者可以执行。
⑧COMMENT 'string' 表示注释信息。
8.7存储过程调用
CALL 存储过程名([参数…]);8.8存储函数
和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作。
8.1.8.1语法在 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 数据库的任意数据类型。
8.8.2例子
Delimiter $$;
create function real_income(employeeID_1 varchar(6),employeeID_2 varchar(6)) returns tinyint(1) begin if (select income - outcome from salary where employeeID=employeeID_1) > (select income - outcome from salary where employeeID=employeeID_2) then return 0; else return 1; end if ; end$$8.9定义变量
MySQL 中可以使用 DECLARE 关键字来定义变量，其基本语法如下：
DECLARE var_name[,…] type [DEFAULT value]
其中：
- DECLARE 关键字是用来声明变量的；
- var_name 参数是变量的名称，这里可以同时定义多个变量；
- type 参数用来指定变量的类型；
- DEFAULT value 子句将变量默认值设置为 value，没有使用 DEFAULT 子句时，默认值为 NULL。
8.9.1例 1
下面定义变量 my_sql，数据类型为 INT 类型，默认值为 10。SQL 语句如下：
DECLARE my_sql INT DEFAULT 10;8.9.2. 为变量赋值
MySQL 中可以使用 SET 关键字来为变量赋值，SET 语句的基本语法如下：
SET var_name = expr[,var_name = expr]...其中：
- SET 关键字用来为变量赋值；
- var_name 参数是变量的名称；
- expr 参数是赋值表达式。
注意：一个 SET 语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
8.9.3例 2
下面为变量 my_sql 赋值为 30。SQL 语句如下：
SET my_sql=30;MySQL 中还可以使用 SELECT..INTO 语句为变量赋值。其基本语法如下：
SELECT col_name [...] INTO var_name[,...]
FROM table_name WEHRE condition其中：
- col_name 参数表示查询的字段名称；
- var_name 参数是变量的名称；
- table_name 参数指表的名称；
- condition 参数指查询条件。
注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行。
8.10例子
8.10.1.创建一个存储过程实例
mysql> delimiter $$
mysql> create procedure show_employeeInfo()
-> begin
-> select *from employee;
-> end$$
Query OK, 0 rows affected (0.49 sec)
然后查看过程存储创建结构：
最后调用存储过程函数：
8.10.2.删除存储过程：使用DROP PROCEDURE语句删除存储过程，语法格式为：
DROP PROCEDURE [IF EXISTS] 存储过程名;8.10.3创建一个存储过程，计算employee表中的员工人数，并存储到一个局部变量中，调用存储过程，并查看该变量结果（使用select @variable）
create procedure count_employee(out count_emp int) begin set count_emp = (select count(*) from employee) ; end $$ 
调用并查询结果：
8.10.4.创建一个存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1，员工用其员工编号识别。
Delimiter $$;
create function real_income(employeeID_1 varchar(6),employeeID_2 varchar(6)) returns tinyint(1) begin if (select income - outcome from salary where employeeID=employeeID_1) > (select income - outcome from salary where employeeID=employeeID_2) then return 0; else return 1; end if ; end$$这时出现报错：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
在MySQL中创建函数时出现这种错误的解决方法：
set global log_bin_trust_function_creators=TRUE;
问题解决。
然后开始调用函数：
成功实现需要的功能。
8.11详细的实验资料以及指导PPT请访问笔者GitHub地址：https://github.com/LJF2402901363/database_experiment.git
