MySQL 学习<九> 存储过程和函数

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

创建存储过程和函数

创建存储过程

基本语法如下：

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

CREATE PROCEDURE 为用来创建存储函数的关键字；sp_name为存储过程的名称； pro_parameter：参数列表。

列表形式： [ IN | OUT | INOUT ] param_name type

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

　　　　　　　　language sql:说明toutine_body部分是由SQL组成;

　　　　　　　　[NOT] DETERMINISTIC:指明相同的输入是否会得到相同的输出，DETERMINISTIC表示结果确定,NOT DETERMINISTIC表示不确定，默认为NOT DETERMINISTIC；

　　　　　　　　{CONTAINS SQL | NOT SQL | READS SQL DATA  | MODIFIES SQL DATA }:指明子程序使用SQL的限制，其中CONTAINS SQL指不包含读写数据的SQL语句；NOT SQL 表示不包含sql语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认CONTAINS SQL。

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

　　　　　　　　COMMENT 'String':注释信息，可以用来描述存储过程。

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

下面的代码演示了存储过程的内容，返回所有水果的平均价格：

CREATE PROCEDURE AvgFruitPrice()

　　　　　　　　BEGIN

　　　　　　　　　　　SELECT AVG(f_price)  AS avg_price FROM fruits;

　　　　　　　    END;

再例如，创建一个查看fruits表的存储过程，代码如下：

其中，DELIMITER 是将MySQL的结束符设置为 ‘//’

创建存储函数

基本语法如下：

CREATE FUNCTION func_name  ( [func_parameter] )

RETURNS type

[characteristic...] routine_body

其中：

1.CREATE FUNCTION为用来创建存储函数的关键字

2.func_name表示存储函数的名称

3.func_parameter为存储过程的参数列表，参数列表形式为[IN|OUT|INOUT] param_name type，和存储过程一样

4.RETURNS type表示函数返回数据的类型

5.characteristic表示存储函数的特性，和存储过程一样

举个栗子~

这里创建了一个名为NameByZip的存储函数，该函数返回SELECT语句的查询结果，数值类型为字符串型。

变量的使用

1.定义变量，基本语法如下：

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

　　　　　　　　var_name是局部变量名称,DEFAULT value子句为变量提供初始值。

2.为变量赋值，基本语法如下：

　　　　　　　　SET var_name = expr1[,var_name = expr2] ...;

声明三个变量，分别为var1，var2，var3，数据类型为int，使用set为变量赋值，代码如下：

　　　　　　　　DECLARE var1, var2, var3 int;

　　　　　　　　SET var1=10, var2=20;

　　　　　　　　SET var3 = var1 + var2;

还可以通过SELECT... INTO为一个或多个变量赋值：SELECT col_name[, ...] INTO var_name[, ...]  table_expr。

其中SELECT 语法把选定的列直接存储到相应位置的变量，col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，报错表名称和WHERE字句。

定义条件和处理程序

1.定义条件使用DECLARE语句，基本语法如下：

DECLARE condition_name CONDITION  FOR [condition_type]

[condition_type]:

 SQLSTATE  [VALUE] sqlstate_value | mysql_error_code
其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1142 (42000)中，sqlstate_value值是42000，mysql_error_code值是1142。

例如：如果定义"ERROR 1142 (42000)"这个错误，名称为can_not_find。可以用两种不同的方法来定义，代码如下：
//方法一：使用sqlstate_value 

DECLARE  can_not_find  CONDITION FOR SQLSTATE  '42000';

 //方法二：使用mysql_error_code 

DECLARE can_not_find  CONDITION FOR 1142;

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个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。
注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。
condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

例如定义处理程序的几种方式。代码如下：
//方法一：捕获sqlstate_value 

DECLARE  CONTINUE  HANDLER  FOR  SQLSTATE  '42S02'  SET  @info='CANNOTFIND'; 

//方法二：捕获mysql_error_code

DECLARE  CONTINUE  HANDLER  FOR  1146  SET  @info='CANNOTFIND'; 

//方法三：先定义条件，然后调用 

DECLARE  can_not_find  CONDITIONFOR1146;

 DECLARE  CONTINUE  HANDLER  FOR  can_not_find  SET  @info=' CANNOTFIND ';

 //方法四：使用SQLWARNING 

DECLARE  EXIT  HANDLER  FOR  SQLWARNING  SET  @info='ERROR';

 //方法五：使用NOT  FOUND 

DECLARE  EXIT  HANDLER  FOR  NOT  FOUND  SET  @info='CANNOTFIND'; 

//方法六：使用SQLEXCEPTION 

DECLARE  EXIT  HANDLER  FOR  SQLEXCEPTIONSET  @info='ERROR';

这里附上一篇比较详细的文章： 定义条件和处理程序

光标的使用

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

1.声明光标：DECLARE   cursor_name CURSOR FOR select_statement;

2.打开光标：OPEN cursor_name;
3.使用光标：FETCH cursor_name INTO var_name [,var_name]…{参数名称}
4.关闭光标：CLOSE cursor_name;

流程控制

1.IF语句,基本语法如下：
IF expr_condition THEN statement_list
　　

[ELSEIF expr_condition THEN statement_list]...
　　

[ELSE statement_list]

END IF

2.case语句,基本语法如下：
CASE case_expr

　　

WHEN when_value THEN statement_list
　　

[WHEN when_value THEN statement_list]...
       

 [ELSE statement_list]

END CASE

3.LOOP语句（循环操作的过程）,基本语法如下：
[loop_label:] LOOP

　　statement_list

END LOOP [loop_label]

4.LEAVE语句（退出循环）

举个栗子~

Delare id int default 0;

add_loop:LOOP

SET id = id + 1;

　　IF id >= 10 THEN LEAVE add_loop;

　　END IF;

END LOOP add_loop;

5.ITERATE 语句，基本语法：ITERATE label
该语句只能出现在循环内，意为“再次循环”。

6.REPEAT语句
REPEAT创建一个带条件判断的循环的过程，每次语句执行完毕，会对条件表达式进行判断，如果表达式为真，则循环结束，否则重复循环，基本语法如下：
[repeat_label:] REPEAT

　　statement_list

UNTIL expr_condition

END REPEAT [repeat_label]

repeat_label为标注名称(可省略)，直至expr_condition条件为真，才会退出循环。

7.WHILE语句
该语句创建带条件判断的循环过程，与REPEAT语句不同，WHILE 执行语句时先对表达式进行判断，如果为真则执行循环内语句，否则退出循环，语法形式如下：

[while_label:] WHILE expr_condition DO

　　statement_list

END WHILE [while_label]

调用存储过程

调用存储过程

基本语法：CALL sp_name([parameter[,...]])

调用存储函数

MySQL中调用存储函数的使用方法和MySQL内部函数的使用方法是一样的； 示例如下：

查看存储过程和函数

1. SHOW STATUS可以查看存储过程核函数的状态，其基本语法如下:

SHOW {PROCEDURE | FUNCTIOn} STATUS [LIKE 'pattern'];

2.使用SHOW CREATE，其基本语法如下：

SHOW CREATE {PROCEDURE | FUNCTION} sp_name；

3.从information_schema.Routines表中查看存储过程和函数，其基本语法如下：

SELECT * FROM information_schema.Routines

WHERE ROUTINE_NAME = ' sp_name';

修改存储过程和函数

基本语法如下：
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

其中characteristic参数指定存储函数的特性，可能的取值有：

CONTAINS SQL：表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL：表示子程序中不包含SQL语句；

READS SQL DATA：表示子程序中包含读数据的语句；

MODIFIES SQL DATA：表示子程序中包含写数据的语句；

SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行；DEFINER表示只有定义者自己才能够执行，INVOKER表示调用者可以执行。

COMMENT 'string'：是注释信息。

删除存储过程和函数

删除存储过程核函数，可以使用DROP语句，基本语法如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name；