最近请DBA美女帮忙找获取汉字拼音的ORACLE的函数。终于找到一个,参考了如下BLOG。
http://hanjs.itpub.net/category/28217/44772
执行的效果就是
select name,f_getspell(name),f_getspell(name,1) from roles
得到结果:
后台管理 htgl houtaiguanli 嘿嘿,完全符合了项目要求。只是要注意,里面的lv和luo,nv和nuo有时不分,要做处理。
建立函数的脚本清单如下:
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 Pipelined
is
Begin
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));
[NextPage]
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));
责任编辑:小草