suchen

Heim >Datenbank >MySQL-Tutorial >Oracle 根据汉字返回拼音函数

Oracle 根据汉字返回拼音函数

WBOY
WBOYOriginal
2016-06-07 16:59:221335Durchsuche

oracle 根据汉字返回拼音函数，研究了一个多小时，才弄出来；上来贴代码吧。。 --------------Type Definition CREATE OR REPLAC

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 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

      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 -10247) Then

        lv_spell := lv_spell || '*';

      Else

        select max(spell)

          Into lv_temp

          from table(f_getSpellcode)

         where code

        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 -10247) Then

        lv_spell := lv_spell || '*';

      Else

        select max(spell)

          Into lv_temp

          from table(f_getSpellcode)

         where code

        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('安徽理工大学')
------------------------------------------------------------------------------

ahlgdx

SQL> select f_getspell('安徽理工大学',1) from dual;

F_GETSPELL('安徽理工大学',1)
----------------------------------------------------------------------------

anhuiligongdaxue

linux

Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：在CentOS 5.5安装Oracle 11.2Nächster Artikel：在 VMWare ESX4.1 中以 Oracle Linux5.5(x64) 安装 3 节点 Oracle 11gr2

In Verbindung stehende Artikel

Mehr sehen