当前页面位置: » 丰搜网 » 文档中心 » 详细内容
【原创】oracle数据库应用中实现汉字“同音”查询
要实现“同音”查询当然要先得到汉字的汉语拼音了，在网上随处可以找到asp的汉字转拼音的代码，如以下代码：
<% set d = createobject("scripting.dictionary") d.add "a",-20319 d.add "ai",-20317 d.add "an",-20304 d.add "ang",-20295 d.add "ao",-20292 d.add "ba",-20283 d.add "bai",-20265 d.add "ban",-20257 d.add "bang",-20242 d.add "bao",-20230 d.add "bei",-20051 d.add "ben",-20036 d.add "beng",-20032 d.add "bi",-20026 d.add "bian",-20002 d.add "biao",-19990 d.add "bie",-19986 d.add "bin",-19982 d.add "bing",-19976 d.add "bo",-19805 d.add "bu",-19784 d.add "ca",-19775 d.add "cai",-19774 d.add "can",-19763 d.add "cang",-19756 d.add "cao",-19751 d.add "ce",-19746 d.add "ceng",-19741 d.add "cha",-19739 d.add "chai",-19728 d.add "chan",-19725 d.add "chang",-19715 d.add "chao",-19540 d.add "che",-19531 d.add "chen",-19525 d.add "cheng",-19515 d.add "chi",-19500 d.add "chong",-19484 d.add "chou",-19479 d.add "chu",-19467 d.add "chuai",-19289 d.add "chuan",-19288 d.add "chuang",-19281 d.add "chui",-19275 d.add "chun",-19270 d.add "chuo",-19263 d.add "ci",-19261 d.add "cong",-19249 d.add "cou",-19243 d.add "cu",-19242 d.add "cuan",-19238 d.add "cui",-19235 d.add "cun",-19227 d.add "cuo",-19224 d.add "da",-19218 d.add "dai",-19212 d.add "dan",-19038 d.add "dang",-19023 d.add "dao",-19018 d.add "de",-19006 d.add "deng",-19003 d.add "di",-18996 d.add "dian",-18977 d.add "diao",-18961 d.add "die",-18952 d.add "ding",-18783 d.add "diu",-18774 d.add "dong",-18773 d.add "dou",-18763 d.add "du",-18756 d.add "duan",-18741 d.add "dui",-18735 d.add "dun",-18731 d.add "duo",-18722 d.add "e",-18710 d.add "en",-18697 d.add "er",-18696 d.add "fa",-18526 d.add "fan",-18518 d.add "fang",-18501 d.add "fei",-18490 d.add "fen",-18478 d.add "feng",-18463 d.add "fo",-18448 d.add "fou",-18447 d.add "fu",-18446 d.add "ga",-18239 d.add "gai",-18237 d.add "gan",-18231 d.add "gang",-18220 d.add "gao",-18211 d.add "ge",-18201 d.add "gei",-18184 d.add "gen",-18183 d.add "geng",-18181 d.add "gong",-18012 d.add "gou",-17997 d.add "gu",-17988 d.add "gua",-17970 d.add "guai",-17964 d.add "guan",-17961 d.add "guang",-17950 d.add "gui",-17947 d.add "gun",-17931 d.add "guo",-17928 d.add "ha",-17922 d.add "hai",-17759 d.add "han",-17752 d.add "hang",-17733 d.add "hao",-17730 d.add "he",-17721 d.add "hei",-17703 d.add "hen",-17701 d.add "heng",-17697 d.add "hong",-17692 d.add "hou",-17683 d.add "hu",-17676 d.add "hua",-17496 d.add "huai",-17487 d.add "huan",-17482 d.add "huang",-17468 d.add "hui",-17454 d.add "hun",-17433 d.add "huo",-17427 d.add "ji",-17417 d.add "jia",-17202 d.add "jian",-17185 d.add "jiang",-16983 d.add "jiao",-16970 d.add "jie",-16942 d.add "jin",-16915 d.add "jing",-16733 d.add "jiong",-16708 d.add "jiu",-16706 d.add "ju",-16689 d.add "juan",-16664 d.add "jue",-16657 d.add "jun",-16647 d.add "ka",-16474 d.add "kai",-16470 d.add "kan",-16465 d.add "kang",-16459 d.add "kao",-16452 d.add "ke",-16448 d.add "ken",-16433 d.add "keng",-16429 d.add "kong",-16427 d.add "kou",-16423 d.add "ku",-16419 d.add "kua",-16412 d.add "kuai",-16407 d.add "kuan",-16403 d.add "kuang",-16401 d.add "kui",-16393 d.add "kun",-16220 d.add "kuo",-16216 d.add "la",-16212 d.add "lai",-16205 d.add "lan",-16202 d.add "lang",-16187 d.add "lao",-16180 d.add "le",-16171 d.add "lei",-16169 d.add "leng",-16158 d.add "li",-16155 d.add "lia",-15959 d.add "lian",-15958 d.add "liang",-15944 d.add "liao",-15933 d.add "lie",-15920 d.add "lin",-15915 d.add "ling",-15903 d.add "liu",-15889 d.add "long",-15878 d.add "lou",-15707 d.add "lu",-15701 d.add "lv",-15681 d.add "luan",-15667 d.add "lue",-15661 d.add "lun",-15659 d.add "luo",-15652 d.add "ma",-15640 d.add "mai",-15631 d.add "man",-15625 d.add "mang",-15454 d.add "mao",-15448 d.add "me",-15436 d.add "mei",-15435 d.add "men",-15419 d.add "meng",-15416 d.add "mi",-15408 d.add "mian",-15394 d.add "miao",-15385 d.add "mie",-15377 d.add "min",-15375 d.add "ming",-15369 d.add "miu",-15363 d.add "mo",-15362 d.add "mou",-15183 d.add "mu",-15180 d.add "na",-15165 d.add "nai",-15158 d.add "nan",-15153 d.add "nang",-15150 d.add "nao",-15149 d.add "ne",-15144 d.add "nei",-15143 d.add "nen",-15141 d.add "neng",-15140 d.add "ni",-15139 d.add "nian",-15128 d.add "niang",-15121 d.add "niao",-15119 d.add "nie",-15117 d.add "nin",-15110 d.add "ning",-15109 d.add "niu",-14941 d.add "nong",-14937 d.add "nu",-14933 d.add "nv",-14930 d.add "nuan",-14929 d.add "nue",-14928 d.add "nuo",-14926 d.add "o",-14922 d.add "ou",-14921 d.add "pa",-14914 d.add "pai",-14908 d.add "pan",-14902 d.add "pang",-14894 d.add "pao",-14889 d.add "pei",-14882 d.add "pen",-14873 d.add "peng",-14871 d.add "pi",-14857 d.add "pian",-14678 d.add "piao",-14674 d.add "pie",-14670 d.add "pin",-14668 d.add "ping",-14663 d.add "po",-14654 d.add "pu",-14645 d.add "qi",-14630 d.add "qia",-14594 d.add "qian",-14429 d.add "qiang",-14407 d.add "qiao",-14399 d.add "qie",-14384 d.add "qin",-14379 d.add "qing",-14368 d.add "qiong",-14355 d.add "qiu",-14353 d.add "qu",-14345 d.add "quan",-14170 d.add "que",-14159 d.add "qun",-14151 d.add "ran",-14149 d.add "rang",-14145 d.add "rao",-14140 d.add "re",-14137 d.add "ren",-14135 d.add "reng",-14125 d.add "ri",-14123 d.add "rong",-14122 d.add "rou",-14112 d.add "ru",-14109 d.add "ruan",-14099 d.add "rui",-14097 d.add "run",-14094 d.add "ruo",-14092 d.add "sa",-14090 d.add "sai",-14087 d.add "san",-14083 d.add "sang",-13917 d.add "sao",-13914 d.add "se",-13910 d.add "sen",-13907 d.add "seng",-13906 d.add "sha",-13905 d.add "shai",-13896 d.add "shan",-13894 d.add "shang",-13878 d.add "shao",-13870 d.add "she",-13859 d.add "shen",-13847 d.add "sheng",-13831 d.add "shi",-13658 d.add "shou",-13611 d.add "shu",-13601 d.add "shua",-13406 d.add "shuai",-13404 d.add "shuan",-13400 d.add "shuang",-13398 d.add "shui",-13395 d.add "shun",-13391 d.add "shuo",-13387 d.add "si",-13383 d.add "song",-13367 d.add "sou",-13359 d.add "su",-13356 d.add "suan",-13343 d.add "sui",-13340 d.add "sun",-13329 d.add "suo",-13326 d.add "ta",-13318 d.add "tai",-13147 d.add "tan",-13138 d.add "tang",-13120 d.add "tao",-13107 d.add "te",-13096 d.add "teng",-13095 d.add "ti",-13091 d.add "tian",-13076 d.add "tiao",-13068 d.add "tie",-13063 d.add "ting",-13060 d.add "tong",-12888 d.add "tou",-12875 d.add "tu",-12871 d.add "tuan",-12860 d.add "tui",-12858 d.add "tun",-12852 d.add "tuo",-12849 d.add "wa",-12838 d.add "wai",-12831 d.add "wan",-12829 d.add "wang",-12812 d.add "wei",-12802 d.add "wen",-12607 d.add "weng",-12597 d.add "wo",-12594 d.add "wu",-12585 d.add "xi",-12556 d.add "xia",-12359 d.add "xian",-12346 d.add "xiang",-12320 d.add "xiao",-12300 d.add "xie",-12120 d.add "xin",-12099 d.add "xing",-12089 d.add "xiong",-12074 d.add "xiu",-12067 d.add "xu",-12058 d.add "xuan",-12039 d.add "xue",-11867 d.add "xun",-11861 d.add "ya",-11847 d.add "yan",-11831 d.add "yang",-11798 d.add "yao",-11781 d.add "ye",-11604 d.add "yi",-11589 d.add "yin",-11536 d.add "ying",-11358 d.add "yo",-11340 d.add "yong",-11339 d.add "you",-11324 d.add "yu",-11303 d.add "yuan",-11097 d.add "yue",-11077 d.add "yun",-11067 d.add "za",-11055 d.add "zai",-11052 d.add "zan",-11045 d.add "zang",-11041 d.add "zao",-11038 d.add "ze",-11024 d.add "zei",-11020 d.add "zen",-11019 d.add "zeng",-11018 d.add "zha",-11014 d.add "zhai",-10838 d.add "zhan",-10832 d.add "zhang",-10815 d.add "zhao",-10800 d.add "zhe",-10790 d.add "zhen",-10780 d.add "zheng",-10764 d.add "zhi",-10587 d.add "zhong",-10544 d.add "zhou",-10533 d.add "zhu",-10519 d.add "zhua",-10331 d.add "zhuai",-10329 d.add "zhuan",-10328 d.add "zhuang",-10322 d.add "zhui",-10315 d.add "zhun",-10309 d.add "zhuo",-10307 d.add "zi",-10296 d.add "zong",-10281 d.add "zou",-10274 d.add "zu",-10270 d.add "zuan",-10262 d.add "zui",-10260 d.add "zun",-10256 d.add "zuo",-10254 function g(num) if num>0 and num<160 then g=chr(num) else if num<-20319 or num>-10247 then g="" else a=d.items b=d.keys for i=d.count-1 to 0 step -1 if a(i)<=num then exit for next g=b(i) end if end if end function function c(str) c="" for i=1 to len(str) c=c&g(asc(mid(str,i,1))) next end function response.write c(request("hz")) %> <form method=post> 请在此处输入中文：<input name=hz> </form>
以上代码的原理十分简单，这里我不多说了，下面我们把上面的码表导入oracle表中，不过要注意一下，oracle中内置的ascii()函数于vb中的asc()函数的返回值范围不同，我们需要加上65536得出的才是oracle中汉字的ascii码，如：vb中asc("啊")=-20319， 在oracle中ascii("啊")=-20319+65536=45217。以下是生成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把此字符串当作双字节来处理。代码如下：
create or replace function getpinyin(keyword in varchar2) return varchar2 isbegindeclare 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:=pinyintemp; i:=i+1; end loop; return(pinyin);end;end getpinyin;
好了，现在可以用这个函数来实现汉字的同音查询了。如查询表table1中字段a中读音和“啊”一样的记录，sql语句可以这样写：
select * from table1 where getpinyin(a) like '%a%'
以上方法仅供参考，如有不妥请批评指正，谢谢！
