oracle汉字转拼音函数.doc

第一种方法：（首字母/全拼）create or replace type spell_code as object(spell varchar2(10),code number);create or replace type t_spellcode is table of spell_code;-返回拼音与代码的对应关系create or replace function f_getSpellcode return t_spellcode PipelinedisBegin PIPE 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;-hanjs,07-10-24,返回拼音与代码的对应关系/-此函数默认返回汉字拼音的首字母，第二个参数不为空则返回全拼。create or replace function f_getFirstOrFullSpell(p_cnStr In varchar2,p_sign In number default null) return varchar2as 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