【原创】oracle数据库应用中实现汉字“同音”查询_ORACLE_学习笔记★闵涛★计算机学习电脑编程软硬件技巧


	转至繁体中文版	\| 网站首页 \| 图文教程 \| 资源下载 \| 站长博客 \| 图片素材 \| 武汉seo \| 武汉网站优化 \|

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

作者：闵涛文章来源：闵涛的学习笔记点击数：3367 更新时间：2009/4/22 22:03:45

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