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