MySQL 存储过程

MySQL 存储函数语法详解

创建函数的完整语法如下：

CREATE
   [DEFINER = user]
   FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
   RETURNS type
   [characteristic ...] routine_body

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

说明：

  • CREATE FUNCTION  创建存储函数的关键字

  • [DEFINER = user]  可选参数，用来指定该存储函数是由谁创建的，需要该用户具有 CREATE PROCEDURE 需要 CREATE ROUTINE 权限。

  • [IF NOT EXISTS]  可选参数，如果当前名称的存储函数已经存在，则不创建。如果不存在，则创建该存储函数。

  • sp_name  存储函数名称

  • func_parameter  可选参数，存储函数允许定义一个空参数列表，但是不能省略括号 “()”，你可以指定多个 func_parameter，每一个 func_parameter 表示一个参数，参数包含下面几个数据：

    • param_name  表示参数名称，必须是一个合法的标识符

    • type  表示参数的数据类型，必须是一个有效的 MySQL 数据类型

  • RETURNS type  该子句是必需的，它定义函数将返回的数据类型。

  • characteristic  可选参数，用来为当前存储函数指定一些特殊特性，取值如下：

    • COMMENT 'string'   用来为存储函数添加注释，以提供有关存储函数的额外说明或描述。注释可以用于文档化存储函数的用途、参数、返回值等。

    • LANGUAGE SQL   用于指定存储函数的语言。LANGUAGE SQL 表示存储函数使用 SQL 语言编写。这是 MySQL 中最常见和最常用的存储函数语言。使用 LANGUAGE SQL 选项，可以在存储函数中使用标准的 SQL 语句和语法来编写逻辑。这使得存储函数的编写和维护更加简单和直观。除了 LANGUAGE SQL，MySQL 还支持其他存储函数语言，如LANGUAGE Java、LANGUAGE C 等。这些语言允许开发人员使用 Java、C 等编程语言来编写存储函数的逻辑。但是，绝大多数情况下，使用 LANGUAGE SQL 已经足够满足大多数需求。

    • [NOT] DETERMINISTIC   用于指定存储函数是否是确定性的修饰符。确定性存储函数是指在相同的输入条件下，每次执行都会产生相同的结果。使用 DETERMINISTIC 修饰符可以提供一些性能优化，因为 MySQL 可以缓存确定性存储函数的结果，避免重复计算。这对于频繁调用的存储函数来说尤为重要。

      注意：MySQL 不会严格检查存储函数是否真的是确定性的，因此在使用 DETERMINISTIC 修饰符时需要确保存储函数的逻辑确实是确定性的。如果存储函数的逻辑不是确定性的，使用 DETERMINISTIC 修饰符可能会导致错误的结果。

      MySQL 还提供了 NOT DETERMINISTIC 修饰符，用于指定存储函数是非确定性的。非确定性存储函数在相同的输入条件下可能会产生不同的结果。如果不显式指定存储函数的确定性，MySQL 默认将其视为非确定性。

    • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

    • SQL SECURITY { DEFINER | INVOKER }   指定存储函数的执行权限。SQL SECURITY 选项有以下两个可选值：

    • DEFINER  表示存储函数的执行权限与定义者（创建存储函数的用户）的权限相同。这是默认的选项。

    • INVOKER  表示存储函数的执行权限与调用者的权限相同。

  • routine_body  存储函数的正文部分，一些有效的 SQL 常规语句，通常情况是一个代码块 BENG ...  END。

默认情况下，存储函数与默认数据库相关联。要将存储函数与指定数据库相关联，请在创建存储函数时将名称指定为 db_name.sp_name（db_name 是数据库名）。MySQL 会自动授予存储函数创建者 ALTER ROUTINE 和 EXECUTE 权限。可以通过禁用 automatic_sp_privileges 系统变量来改变这种行为。

DEFINER 和 SQL SECURITY 子句指定了在存储函数执行时检查访问权限时使用的安全上下文，本节稍后将对此进行介绍。

如果存储函数名称与内置 SQL 函数的名称相同，则会出现语法错误，除非在定义存储函数或稍后调用存储函数时在名称和后面的括号之间使用空格。因此，请避免将现有 SQL 函数的名称用于您自己的存储函数。

IGNORE_SPACE SQL 模式适用于内置函数，不适用于存储例程。

RETURN 语句

RETURN 语句用来终止存储函数的执行，并将指定值返回给调用程序。存储函数中的 RETURN 语句越多越好。下面例子展示了一个包含多个 RETURN 语句的存储函数：

CREATE DEFINER=`root`@`localhost` FUNCTION `demo`(in_status CHAR(1))
-- 指定返回类型
RETURNS varchar(20)
BEGIN
   -- IF 语句
   IF in_status = 'O' THEN
       -- 返回字符串
       RETURN('Overdue');
   ELSEIF in_status = 'U' THEN
       RETURN('Up to date');
   ELSEIF in_status = 'N' THEN
       RETURN('New');
   END IF;
END

调用存储过程，输出如下：

mysql> select demo('N');
+-----------+
| demo('N') |
+-----------+
| New       |
+-----------+
1 row in set (0.05 sec)

不过，通常认为好的做法是只包含一条 RETURN 语句（单进单出），并在条件语句中使用变量赋值来改变返回值。使用单个 RETURN 语句除了可以使程序流程更易于理解外，还可以避免 RETURN 语句都不执行的情况。如果我们调用 demo 存储函数时，传递的参数不是 “0”、“U” 或 “N”，则会抛出错误：

mysql> SELECT demo('M');
ERROR 1321 (2F005): FUNCTION demo ended without RETURN

这是因为参数 “M” 不会匹配任何 IF 语句，导致 demo 存储函数不能执行任何 RETURN 语句。我们可以通过将返回值通过变量保存，在存储函数最后面通过 RETURN 语句返回该变量的值：

CREATE DEFINER=`root`@`localhost` FUNCTION `demo`(in_status CHAR(1))
-- 指定返回类型
RETURNS varchar(20)
BEGIN
   -- 声明变量，存储返回结果
   DECLARE result VARCHAR(20);
   
   -- IF 语句
   IF in_status = 'O' THEN
       SET result = 'Overdue';
   ELSEIF in_status = 'U' THEN
       SET result = 'Up to date';
   ELSEIF in_status = 'N' THEN
       SET result = 'New';
   END IF;
   
   RETURN result;
END

再次调用存储过程，输出如下：

mysql> SELECT demo('M');
+-----------+
| demo('M') |
+-----------+
| NULL      |
+-----------+
1 row in set (0.00 sec)

注意：在存储函数的最后一行可执行代码中只包含一条 RETURN 语句是一种好的做法。避免任何可能允许存储函数在不调用 RETURN 语句的情况下终止的流程控制。

存储函数的参数

存储函数可以包含多个参数，但这些参数只能是 IN 类型参数。也就是说，在定义参数时，既不能指定 OUT 子句，也不能指定 INOUT 子句（甚至不能指定 IN 子句）。因此，下面定义的函数将无法编译：

-- 修改分隔符
mysql> delimiter &

-- 创建存储函数
mysql> CREATE FUNCTION f_inout(INOUT x INT) RETURNS INT
   -> BEGIN
   ->     SET x=1;
   ->     RETURN(1);
   -> END
   -> &
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INOUT x INT) RETURNS INT
BEGIN
   SET x=1;
   RETURN(1);
END' at line 1

注意：存储函数不能包含 OUT 或 INOUT 类型参数。如果需要从存储函数中返回多个变量，那么存储过程可能比函数更合适。

存储函数示例

示例一：计算输入参数和并返回

该示例是一个简单的函数示例，计算输入参数的和，然后返回。代码如下：

-- 设置分隔符为 &，当然可以设置为其他符号，如：//
mysql> delimiter &

-- 创建一个简单函数
mysql> CREATE FUNCTION calc_sum(in_val1 INT, in_val2 INT)
   -> RETURNS INT
   -> BEGIN
   ->     DECLARE result INT;
   ->     SET result=in_val1+in_val2;
   ->     RETURN result;
   -> END
   -> &
Query OK, 0 rows affected (0.01 sec)

-- 将分隔符设置为默认值 ;
mysql> DELIMITER ;

-- 调用函数
mysql> SELECT calc_sum(10,20);
+-----------------+
| calc_sum(10,20) |
+-----------------+
|              30 |
+-----------------+
1 row in set (0.06 sec)

示例二：DEFINER 关键字的使用

下面使用 DEFINER 关键字指定当前存储函数的拥有者为 admin@localhost 用户：

CREATE DEFINER=`root`@`localhost` FUNCTION `calc_sum`(in_val1 INT, in_val2 INT)
RETURNS int(11)
BEGIN
   -- 声明一个变量
   DECLARE result INT;
   -- 计算和，并赋值给 result 变量
   SET result=in_val1+in_val2;
   -- 返回结果
   RETURN result;
END

示例三：SQL SECURITY INVOKER 关键字的使用

下面示例假设存储函数是用 SQL SECURITY INVOKER 特性定义的：

CREATE DEFINER=`root`@`localhost` FUNCTION `calc_sum`(in_val1 INT, in_val2 INT)
   RETURNS int(11)
   SQL SECURITY INVOKER
BEGIN
   -- 声明一个变量
   DECLARE result INT;
   -- 计算和，并赋值给 result 变量
   SET result=in_val1+in_val2;
   -- 返回结果
   RETURN result;
END

注意，存储函数的 DEFINER 仍然是 'admin'@'localhost'，但在这种情况下，它以调用用户的权限执行（因为使用了 SQL SECURITY INVOKER 语句）。因此，存储过程成功或失败取决于调用者是否拥有 EXECUTE 权限。

上一章：MySQL 存储函数简介
说说我的看法
全部评论（）
没有评论
关于
本网站专注于 Java、数据库（MySQL、Oracle）、Linux、软件架构及大数据等多领域技术知识分享。涵盖丰富的原创与精选技术文章，助力技术传播与交流。无论是技术新手渴望入门，还是资深开发者寻求进阶，这里都能为您提供深度见解与实用经验，让复杂编码变得轻松易懂，携手共赴技术提升新高度。如有侵权，请来信告知：hxstrive@outlook.com
链接
其他应用
公众号
©2018-2020 hxstrive.com 版权所有 ICP证：蜀ICP备14031461号-2   川公网安备51010802032098