MySQL自定义函数和存储过程
一、自定义函数
自定义函数(user-defined function
简称UDF
)是一种对MySQL
扩展的途径,其用法和内置函数相同。
自定义函数的两个必要条件:
- 参数
- 返回值(必须有)。函数可以返回任意类型的值。
1.1 创建
DELIMITER $$ --将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE FUNCTION 函数名([<参数1><类型1>[,<参数2><类型2>...]]) --定义函数
returns 数据类型 --定义返回值类型
-> BEGIN --以关键字BEGIN开始
-> select * from city; --执行语句
-> RETURN 值; --返回值
-> END $$ --以关键字END结束
DELIMITER; --将语句的结束符号恢复为分号
- 还可以有一些特别的选项,特别的选项写在
return
之后,begin
之前,如:- comment: 一个关于函数的描述
- 还有一些比如sql security等选项
1.2 调用
-- 无参调用
select myselect3();
-- 传参调用
select myselect5("python");
select * from class where id = myselect5("python");
1.3 查看
查看函数创建语句:
show create function 函数名;
查看所有函数:
show function status [like 'pattern'];
1.4 修改
函数的修改只能修改一些如comment
的选项,不能修改内部的sql
语句和参数列表。
alter function 函数名 选项;
1.5 删除
drop function 函数名;
1.6 案例
以下是拆分大整数为2幂的和算法
bigint
目前支持最大值为
/*判断是否存在,如果存在就删除*/
DROP FUNCTION IF EXISTS binaryPow;
/*创建函数*/
create function binaryPow(num bigint(20))
/*返回值数据类型*/
RETURNS varchar(512)
begin
/*参数二进制的变量*/
DECLARE binaryNum VARCHAR(128) DEFAULT 0;
/*自增变量*/
DECLARE chooseNum int DEFAULT 0;
/*返回值变量*/
DECLARE returnNum varchar(255) DEFAULT 0;
/*参数的长度*/
DECLARE numLength varchar(255) DEFAULT 0;
/*变量值*/
DECLARE cur tinyint(1) DEFAULT 0;
/*将参数转换成二进制并赋给binaryNum变量*/
set binaryNum = bin(num);
/*获得binaryNum长度赋给numLength*/
set numLength = LENGTH(binaryNum);
/*开启循环*/
repeat
/*分割binaryNum变量,每次获得1位*/
set cur = substr(binarynum, numLength - chooseNum, 1);
/*cur为0时,*/
if cur > 0 then
if returnNum != 0 then
set returnNum = CONCAT(returnNum, ',', cast(POW(2, chooseNum) as signed int));
end if;
if returnNum = 0 then
set returnNum = cast(POW(2, chooseNum) as signed int);
end if;
end if;
/*递增*/
set chooseNum = chooseNum + 1;
UNTIL chooseNum = numLength end repeat;
return returnNum;
end;
/*调用*/
SELECT binaryPow(4611686018427387904);
注意:declare定义的变量名不能和select查询的列名相同,否则赋值不成功
如果开启bin_log时,会报以下错误:
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
在函数创建前,需要设置参数
set global log_bin_trust_function_creators = true;
二、存储过程
存储过程是一组为了完成特定功能的SQL
语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL
语句写好并用一个指定的名称存储起来,这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时,只需要调用它即可。存储过程在执行上比传统SQL
速度更快、执行效率更高。
2.1 创建
DELIMITER $$ --将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc() --创建存储过程,过程名为Proc,不带参数
-> BEGIN --过程体以关键字BEGIN开始
-> select * from city; --过程体语句
-> END $$ --过程体以关键字END结束
DELIMITER ; --将语句的结束符号恢复为分号
2.2 调用
CALL Proc;
2.3 查看
SHOW CREATE PROCEDURE [数据库.]存储过程名; --查看某个存储过程的具体信息
SHOW CREATE PROCEDURE Proc; --查看已存在的存储过程
SHOW PROCEDURE STATUS LIKE '%Proc%' \G; --查看存储过程和函数的状态
2.4 修改
IN(输入参数):表示调用者向过程传入值(传入值可以是字面量或变量)
OUT(输出参数):表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT(输入输出参数):既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
DELIMITER $$
CREATE PROCEDURE Proc1(IN inname CHAR(16))
-> BEGIN
-> SELECT * FROM city WHERE Store_Name = inname;
-> END $$
DELIMITER ;
CALL Proc1('Boston');
2.5 删除
存储过程内容的修改方法是通过删除原有存储过程,之后再以相同的名称创建新的存储过程。
DROP PROCEDURE IF EXISTS Proc; --仅当存在时删除,如果指定的过程不存在,则产生一个错误
2.6 控制语句
2.6.1 if-then-else
DELIMITER $$
CREATE PROCEDURE Proc2(IN inid int)
-> begin
-> declare var int;
-> set var = inid * 2;
-> if var >= 10 then
-> update t set id = id + 1;
-> else
-> update t set id = id - 1;
-> end if;
-> end $$
DELIMITER;
2.6.2 循环while
DELIMITER $$
CREATE PROCEDURE Proc3()
-> begin
-> declare var int(10);
-> set var = 0;
-> while var < 6 do
-> insert into t values(var);
-> set var = var + 1;
-> end while;
-> end $$
DELIMITER;
2.7 案例
-- subject_id 递增 start
/*判断是否存在,如果存在就删除*/
/*取一个系统中绝对不会用到的存储过程名称*/
drop procedure if exists tempProcedure_Test;
/*创建临时存储过程*/
create procedure tempProcedure_Test()
BEGIN
/*declare定义的变量名不能和select查询的列名相同*/
/*判断是否还有记录*/
declare isDone int default 0;
/*用于自增的变量*/
declare intNumber BIGINT(20) default 190122100000177;
/*每条记录循环时的临时ID*/
declare tempId BIGINT(20);
/*取出来所有需要循环的数据*/
declare folderIds cursor for
/*sql语句 开始*/
SELECT
a.id
FROM
t_account_subject a,(
SELECT
account_id,
subject_code,
max( `year` ) `year`
FROM
`t_account_subject` d
GROUP BY
d.account_id,
d.subject_code
ORDER BY
d.YEAR DESC
) b
WHERE
a.account_id = b.account_id
AND a.YEAR = b.YEAR
AND a.subject_code = b.subject_code
AND a.subject_id = 0;
/*sql语句 结束*/
/*如果不存在就设置为1,即为true*/
declare continue handler for not FOUND set isDone = 1;
/*OPEN*/
open folderIds;
REPEAT
/*开始循环数据*/
fetch folderIds into tempId;
if not isDone THEN
update t_account_subject set subject_id = intNumber where id = tempId;
set intNumber = intNumber + 1;
end if;
until isDone end repeat;
close folderIds; /*CLOSE,对应上面的open folderIds;*/
END;
/*调用下这个临时的存储过程*/
call tempProcedure_Test();
drop procedure tempProcedure_Test; /*使用完毕后要删除垃圾*/
-- subject_id 递增 end
总结
存储过程和函数主要区别
- 存储过程实现的功能要复杂一点,函数实现的功能针对性比较强;
存储过程,功能强大,可以执行包括修改表等一系列数据库操作;
用户定义函数不能用于执行一组修改全局数据库状态的操作。 - 对于存储过程来说可以返回参数,如记录集,函数只能返回值或者表对象;
函数只能返回一个变量;而存储过程可以返回多个;
存储过程的参数可以有IN
,OUT
,INOUT
三种类型,而函数必须指定返回值,且参数默认为IN类
型;
存储过程声明时不需要返回类型,而函数声明时需要描述返回类型,且函数体中必须包含一个有效的RETURN
语句。 - 存储过程,可以使用非确定函数,不允许在用户定义函数主体中内置非确定函数。
- 存储过程一般是作为一个独立的部分来执行(
EXECUTE
语句执行),而函数可以作为查询语句的一个部分来调用(SELECT
调用);
由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM
关键字的后面。SQL
语句中不可用存储过程,而可以使用函数。 - 调用方式:
函数select my_fun()
;
过程call my_pro()
;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器