首先我们介绍一下存储过程的参数，然后通过一个代码实例来介绍这一过程的实现。接下来我们就开始学习吧。
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_templv_temp:=lv_temp||lv_spell; if i<length(v_string) then lv_templv_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.汉字字典表
本文就介绍到这里，希望通过上面的这个例子能够给各位带来一些收获，谢谢大家！