Oracle 根据汉字返回拼音函数

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

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（1）

评论声明

尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论