mysql存储过程和函数总结

1. 概述

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
——百度百科

如图所示，在普通模式下获取数据，用户需要输入SQL命令与数据库进行交互，而存储过程是编写好的SQL命令，存储在数据库中，用户操作的时候只需要调用存储过程，而不用重新输入冗余繁杂的SQL命令。因此
存储过程有什么优点？
（1）存储过程可以重复使用，大大减小开发人员的负担；
（2）对于网络上的服务器，可以大大减小网络流量，因为只需要传递存储过程的名称即可；
（3）可以防止对表的直接访问，只需要赋予用户存储过程的访问权限。

2. 创建存储过程

MYSQL中创建存储过程和函数分别使用CREATE PROCEDURE和CREATE FUNCTION
使用CALL语句来调用存储过程，存储过程也可以调用其他存储过程，函数可以从语句外调用，能返回标量值

2.1 存储过程相关命令汇总

操作	SQL命令
创建存储过程	CREATE PROCEDURE 存储过程名(参数种类1 参数1 数据类型1,[…] BEGIN 具体的procedure(处理) END
查看数据库中的存储过程	SHOW PROCEDURE STATUS\G
查看具体的存储过程	SHOW CREATE PROCEDURE 存储过程名\G
调用（执行）存储过程	CALL 存储过程名(参数1,…);
删除存储过程	DROP PROCEDURE 存储过程名
变量声明	DECLARE 变量名 数据类型;
变量赋值	SET 变量名= ;

创建存储过程语法：

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

proc_parameter指定存储过程的参数列表，列表形式如下：

[IN|OUT|INOUT] param_name type

其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型
该类型可以是MYSQL数据库中的任意类型，有以下取值：

characteristic:

   LANGUAGE SQL | [NOT] DETERMINISTIC 
   | { CONTAINS
        SQL | NO
        SQL | READS SQL DATA | MODIFIES SQL DATA 
     }
   | SQL SECURITY { DEFINER | INVOKER }
   | COMMENT 'string'
routine_body:
    Valid SQL procedure
statement or
statements

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’ ：注释信息，可以用来描述存储过程或函数
routine_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

2.2 存储函数

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

CREATE
FUNCTION  func_name([func_parameter])
RETURNS
TYPE
[characteristics...] routine_body

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

[IN|OUT|INOUT]PARAM_NAME TYPE

其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；
param_name表示参数名称；type表示参数类型，该类型可以是MYSQL数据库中的任意类型
RETURNS TYPE语句表示函数返回数据的类型；characteristics：指定存储函数的特性，取值与创建存储过程时相同

3. 变量的使用

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

3.1 定义变量

在存储过程中定义变量

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

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

3. 2 为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值，MYSQL中使用SET语句为变量赋值

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

在存储过程中的SET语句是一般SET语句的扩展版本。
被SET的变量可能是子程序内的变量，或者是全局服务器变量，如系统变量或者用户变量
MYSQL中还可以通过SELECT…INTO为一个或多个变量赋值，如下;

DECLARE NAME  CHAR(50);
DECLARE id DECIMAL(8,2);

SELECT id,NAME INTO  id ,NAME
FROM  t3 WHERE id=2;

4. 定义条件和处理程序

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

4.1 定义条件

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为数值类型错误代码，例如：ERROR1142(42000)中，sqlstate_value的值是42000，
mysql_error_code的值是1142
这个语句指定需要特殊处理条件。他将一个名字和指定的错误条件关联起来。
这个名字随后被用在定义处理程序的DECLARE HANDLER语句中
定义ERROR1148(42000)错误，名称为command_not_allowed。
可以用两种方法定义

//方法一：使用sqlstate_value
DECLARE
command_not_allowed CONDITION FOR
SQLSTATE '42000'

//方法二：使用mysql_error_code
DECLARE
command_not_allowed CONDITION FOR
SQLSTATE 1148

4. 2．定义处理程序

MySQL中可以使用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 '42000'
SET
@info='CAN NOT FIND';

//方法二：捕获mysql_error_code
DECLARE
CONTINUE  HANDLER FOR
<strong>1148</strong>SET
@info='CAN NOT FIND';

//方法三：先定义条件，然后调用
DECLARE
can_not_find CONDITION FOR
1146 ;
DECLARE
CONTINUE  HANDLER FOR
can_not_find SET
@info='CAN NOT FIND';

//方法四：使用SQLWARNING
DECLARE
EXIT HANDLER FOR
SQLWARNING SET
@info='ERROR';

//方法五：使用NOT
FOUND
DECLARE
EXIT HANDLER FOR
NOT  FOUND SET
@info='CAN NOT FIND';

//方法六：使用SQLEXCEPTION
DECLARE
EXIT HANDLER FOR
SQLEXCEPTION SET
@info='ERROR';

上述代码是6种定义处理程序的方法。
第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42000，执行CONTINUE操作，并且输出”CAN NOT FIND”信息。
第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1148，执行CONTINUE操作，并且输出”CAN NOT FIND”信息。
第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1148错误就执行CONTINUE操作。
第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。
第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出”CAN NOT FIND”信息。
第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息