mysql 存储过程官方api_MySQL 存储过程

最新推荐文章于 2021-01-27 11:54:00 发布

EDopamine

最新推荐文章于 2021-01-27 11:54:00 发布

阅读量265

点赞数

文章标签： mysql 存储过程官方api

本文链接：https://blog.youkuaiyun.com/weixin_36132709/article/details/113319215

版权

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.

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

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

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

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

CALL ;

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 查看一个已存在的存储过程的信息

参考:

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

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

SET GLOBAL event_scheduler = 1;

CREATE EVENT [IF NOT EXISTS]

ON SCHEDULE

;

其它命令:

SHOW EVENTS 列出所有事件

SHOW CREATE EVENT 查看一个已存在的事件的信息

参考:

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

使用事件, 每半小时调用存储过程, 存储过程是一个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

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)

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