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;
?
?
?
?
?
