如何用存储过程实现拼音检索任意字符

首先我们介绍一下存储过程的参数，然后通过一个代码实例来介绍这一过程的实现。接下来我们就开始学习吧。

1. 存储过程

参数说明：

v_string 输入字符，支持任意字符v_tablename 表名v_fieldname 字段名v_number 默认显示数量outCursor 以游标的形式返回结果集

该实例的实现代码如下：

createorreplaceprocedurepro_pyquery(v_stringInvarchar2defaultnull, v_tablenameInvarchar2,v_fieldnameinvarchar2,v_numberinnumber, outCursoroutSYS_REFCURSOR) as lv_spellvarchar2(2000); lv_tempstring(32000); lv_charvarchar2(100); --no_stringexception; begin --ifv_stringisnullthen --raiseno_string; --endif; lv_temp:='selectdistinct'||v_fieldname||'from'||v_tablename||'where'; foriIn1..length(v_string)loop lv_char:=substr(v_string,i,1); ifupper(lv_char)='A'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''A'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''a''))andrownum<='||v_number; endif; ifupper(lv_char)='B'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''B'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''b''))andrownum<='||v_number; endif; ifupper(lv_char)='C'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''C'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''c''))andrownum<='||v_number; endif; ifupper(lv_char)='D'then lv_spell:=' ((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''D'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''d''))andrownum<='||v_number; endif; ifupper(lv_char)='E'then ((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''E'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''e''))andrownum<='||v_number; endif; ifupper(lv_char)='F'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''F'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''f''))andrownum<='||v_number; endif; ifupper(lv_char)='G'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''G'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''g''))andrownum<='||v_number; endif; ifupper(lv_char)='H'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''H'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''h''))andrownum<='||v_number; endif; ifupper(lv_char)='I'then lv_spell:='(substr(lower('||v_fieldname||'),'||i||',1)=''i'')andrownum<='||v_number; endif; ifupper(lv_char)='J'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''J'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''j''))andrownum<='||v_number; endif; ifupper(lv_char)='K'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''K'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''k''))andrownum<='||v_number; endif; ifupper(lv_char)='L'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''L'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''l''))andrownum<='||v_number; endif; ifupper(lv_char)='M'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''M'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''m''))andrownum<='||v_number; endif; ifupper(lv_char)='N'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''N'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''n''))andrownum<='||v_number; endif; ifupper(lv_char)='O'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''O'')) or (substr(lower('||v_fieldname||'),'||i||',1)=''o'')) andrownum<='||v_number; endif; ifupper(lv_char)='P'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''P'')) or (substr(lower('||v_fieldname||'),'||i||',1)=''p'')) andrownum<='||v_number; endif; ifupper(lv_char)='Q'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''Q'')) or (substr(lower('||v_fieldname||'),'||i||',1)=''q'')) andrownum<='||v_number; endif; ifupper(lv_char)='R'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''R'')) or (substr(lower('||v_fieldname||'),'||i||',1)=''r'')) andrownum<='||v_number; endif; ifupper(lv_char)='S'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''S'')) or (substr(lower('||v_fieldname||'),'||i||',1)=''s'')) andrownum<='||v_number; endif; ifupper(lv_char)='T'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''T'')) or (substr(lower('||v_fieldname||'),'||i||',1)=''t'')) andrownum<='||v_number; endif; ifupper(lv_char)='U'then lv_spell:='(substr(lower('||v_fieldname||'),'||i||',1)=''u'')andrownum<='||v_number; endif; ifupper(lv_char)='V'then --openoutCursorforselect*fromdualwhere1=2; lv_spell:='(substr(lower('||v_fieldname||'),'||i||',1)=''v'') andrownum<='||v_number; endif; ifupper(lv_char)='W'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''W'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''w''))andrownum<='||v_number; endif; ifupper(lv_char)='X'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''X'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''x''))andrownum<='||v_number; endif; ifupper(lv_char)='Y'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''Y'')) or (substr(lower('||v_fieldname||'),'||i||',1)=''y'')) andrownum<='||v_number; endif; ifupper(lv_char)='Z'then lv_spell:='((substr('||v_fieldname||','||i||',1)in(selecthzmcfromhzzdwherehzszm=''Z'')) or(substr(lower('||v_fieldname||'),'||i||',1)=''z''))andrownum<='||v_number; endif; ifascii(upper(lv_char))>90orascii(upper(lv_char))<65then lv_spell:='substr('||v_fieldname||','||i||',1)='''||lv_char||'''andrownum<='||v_number; endif; lv_templv_temp:=lv_temp||lv_spell; ifi<length(v_string)then lv_templv_temp:=lv_temp||'and'; endif; endloop; openoutCursorforlv_temp; /*exception whenno_stringthen --raise_application_error(-20001,'您未输入任何字符'); openoutCursorforselect*fromdualwhere1=2; rollback;*/ exception whenothersthen lv_spell:='selectdistinct'||v_fieldname||'from'||v_tablename||'whererownum<='||v_number; openoutCursorforlv_spell; rollback; end;

2.汉字字典表

本文就介绍到这里，希望通过上面的这个例子能够给各位带来一些收获，谢谢大家！

【编辑推荐】

Java和Ibatis调用存储过程并取得返回值详解一个SQLite数据库修改和删除数据的代码实例查询数据量大时，关联表查询与循环查询哪个更好一个SQLite数据库的ORDER BY和GROUP BY代码实例利用PL/SQLDeveloper将CSV数据导入ORACLE对应表中