[collect]MySQL的存储过程


=Start=

缘由:

工作需要,学习需要

正文:

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

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

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

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

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

一个基本的格式:

DELIMITER //
DROP PROCEDURE IF EXISTS usp_demo;
CREATE PROCEDURE usp_demo(IN param1 INT,...)
BEGIN
    -- Do SQL Operation
END //
DELIMITER ;

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

原因:

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命令调用:

CALL <usp_name>;

DECLARE 声明局部变量:

DECLARE var_name,[var_name...] data_type DEFAULT default_value

如:

DECLARE a, b, c INT DEFAULT 5;

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

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

如:

DELIMITER //
DROP PROCEDURE IF EXISTS usp_demo;
CREATE PROCEDURE usp_demo(OUT num INT)
BEGIN
    DECLARE myvar INT;
    SET myvar = (SELECT id FROM users LIMIT 1);
    SELECT myvar into num;
END //
DELIMITER ;

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call usp_demo(@a);
Query OK, 1 row affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 3938 |
+------+
1 row in set (0.00 sec)

简单例子:

DELIMITER //
DROP PROCEDURE IF EXISTS usp_demo;
CREATE PROCEDURE usp_demo(IN num INT)
BEGIN
    select num;
    set num = 100;
    select num;
END //
DELIMITER ;

mysql> CALL usp_demo(1);
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|  5   |
+------+
1 row in set (0.00 sec)
mysql> set @a = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL usp_demo(@a);
+------+
| num  |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
DELIMITER //
DROP PROCEDURE IF EXISTS usp_demo;
CREATE PROCEDURE usp_demo(OUT num INT)
BEGIN
    set num = 100;
    select num;
END //
DELIMITER ;

mysql> set @a = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL usp_demo(@a);
+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

其它命令:

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

参考:


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

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

SET GLOBAL event_scheduler = 1;

创建语法:

CREATE EVENT [IF NOT EXISTS] <event_name>
ON SCHEDULE <schedule>
DO
<event_body>;

其它命令:

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

参考:


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

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

-- 定义存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS usp_del_user;
CREATE PROCEDURE usp_del_user(IN expire_interval INT, IN delete_per_count INT)
-- expire_interval: the unit is hour
-- delete_per_count: specify the count do every delete operation
BEGIN
    WHILE EXISTS (select 1 from users where ts < DATE_SUB(NOW(), INTERVAL expire_interval HOUR)) DO
        delete from users order by ts limit delete_per_count;
    END WHILE;
END //
DELIMITER ;

-- 定义事件, 调用存储过程
DROP EVENT IF EXISTS del_user;
CREATE EVENT del_user
ON SCHEDULE EVERY 30 MINUTE
DO
CALL usp_del_user(2, 1000)

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

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

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

sudo mysql_upgrade -uroot -p

MySQL 输出时的\G

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

mysql> show events\G;
*************************** 1. row ***************************
                  Db: testsp
                Name: delete_user
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 5
      Interval field: SECOND
              Starts: 2015-03-26 17:26:27
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

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

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

=END=


发表回复

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