数据库MySQL

MySQL存储过程及函数核心知识点

MySQL, 数据库

本文记录了MySQL存储过程的一些核心知识点，详细内容请参考官方文档。文中的MySQL版本为5.7。

语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
func_parameter:
    param_name type
type:
    Any valid MySQL data type
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
routine_body:
    Valid SQL routine statement
  1. 存储过程可以加 db_name限定数据库，不加就使用默认数据库
  2. 默认情况下，MYSQL给存储过程创建者ALTER ROUTINE和EXECUTE权限。

proc_parameter

默认IN，可以选择OUT，INOUT

proc_parameter

只支持IN

characteristic

  • COMMENT用于写备注
  • LANGUAGE这个server被忽略，只是为了符合SQL标准
  • DETERMINISTIC和NOT DETERMINISTIC。这个只是创建者自己定义的，MYSQL不会做任何检查。但是如果把NOT DETEMINISTIC定义为DETERMINISTIC，可能导致优化器做出错误的执行计划。相反，把DETEMINISTIC定义为NOT DETERMINISTIC可能让一些可用的优化措施无法使用。如果使用binary logging，需要参考Binary Logging of Stored Programs.
  • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }这组被server忽略
  • SQL SECURITY定义谁可以调用。DEFINER表示由DEFINER属性所指定的用户的权限来执行，INVOKER表示以调用这个存储过程的用户的权限来执行。默认DEFINER，且值为CURRENT_USER。如果用户没有过程中需要的权限，那么INVOKER类型过程的就无法调用。

routine_body

可以执行Compound-Statement.以及DDL语句，同时存储过程支持事务。

Compiund Statement

BEGIN END语法

语句块，可嵌套，可结合Lable使用。

Label语法

可用于标记BEGIN，LOOP，REPEAT以及WHILE语句。可以用ITERATE和LEAVE指令控制流程。

DECLARE语法

声明变量，条件处理或者游标。限制如下：

  1. 只能在BEGIN语句中，且必须在最前面
  2. 必须按照变量，游标，条件处理的顺序声明。

变量

所用DECLARE声明，赋值可以使用SET语法，SELECT ... INTO var_list 或者 FETCH ... INTO var_list

定义格式：

DECLARE var_name [, var_name] ... type [DEFAULT value]

控制流程

和C语言的类比

MYSQL C 备注
CASE switch
IF if
LOOP while(1){} 用ITERATE模拟continue，LEAVE模拟break
REPATE do…while 用ITERATE模拟continue，LEAVE模拟break
WHILE while 用ITERATE模拟continue，LEAVE模拟break

游标

游标使用的流程遵循：定义，OPEN，FETCH，CLOSE。语法如下

DECLARE cursor_name CURSOR FOR select_statement
OPEN cursor_name
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name

条件处理

程序执行过程中可能出现一些需要特殊处理的情况，比如继续执行还是推出程序？可以为一个条件定义处理器，条件也可以被命名。

  1. 命名条件使用DECLARE … CONDITION 语法，此步骤可选。
  2. 定义处理器使用DECLARE … HANDLER 语法
  3. 自己抛出一个条件使用SIGNAL 语法，定义条件处理器中继续抛出使用RESIGNAL 语法
  4. 获取错误内容使用 GET DIAGNOSTICS 语法

例子：

-- 命名
DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';
-- 定义处理器以及使用RESINGAL
 DECLARE CONTINUE HANDLER FOR division_by_zero 
 RESIGNAL SET MESSAGE_TEXT = 'Division by zero / Denominator cannot be zero';
 
-- GET DIAGNOSTICS语法
DROP TABLE test.no_such_table;
-- ERROR 1051 (42S02): Unknown table 'test.no_such_table'
GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1, @p2;
+-------+------------------------------------+
| @p1   | @p2                                |
+-------+------------------------------------+
| 42S02 | Unknown table 'test.no_such_table' |
+-------+------------------------------------+

常用的处理器：

常见问题

有许多常见问题是因为MySQL本身的一些限制，可以参考：Restrictions on Stored Programs。下面记录一些常见的或者我碰到的问题，欢迎补充。

FETCH拿不到任何记录

如果定义的变量名和SELECT的字段名一样，那么可能出现问题，这是MySQL的一个bug，解决方案是不要使变量名和字段名一样。

ERROR：Cursor declaration after handler declaration

DECLARATION定义顺序错误，类似的还有Variable or condition declaration after cursor or handler declaration错误

参考资料

分享到