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”信息

文章来源: mysql存储过程和函数总结

人吐槽 人点赞

猜你喜欢

发表评论

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:mysql存储过程和函数总结