Skip to content

16. MySQL存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合。二者合称为routine。

区别：

  • 函数必须有返回值，而存储过程没有
  • 存储过程的参数可以使用 IN、 OUT、 INOUT 类型，而函数的参数只能是 IN 类型的

创建存储过程或者函数需要 CREATE ROUTINE 权限，修改或者删除存储过程或者函数需要 ALTER ROUTINE 权限，执行存储过程或者函数需要 EXECUTE 权限。

存储过程详细介绍：https://www.runoob.com/w3cnote/mysql-stored-procedure.html。

1. 创建⚓

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
  • 存储过程和函数中不允许执行 LOAD DATA INFILE 语句。
  • 存储过程中的事务开始语句需要替换为START TRANSACTION。
  • 存储过程不能包含 只能在运行时确定返回结果集的语句。
  • 函数不允许返回结果集，存储过程可以。
  • 函数不能包含事务语句。
  • 不允许递归使用函数。
  • 不允许使用USE。
/* 定义存储过程 */
MariaDB [MYISAM_TEST]> delimiter $$
MariaDB [MYISAM_TEST]> CREATE PROCEDURE get_child_count(IN pid INT, OUT count INT) READs SQL DATA BEGIN SELECT COUNT(*) FROM CHILD WHERE PARENT_ID=pid; SELECT FOUND_ROWS() INTO count; END$$
MariaDB [MYISAM_TEST]> delimiter ;

/* 定义函数 */
MariaDB [MYISAM_TEST]> delimiter //
MariaDB [MYISAM_TEST]> CREATE FUNCTION get_date() RETURNS CHAR(10) BEGIN DECLARE RESULT CHAR(10); SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d') INTO RESULT; RETURN RESULT; END //
MariaDB [MYISAM_TEST]> delimiter ;
  • delimiter $$：在创建之前定义结束符，防止在定义时输入;而错误地结束
  • delimiter ;：改回默认的结束符

2. 修改⚓

ALTER {PROCEDURE | FUNCTION} proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

以下是提供子程序使用数据的内在信息的特征值，只是提供给服务器，并没有限制实际的数据使用情况：

  • CONTAINS SQL：表示子程序不包含读或写数据的语句
  • NO SQL：表示子程序不包含 SQL 语句
  • READS SQL DATA：包含读数据的语句，不包含写
  • MODIFIES SQL DATA：包含写数据的语句

SQL SECURITY 特征值：

  • DEFINER：表示以创建者的权限执行存储过程中的语句。
  • INVOKER：表示以调用者的权限执行存储过程中的语句。如果调用者没有执行其中子程序的权限会报错。

3. 调用⚓

存储过程

CALL sp_name([parameter[,...]])
CALL sp_name[()]

函数

SELECT function();
或在其他子句中直接使用

4. 查看⚓

4.1 状态⚓

SHOW {PROCEDURE | FUNCTION} STATUS
    [LIKE 'pattern' | WHERE expr]

/* 举例 */
/* 默认列出当前数据库下所有的存储过程 */
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'get_child_count';
/* WHERE 用于对字段做出限定。 */
SHOW PROCEDURE STATUS WHERE Name LIKE 'get_child_count';

4.2 定义⚓

SHOW CREATE {PROCEDURE | FUNCTION} sp_name;

SHOW CREATE PROCEDURE get_child_count \G;

4.3 信息⚓

通过查看系统表 information_schema.Routines 就可以获得存储过程和函数的信息。

MariaDB [information_schema]> SELECT * FROM ROUTINES WHERE ROUTINE_NAME = 'get_child_count' \G;

5. 删除⚓

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;