MySQL 存储函数与数据库交互

存储函数中的 SQL 语句

你可以在存储函数中包含 SQL 语句，不过在存储函数中包含 SQL 语句时要非常小心，因为存储函数本身可能会在 SQL 语句中使用。

注意，不能从存储函数中返回结果集。如果试图创建一个包含 SELECT 语句但不包含 INTO 子句的存储函数，将出现错误：

-- 修改分隔符
mysql> delimiter &

-- 创建函数
mysql> create function demo()
   -> return int
   -> begin
   ->   select * from user; -- 不允许从函数中返回结果集
   ->   return 1;
   -> end
   -> &
ERROR 1064 (42000): 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 'return int
begin
 select * from user;
 return 1;
end' at line 2

调用存储函数

只要指定函数名称和参数列表，就可以调用函数。为了展示如何调用存储函数，我们将使用如下存储函数：

CREATE FUNCTION isodd(input_number int) RETURNS int
BEGIN
   -- 声明一个变量
   DECLARE v_isodd INT;

   -- 判断是否为偶数
   IF MOD(input_number,2)=0 THEN
       SET v_isodd=FALSE; -- 偶数
   ELSE
       SET v_isodd=TRUE; -- 奇数
   END IF;

   -- 返回结果
   RETURN v_isodd;
END;

mysql 广告位

从 MySQL 命令行调用存储函数

下面展示了如何在 SET 语句和 IF 语句中调用存储函数，例如：

-- SET 语句调用存储函数
mysql> SET @result=isodd(42);
Query OK, 0 rows affected (0.00 sec)

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

-- SELECT 语句调用存储函数
mysql> select isodd(42);
+-----------+
| isodd(42) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

从存储过程中调用存储函数

下面演示了如何在存储过程中调用存储函数，例如：

CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`(IN `in_val` int)
BEGIN
   -- 声明一个变量
   DECLARE result INT;
   
   -- SET 语句中调用函数
   SET result = isodd(in_val);
   select result;
   
   -- IF 语句中调用函数
   IF (isodd(in_val)) THEN
       SELECT CONCAT(in_val, " is odd") AS isodd;
   ELSE
       SELECT CONCAT(in_val, " is even") AS isodd;
   END IF;
END

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

mysql> call demo(42);
+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

+------------+
| isodd      |
+------------+
| 42 is even |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

从 SQL 语句中调用存储函数

下面示例演示怎样在 SELECT 语句中使用 CASE 语句转换状态信息，然后改进为使用自定义函数替换 CASE 语句。

（1）SELECT 中使用 CASE 语句

SELECT CASE sex
       WHEN 0 THEN '女'
       WHEN 1 THEN '男'
       END AS SEX, COUNT(*) AS SEX_COUNT
FROM `user`
GROUP BY CASE sex
           WHEN 0 THEN '女'
           WHEN 1 THEN '男'
           END

运行 SQL，输出如下：

+-----+-----------+
| SEX | SEX_COUNT |
+-----+-----------+
| 女  |         2 |
| 男  |         4 |
+-----+-----------+
2 rows in set (0.05 sec)

现在想象一下，在我们的应用程序中散布着许多类似的 CASE 语句，以及涉及复杂计算，将导致代码难以理解和维护。每当需要修改 CASE 结构或业务计算时，就必须查找并修改大量 SQL 语句，这将影响到许多不同的模块。

（2）通过一个自定义函数简化上面例子的 SQL 语句，如下：

CREATE FUNCTION cust_status(in_status INT)
   RETURNS VARCHAR(20)
BEGIN
   DECLARE long_status VARCHAR(20);
   IF in_status = 0 THEN
       SET long_status='女';
   ELSEIF in_status = 1 THEN
       SET long_status='男';
   ELSE
       SET long_status='未知';
   END IF;
   RETURN(long_status);
END;

然后，使用上面的 cust_status 函数优化 SQL 语句，如下：

SELECT cust_status(sex), COUNT(*) AS SEX_COUNT
FROM `user`
GROUP BY cust_status(sex)

注意，上面例子由于隐藏了性别计算公式的细节，因此去除了重复 CASE 代码，查询的可读性也大大提高。如果程序员需要了解用于确定性别状态的逻辑，可以打开存储函数查看。