ORACLE之拼音检索（任意字符）

最新推荐文章于 2023-01-09 12:25:08 发布

原创最新推荐文章于 2023-01-09 12:25:08 发布 · 1.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #string #application #null #c

这篇博客介绍了如何利用Oracle存储过程结合汉字字典表，实现对任意字符的快速拼音检索，详细阐述了存储过程的参数说明及汉字字典表的使用方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

存储过程结合汉字字典表实现全字符快速检索：

1.储存过程

参数说明：

--v_string 输入字符，支持任意字符

--v_tablename 表名

--v_fieldname 字段名

--v_number 默认显示数量

--outCursor 以游标的形式返回结果集

create or replace procedure pro_pyquery(v_string In varchar2 default null,
v_tablename In varchar2,v_fieldname in varchar2,v_number in number,
outCursor out SYS_REFCURSOR)
as
  lv_spell varchar2(2000);
  lv_temp string(32000);
  lv_char varchar2(100);
  --no_string exception;
begin
  --if v_string is null then
  --raise no_string;
  --end if;
lv_temp:='select distinct '||v_fieldname||' from '||v_tablename||' where ';
for i In 1..length(v_string) loop
lv_char:=substr(v_string,i,1);
if upper(lv_char)='A' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''A''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''a'')) and rownum<='||v_number;
end if;
if upper(lv_char)='B' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''B''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''b'')) and rownum<='||v_number;

end if;
if upper(lv_char)='C' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''C''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''c'')) and rownum<='||v_number;

end if;
if upper(lv_char)='D' then
lv_spell:='
((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''D''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''d'')) and rownum<='||v_number;

end if;
if upper(lv_char)='E' then

( (substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''E''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''e'')) and rownum<='||v_number;

end if;
if upper(lv_char)='F' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''F''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''f'')) and rownum<='||v_number;

end if;
if upper(lv_char)='G' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''G''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''g'')) and rownum<='||v_number;
end if;
if upper(lv_char)='H' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''H''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''h'')) and rownum<='||v_number;

end if;
if upper(lv_char)='I' then
lv_spell:='(substr(lower('||v_fieldname||'),'||i||',1)=''i'') and rownum<='||v_number;
end if;
if upper(lv_char)='J' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''J''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''j'')) and rownum<='||v_number;

end if;
if upper(lv_char)='K' then
lv_spell:=' ((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''K''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''k'')) and rownum<='||v_number;

end if;
if upper(lv_char)='L' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''L''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''l'')) and rownum<='||v_number;

end if;
if upper(lv_char)='M' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''M''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''m'')) and rownum<='||v_number;

end if;
if upper(lv_char)='N' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''N''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''n'')) and rownum<='||v_number;

end if;
if upper(lv_char)='O' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''O''))
 or
(substr(lower('||v_fieldname||'),'||i||',1)=''o''))
and rownum<='||v_number;

end if;
if upper(lv_char)='P' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''P''))
 or
(substr(lower('||v_fieldname||'),'||i||',1)=''p''))
and rownum<='||v_number;

end if;
if upper(lv_char)='Q' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''Q''))
 or
(substr(lower('||v_fieldname||'),'||i||',1)=''q''))
and rownum<='||v_number;

end if;
if upper(lv_char)='R' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''R''))
 or
(substr(lower('||v_fieldname||'),'||i||',1)=''r''))
and rownum<='||v_number;

end if;
if upper(lv_char)='S' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''S''))
 or
(substr(lower('||v_fieldname||'),'||i||',1)=''s''))
and rownum<='||v_number;

end if;
if upper(lv_char)='T' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''T''))
 or
(substr(lower('||v_fieldname||'),'||i||',1)=''t''))
and rownum<='||v_number;

end if;
if upper(lv_char)='U' then
lv_spell:='(substr(lower('||v_fieldname||'),'||i||',1)=''u'') and rownum<='||v_number;
end if;
if upper(lv_char)='V' then
--open outCursor for select * from dual where 1=2;
lv_spell:='(substr(lower('||v_fieldname||'),'||i||',1)=''v'')
and rownum<='||v_number;
end if;
if upper(lv_char)='W' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''W''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''w'')) and rownum<='||v_number;

end if;
if upper(lv_char)='X' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''X''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''x'')) and rownum<='||v_number;

end if;
if upper(lv_char)='Y' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''Y''))
 or
(substr(lower('||v_fieldname||'),'||i||',1)=''y''))
and rownum<='||v_number;

end if;
if upper(lv_char)='Z' then
lv_spell:='((substr('||v_fieldname||','||i||',1) in (select hzmc from hzzd where hzszm=''Z''))
 or (substr(lower('||v_fieldname||'),'||i||',1)=''z'')) and rownum<='||v_number;

end if;
if ascii(upper(lv_char))>90 or ascii(upper(lv_char))<65 then
lv_spell:='substr('||v_fieldname||','||i||',1)='''||lv_char||''' and rownum<='||v_number;
end if;
lv_temp:=lv_temp||lv_spell;
if i<length(v_string) then
lv_temp:=lv_temp||' and ';
end if;
end loop;
open outCursor for lv_temp;
/*exception
when no_string then
--raise_application_error(-20001,'您未输入任何字符');
open outCursor for select * from dual where 1=2;
rollback;*/
exception
when others then
lv_spell:='select distinct '||v_fieldname||' from '||v_tablename||' where rownum<='||v_number;
open outCursor for lv_spell;
rollback;
end;

2.汉字字典表