create or replace procedure usp_yy_getpyzt( inputstr in varchar2, pybz in number, outputstr out varchar2) as /********** [版本号]1.0.0.0.0 [创建时间]2011.07.20 [描述]生成单个拼音或五笔 [功能说明] 拼音和五笔字头 [参数说明] @inputstr varchar(255), --输入汉字 @pybz smallint, --0:生成拼音 1:生成五笔 @outputstr varchar(255) output --输出的拼音和五笔 hzpytmp 字符对照表[返回值] [结果集、排序] [调用的usp] 无 [调用实例] **********/ v_chzchar varchar2(2); v_chz varchar2(2); v_cpy varchar2(255); v_i number(8); v_bsm varchar2(2); v_exists number(2); begin v_cpy:=''; v_chz:=''; v_chzchar:=''; v_i:=1; v_exists:=0; while v_i<=length(inputstr) loop v_chzchar:=substr(inputstr,v_i,1); v_chzchar:=lower(v_chzchar); if ascii(v_chzchar)>127 then begin if v_chz is null then v_chz:=v_chzchar; else v_chz:=v_chz||v_chzchar; end if; end; elsif v_chzchar is null then begin exit; end; elsif ascii(v_chzchar) between 48 and 57 then begin v_cpy:=v_cpy||(case ascii(v_chzchar) when 48 then 'l' when 49 then 'y' when 50 then 'e' when 51 then 's' when 52 then 's' when 53 then 'w' when 54 then 'l' when 55 then 'q' when 56 then 'b' when 57 then 'j' end) ; v_chz:=''; end; elsif (ascii(v_chzchar) between 65 and 90) or (ascii(v_chzchar) between 97 and 122) then begin v_cpy:=v_cpy||trim(lower(v_chzchar)); v_chz:=''; end; end if; if lengthb(v_chz)=2 then begin select 1 into v_exists from hzpytmp where bhz=v_chz; if sql%rowcount>0 then begin if v_exists =1 then begin if pybz=0 then select bsm into v_bsm from hzpytmp where bhz=v_chz; else select wbm into v_bsm from hzpytmp where bhz=v_chz; end if; v_cpy:=v_cpy||trim(to_char(v_bsm)); end; end if; end; end if; v_chz:=''; end; end if; v_i:=v_i+1; end loop; outputstr:=trim(v_cpy); end;
这个你可以参考一下http://download.csdn.net/download/yzl600/3192736
create or replace procedure usp_yy_getpyzt(
inputstr in varchar2,
pybz in number,
outputstr out varchar2)
as
/**********
[版本号]1.0.0.0.0
[创建时间]2011.07.20
[描述]生成单个拼音或五笔
[功能说明]
拼音和五笔字头
[参数说明]
@inputstr varchar(255), --输入汉字
@pybz smallint, --0:生成拼音 1:生成五笔
@outputstr varchar(255) output --输出的拼音和五笔
hzpytmp 字符对照表[返回值]
[结果集、排序]
[调用的usp] 无
[调用实例]
**********/
v_chzchar varchar2(2);
v_chz varchar2(2);
v_cpy varchar2(255);
v_i number(8);
v_bsm varchar2(2);
v_exists number(2);
begin
v_cpy:='';
v_chz:='';
v_chzchar:='';
v_i:=1;
v_exists:=0;
while v_i<=length(inputstr)
loop
v_chzchar:=substr(inputstr,v_i,1);
v_chzchar:=lower(v_chzchar);
if ascii(v_chzchar)>127 then
begin
if v_chz is null then
v_chz:=v_chzchar;
else
v_chz:=v_chz||v_chzchar;
end if;
end;
elsif v_chzchar is null then
begin
exit;
end;
elsif ascii(v_chzchar) between 48 and 57 then
begin
v_cpy:=v_cpy||(case ascii(v_chzchar) when 48 then 'l' when 49 then 'y' when 50 then 'e'
when 51 then 's' when 52 then 's' when 53 then 'w' when 54 then 'l' when 55 then 'q'
when 56 then 'b' when 57 then 'j' end) ;
v_chz:='';
end;
elsif (ascii(v_chzchar) between 65 and 90) or (ascii(v_chzchar) between 97 and 122) then
begin
v_cpy:=v_cpy||trim(lower(v_chzchar));
v_chz:='';
end;
end if;
if lengthb(v_chz)=2 then
begin
select 1 into v_exists from hzpytmp where bhz=v_chz;
if sql%rowcount>0 then
begin
if v_exists =1 then
begin
if pybz=0 then
select bsm into v_bsm from hzpytmp where bhz=v_chz;
else
select wbm into v_bsm from hzpytmp where bhz=v_chz;
end if;
v_cpy:=v_cpy||trim(to_char(v_bsm));
end;
end if;
end;
end if;
v_chz:='';
end;
end if;
v_i:=v_i+1;
end loop;
outputstr:=trim(v_cpy);
end;