oracle数据库应用中实现汉字“同音”查询

　2008-10-25 12:58:08　来源：WEB开发网　　　

核心提示： 以上代码的原理十分简单，这里我不多说了，oracle数据库应用中实现汉字“同音”查询(2)，下面我们把上面的码表导入oracle表中，不过要注意一下，与200万技术人员互动交流 >>进入代码如下：create or replace function GetPinYin(Keyw

以上代码的原理十分简单，这里我不多说了，下面我们把上面的码表导入oracle表中，不过要注意一下，oracle中内置的ASCII()函数于VB中的ASC()函数的返回值范围不同，我们需要加上65536得出的才是oracle中汉字的ascii码，如：VB中ASC("啊")=-20319，在oracle中ASCII("啊")=-20319+65536=45217。

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入

以下是生成oracle码表的脚本。

/*TABLE NAME:T_PINYIN*/ /*CREATE TIME:2005-2-19 0:19:26*/ 　　 CREATE table BJXKS."T_PINYIN"　( 　 HZPY　　　　　　　　 VARCHAR(10), 　 NUM　　　　　　　　　INT ) / 　　 INSERT INTO T_PINYIN(HZPY,NUM) VALUES('★','41455') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('*','255') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('*','1') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('a','45217') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ai','45219') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('an','45232') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ang','45241') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ao','45244') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ba','45253') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bai','45271') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ban','45279') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bang','45294') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bao','45306') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bei','45485') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ben','45500') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('beng','45504') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bi','45510') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bian','45534') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('biao','45546') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bie','45550') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bin','45554') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bing','45560') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bo','45731') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('bu','45752') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ca','45761') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cai','45762') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('can','45773') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cang','45780') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cao','45785') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ce','45790') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ceng','45795') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cha','45797') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chai','45808') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chan','45811') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chang','45821') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chao','45996') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('che','46005') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chen','46011') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cheng','46021') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chi','46036') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chong','46052') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chou','46057') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chu','46069') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chuai','46247') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chuan','46248') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chuang','46255') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chui','46261') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chun','46266') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('chuo','46273') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ci','46275') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cong','46287') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cou','46293') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cu','46294') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cuan','46298') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cui','46301') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cun','46309') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('cuo','46312') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('da','46318') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dai','46324') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dan','46498') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dang','46513') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dao','46518') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('de','46530') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('deng','46533') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('di','46540') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dian','46559') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('diao','46575') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('die','46584') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ding','46753') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('diu','46762') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dong','46763') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dou','46773') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('du','46780') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('duan','46795') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dui','46801') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('dun','46805') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('duo','46814') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('e','46826') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('en','46839') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('er','46840') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('fa','47010') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('fan','47018') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('fang','47035') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('fei','47046') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('fen','47058') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('feng','47073') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('fo','47088') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('fou','47089') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('fu','47090') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ga','47297') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gai','47299') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gan','47305') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gang','47316') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gao','47325') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ge','47335') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gei','47352') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gen','47353') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('geng','47355') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gong','47524') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gou','47539') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gu','47548') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gua','47566') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('guai','47572') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('guan','47575') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('guang','47586') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gui','47589') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('gun','47605') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('guo','47608') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ha','47614') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hai','47777') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('han','47784') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hang','47803') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hao','47806') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('he','47815') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hei','47833') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hen','47835') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('heng','47839') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hong','47844') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hou','47853') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hu','47860') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hua','48040') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('huai','48049') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('huan','48054') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('huang','48068') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hui','48082') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('hun','48103') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('huo','48109') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ji','48119') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jia','48334') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jian','48351') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jiang','48553') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jiao','48566') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jie','48594') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jin','48621') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jing','48803') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jiong','48828') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jiu','48830') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ju','48847') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('juan','48872') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jue','48879') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('jun','48889') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ka','49062') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kai','49066') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kan','49071') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kang','49077') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kao','49084') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ke','49088') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ken','49103') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('keng','49107') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kong','49109') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kou','49113') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ku','49117') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kua','49124') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kuai','49129') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kuan','49133') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kuang','49135') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kui','49143') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kun','49316') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('kuo','49320') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('la','49324') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lai','49331') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lan','49334') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lang','49349') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lao','49356') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('le','49365') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lei','49367') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('leng','49378') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('li','49381') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lia','49577') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lian','49578') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('liang','49592') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('liao','49603') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lie','49616') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lin','49621') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ling','49633') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('liu','49647') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('long','49658') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lou','49829') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lu','49835') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lv','49855') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('luan','49869') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lue','49875') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('lun','49877') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('luo','49884') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ma','49896') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mai','49905') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('man','49911') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mang','50082') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mao','50088') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('me','50100') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mei','50101') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('men','50117') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('meng','50120') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mi','50128') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mian','50142') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('miao','50151') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mie','50159') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('min','50161') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ming','50167') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('miu','50173') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mo','50174') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mou','50353') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('mu','50356') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('na','50371') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nai','50378') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nan','50383') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nang','50386') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nao','50387') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ne','50392') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nei','50393') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nen','50395') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('neng','50396') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ni','50397') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nian','50408') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('niang','50415') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('niao','50417') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nie','50419') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nin','50426') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ning','50427') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('niu','50595') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nong','50599') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nu','50603') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nv','50606') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nuan','50607') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nue','50608') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('nuo','50610') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('o','50614') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ou','50615') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pa','50622') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pai','50628') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pan','50634') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pang','50642') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pao','50647') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pei','50654') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pen','50663') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('peng','50665') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pi','50679') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pian','50858') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('piao','50862') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pie','50866') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pin','50868') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ping','50873') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('po','50882') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('pu','50891') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qi','50906') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qia','50942') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qian','51107') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qiang','51129') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qiao','51137') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qie','51152') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qin','51157') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qing','51168') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qiong','51181') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qiu','51183') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qu','51191') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('quan','51366') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('que','51377') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('qun','51385') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ran','51387') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('rang','51391') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('rao','51396') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('re','51399') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ren','51401') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('reng','51411') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ri','51413') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('rong','51414') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('rou','51424') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ru','51427') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ruan','51437') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('rui','51439') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('run','51442') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ruo','51444') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sa','51446') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sai','51449') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('san','51453') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sang','51619') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sao','51622') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('se','51626') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sen','51629') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('seng','51630') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sha','51631') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shai','51640') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shan','51642') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shang','51658') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shao','51666') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('she','51677') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shen','51689') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sheng','51705') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shi','51878') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shou','51925') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shu','51935') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shua','52130') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shuai','52132') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shuan','52136') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shuang','52138') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shui','52141') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shun','52145') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('shuo','52149') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('si','52153') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('song','52169') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sou','52177') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('su','52180') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('suan','52193') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sui','52196') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('sun','52207') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('suo','52210') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ta','52218') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tai','52389') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tan','52398') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tang','52416') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tao','52429') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('te','52440') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('teng','52441') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ti','52445') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tian','52460') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tiao','52468') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tie','52473') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ting','52476') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tong','52648') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tou','52661') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tu','52665') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tuan','52676') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tui','52678') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tun','52684') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('tuo','52687') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('wa','52698') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('wai','52705') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('wan','52707') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('wang','52724') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('wei','52734') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('wen','52929') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('weng','52939') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('wo','52942') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('wu','52951') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xi','52980') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xia','53177') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xian','53190') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xiang','53216') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xiao','53236') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xie','53416') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xin','53437') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xing','53447') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xiong','53462') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xiu','53469') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xu','53478') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xuan','53497') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xue','53669') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('xun','53675') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ya','53689') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yan','53705') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yang','53738') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yao','53755') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ye','53932') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yi','53947') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yin','54000') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ying','54178') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yo','54196') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yong','54197') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('you','54212') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yu','54233') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yuan','54439') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yue','54459') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('yun','54469') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('za','54481') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zai','54484') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zan','54491') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zang','54495') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zao','54498') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('ze','54512') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zei','54516') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zen','54517') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zeng','54518') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zha','54522') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhai','54698') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhan','54704') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhang','54721') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhao','54736') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhe','54746') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhen','54756') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zheng','54772') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhi','54949') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhong','54992') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhou','55003') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhu','55017') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhua','55205') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhuai','55207') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhuan','55208') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhuang','55214') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhui','55221') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhun','55227') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zhuo','55229') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zi','55240') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zong','55255') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zou','55262') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zu','55266') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zuan','55274') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zui','55276') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zun','55280') / INSERT INTO T_PINYIN(HZPY,NUM) VALUES('zuo','55282') /

接下来我们要编写一个函数来实现获取汉字拼音编码。在编写这个函数的过程中我发现了oracle处理中英文混合字符串的bug，因此函数中采用在任何字符串前添加一个★号来强制oracle把此字符串当作双字节来处理。

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入

代码如下：

create or replace function GetPinYin(Keyword in varchar2) return varchar2 is begin DECLARE 　i int; 　j int; 　PinYin varchar2(500); 　Temp varchar2(10); 　TempStr varchar2(2); begin　　i:=1; 　j:=Length('★'||Keyword); 　PinYin:=''; 　While i<=j LOOP 　　TempStr:=substr('★'||KeyWord,i,1); 　　select HZPY INTO Temp from BJXKS.t_Pinyin Where Num=(select max(num) from BJXKS.t_Pinyin Where Num<=ASCII(TempStr)); 　　Temp:=Replace(Temp,'*',TempStr); 　　Temp:=Replace(Temp,'★',''); 　　PinYin:=PinYin||Temp; 　　i:=i+1; 　End loop; 　return(PinYin); end; end GetPinYin;