oracle获取汉字拼音和拼音首字母

select name,f_getspell(name),f_getspell(name,1) from roles
得到结果：
后台管理 htgl houtaiguanli  

要注意，里面的lv和luo，nv和nuo有时不分
同时部分字认不出来 如亳州市中的亳 *zs *zhoushi

建立函数的脚本清单如下：

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)); 
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--支持ZHS16GBK 
--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--打算支持utf8编码的 
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; 
--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; 
end if; 
end loop; 
return lv_spell; 
end;