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

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

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

一、流程控制的使用

在存储过程中可以定义流程控制语句,流程控制语句用来根据条件控制语句的执行,MySQL中的用来构造控制流程的语句有:IF语句、CASE语句、LOOP语句、WHILE语句、LEAVE语句、ITERATE语句和REPEAT语句。每个流程中可能包含一个单独语句,或者是使用BEGIN…END构造的复合语句,构造可以被嵌套。下面介绍这些流程控制语句

  • IF语句

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

IF实现了一个基本的条件构造,如果condition求值为真,响应的SQL语句列表被执行;如果没有condition匹配,则ELSE字句里的语句列表被执行。Statement可以包括一个或多个语句。如果非常熟悉Shell变量应该很好理解这些语句,另外MySQL中还有一个IF()函数,它不同于这里描述的IF语句。

示例

该示例中,create procedure pif(n int,j char(1))语句怎么使用在创建函数中有说明,IF主要是判断j参数的值,如果为“gao”那么执行第一条SQL语句,如果j的值不等“gao”那么就执行第二条SQL语句,IF语句都需要使用END IF来结束。

调用存储过程pif并输入参数n和j的值。

  • CASE语句

CASE是另一个进行条件判断的语句,该语句有2中语句格式,第1种格式如下:

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

示例

使用CASE判断当val值为1时,输出字符串“val is 1”;当val值为2时,输出字符串“val is 2”;否则输出字符串“val is not 1 or 2”。

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

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

示例

当val值为空,输出字符串“val is NULL”;当val值小于0时,输出字符串“val is less than 0”;当val值大于0时,输出字符串“val is greater than 0”;否则输出字符串“val is 0”。

  • LOOP语句

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

其中,loop_label表示LOOP语句的标注名称,该参数可以省略;statement参数表示需要循环执行的语句。

示例

  • LEAVE语句

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

其中,label参数表示循环的标志,LEAVE和BEGIN….END或循环一起使用。使用LEAVE语句退出循环,代码如下:

该循环执行count加1的操作,当count的值等于50时,使用LEAVE语句跳出循环。

  • ITERATE语句

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

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

示例

var等于0,如果var的值小于10时,重复执行var加1操作;当var大于等于10并且小于20时,打印消息“var is between 10 and 20”;当var大于20时,退出循环。

  • REPEAT语句

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

示例:id值小于等于0之前,将重复执行循环过程

如果写成 until 0 的话,那么就是一直循环执行。

  • WHILE语句

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

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

示例:使用WHILE计算1+n的和

二、游标的使用

查询语句可能返回多条记录,如果数据量非常大,需要在存储过程和存储函数中使用游标来逐条读取查询结果集中的记录。在存储过程中可以使用游标对结果集进行循环处理,应用程序可以根据需要滚动或浏览其中的数据。游标必须在声明处理程序之前被声明,并且变量和条件还必须在声明游标或处理程序之前被声明。

  • 声明游标

MySQL中使用DECLARE关键字来声明游标,其语法的基本形式如下:

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

声明名称为cursor_first的游标,代码如下:

  • 打开游标

  • 读取游标

这个语句用指定的打开游标读取下一行,并且前进游标指针。其中,cursor_name参数表示游标的名称;var_name参数表示将游标中的SELECT语句查询出来的信息存入该参数中,var_name必须在声明游标之前就定义好。

使用名称为cursor_first的游标,将查询出来的数据存入one_name和two_name这两个变量中,代码如下:

  • 关闭游标

这个语句关闭先前打开的游标,如果未被明确地关闭,游标在它被声明的复合语句的末尾被关闭。

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

示例1:把testdb.info中ID字段的值赋给游标mycursor,然后把mycursor读取到变量a上,最后使用LOOP循环显示a变量

三、调用存储过程

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

CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程,其中procedure_name为存储过程的名称,[parameter]为存储过程的参数,可选。下面顶一个countproc的存储过程,然后调用这个存储过程,代码执行如下:

调用存储过程

查看返回结果

该存储过程返回了info表中工资高于5000的人数,把人数返回值存储在num变量中,使用SELECT查看,返回结果为3.

四、查看存储过程和存储函数

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

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

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

这个语句时一个MySQL的扩展,类似于SHOW CREATE TABLE,它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数;LIKE语句表示匹配存储过程或函数的名称。SHOW CREATE语句示例,代码如下:

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

其中,ROUTINE_NAME字段中存储的是存储过程和函数的名称;sp_name参数表示存储过程或函数的名称。从Routines表中查询名称为var的存储函数的信息,代码如下:

在information_schema数据库下的Routines表中,存储所有存储过程和函数的定义。使用SELECT语句查询Routines表中的存储过程和函数的定义时,一定要使用ROUTINE_NAME字段指定存储过程或函数的名称。否则,将查询出所有的存储过程或函数的定义。如果有存储过程和存储函数名称相同,则需要同时指定ROUTINE_TYPE字段表明查询的是哪种类型的存储程序。

五、修改存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性,语法如下:

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

CONTAINS SQL – 表示子程序包含SQL语句,但不包含读或写数据的语句。

NO SQL – 表示子程序中不包含SQL语句。

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

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

SQL SECURITY { DEFINER | INVOKER } – 指明谁有权限来执行。

DEFINER – 表示只有定义者自己才能够执行。

INVOKER – 表示调用者可以执行。

COMMENT ‘string’ – 表示注释信息。

下面修改存储过程

存储过程中的代码可以修改吗?MySQL目前还不支持对已存在的存储过程代码的更改。如果必须要更改存储过程,必须使用DROP语句删除之后,再重新写代码。

六、使用实例

不知道你的公司中是否有Json处理的需求,在MySQL 5.7虽然开始支持原生Json格式,但很多企业可能还是低于这个版本的。下面这个函数就是一个接受一个Json对象和Key,然后返回一个value的小程序。这样程序就可以在SQL语句中直接使用。

使用方式如下:

也支持多级嵌套:

那么就可以直接在SQL中使用了,如“SELECT  GetJSONValue(field, ‘key’) FROM DB.TABLE”。


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

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