MySQL自定义函数实例「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

关于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 ;