[collect]MySQL的存储过程

本文最后更新于2016年11月30日,已超过 1 年没有更新,如果文章内容失效,还请反馈给我,谢谢!

=Start=

缘由:

工作需要,学习需要

正文:

MySQL 存储过程(Stored Procedure) 是通过给定的语法格式编写自定义的数据库API,包含一系列SQL语句的集合,完成一个复杂的功能。这个API可以反复使用,执行效率也很高。

功能上有点像function,但是和function还是有些区别。

存储过程的参数有三种类型:

  • IN:输入参数。在调用存储过程时指定,默认未指定类型时则是此类型。
  • OUT:输出参数。在存储过程里可以被改变,并且可返回
  • INOUT:输入输出参数。IN 和 OUT 的结合。

(关于IN和OUT的区别,个人理解IN比较像C/C++中的值传递,OUT像引用传递)

一个基本的格式:

一般会在存储过程开始时将结束符改为其它字符,在结束时改回原来的。

原因:

Because we want to pass the stored procedure to the server as a whole rather than letting mysql tool to interpret each statement at a time. Following the END keyword, we use the delimiter // to indicate the end of the stored procedure.

(摘自:Getting Started with MySQL Stored Procedures)

然后定义存储过程, 参数可有可无, 就和编码写函数一样, 定义几个参数, 调用时就写几个参数.

然后以BEGIN开始, 以END结束. 里面是一系列的SQL语句, 并且还提供了结构控制语句, 比如 IFWHILECASE等等, 可以完成复杂的操作.

另外, 定义存储过程, 以usp_前缀是区别系统存储过程和用户自定义存储过程的最佳实践.

定义存储过程后, 通过CALL命令调用:

DECLARE 声明局部变量:

如:

SET 对已声明的变量赋值或重新赋值.

SELECT 显示变量; SELECT var into out_var 将变量值写入OUT参数

如:

简单例子:

其它命令:

  • SHOW PROCEDURE STATUS 列出所有存储过程
  • SHOW CREATE PROCEDURE <sp_name> 查看一个已存在的存储过程的信息

参考:


事件(Event), 可以定义一些任务调度.

首先需要开启事件调度的支持:

创建语法:

其它命令:

  • SHOW EVENTS 列出所有事件
  • SHOW CREATE EVENT <event_name> 查看一个已存在的事件的信息

参考:


一个存储过程配合事件的例子:

使用事件,每半小时调用存储过程,存储过程是一个WHILE循环,一直删除2个小时之前的数据,每次删除1000条。


在本地测试创建存储过程的时候,遇到这个报错:

ERROR 1548 (HY000): Cannot load from mysql.proc. The table is probably corrupted

应该是mysql升级后有些表格的信息需要相应升级,修复:


MySQL 输出时的\G

有时使用SHOW输出一些信息表格时,限制于屏幕的宽度,表格会比较混乱。加上\G后显示效果如:

这种左右的结构看上去就会比较美观。

原文链接https://blog.tankywoo.com/2015/04/01/mysql-stored-procedure.html

=END=

声明: 除非注明,ixyzero.com文章均为原创,转载请以链接形式标明本文地址,谢谢!
https://ixyzero.com/blog/archives/2997.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注