当前位置： 首页 > article >正文

【MySQL】存储函数

article 2025/1/30 21:19:56

存储函数

根据调用方式的不同，可以把存储程序分为存储例程、触发器和事件这几种类型，存储例程又可以被细分为存储函数和存储过程

存储程序
├──存储例程
│	├──存储函数
│	└──存储过程
├──触发器
└──事件

用户自定义变量

定义变量

set @a = 1;

查看变量的值

select @a;

把一个常量赋值给变量

set @a = '哈哈哈';

把一个变量赋值给变量

set @b = @a;

还可以将某个查询的结果赋值给变量，前提是这个变量最多包含一行一列，如果结果集为空集，则变量值为null

set @a = (select m1 from t1 limit 1);
select m1 from t1 limit 1 into @b;

当结果集包含多个列时，不能用set，只能用into子句来完成这个功能

select m1,n1 from t1 limit 1 into @a,@b;

语句结束分隔符

MySQL客户端检测输入结束用的符号和分隔各个语句的符号是一样的，其实我们也可以用delimiter命令来自定义MySQL的检测语句输入结束的符号，也就是所谓的语句结束分隔符，比如这样：

mysql> delimiter $
mysql> SELECT * FROM t1 LIMIT 1;
    -> SELECT * FROM t2 LIMIT 1;
    -> SELECT * FROM t3 LIMIT 1;
    -> $

delimiter $命令意味着修改语句结束分隔符为$，也就是说之后MySQL客户端检测用户语句输入结束的符号为$。上边例子中我们虽然连续输入了3个以分号;结尾的查询语句并且按了回车键，但是输入的内容并没有被提交，直到敲下$符号并回车，MySQL客户端才会将我们输入的内容提交到服务器，此时我们输入的内容里已经包含了3个独立的查询语句了，所以返回了3个结果集。

我们也可以将语句结束分隔符重新定义为$以外的其他包含单个或多个字符的字符串，比方说这样：

mysql> delimiter EOF
mysql> SELECT * FROM t1 LIMIT 1;
    -> SELECT * FROM t2 LIMIT 1;
    -> SELECT * FROM t3 LIMIT 1;
    -> EOF

改回我们常用的分号

mysql> delimiter ;

存储函数

创建存储函数

存储函数其实就是一种函数，只不过在这个函数里可以执行MySQL的语句而已。

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容
END

定义一个存储函数需要指定函数名称、参数列表、返回值类型以及函数体内容。如果该函数不需要参数，那参数列表可以被省略，函数体内容可以包括一条或多条语句，每条语句都要以分号;结尾。

例：

mysql> delimiter $
mysql> CREATE FUNCTION avg_score(s VARCHAR(100))
    -> RETURNS DOUBLE
    -> BEGIN
    ->     RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
    -> END $
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

我们定义了一个名叫avg_score的函数，它接收一个VARCHAR(100)类型的参数，声明的返回值类型是DOUBLE，需要注意的是，我们在RETURN语句后边写了一个SELECT语句，表明这个函数的返回结果就是根据这个查询语句产生的，也就是返回了指定科目的平均成绩。

存储函数的调用

mysql> SELECT avg_score('母猪的产后护理');
+------------------------------------+
| avg_score('母猪的产后护理')        |
+------------------------------------+
|                                 73 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT avg_score('论萨达姆的战争准备');
+------------------------------------------+
| avg_score('论萨达姆的战争准备')          |
+------------------------------------------+
|                                    73.25 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

查看和删除存储函数

如果我们想查看我们已经定义了多少个存储函数，可以使用下边这个语句：

SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]

查看某个函数的具体是怎么定义

show create function 函数名;

删除某个存储函数

DROP FUNCTION 函数名

函数体的定义

在函数体中定义局部变量
declare 变量名1,变量名2, ... 数据类型 [default 默认值];

在函数体内声明的变量只在该函数体内有用，当存储函数执行完成后，就不能访问到这些变量了，所以这些变量也被称为局部变量。我们可以在一条语句中声明多个相同数据类型的变量。不过需要特别留心的是，函数体中的局部变量名不允许加@前缀，这一点和我们之前直接使用SET语句自定义变量的方式是截然不同的，特别注意一下。在声明了这个局部变量之后，才可以使用它

mysql> delimiter $;
mysql> CREATE FUNCTION var_demo()
-> RETURNS INT
-> BEGIN
->     DECLARE c INT;
->     SET c = 5;
->     RETURN c;
-> END $
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select var_demo();
+------------+
| var_demo() |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

如果我们不对声明的局部变量赋值的话，它的默认值就是NULL，当然我们也可以通过DEFAULT子句来显式的指定局部变量的默认值

mysql> delimiter $
mysql> CREATE FUNCTION var_default_demo()
-> RETURNS INT
-> BEGIN
->     DECLARE c INT DEFAULT 1;
->     RETURN c;
-> END $
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> SELECT var_default_demo();
+--------------------+
| var_default_demo() |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

可以将某个查询语句的结果赋值给局部变量

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
    DECLARE a DOUBLE;
    SET a = (SELECT AVG(score) FROM student_score WHERE subject = s);
    return a;
END

在存储函数的函数体中，DECLARE语句必须放到其他语句的前边。

在函数体中使用自定义变量

除了局部变量外，也可以在函数体中使用我们之前用过的自定义变量

mysql> delimiter $
mysql> CREATE FUNCTION user_defined_var_demo()
    -> RETURNS INT
    -> BEGIN
    ->     SET @abc = 10;
    ->     return @abc;
    -> END $
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> SELECT user_defined_var_demo();
+-------------------------+
| user_defined_var_demo() |
+-------------------------+
|                      10 |
+-------------------------+
1 row in set (0.01 sec)

虽然现在存储函数执行完了，但是由于在该函数执行过程中为自定义变量abc赋值了，那么在该函数执行完之后我们仍然可以访问到该自定义变量的值，就像这样：

mysql> SELECT @abc;
+------+
| @abc |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

这一点和在函数体中使用DECLARE声明的局部变量有明显区别

set定义的变量在函数内外都可以访问

存储函数的参数

在定义存储函数的时候，可以指定多个参数，每个参数都要指定对应的数据类型，就像这样：

参数名 数据类型

比如我们上边编写的这个avg_score函数：

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END

参数名不要和函数体语句中的其他变量名、列名啥的冲突，比如上边的例子中如果把变量名s改为为subject，它就与下边用到WHERE子句中的列名冲突了。

函数参数不可以指定默认值，我们在调用函数的时候，必须显式的指定所有的参数，并且参数类型也一定要匹配，比方说我们在调用函数avg_score时，必须指定我们要查询的课程名，不然会报错的：

mysql> select avg_score();
ERROR 1318 (42000): Incorrect number of arguments for FUNCTION xiaohaizi.avg_score; expected 1, got 0
判断语句的编写
IF 表达式 THEN
    处理语句列表
[ELSEIF 表达式 THEN
    处理语句列表]
... # 这里可以有多个ELSEIF语句
[ELSE
    处理语句列表]
END IF;

其中处理语句列表中可以包含多条语句，每条语句以分号;结尾就好。

我们举一个包含IF语句的存储函数的例子：

delimiter $;
create function demo_condition(i int)
returns varchar(10)
begin
    declare result varchar(10);
    if i = 1 then
        set result = '结果是1';
    elseif i = 2 then
        set result = '结果是2';
    elseif i = 3 then
        set result = '结果是3';
    else
        set result = '参数非法';
    end if;
    return result;
end $;
delimiter ;
循环语句的编写

MySQL支持3种形式的循环语句

WHILE循环语句：

WHILE 表达式 DO
    处理语句列表
END WHILE;

mysql> delimiter $
mysql> CREATE FUNCTION sum_all(n INT UNSIGNED)
-> RETURNS INT
-> BEGIN
->     DECLARE result INT DEFAULT 0;
->     DECLARE i INT DEFAULT 1;
->     WHILE i <= n DO
->         SET result = result + i;
->         SET i = i + 1;
->     END WHILE;
->     RETURN result;
-> END $
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> SELECT sum_all(3);
+------------+
| sum_all(3) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

REPEAT循环语句

REPEAT循环语句和WHILE循环语句类似，只是形式上变了一下：

REPEAT
    处理语句列表
UNTIL 表达式 END REPEAT;

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    REPEAT
        SET result = result + i;
        SET i = i + 1;
    UNTIL i > n END REPEAT;
    RETURN result;
END

LOOP循环语句

这只是另一种形式的循环语句：

LOOP
    处理语句列表
END LOOP;

不过这种循环语句有一点比较奇特，它没有判断循环终止的条件？那这个循环语句怎么停止下来呢？其实可以把循环终止的条件写到处理语句列表中然后使用RETURN语句直接让函数结束就可以达到停止循环的效果，比方说我们可以这样改写sum_all函数：

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    LOOP
        IF i > n THEN
            RETURN result;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP;
END

如果我们仅仅想结束循环，而不是使用RETURN语句直接将函数返回，那么可以使用LEAVE语句。不过使用LEAVE时，需要先在LOOP语句前边放置一个所谓的标记，比方说我们使用LEAVE语句再改写sum_all函数：

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    flag:LOOP
        IF i > n THEN
        	LEAVE flag;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP flag;
    RETURN result;
END

http://www.kler.cn/a/521937.html

相关文章：

  • (done) MIT6.S081 2023 学习笔记 (Day6: LAB5 COW Fork)
  • 2025春招 SpringCloud 面试题汇总
  • 【Jave全栈】Java与JavaScript比较
  • 使用飞书群机器人监控服务器GPU使用率
  • 【第十天】零基础入门刷题Python-算法篇-数据结构与算法的介绍-两种常见的字符串算法（持续更新）
  • 浅谈Linux的发展
  • 主机监控软件WGCLOUD使用指南 - 如何设置主题背景色
  • 第05章 07 切片图等值线代码一则
  • 深入了解 npm 和 pnpm：前端包管理工具的选择与比较
  • LQ1052 Fibonacci斐波那契数列
  • kotlin 简介
  • TikTok广告投放优化策略：提升ROI的核心技巧
  • OpenSIPS-由浅入深编译更多可选模块
  • Go优雅实现redis分布式锁
  • CAS是什么？ABA会带来什么影响？怎么解决ABA问题？
  • Blazor-Blazor Web App项目结构
  • Hive数据仓库中的数据导出到MySQL的数据表不成功
  • 4、PyTorch 第一个神经网络，手写神经网络的基本部分组成
  • 银行卡三要素验证接口：方便快捷地实现银行卡核验功能
  • 网络安全态势感知：企业数字化转型的 “安全密钥”
  • SCRM在企业私域流量与客户管理中的变革之路探索
  • 如何实现滑动删除功能
  • Linux第二讲--用户权限
  • 使用 Vue 3 的 watchEffect 和 watch 进行响应式监视
  • RAG是否被取代（缓存增强生成-CAG）吗？
  • 字节iOS面试经验分享：HTTP与网络编程