• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL存储过程和存储函数(一)

MySQL 彭东稳 8年前 (2016-04-09) 22529次浏览 已收录 0个评论

一、存储过程与函数介绍

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

存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。

MySQL存储过程和函数有什么区别?在本质上他们都是存储程序,函数只能通过return语句返回单个值或者表对象;而存储过程不允许执行return,但是可以通过out参数返回多个值。函数限制比较多,不能用临时表,只能用表变量,还有一个些函数都不可用等等;而存储过程的限制相对就比较少。函数可以嵌入在SQL语句中使用,可以在SELECT语句中作为查询语句的一个部分使用,与MySQL内置的MAX()、MIN()等原生使用方法类似;而存储过程一般是作为一个独立的部分来执行,使用CALL关键字调用。

存储过程的好处:

1. 由于数据库执行动作时,是先编译后执行的,然而存储过程是一个编译过的代码块,所以执行效率要比T-SQL语句高。

2. 一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句,所以也能降低网络的通信量,提高通信速率。

3. 通过存储过程能够使没有权限的用户在控制之下间接地存取数据库,从而确保数据的安全。

4. 可在生产环境下执行,修复BUG。

二、存储过程与函数创建语法

创建存储过程和存储函数,基本语法格式如下:

其中“CREATE PROCEDURE”是用来创建存储函数或存储函数的关键字。sp_name为存储过程的名称;proc_parameter为指定存储过程的参数列表,可省略,列表形式如:[ IN | OUT | INOUT ] param_name type。其中,IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型,该类型可以是MySQL数据库中的任意类型。另外如果你传入的参数中文的,那么还可以在“[ IN | OUT | INOUT ] param_name type”加入字符集,如“IN id INT character set utf8”。

其中“CREATE FUNCTION”为用来创建存储函数的关键字。fun_name表示存储函数的名称;fun_parameter为存储过程的参数列表,参数列表形式如下:[ IN | OUT | INOUT ] param_name type。其中,IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型,该类型可以是MySQL数据库中的任意类型。外如果你传入的参数中文的,那么还可以在“[ IN | OUT | INOUT ] param_name type”加入字符集,如“IN id INT character set utf8”。RETURNS type语句表示函数返回数据的类型。

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,并且用户指定为创建者。所以,其他用户是无法使用这个存储过程或函数的。

definer:在执行存储过程前验证definer对应的用户,如:’root’@’192.168.2.%’是否存在,以及是否具有执行存储过程的权限,若没有则报错。简单来说就是执行这个存储过程的用户不管是谁也不管有没有权限执行,只要’root’@’192.168.2.%’这个用户存在且有执行的权限即可。

invoker:在执行存储过程时判断invoker,即调用该存储过程的用户是否有相应权限,若没有则报错。简单来说也就是根据执行用户本身权限为主(需要EXECUTE, ALTER ROUTINE ON FUNCTION权限),而忽略definer定义的用户。使用definer时一次只能定义一个用户,当有多个用户访问需求时就只能使用invoker了。

COMMENT ‘string’

注释信息,可以用来描述存储过程或函数。

routine_body

是用来写SQL代码的内容,可以用BEGIN…END来表示SQL代码的开始和结束。

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

三、创建存储过程和函数

自行创建一个员工信息表info,里面记录着员工姓名,年龄,地址以及工资,实验使用。

下面我们定义一个简单的存储过程实例:

其中,“delimiter $”语句的作用是将MySQL的结束符设置为“$”符,因为MySQL默认的语句结束符为分号“;”,为了避免与存储过程中SQL语句结束符相冲突,所以就是用delimiter改变存储过程的结束符,并以“END $”结束存储过程,其中存储过程定义完毕之后再使用“delimiter ;”恢复默认结束符。

然后开始定义存储过程,此存储过程名为test,使用CREATE PROCEDURE test()语句定义。此存储过程没有参数,但是后面的()仍然需要。BEGIN和END语句用来限定存储过程体,过程本身仅是一个简单的SELECT语句。每次调用这个存储过程的时候都会执行SELECT语句查看表。当然存储过程也可以是很多语句的复杂的组合,就好像这个例子刚开始给出的那个语句一样,其本身也可以调用其他的函数,来组成更加复杂的操作。

下面再创建一个可以传参的存储过程。

创建一个param存储过程,定义一个参数n,类型为int。然后执行SQL语句的时候使用参数n作为条件,最后调用存储过程时输入int类型的参数值。

下面创建一个存储函数,名称为fun,参数定义为空,该函数返回SELECT语句的查询结果,数值为字符串型,代码如下:

函数的使用跟存储过程就不太一样了,使用函数就跟我们使用系统函数一样,使用SELECT调用,或者在SQL语句中使用。

如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值,返回值将被强制为恰当的类型。比如,如果一个函数返回一个ENUM或SET类型,但是RETURN语句返回一个整数,对于SET成员集的相应的ENUM成员,从函数返回的值是字符串。

四、操作存储过程

关于存储过程和存储函数在基本管理上都是差不多的,只是关键字不同而已。如果是存储过程就使用PROCEDURE关键字,如果是存储函数就使用FUNCTION关键字。不过在调用存储过程和函数方面不太一样,存储过程使用CALL关键字,函数就跟系统函数一样使用即可。

  • 查看存储过程

存储过程的默认安全类型是DEFINER,并且Definer的用户为创建这个存储过程的用户。

另外在mysql.proc中也有存储过程的信息。

  • 查看存储过程创建过程

  • 调用存储过程

  • 删除存储过程

  • 修改存储过程权限

方法一:修改存储过程的DEFINER

也可以在创建存储过程的时候给定definer=’test’@’%’,如下:

此时这个用户会得到EXECUTE, ALTER ROUTINE ON FUNCTION权限,这个时候由于是definer模式,所以如果你的’test’@’%’用户没有执行这个存储过程的权限或者此用户不存在的话,其他用户执行这个存储过程就会报错。信息如下:

方法二:修改SQL SECURITY

SQL SECURITY的值决定了调用存储过程的方式,取值definer或者invoker(默认是definer)。关于definer或invoker区别上面已经介绍了。

修改语法:

Tips:在编写存储过程或存储函数时一定注意 space 和 tab 的使用,缩进的格式不对会导致存储过程或函数语法报错,此时还特别难发现和调试。

五、变量的使用

在编写存储过程中,可以定义变量,变量可以在子程序中声明并使用,这些变量的作用范围是在BEGIN…END程序中。

  • 定义变量

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

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

实例:定义名称为myparam的变量,类型为INT类型,默认值为100,代码如下。

  • 输出变量

在MySQL中输出变量的方式为select @@log_bin;,但在存储过程中输出变量的方式为SELECT var_name;即可,具体代码如下:

  • 变量赋值

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

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

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现,这允许SET a=x,b=y,…这样的扩展语法。其中不同的变量类型(局域声明变量即全局变量)可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。下面声明三个变量,然后使用SET为变量赋值。代码如下:

MySQL中可以通过SELECT … INTO为一个或多个变量赋值,语法如下:

代码如下:

  • 定义条件和处理程序

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

六、需要注意的问题

需要注意的是,在编写存储过程和函数时建议明确指定上面characteristic部分的状态,特别是存在复制的环境中,如果创建函数不明确指定这些状态会报错,从一个非复制环境将带函数的数据库迁移到复制环境的机器上,如果没有明确指定DETERMINISTIC,NO SQL,or READS SQL DATA该三个状态也会报错。

报错信息:Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

这是因为CREATE PROCEDURE,CREATE FUNCTION,ALTER PROCEDURE,ALTER FUNCTION,CALL,DROP PROCEDURE,DROP FUNCTION等语句都会被写进二进制日志,然后在从服务器上执行。但是,一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的,在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同,从服务器不同于主服务器的情况。为了解决这个问题,MySQL强制要求:在主服务器上,除非子程序被声明为确定性的或者不更改数据,否则创建或者替换子程序将被拒绝。这意味着当创建一个子程序的时候,必须要么声明它是确定性的,要么它不改变数据。

声明方式有两种:

第一种:声明是否是确定性的

DETERMINISTIC和NOT DETERMINISTIC指出一个子程序是否对给定的输入总是产生同样的结果。如果没有给定任一特征,默认是NOT DETERMINISTIC,所以必须明确指定DETERMINISTIC来声明一个子程序是确定性的。

这里要说明的是:使用NOW() 函数(或它的同义)或者RAND() 函数不会使一个子程序变成非确定性的。对NOW()而言,二进制日志包括时间戳并会被正确的执行。RAND()只要在一个子程序内被调用一次也可以被正确的复制。所以,可以认为时间戳和随机数种子是子程序的确定性输入,它们在主服务器和从服务器上是一样的。

第二种:声明是否会改变数据

CONTAINS SQL, NO SQL,READS SQL DATA,MODIFIES SQL用来指出子程序是读还是写数据的。无论NO SQL还是READS SQL DATA都指出子程序没有改变数据,但是必须明确地指定其中一个。因为没有任何指定,默认的指定是CONTAINS SQL。

默认情况下,如果允许CREATE PROCEDURE或CREATE FUNCTION语句被接受,就必须明确地指定DETERMINISTIC或NO SQL与READS SQL DATA中的一个,否则就会产生1418错误。

创建存储过程或存储函数时的声明语法如下:

除了使用上面这种声明的方式解决这个问题外,其实也可以将系统log_bin_trust_function_creators参数设置为ON,这样一来开启了binlog的MySQL Server便可以随意创建function。这里存在潜在的数据安全问题,除非明确的知道创建的function在master和slave上的行为完全一致。设置该参数可以用动态的方式或者指定该参数来启动数据库服务器或者修改配置文件后重启服务器。需注意的是,动态设置的方式会在服务器重启后失效。

另外如果是在master上创建函数,想通过主从复制的方式将函数复制到slave上则也需在开启了binlog的slave中设置上述变量的值为ON(变量的设置不会从master复制到slave上,这点需要注意),否则主从复制会报错。

<参考>

https://www.cnblogs.com/chenmh/p/5201473.html

https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (0)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!