ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle数据库应用中实现汉字“同音”查询(转)

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

原创 Linux操作系统 作者:jcszjswkzhou 时间:2019-02-22 11:21:04 0 删除 编辑
oracle数据库应用中实现汉字“同音”查询,sql,sql教程,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")) %>
请在此处输入中文:


以上代码的原理十分简单这里我不多说了下面我们把上面的码表导入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:=PinYin||Temp; i:=i+1; End loop; return(PinYin);end;end GetPinYin;

好了现在可以用这个函数来实现汉字的同音查询了如查询表table1中字段A中读音和“啊”一样的记录SQL语句可以这样写:

select * from table1 where getpinyin(A) like '%a%'

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10294527/viewspace-122194/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-08-29

  • 博文量
    3488
  • 访问量
    2621607