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

1.全拼

?

CREATE OR REPLACE FUNCTION TOASPELL (Name in varchar2) return Varchar2 DETERMINISTIC   is  Result varchar2(200);  r varchar2(200) ;beginp_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) ;beginp_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;

?

?

?

?

?