CREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2)
RETURN VARCHAR2
As
V_strlen INT;
V_return VARCHAR2 (500);
V_ii INT;
V_n INT;
V_c VARCHAR2 (2);
V_chn VARCHAR2 (2);
V_RC VARCHAR2 (500);
/*************************************************************************
The function of generating pinyin code for Chinese characters. WALLIMN 2009-06-21
**************************************************************************/
BEGIN
--dbms_output.put_line (V_STR);
V_RC: = V_str;
V_strlen: = LENGTH (V_RC);
V_return: = ";
V_ii: = 0;
While V_ii < V_strlen
LOOP
V_ii: = V_ii + 1;
V_n: = 63;
SELECT SUBSTR (V_RC, V_ii, 1)
Into V_chn
From DUAL;
SELECT V_n + MAX (rowsf)
Into V_n
From (SELECT CHN, ROWNUM rowsf
From (SELECT CHN
From (SELECT ' Acridine ' CHN
From DUAL
UNION
SELECT ' eight '
From DUAL
UNION All
SELECT ' Cha '
From DUAL
UNION All
SELECT ' Otah '
From DUAL
UNION All
SELECT ' Ehegan '
From DUAL
UNION All
SELECT ' Hair '
From DUAL
UNION All
SELECT ' Xu '
From DUAL
UNION All
SELECT ' Hafnium '
From DUAL
UNION All
SELECT ' not '
From DUAL--because has no ' i '
UNION All
SELECT ' not '
From DUAL
UNION All
SELECT ' ka '
From DUAL
UNION All
SELECT ' junk '
From DUAL
UNION All
SELECT ' 嘸 '
From DUAL
UNION All
SELECT ' Hallasan '
From DUAL
UNION All
SELECT ' Oh '
From DUAL
UNION All
SELECT ' 妑 '
From DUAL
UNION All
SELECT ' Seven '
From DUAL
UNION All
SELECT ' 呥 '
From DUAL
UNION All
SELECT ' three '
From DUAL
UNION All
SELECT ' him '
From DUAL
UNION All
SELECT ' 屲 '
From DUAL
UNION All
SELECT ' 屲 '
From DUAL
UNION All
SELECT ' 屲 '
From DUAL
UNION All
SELECT ' XI '
From DUAL
UNION All
SELECT ' ya '
From DUAL
UNION All
SELECT ' as '
From DUAL
UNION All
SELECT V_chn
From DUAL) A
ORDER by Nlssort (CHN, ' Nls_sort=schinese_pinyin_m ')) c) b
WHERE CHN = V_chn;
V_c: = CHR (V_n);
IF CHR (v_n) = ' @ '
Then-English direct return
V_c: = V_chn;
END IF;
V_return: = V_return | | V_c;
V_return: = lower (V_return);
END LOOP;
RETURN V_return;
END fgetpy;
Oracle obtains phonetic functions