MySQL自定义函数实例「建议收藏」
大家好，又见面了，我是你们的朋友全栈君。
关于MySQL函数的基本内容，网上有很多的资料，仅在此整理一些有价值的sql实例，以方便自己阅读，见例子便知内容。
1.求两点之间的距离?
DELIMITER //
CREATE FUNCTION distance_fn(x1 DOUBLE, y1 DOUBLE, x2 DOUBLE, y2 DOUBLE)
RETURNS DOUBLE
BEGIN
SET @distance = sqrt(pow(x2-x1, 2) + pow(y2-y1, 2));
RETURN @distance;
END;
//
DELIMITER ;2.求圆的面积和周长？
DELIMITER //
CREATE FUNCTION circle_cal_fn(r DOUBLE)
RETURNS VARCHAR(50)
BEGIN
DECLARE result VARCHAR(50) DEFAULT '';
DECLARE len DOUBLE;
DECLARE area DOUBLE;
SET len = 2 * pi() *r;
SET area = pi() * pow(r, 2);
SET len = round(len, 2);
SET area = round(area, 2);
SET result = concat('radius:', r, ' long:', len, ' area:', area);
RETURN result;
END;
//
DELIMITER ;3.按成绩分数分等级？
DELIMITER //
DROP FUNCTION IF EXISTS grade_switch;
CREATE FUNCTION grade_switch(score INT)
RETURNS VARCHAR(30)
BEGIN
DECLARE consult INT;
DECLARE grade VARCHAR(30);
IF(score >= 0) THEN
SET consult = score div 10;
CASE consult
WHEN 10 THEN
SET grade = 'A';
WHEN 9 THEN
SET grade = 'A';
WHEN 8 THEN
SET grade = 'B';
WHEN 7 THEN
SET grade = 'C';
WHEN 6 THEN
SET grade = 'D';
WHEN 5 THEN
SET grade = 'E';
WHEN 4 THEN
SET grade = 'E';
WHEN 3 THEN
SET grade = 'E';
WHEN 2 THEN
SET grade = 'E';
WHEN 1 THEN
SET grade = 'E';
WHEN 0 THEN
SET grade = 'E';
ELSE
SET grade = 'score is error!';
END CASE;
ELSE
SET grade = 'score is error!';
END IF;
RETURN grade;
END;
//
DELIMITER ;4.找出三位数的水仙花数？
DELIMITER //
#分解出数字的个位，十位，百位，然后判断其是否水仙花数
DROP FUNCTION IF EXISTS narcissus_number_fun//
CREATE FUNCTION narcissus_number_fun(num INT)
RETURNS BOOL
BEGIN
DECLARE a INT;
DECLARE b INT;
DECLARE c INT;
SET a = num div 100;
SET b = num % 100 div 10;
SET c = num % 100 % 10;
IF(num = (pow(a,3) + pow(b,3) + pow(c,3))) THEN
RETURN TRUE;
ELSE RETURN FALSE;
END IF;
END;
//
#获取m~n之间的水仙花数，并且返回字符串如：between 101 and 999 narcissus is:153, 370, 371, 407,
DROP FUNCTION IF EXISTS find_narcissus_number_fun//
CREATE FUNCTION find_narcissus_number_fun(m INT, n INT)
RETURNS VARCHAR(100)
BEGIN
DECLARE result VARCHAR(100) DEFAULT '';
DECLARE a INT;
DECLARE b INT;
DECLARE c INT;
DECLARE num INT DEFAULT 0;
IF(m <= n && m >= 100 && m <= 999) THEN
SET result = concat('between ', m, ' and ', n, ' narcissus is:');
narcissus_number:LOOP
IF(narcissus_number_fun(m)) THEN
SET num = num + 1;
SET result = concat(result, m, ', ');
END IF;
SET m = m +1;
IF(m > n) THEN LEAVE narcissus_number;
END IF;
END LOOP narcissus_number;
IF(num=0) THEN SET result = concat(result, 'not find');
END IF;
ELSE
SET result = 'invalid data';
END IF;
RETURN result;
END;
//
DELIMITER ;5.求出2-n之间的所有素数？
DELIMITER //
#判断是否是素数
CREATE FUNCTION judge_prime(num INT) RETURNS BOOL
BEGIN
DECLARE i INT DEFAULT 2;
DECLARE flag INT DEFAULT 0;
prime:WHILE i <= floor(sqrt(num)) DO
IF(num % i = 0) THEN
RETURN FALSE;
END IF;
SET i = i + 1;
END WHILE prime;
RETURN TRUE;
END;
//
#给定区间上限n，求出2-n 之间的所有素数，并返回结果
CREATE FUNCTION find_prime_fun(n INT) RETURNS VARCHAR(100)
BEGIN
DECLARE result VARCHAR(100) DEFAULT '';
DECLARE m INT DEFAULT 2;
IF(n >= 2) THEN
SET result = concat('between ', 2, ' and ', n, ' prime is: ');
prime:WHILE m <= n DO
IF(judge_prime(m)) THEN
SET result = concat(result, m, ', ');
END IF;
SET m = m+1;
END WHILE prime;
ELSE
SET result = 'invalid data!';
END IF;
RETURN result;
END;
//
DELIMITER ; 6.1.求s=a+aa+aaa+…+aa…a之值，其中a是一个数字，最后一个加数有n个a。例如：2+22+222+2222+22222，此时n=5。a与n由键盘输入?
DELIMITER //
#每一位的数都相同且位m，一共n位，求和
CREATE FUNCTION get_plug_fun(m INT, n INT) RETURNS INT
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE result INT DEFAULT 0;
plug: WHILE(i < n) DO
SET result = result + pow(10, i)*m;
SET i = i + 1;
END WHILE plug;
RETURN result;
END;
//
CREATE FUNCTION get_accumulate_fun(m INT, n INT) RETURNS INT
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE result INT DEFAULT 0;
accumulate: WHILE(i <= n) DO
SET result = result + get_plug_fun(m, i);
SET i = i+1;
END WHILE accumulate;
RETURN result;
END;
//
DELIMITER ; 7.阿拉伯数字转汉字?
DELIMITER //
DROP FUNCTION IF EXISTS tohanzi//
create FUNCTION tohanzi(n_LowerMoney DECIMAL)
RETURNS VARCHAR(120)
BEGIN
Declare v_LowerStr VARCHAR(200) ;
Declare v_UpperPart VARCHAR(200) ;
Declare v_UpperStr VARCHAR(200) ;
Declare i_I int ;
set v_LowerStr = LTRIM(RTRIM(ROUND(n_LowerMoney,2 ) ) ) ;
set i_I = 1 ;
set v_UpperStr = '' ;
while ( i_I <=char_length(v_LowerStr ) ) do
set v_UpperPart = CONCAT( case substring(v_LowerStr,char_length(v_LowerStr) - i_I + 1,1 )
WHEN '.' THEN '元'
WHEN '0' THEN '零'
WHEN '1' THEN '壹'
WHEN '2' THEN '贰'
WHEN '3' THEN '叁'
WHEN '4' THEN '肆'
WHEN '5' THEN '伍'
WHEN '6' THEN '陆'
WHEN '7' THEN '柒'
WHEN '8' THEN '捌'
WHEN '9' THEN '玖'
END,
case i_I
WHEN 1 THEN '分'
WHEN 2 THEN '角'
WHEN 3 THEN ''
WHEN 4 THEN ''
WHEN 5 THEN '拾'
WHEN 6 THEN '佰'
WHEN 7 THEN '仟'
WHEN 8 THEN '万'
WHEN 9 THEN '拾'
WHEN 10 THEN '佰'
WHEN 11 THEN '仟'
WHEN 12 THEN '亿'
WHEN 13 THEN '拾'
WHEN 14 THEN '佰'
WHEN 15 THEN '仟'
WHEN 16 THEN '万'
ELSE ''
END );
set v_UpperStr =CONCAT( v_UpperPart , v_UpperStr) ;
set i_I = i_I + 1 ;
end while;
set v_UpperStr = REPLACE(v_UpperStr,'零拾','零') ;
set v_UpperStr = REPLACE(v_UpperStr,'零佰','零') ;
set v_UpperStr = REPLACE(v_UpperStr,'零仟','零') ;
set v_UpperStr = REPLACE(v_UpperStr,'零零零','零') ;
set v_UpperStr = REPLACE(v_UpperStr,'零零','零') ;
set v_UpperStr = REPLACE(v_UpperStr,'零角零分','整') ;
set v_UpperStr = REPLACE(v_UpperStr,'零分','整') ;
set v_UpperStr = REPLACE(v_UpperStr,'零角','零') ;
set v_UpperStr = REPLACE(v_UpperStr,'零亿零万零元','亿元') ;
set v_UpperStr = REPLACE(v_UpperStr,'亿零万零元','亿元') ;
set v_UpperStr = REPLACE(v_UpperStr,'零亿零万','亿') ;
set v_UpperStr = REPLACE(v_UpperStr,'零万零元','万元') ;
set v_UpperStr = REPLACE(v_UpperStr,'万零元','万元') ;
set v_UpperStr = REPLACE(v_UpperStr,'零亿','亿') ;
set v_UpperStr = REPLACE(v_UpperStr,'零万','万') ;
set v_UpperStr = REPLACE(v_UpperStr,'零元','元') ;
set v_UpperStr = REPLACE(v_UpperStr,'零零','零') ;
if ( '元' = substring(v_UpperStr,1,1)) then
set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1));
end if;
if ( '零' = substring(v_UpperStr,1,1)) then
set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;
end if;
if ( '角' = substring(v_UpperStr,1,1)) then
set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;
end if;
if ( '分' = substring(v_UpperStr,1,1)) then
set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;
end if;
if ('整' = substring(v_UpperStr,1,1)) then
set v_UpperStr = '零元整' ;
end if;
return v_UpperStr;
END;
//
DELIMITER ;8.随机产生姓名的函数?
DELIMITER //
DROP FUNCTION IF EXISTS tohanzi//
CREATE FUNCTION CreateName()
RETURNS varchar(3)
begin
DECLARE LN VARCHAR(300);
DECLARE MN VARCHAR(500);
DECLARE FN VARCHAR(500);
DECLARE LN_N INT;
DECLARE MN_N INT;
DECLARE FN_N INT;
SET LN='李王张刘陈杨黄赵周吴徐孙朱马胡郭林何高梁郑罗宋谢唐韩曹许邓萧冯曾程蔡彭潘袁于董余苏叶吕魏蒋田杜丁沈姜范江傅钟卢汪戴崔任陆廖姚方金邱夏谭韦贾邹石熊孟秦阎薛侯雷白龙段郝孔邵史毛常万顾赖武康贺严尹钱施牛洪龚';
SET MN='伟刚勇毅俊云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧才发武丽琳轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德河哲江超浩璐娅琦晶裕华慧巧美婕馨影荔枝思心邦承乐绍功松善厚庆磊民友玉萍红娥玲芬芳燕彩兰凤洁梅秀娟英行时泰盛雄琛钧冠策腾楠榕风航弘峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘';
SET FN='伟刚勇毅俊云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧才发武丽琳轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德河哲江超浩璐娅琦晶裕华慧巧美婕馨影荔枝思心邦承乐绍功松善厚庆磊民友玉萍红娥玲芬芳燕彩兰凤洁梅秀娟英行时泰盛雄琛钧冠策腾楠榕风航弘峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘';
SET LN_N=CHAR_LENGTH(LN);
SET MN_N=CHAR_LENGTH(MN);
SET FN_N=CHAR_LENGTH(FN);
return Concat(substring(LN,ceil(rand()*LN_N),1),substring(MN,ceil(rand()*MN_N),1),substring(FN,ceil(rand()*FN_N),1));
End;
//
DELIMITER ;版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/196148.html原文链接：https://javaforall.cn
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2022年9月7日 下，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。