sql：mysql：自定义函数

article/2025/9/24 18:48:02

一、基本语法

　delimiter 自定义符号　　-- 如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略create function 函数名(形参列表) returns 返回类型　　-- 注意是retrunsbegin函数体　　　　-- 函数内定义的变量如：set @x = 1; 变量x为全局变量，在函数外面也可以使用返回值end自定义符号delimiter ;

二、示例

delimiter $$
create function test(ia int, ib int) returns int
beginreturn ia + ib;
end
$$
delimiter ;

三、查看函数
　　1. show function status [like 'pattern'];　　-- 查看所有自定义函数, 自定义函数只能在本数据库使用。

　　2. show create function 函数名;　　-- 查看函数创建语句

四、删除函数

　　drop function 函数名;

五、综合应用

实例一：

目前数据库中有现成的一张分数表，就拿来测试了。

表：

insert into TB_SCORE (id, userid, subject, score) values (1, '001', '语文', 90.0);
insert into TB_SCORE (id, userid, subject, score) values (2, '001', '数学', 92.0);
insert into TB_SCORE (id, userid, subject, score) values (3, '001', '英语', 80.0);
insert into TB_SCORE (id, userid, subject, score) values (4, '002', '语文', 88.0);
insert into TB_SCORE (id, userid, subject, score) values (5, '002', '数学', 90.0);
insert into TB_SCORE (id, userid, subject, score) values (6, '002', '英语', 75.5);
insert into TB_SCORE (id, userid, subject, score) values (7, '003', '语文', 70.0);
insert into TB_SCORE (id, userid, subject, score) values (8, '003', '数学', 85.0);
insert into TB_SCORE (id, userid, subject, score) values (9, '003', '英语', 90.0);
insert into TB_SCORE (id, userid, subject, score) values (10, '003', '政治', 82.0);
insert into TB_SCORE (id, userid, subject, score) values (11, '001', '语文', 91.0);

CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`test2`(NUM INT, M INT) RETURNS varchar(200) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ciREADS SQL DATA
BEGINDECLARE result VARCHAR(100) DEFAULT '';DECLARE LEN INT;DECLARE i INT DEFAULT 0;SET result = '0';SET LEN=1;IF NUM IS NULL OR M IS NULL THENRETURN '参数不能为空';END IF;#根据ID查询分数IF NUM > 5 AND NUM < 20 THENSELECT SCORE INTO result FROM TB_SCORE WHERE ID=NUM;IF result > M THENSET result = CONCAT(' 注意：超过自定义分数线 ',M,'分--->',result); WHILE i <= LEN DOset result=CONCAT('优秀！',result);SET i = i + 1;END WHILE;END IF;ELSEIF NUM <= 5 THENSELECT CONCAT('<---注意：这是ID排在前五的同学的分数-->  ',SCORE,'分') INTO result FROM TB_SCORE WHERE ID=NUM;ELSESET result = '超出范围！';END IF;RETURN CONCAT('查询结果： ', result, '  输入ID为：',NUM);
END

执行1：

执行2：

执行3：

执行4：

执行5：

实例二：

1. 使用全局变量

-- 计算 1 ~ 指定数据之间的和，即从1开始加到输入参数的和
delimiter $$
create function my_sum(x int) returns int
beginset @i = 1;set @sum = 0;while @i <= x doset @sum = @sum + @i;set @i = @i + 1;end while;return @sum;
end
$$
delimiter ;

执行效果：

2. 使用局部变量

-- 求1 ~ 指定数之前的和，但5的倍数不加
delimiter $$
create function my_sum2(x int) returns int
begindeclare i int default 1;declare sum int default 0;sumwhile:while i <= x doif i % 5 = 0 thenset i = i + 1;iterate sumwhile;end if;set sum = sum + i;set i = i + 1;end while;return sum;
end
$$
delimiter ;