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