九、MySQL存储过程和函数

　　简单地说，存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。

一、创建存储过程和函数

　　存储程序可分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是：CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（即通过引用函数名），也能返回标量值。存储过程也能够调用其他存储过程。

1.1 创建存储过程

　　创建存储过程，需要使用CREATE PROCEDURE语句，基本语法格式如下：

CREATE PROCEDURE sp_name ([proc_parameter]) [characteristics ...] routine_body

　　CREATE PROCEDURE为用来存储函数的关键字；sp_name为存储过程的名称；proc_parameter为指定存储过程的参数列表，列表形式如下：

[IN|OUT|INOUT] param_name type

　　其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。

　　characteristics指定存储过程的特性，有以下取值：

 　　（1）LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。

　　（2）[NOT]DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

　　（3）{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。

　　（4）SQL SECURITY{DEFINER|INVOKER}：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。

　　（5）COMMENT 'string'：注释信息，可以用来描述存储过程或函数。

　　routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。

　　编写存储过程并不是件简单的事情，可能存储过程中需要复杂的SQL语句，并且要有创建存储过程的权限；但是使用存储过程将简化操作，减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率，因此存储过程是非常有用的，而且应该尽可能地学会使用。

　　提示：“DELIMITER //”语句可以将MySQL的结束符设置为“//”，因为MySQL默认的语句结束符号是分号“;”，为了避免与存储过程中SQL语句的结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以“END//”结束存储过程。存储过程定义完毕之后在使用“DELIMITER;”恢复默认结束符。“DELIMITER”也可以指定其他符号作为结束符。但应避免使用反斜杠“\”字符，因为反斜杠是MySQL的转义字符。

1.2 创建存储函数

　　创建存储函数，需要使用CREATE FUNCTION语句，基本语法格式如下：

CREATE FUNCTION func_name([func_parameter])
RETURNS type
[characteristic ...] routine_body

　　CREATE FUNCTION为用来创建存储函数的关键字：func_name表示存储函数的名称；func_parameter为存储过程的参数列表，参数列表形式如下：

[IN|OUT|INOUT] param_name type

　　其中，IN表示输入参数，OUT表示输出参数，INOUT表示即可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。

　　RETURNS type语句表示函数返回数据的类型；characteristic指定存储函数的特性，取值与创建存储过程时相同。

　　提示：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。（FUNCTION中总是默认为IN参数）。RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

1.3 变量的使用

　　变量可以在子程序中声明并使用，这些变量的作用范围是BEGIN...IN程序中。

1.3.1 定义变量

　　在存储过程中使用DECLARE语句定义变量，语法格式如下：

DECLARE var_name[,varname]... date_type [DEFAULT value];

　　var_name为局部变量的名称。DEFAULT value子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL。

1.3.2 为变量赋值

　　定义变量之后，为变量赋值可以改变变量的默认值，MySQL中使用SET语句为变量赋值，语法格式如下：

SET var_name=expr [,var_name=expr] ...;

　　在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

　　在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x,b=y,...这样的扩展语法。其中不同的变量类型（局部声明变量及全局变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

　　MySQL中哈可以通过SELECT...INTO...为一个或多个变量赋值，语法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr;

　　这个SELECT语法把选定的列直接存储到对应位置的变量。col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。

1.4 定义条件和处理程序

 　　特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

1.4.1 定义条件

　　定义条件使用DECLARE语句，语法格式如下：

DECLARE condition_name CONDITION FOR [condition_type]

[condition_type]:
SQLSTATE [VALUE] sqlstate_value|mysql_error_code

　　其中condition_name参数表示条件的名称；condition_type表示条件的类型；sqlstate_value和mysql_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码，mysql_error_code为数值类型错误代码。

　　这个语句指定需要特殊处理的条件。它将一个名字和指定的错误关系关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

1.4.2 定义处理程序

　　定义处理程序时，使用DECLARE语句的语法如下：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE|EXIT|UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
    |condition_name
    |SQLWARNING
    |NOT FOUND
    |SQLEXCEPTION
    |mysql_error_code

　　其中，handler_type为错误处理方式，参数取3个值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不处理，继续执行；EXIT遇到错误马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。

　　condition_value表示错误类型，可以有以下取值：

　　（1）SQLSTATE[VALUE] sqlstate_value：包含5个字符的字符串错误值；

　　（2）condition_name：表示DECLARE CONDITION定义的错误条件名称；

　　（3）SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；

　　（4）NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；

　　（5）SQLEXCEPTION：匹配所有没被SQLWARNING或NOT FOUND捕获的SQLSTATE代码；

　　（6）mysql_error_code：匹配数值类型错误代码。

　　sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

1.5 光标的使用

　　查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。

　　光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.5.1 声明光标

　　MySQL中使用DECLARE关键字来声明光标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement

　　其中，cursor_name参数表示光标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建光标的结果集。

1.5.2 打开光标

　　打开光标的语法如下：

OPEN cursor_name{光标名称}

　　这个语句打开先前声明的名称为cursor_name的光标。

1.5.3 使用光标

　　使用光标的语法如下：

FETCH cursor_name INTO var_name [, var_name] ...{参数名称}

　　其中，cursor_name参数表示光标的名称；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

1.5.4 关闭光标

　　关闭光标的语法如下：

CLOSE cursor_name{光标名称}

　　这个语句关闭闲钱打开的光标。

　　如果未被明确的关闭，光标在它被声明复合语句的末尾被关闭。

　　MySQL中光标只能在存储存储过程和函数中使用。

1.6 流程控制的使用

　　流程控制语句用来根据条件控制语句的执行。MySQL中用来构造流程控制的语句有：IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句和WHILE语句。

　　每个流程中可能包含一个单独语句，或者是使用BEGIN...END构造的复合语句，构造可以被嵌套。

1.6.1 IF语句

　　IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式如下：

IF expr_condition THEN statement_list
    [ELSEIF expr_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

　　IF实现了一个基本的条件构造。如果expr_condition求值为真（TRUE），相应的SQL语句列表被执行；如果没有expr_condition匹配，则ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

　　MySQL中还有一个IF()函数，它不同于这里描述的IF语句。

1.6.2 CASE语句

　　CASE是另一个进行条件判断的语句，该语句有2种语句格式，第一种格式如下：

CASE case_expr
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;

　　其中，case_expr参数表示条件判断的表达式，决定了哪一个WHEN子句会被执行；when_value参数表示表达式可能的值，如果某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement_list中的语句；statement_list参数表示不同when_value值的执行语句。

　　CASE语句的第二种格式如下：

CASE
    WHEN expr_condition THEN statement_list
    [WHEN expr_consition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

　　其中，expr_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。该语句中，WHEN语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应THEN关键字后面的statement_list语句。如果没有条件匹配，ELSE子句里的语句被执行。

　　这里介绍的用在存储程序里的CASE语句与“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句，并且用END CASE替代END来终止。

1.6.3 LOOP语句

　　LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出，跳出循环过程，使用LEAVE子句，LOOP语句的基本格式如下：

[loop_label:]LOOP
    statement_list
END LOOP [loop_label]

　　loop_label表示LOOP语句的标注名称，该参数可以省略；statement_list参数表示需要循环执行的语句。

1.6.4 LEAVE语句

　　LEAVE语句用来退出任何被标注的流程控制制造，LEAVE语句基本格式如下：

LEAVE label

　　其中，label参数表示循环的标志。LEAVE和BEGIN...END或循环一起被使用。

1.6.5 ITERATE语句

　　ITERATE语句将执行顺序转到语句段开头处，语句基本格式如下：

ITERATE label

　　ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为“再次循环”，label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。

1.6.6 REPEAT语句

　　REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。REPEAT语句的基本格式如下：

[repeat_label:]REPEAT
    statement_list
UNTLL expr_codition
END REPEAT [repeat_label]

　　repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_codition为真。

1.6.7 WHERE语句

　　WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。WHILE语句的基本格式为：

[while_label:]WHILE expr_condition DO
    statement_list
END WHILE [while_label]

　　while_label为WHILE语句的标注名称；expr_condition为进行判断的表达式，如果表达式结果为真，WHILE语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

二、调用存储过程和函数

 　　存储过程已经定义好了，接下来需要知道如何调用这些过程和函数。存储过程和函数有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库有关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。存储函数的调用与MySQL中预定义的函数的调用方式相同。

2.1 调用存储过程

　　存储过程是通过CALL语句进行调用的，语法如下：

CALL sp_name([parameter[,...]])

　　CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程，其中sp_name为存储过程名称，parameter为存储过程的参数。

2.2 调用存储函数

　　在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是自己定义的，而内部函数是开发者定义的。

三、查看存储过程和函数

 　　MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可以直接从系统的information_schema数据库中查询。

3.1 使用SHOW STATUS语句查看存储过程和函数的状态

　　SHOW STATUS语句可以查看存储过程和函数的状态，其基本语法结构如下：

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']

　　这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库、名字、类型创建者及创建日期和修改日期。如果没有指定样式，根据使用的语句，所有存储程序和存储函数的信息都被列出。PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE表示匹配存储过程或函数的名称。

3.2 使用SHOW CREATE语句查看存储过程和函数的定义

　　除了SHOW STATUS之外，MySQL还可以使用SHOW CREATE语句查看存储过程和函数的状态。

SHOW CREATE {PROCEDURE|FUNCTION} sp_name

　　这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数；sp_name参数表示匹配存储过程或函数的名称。

3.3 从information_schema.Routings表中查看存储过程和函数的信息

　　MySQL中存储过程和函数的信息存储在information_schema数据库下的Routings表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

SELECT * FROM information_schema.Routings WHERE ROUTINE_NAME='sp_name'

　　其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称；sp_name参数表示存储过程或函数的名称。

四、修改存储过程和函数

 　　使用ALTER语句可以修改存储过程或函数的特性。

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic...]

　　其中，sp_name参数表示存储过程或函数的名称；characteristic参数指定存储函数的特性，可能的取值有：

　　（1）CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句。

　　（2）NO SQL表示子程序中不包含SQL语句。

　　（3）READS SQL DATA说明子程序包含读数据的语句。

　　（4）MODIFIES SQL DATA表明子程序包含写数据的语句。

　　（5）SQL SECURITY{DEFINER|INVOKER}：指明谁有权限来执行。

　　（6）DEFINER表示只有定义者才能执行。

　　（7）INVOKER表示拥有权限的调用者可以执行。

　　（8）COMMENT 'string'：表示注释信息。

　　修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是这两个语句的结构是一样的，语句中所有参数也是一样的。而且，它们与创建存储过程或函数的语句中的参数也是基本一样的。

五、删除存储过程和函数

　　删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE|FUNCTION} {IF EXISTS} sp_name

　　这个语句用来移除一个存储过程或函数。sp_name为要移除的存储过程或函数的名称。

　　IF EXISTS子句是一个MySQL的扩展。如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。