第 9 章 存储过程与函数

存储过程和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建中的子程序。一个子程序要么是一个存储程序要么是一个函数。在MySQL中，使用CALL语句来调用子程序。本章主要介绍如何创建存储过程和函数以及变量的使用，如何查看、修改、删除存储过程和函数。

9.1 存储过程的定义

存储过程是一组为了完成特定功能的SQL语句集合。使用存储过程的目的是将常用或复杂的工作，预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需用CALL语句来调用存储过程的名字，即可自动完成命令。
存储过程的优点：
（1）运行效率高。（2）降低了网络通信量。（3）业务逻辑可以封装在存储过程中，方便实施企业规则。

9.2 存储过程的创建

创建存储过程，需要使用CREATE PROCEDURE语句，基本语法格式如下：
CREATE PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic …] routine_body
（1）CREATE PROCEDURE为创建存储过程的关键字。
（2）Sp_name为存储过程的名称，默认为存储过程与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。
（3）proc_parameter指定存储过程的参数列表，列表形式如下：
[IN | OUT | INOUT]param_name type 其中，IN表示输入参数；OUT表示输出参数；INOUT表示既可以输入也可以输出；param_name表示参数名称，type表示参数的类型，该类型可以是MySQL数据库中的任意类型。
（4）characteristics指定存储过程的特性，有以下取值：
LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
[NOT DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出；[NOT] DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为[NOT]DETERMINISTIC。
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA:指明子程序使用SQL语句的限制。CONTAINS SQL:表明子程序包含SQL语句，但是不包含读写数据的语句。NO SQL：表明子程序不包含SQL语句。READS SQL DATA：说明子程序包含读数据的语句。MODIFIES SQL DATA：表明子程序包含写数据的语句。默认情况下，系统会指定CONTAINS SQL。
SQL SECURITY{DEFINER | INVOKER}：指明谁有权限来执行。DEFINER表示只有定义者才能执行；INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
COMMENT ‘string’：注释信息，可以用来描述存储过程或函数。
（5）routine_body是SQL代码的内容，可以用BEGIN … END来表示SQL代码的开始和结束。
提示：由括号周围的参数列表必须总是存在。如果没有参数，也该使用一个空参数列表（）。每个参数默认都是一个IN参数。要指定为其他参数，可在参数名之前使用关键词OUT或IN OUT.

9.3 存储过程的操作

9.3.1 存储过程的调用

CALL语句调用一个CREATE PROCEDURE创建好的存储过程。基本语法如下：
CALL sp_name（[parameter[,…]]）
CALL调用语句中的Sp_name为存储过程名称，parameter为存储过程的参数。

9.3.2 存储过程的查看

1. 使用SHOW PROCEDURE STATUS语句查看存储过程的状态
   SHOW RPOCEDURE STATUS[LIKE ‘pattern’ ]
   这个语句是一个MySQL的扩展。它返回存储过程的特征，如所属数据库、名称、类型、创建者及创建和修改日期。如果没有指定样式，根据用户使用的语句，所有存储过程被列出。LIKE语句表是匹配存储过程的名称。
2. SHOW CREATE PROCEDURE查看存储过程的信息
   SHOW CREATE PROCEDURE Sp_name
   该语句是一个MySQL的扩展。返回一个可用来重新创建已命名存储过程的确切字符串。
3. 通过INFORMATION_SCHEMAT.ROUTINES查看存储过程的信息
   INFORMATION_SCHEMA是信息数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。该数据库中的ROUTINES表提供存储过程的信息。通过查询该表可以查询相关存储过程的信息，语法如下：
   select * from information_schema.routines
   Where routine_name=‘Sp_name’；
   其中，routine_name字段存储所有存储子程序的名称；Sp_name是需要查询的存储过程名称。
4. 存储过程写好后，可以通过ALTER 语句来修改它的特性，语法如下：
   ALTER {PROCEDURE | FUNCTION} Sp_name[characteristic …]
   其中，Sp_name为待修改的存储过程名称；characteristic来指定特性，可能取值如下：
   {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
   | SQL_SECURITY {DEFINER | INVOKER}
   | COMMENT ‘string’
   CONTAINS SQL表示存储过程包含SQL语句，但不包含读或写数据的语句；NO SQL表示存储过程中不包含SQL语句；READS SQL DATA表示存储过程中包含读数据的语句；MODIFIES SQL DATA表示存储过程中包含写数据的语句。SQL SECURITY {DEFINER | INVOKER }指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。COMMENT‘string’是注释信息。

9.3.3 存储过程删除

MySQL 中使用DROP PROCEDURE语句来删除存储过程。其基本语法如下：
DROP PROCEDURE Sp_name；
其中，Sp_name参数表示存储过程名称。

9.4 自定义函数

9.4.1 自定义函数的创建

在MySQL中，创建自定义函数的语法如下：
CREATE FUNCTION Sp_name（[func_parameter[, … ]]）
RETURNS type
[characteristic … ] routine_body
CREATE FUNCTION创建自定义函数的关键字
Sp_name参数是自定义函数的名称
func_parameter表示自定义函数的参数列表。func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式为：param_name type 其中，param_name参数是自定义函数的参数名称；type参数指定自定义函数的参数类型，该类型可以是MySQL数据库的任意数据类型。
RETURNS type指定返回值的类型，只能返回一个结果
characteristic 参数指定自定义函数的特性，该参数的取值与存储过程中的取值是一样的。
routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。
提示：RETURNS语句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

9.4.2 自定义函数的调用

在MySQL中，自定义函数的调用方法与MySQL内部函数的调用方法是相同的。用户自定义的函数与MySQL内部函数性质相同，区别在于，自定义函数是用户自定义的，而内部函数是MySQL的开发者定义的。

9.4.3 变量

在存储过程和自定义函数中，都可以定义和使用变量。变量的定义使用DECLARE关键字，定义后可以为变量赋值。变量的作用域在BEGIN…END程序段中。

1. 定义变量
   MySQL中使用DECLARE关键字来定义变量，基本语法如下：
   DECLARE var_name[, … ] type [DEFAULT value]
   其中，var_naem是变量的名称，可以同时定义多个变量；type用来指定变量的类型；DEFAULT value 子句为变量提供一个默认值。默认值可以是一个常数，也可以是一个表达式。如果没有给定变量指定默认值，初始值为NULL。
2. 变量的赋值
   MySQL中使用SET语句为变量赋值，语法格式如下：
   SET var_name = expr [, var_name = expr]…
   其中，SET关键字是用来给变量赋值的；var_name为变量的名称；expr是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
   MySQL中还可以使用SELECT…into语句为变量赋值。其基本语法如下：
   SELECT col_name[, … ] INTO var_name[, … ]
   FROM table_name WHERE conditon
   该语句实现将SELECT选定的列值直接存储在对应位置的变量中，语句中的con_name为查询的字段名称；var_name为变量的名称；table_name参数指定查询的表的名称；condition参数指定查询条件。

9.4.4 流程控制语句

存储过程和自定义函数中使用流程控制来控制语句的执行。MySQL中常用来构造控制流程的语句有：IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHLE语句。

1. IF语句
   用来进行条件判断，根据判断结果为TRUE或FALSE执行不同的语句。其语法格式如下：
   IF search_condition THEN statement_list
   [ELSEIF search_condition THEN statement_list] …
   [ELSE statement_list]
   END IF
   语法中的search_condition参数表示条件判断语句，如果该参数值为TRUE，执行相应的SQL语句，如果参数为假，则执行ELSE子句中的语句。statement_list参数表示不同条件的执行语句，可以包含一条或多条语句。

2. CASE语句
   也用来进行条件判断，可以实现那比IF语句更为复杂的条件判断。CASE语句有两种基本形式。第一种基本形式如下：
   CASE case_value
   WHEN when_value THEN statement_list
   [WHEN when_value THEN statement_list]…
   [ELSE statemet_list]
   END CASE
   语法中的case_value参数表示条件判断的表达式，该表达式的值决定哪个WHEN子句被执行。when_value参数表示表达式可能的取值；如果某个when_value表达式与case_value表达式的结果相同，则执行相应的THEN后面的statement_list中的语句。
   CASE语句另一种形式的语法如下：
   CASE
   WHEN search_condition THEN statement_list
   [WHEN search_condition THEN statement_list]…
   [ELSE statement_list]
   END CASE
   其中，search_conditon参数表示条件判断语句，若search_condition判断为真，则执行相应的THEN后面的statement_list语句。如果没有条件匹配，ELSE子句后面的语句将被执行。

3. LOOP语句
   可以重复执行特性的语句，实现简单的循环。但是loop语句本身并不执行条件判断，没有停止循环的语句，必须使用LEAVE语句才能停止循环，跳出循环过程。LOOP语句的语法基本形式如下：
   [begin_label:]LOOP
   statement_list
   END LOOP[end_label]
   其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；statement_list参数表示需要循环执行的语句。

4. LEAVE语句
   主要用来跳出任何被标注的流程控制语句。其语法形式如下：
   LEAVE label
   其中，label参数表示循环的标志。LEAVE和循环或BEGIN…END语句一起使用。

5. ITERATE语句
   也是用来跳出循环的语句。但ITERATE只可以出现在LOOP、REPEA和WHILE语句内。ITERATE语句是跳出本次循环，然后直接进入下一次循环。ITERATE 语句的基本语法形式如下：
   ITERATE label
   其中，label参数表示循环的标志。
   提示：LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。

6. REPEAT语句
   REPEAT语句创建的是带条件判断的循环过程。循环语句每次执行完都会对表达式进行判断，若表达式为真，则结束循环，否则再次重复执行循环中的语句。当条件判断为真时，就会跳出循环语句。REPEAT语句的基本语法形式如下：
   [begin_label:]REPEAT
   statement_list
   UNTIL search_condition
   END REPEAT[end_label]
   其中，begin_label和end_label为开始标记和结束标记，两者均可以省略；statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，该条件为真时结束跳出循环，该参数为假时，再次执行循环语句。

7. WHILE语句
   也是有条件控制的循环语句。但与REPEAT语句不同的是，WHILE语句在执行语句时，先对条件表达式进行判断，若该条件表达式为真，则执行循环内的语句。否则，退出循环过程。WHILE语句的基本语法形式如下：
   [begin_label:]WHILE search_condition DO
   statement_list
   END WHILE[end_label]
   其中，begin_label和end_label为开始标记和结束标记，两者均可以省略；search_condition为条件判断表达式，若该条件判断表达式为真，则执行循环中的语句，否则退出循环。

9.4.5 光标的使用

在存储过程或自定义函数中的查询可能返回多条记录，可以使用光标来逐条读取查询结果集中的记录。光标的使用包括光标的声明、打开光标、使用光标和关闭关闭光标。需要注意的是，光标必须在处理程序之前声明，在变量和条件之后声明。

1. 声明光标
   MySQL中使用DECLARE来声明光标，语法如下：
   DECLARE cursor_name CURSOR FOR select_statement
   其中，cursor_name为光标的名称；select_statement为查询语句，返回一个结果集，声明的光标基于该结果集进行操作。可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名称。

2. 打开光标
   OPEN cursor_name
   其中，cursor_name为先前声明的光标。

3. 使用光标
   MySQL中使用FETCH语句来操作和使用光标，语法如下：
   FETCH cursor_name INTO var_name [, var_name] …
   其中，cursor_name为先前声明并打开的光标名称；var_name参数表示将光标声明中的SELECT语句中的查询信息存储在该参数中；var_name必须在光标声明前定以好。

4. 关闭光标
   CLOSE cursor_name
   MySQL中使用CLOSE来执行关闭光标，cursor_name为声明并打开的光标。如果为被明确地关闭，光标在它被声明的复合语句的末尾被关闭。
   提示：MySQL中的光标只能在存储过程和自定义函数中使用。

9.4.6 定义条件和处理程序

在程序的运行过程中可能会遇到问题，可以使用定义条件和处理程序来事先定义这些问题，并且可以在处理程序中定义在遇到这些问题时应该采用什么样的处理方式，提出解决方法，保证存储过程或自定义函数在遇到警告或错误时能够继续执行，从而增强程序处理问题的能力，避免程序异常被停止执行。在MySQL中，使用DECLARE来定义条件和处理程序。

1. 定义条件
   DECLARE condition_name CONDITION FOR condition_value
   condition_value:
   SQLSTATE[VALUE] sqlstate_value | mysql_error_code
   其中，condition_name 参数为条件的名称；condition_value参数为条件的类型；sqlstate_value和mysql_error_code都可以表示mysql的错误，sqlstate_value为长度为5的字符串类型的错误码，mysql_error_code为数值类型错误代码。

2. 定义处理程序
   DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement
   其中，handler_type表示CONTINUE | EXIT | UNDO语句中的handler_type为错误处理的方式，取以下3个值中的一个：CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误马上退出；UNDO表示遇到错误后撤销之前的操作。
   condition_value:表示错误的类型，可有以下的取值
   SQLSTATE[VALUE] sqlstate_value 字符串错误值
   | condition_name 使用Ddeclare condition定义的错误条件名称
   | SQLWARING 匹配所有以01开头的SQLSTATE错误代码
   | NOT FOUND 匹配所有以02开头的SQLSTATE错误代码
   | SQLEXCEPTION 匹配所有没有被SQLWARING或NOTFOUND捕获的SQLSTATEC错误代码。
   | mysql_error_code

9.6 高手点拨

1. 自定义函数都是输入参数。函数运算结果通过returns返回，且只能返回一个结果
2. 存储过程中定义的局部变量和会话变量是不同的。会话变量前面必须加“@”符号，且会话变量的作用与是整个会话；存储过程体可以使用DECLARE语句定义局部变量，存储过程的参数也被认为是局部变量，对局部变量的使用不能在前加“@”符号。
3. 存储过程的代码不可以改变，只能改变特征信息。