create or replace function f_ywm(chinese1 in varchar2)
return varchar2
/*
說明：此函數(shù)用于獲取漢字的拼音聲母，本函數(shù)只生成前10位漢字音序碼。
用法：將要處理的字符作為參數(shù)傳給此函數(shù)。
示例：
SQL> select f_ywm('我愛中華') from dual ;
輸出：WAZH
*/
is
ls_one_hz varchar2(10);
ls_temp_py varchar2(100);
li_length integer;
i integer;
j integer;
li_count integer;
ls_py varchar2(100);
begin
li_length := length(rtrim(ltrim(chinese1)));
if nvl(li_length,0)=0 then
li_length:=0;
end if;
ls_py := '';
ls_one_hz := '';
ls_temp_py := '';
i := 1;
LOOP
if i > li_length then
exit;
end if;
if length(ls_py) > 10 then
exit;
end if;
ls_one_hz := substr(chinese1,i,1);
if ascii(ls_one_hz) > 128 then
ls_one_hz := substr(chinese1,i,1);
--i := i + 1;
select count(*) into li_count from t_ywm_zk where z = ls_one_hz;
if li_count > 0 then
select n into ls_temp_py from t_ywm_zk where z = ls_one_hz;
if sqlcode < 0 then
return('錯誤');
end if;
if ls_temp_py is null then
ls_temp_py := '?';
end if;
else
ls_temp_py := '?';
end if;
ls_temp_py := upper(ls_temp_py);
ls_py := ls_py||ls_temp_py;
elsif ascii(ls_one_hz) <> 32 then
ls_one_hz := upper(ls_one_hz);
ls_py := ls_py||ls_one_hz;
end if;
i := i + 1;
END LOOP;
return ls_py;
end;