oracle自定义函数(全拼和简拼)

1.全拼

 

CREATE OR REPLACE FUNCTION TOASPELL (Name in varchar2) return Varchar2 DETERMINISTIC   is
  Result varchar2(200);

  r varchar2(200) ;
begin

p_spell(name ,r ,result) ;

  return(result);
end toaSpell;

 

2.简拼

 

CREATE OR REPLACE FUNCTION TOSPELL (Name in varchar2) return Varchar2 DETERMINISTIC   is
  Result varchar2(200);

  r varchar2(200) ;
begin

p_spell(name ,r ,result) ;

  return(r);
end toSpell;

 

3.存储过程的实现

 

CREATE OR REPLACE PROCEDURE P_SPELL (
  p_in_string  in   varchar2,
  p_out_spell  out  varchar2,
  p_out_aspell out  varchar2
) is
--*******************************************************
--模块功能：对输入的汉字
--输入参数：  p_in_string    输入字符串
--输出参数：  p_out_spell    输出简拼
--            p_out_aspell   输出全拼
--*******************************************************
  v_in_string         varchar2(200);
  v_in_char           varchar2(200);
  v_out_spell_temp    varchar2(200);
  v_out_spell         varchar2(200);
  v_out_aspell_temp   varchar2(200);
  v_out_aspell        varchar2(200);
  V_i                 integer;
  v_ascii             integer;
  v_word_number       integer;
begin
  v_i := 0;
  v_out_spell :=null;
  v_out_aspell :=null;
  v_in_string := ltrim(rtrim(p_in_string));
  while (v_i<length(v_in_string))
loop
  v_i:=v_i+1;
    v_in_char := Substr(v_in_string,v_i,1);
  select ascii(v_in_char) into v_ascii from dual;
  if v_ascii <> 32 then
     select count(word) into v_word_number from spell where word = v_in_char;
    if v_word_number>0 then
     select spell into v_out_spell_temp from spell where word = v_in_char;
     select aspell into v_out_aspell_temp from spell where word = v_in_char;
     v_out_spell := v_out_spell || v_out_spell_temp;
     v_out_aspell := v_out_aspell || v_out_aspell_temp;
    end if;
  end if;
end loop;

p_out_spell:=v_out_spell;
p_out_aspell:=v_out_aspell;
end P_SPELL;