MySQL數據庫高級（二）——自定義函數

MySQL數據庫高級（二）——自定義函數

一、自定義函數簡介

自定義函數 (user-defined function UDF)是一種對MySQL擴展的途徑，其用法和內置函數相同。
自定義函數的兩個必要條件：
A、參數
B、返回值（必須有）。函數可以返回任意類型的值。

二、自定義函數的使用

1、自定義函數語法

CREATE?FUNCTION?function_name(parameter_nametype,[parameter_name type,...])
RETURNS?{STRING|INTEGER|REAL}
runtime_body

在函數體中可以使用更為復雜的語法，比如復合結構/流程控制/任何SQL語句/定義變量等。
帶復合結構的函數體的自定義函數的創建語法如下：

DELIMITER //
CREATE?FUNCTION?function_name(parameter_nametype,[parameter_name type,...])
RETURNS?{STRING|INTEGER|REAL}
BEGIN
//body
END
//      /* 此處的”//“為告訴系統函數定義結束 */

當函數體內需要執行的是多條語句時，要使用BEGIN...END語句；且當編寫函數體內容的時候，需要使用DELIMITER關鍵字將分隔符先修改為別的，否則編寫語句的時候寫到’;’的時候會直接執行，導致函數編寫失敗。

2、創建帶復合結構的函數體的自定義函數

在函數體中，如果包含多條語句，需要把多條語句放到BEGIN...END語句塊中。
?復合結構可以包括聲明、循環、控制結構。

3、自定義函數中定義局部變量

變量定義的語法：
DECLARE var_name[,varname]...date_type [DEFAULT VALUE];
為變量賦值的語法：

SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name

實例：

DECLARE x int;
SELECT COUNT(id) FROM tdb_name INTO x;
SET @x = 100;

4、流程控制

自定義函數中可以使用流程控制來控制語句的執行。
MySQL中可以使用IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句來進行流程控制。
A、IF語句
IF語句用來進行條件判斷。根據是否滿足條件，將執行不同的語句。其語法的基本形式如下：

IF search_condition THEN statement_list 
   [ELSEIF search_condition THEN statement_list] ... 
   [ELSE statement_list] 
END IF 

search_condition參數表示條件判斷語句；statement_list參數表示不同條件的執行語句。
IF語句都需要使用END IF來結束。
IF語句實例：

IF age>20 THEN SET @count1=@count1+1;  
ELSEIF age=20 THEN SET @count2=@count2+1;  
ELSE SET @count3=@count3+1;  
END IF; 

B、CASE語句
CASE語句也用來進行條件判斷，其可以實現比IF語句更復雜的條件判斷。CASE語句的基本形式如下：

CASE case_value 
     WHEN when_value THEN statement_list 
     [WHEN when_value THEN statement_list] ... 
     [ELSE statement_list] 
END CASE 

case_value參數表示條件判斷的變量；when_value參數表示變量的取值；
statement_list參數表示不同when_value值的執行語句。
CASE語句實例：

CASE age 
WHEN 20 THEN SET @count1=@count1+1; 
ELSE SET @count2=@count2+1; 
END CASE ; 

CASE語句還有另一種形式，語法如下：

CASE 
       WHEN search_condition THEN statement_list 
       [WHEN search_condition THEN statement_list] ... 
       [ELSE statement_list] 
END CASE 

search_condition參數表示條件判斷語句；statement_list參數表示不同條件的執行語句。
CASE語句實例：

CASE 
       WHEN age=20 THEN SET @count1=@count1+1; 
       ELSE SET @count2=@count2+1; 
END CASE ; 

C、LOOP語句
LOOP語句可以使某些特定的語句重復執行，實現一個簡單的循環。但LOOP語句本身沒有停止循環的語句，必須是遇到LEAVE語句等才能停止循環。
LOOP語句的語法的基本形式如下：

[begin_label:] LOOP 
statement_list 
END LOOP [end_label] 

begin_label參數和end_label參數分別表示循環開始和結束的標志，兩個標志必須相同，而且都可以省略；statement_list參數表示需要循環執行的語句。
LOOP語句實例：

add_num: LOOP  
SET @count=@count+1;  
END LOOP add_num ; 

D、LEAVE語句
LEAVE語句主要用于跳出循環控制。其語法形式如下：
LEAVE label
label參數表示循環的標志。
LEAVE語句實例：

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
END LOOP add_num ; 

E、ITERATE語句
ITERATE語句也是用來跳出循環的語句。但是，ITERATE語句是跳出本次循環，然后直接進入下一次循環。
ITERATE語句只可以出現在LOOP、REPEAT、WHILE語句內。
ITERATE語句的基本語法形式如下：
ITERATE label
其中，label參數表示循環的標志。
ITERATE語句實例：

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
ELSE IF MOD(@count,3)=0 THEN 
ITERATE add_num; 
SELECT * FROM employee ; 
END LOOP add_num ; 

LEAVE語句是跳出整個循環，然后執行循環后面的程序。而ITERATE語句是跳出本次循環，然后進入下一次循環。
F、REPEAT語句
REPEAT語句是有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句。REPEAT語句的基本語法形式如下：

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label] 

REPEAT循環都用END REPEAT結束。
其中，statement_list參數表示循環的執行語句；search_condition參數表示結束循環的條件，滿足該條件時循環結束。

REPEAT 
SET @count=@count+1; 
UNTIL @count=100 
END REPEAT ; 

G、WHILE語句
WHILE語句也是有條件控制的循環語句。但WHILE語句和REPEAT語句是不一樣的。
WHILE語句是當滿足條件時，執行循環內的語句。
WHILE語句的基本語法形式如下：

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label] 

WHILE循環需要使用END WHILE來結束。
其中，search_condition參數表示循環執行的條件，滿足該條件時循環執行；
statement_list參數表示循環的執行語句。

WHILE @count<100 DO 
SET @count=@count+1; 
END WHILE ; 

5、刪除自定義函數

DROP FUNCTION functionName;

6、自定義函數的調用

SELECT function_name(parameter_value,...);

三、自定義函數實例

1、根據學生成績判斷學生成績是否優秀

以下函數能夠根據輸入值范圍輸出成績是否優良差。
小于60 不及格
60-69 需要努力
70-79 成績中等
80-89 成績優秀
90-100 成績良好

create function getGrade(mark int)
returns VARCHAR(20)
begin
return (
       case FLOOR(mark/10) 
       when 5 then '不及格' 
       when 6 then '繼續努力' 
       when 7 then '成績良好' 
       else '成績優秀'
       end);
END

2、漢字轉拼音函數

需要先創建一張表，存儲字符集GBK的代碼和拼音之間對應關系。

CREATE TABLE IF NOT EXISTS `t_base_pinyin` (
  `pin_yin_` varchar(255) CHARACTER SET gbk NOT NULL,
  `code_` int(11) NOT NULL,
  PRIMARY KEY (`code_`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

插入數據：
INSERT INTO t_base_pinyin (pin_yin_,code_) VALUES ("a", 20319),("ai", 20317),("an", 20304),("ang", 20295),("ao", 20292),("ba", 20283),("bai", 20265),("ban", 20257),("bang", 20242),("bao", 20230),("bei", 20051),("ben", 20036),("beng", 20032),("bi", 20026),("bian", 20002),("biao", 19990),("bie", 19986),("bin", 19982),("bing", 19976),("bo", 19805),("bu", 19784),("ca", 19775),("cai", 19774),("can", 19763),("cang", 19756),("cao", 19751),("ce", 19746),("ceng", 19741),("cha", 19739),("chai", 19728),("chan", 19725),("chang", 19715),("chao", 19540),("che", 19531),("chen", 19525),("cheng", 19515),("chi", 19500),("chong", 19484),("chou", 19479),("chu", 19467),("chuai", 19289),("chuan", 19288),("chuang", 19281),("chui", 19275),("chun", 19270),("chuo", 19263),("ci", 19261),("cong", 19249),("cou", 19243),("cu", 19242),("cuan", 19238),("cui", 19235),("cun", 19227),("cuo", 19224),("da", 19218),("dai", 19212),("dan", 19038),("dang", 19023),("dao", 19018),("de", 19006),("deng", 19003),("di", 18996),("dian", 18977),("diao", 18961),("die", 18952),("ding", 18783),("diu", 18774),("dong", 18773),("dou", 18763),("du", 18756),("duan", 18741),("dui", 18735),("dun", 18731),("duo", 18722),("e", 18710),("en", 18697),("er", 18696),("fa", 18526),("fan", 18518),("fang", 18501),("fei", 18490),("fen", 18478),("feng", 18463),("fo", 18448),("fou", 18447),("fu", 18446),("ga", 18239),("gai", 18237),("gan", 18231),("gang", 18220),("gao", 18211),("ge", 18201),("gei", 18184),("gen", 18183),("geng", 18181),("gong", 18012),("gou", 17997),("gu", 17988),("gua", 17970),("guai", 17964),("guan", 17961),("guang", 17950),("gui", 17947),("gun", 17931),("guo", 17928),("ha", 17922),("hai", 17759),("han", 17752),("hang", 17733),("hao", 17730),("he", 17721),("hei", 17703),("hen", 17701),("heng", 17697),("hong", 17692),("hou", 17683),("hu", 17676),("hua", 17496),("huai", 17487),("huan", 17482),("huang", 17468),("hui", 17454),("hun", 17433),("huo", 17427),("ji", 17417),("jia", 17202),("jian", 17185),("jiang", 16983),("jiao", 16970),("jie", 16942),("jin", 16915),("jing", 16733),("jiong", 16708),("jiu", 16706),("ju", 16689),("juan", 16664),("jue", 16657),("jun", 16647),("ka", 16474),("kai", 16470),("kan", 16465),("kang", 16459),("kao", 16452),("ke", 16448),("ken", 16433),("keng", 16429),("kong", 16427),("kou", 16423),("ku", 16419),("kua", 16412),("kuai", 16407),("kuan", 16403),("kuang", 16401),("kui", 16393),("kun", 16220),("kuo", 16216),("la", 16212),("lai", 16205),("lan", 16202),("lang", 16187),("lao", 16180),("le", 16171),("lei", 16169),("leng", 16158),("li", 16155),("lia", 15959),("lian", 15958),("liang", 15944),("liao", 15933),("lie", 15920),("lin", 15915),("ling", 15903),("liu", 15889),("long", 15878),("lou", 15707),("lu", 15701),("lv", 15681),("luan", 15667),("lue", 15661),("lun", 15659),("luo", 15652),("ma", 15640),("mai", 15631),("man", 15625),("mang", 15454),("mao", 15448),("me", 15436),("mei", 15435),("men", 15419),("meng", 15416),("mi", 15408),("mian", 15394),("miao", 15385),("mie", 15377),("min", 15375),("ming", 15369),("miu", 15363),("mo", 15362),("mou", 15183),("mu", 15180),("na", 15165),("nai", 15158),("nan", 15153),("nang", 15150),("nao", 15149),("ne", 15144),("nei", 15143),("nen", 15141),("neng", 15140),("ni", 15139),("nian", 15128),("niang", 15121),("niao", 15119),("nie", 15117),("nin", 15110),("ning", 15109),("niu", 14941),("nong", 14937),("nu", 14933),("nv", 14930),("nuan", 14929),("nue", 14928),("nuo", 14926),("o", 14922),("ou", 14921),("pa", 14914),("pai", 14908),("pan", 14902),("pang", 14894),("pao", 14889),("pei", 14882),("pen", 14873),("peng", 14871),("pi", 14857),("pian", 14678),("piao", 14674),("pie", 14670),("pin", 14668),("ping", 14663),("po", 14654),("pu", 14645),("qi", 14630),("qia", 14594),("qian", 14429),("qiang", 14407),("qiao", 14399),("qie", 14384),("qin", 14379),("qing", 14368),("qiong", 14355),("qiu", 14353),("qu", 14345),("quan", 14170),("que", 14159),("qun", 14151),("ran", 14149),("rang", 14145),("rao", 14140),("re", 14137),("ren", 14135),("reng", 14125),("ri", 14123),("rong", 14122),("rou", 14112),("ru", 14109),("ruan", 14099),("rui", 14097),("run", 14094),("ruo", 14092),("sa", 14090),("sai", 14087),("san", 14083),("sang", 13917),("sao", 13914),("se", 13910),("sen", 13907),("seng", 13906),("sha", 13905),("shai", 13896),("shan", 13894),("shang", 13878),("shao", 13870),("she", 13859),("shen", 13847),("sheng", 13831),("shi", 13658),("shou", 13611),("shu", 13601),("shua", 13406),("shuai", 13404),("shuan", 13400),("shuang", 13398),("shui", 13395),("shun", 13391),("shuo", 13387),("si", 13383),("song", 13367),("sou", 13359),("su", 13356),("suan", 13343),("sui", 13340),("sun", 13329),("suo", 13326),("ta", 13318),("tai", 13147),("tan", 13138),("tang", 13120),("tao", 13107),("te", 13096),("teng", 13095),("ti", 13091),("tian", 13076),("tiao", 13068),("tie", 13063),("ting", 13060),("tong", 12888),("tou", 12875),("tu", 12871),("tuan", 12860) ,("tui", 12858),("tun", 12852),("tuo", 12849),("wa", 12838),("wai", 12831),("wan", 12829),("wang", 12812),("wei", 12802),("wen", 12607),("weng", 12597),("wo", 12594),("wu", 12585),("xi", 12556),("xia", 12359),("xian", 12346),("xiang", 12320),("xiao", 12300),("xie", 12120),("xin", 12099),("xing", 12089),("xiong", 12074),("xiu", 12067),("xu", 12058),("xuan", 12039),("xue", 11867),("xun", 11861),("ya", 11847),("yan", 11831),("yang", 11798),("yao", 11781),("ye", 11604),("yi", 11589),("yin", 11536),("ying", 11358),("yo", 11340),("yong", 11339),("you", 11324),("yu", 11303),("yuan", 11097),("yue", 11077),("yun", 11067),("za", 11055),("zai", 11052),("zan", 11045),("zang", 11041),("zao", 11038),("ze", 11024),("zei", 11020),("zen", 11019),("zeng", 11018),("zha", 11014),("zhai", 10838),("zhan", 10832),("zhang", 10815),("zhao", 10800),("zhe", 10790),("zhen", 10780),("zheng", 10764),("zhi", 10587),("zhong", 10544),("zhou", 10533),("zhu", 10519),("zhua", 10331),("zhuai", 10329),("zhuan", 10328),("zhuang", 10322),("zhui", 10315),("zhun", 10309),("zhuo", 10307),("zi", 10296),("zong", 10281),("zou", 10274),("zu", 10270),("zuan", 10262),("zui", 10260),("zun", 10256),("zuo", 10254);
漢字生成拼音的函數：

DROP FUNCTION IF EXISTS PINYIN;
CREATE FUNCTION PINYIN(NAME VARCHAR(255) CHARSET GBK)
RETURNS VARCHAR(255) CHARSET gbk
BEGIN
    DECLARE mycode INT;
    DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;
    DECLARE lcode INT;
    DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;
    DECLARE rcode INT;
    DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';
    DECLARE lp INT;
    SET mycode = 0;
    SET lp = 1;
    SET NAME = HEX(NAME);
    WHILE lp < LENGTH(NAME) DO
        SET tmp_lcode = SUBSTRING(NAME, lp, 2);
        SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED); 
        SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
        SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED); 
        IF lcode > 128 THEN
            SET mycode =65536 - lcode * 256 - rcode ;
            SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1;
        SET lp = lp + 4;
        ELSE
SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
            SET lp = lp + 2;
        END IF;
    END WHILE;
    RETURN LOWER(mypy);
END;

3、阿拉伯數字轉漢字

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

4、隨機產生姓名的函數

使用三個字符串，存放用戶的姓名，使用隨機函數從姓名中隨機排列組合成人名。

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