oracle生成拼音函数,oracle得到拼音函数

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

/*************************************************************************

生成汉字拼音码的函数。　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 '吖' chn

FROM DUAL

UNION

SELECT '八'

FROM DUAL

UNION ALL

SELECT '嚓'

FROM DUAL

UNION ALL

SELECT '咑'

FROM DUAL

UNION ALL

SELECT '妸'

FROM DUAL

UNION ALL

SELECT '发'

FROM DUAL

UNION ALL

SELECT '旮'

FROM DUAL

UNION ALL

SELECT '铪'

FROM DUAL

UNION ALL

SELECT '丌'

FROM DUAL              --because have no 'i'

UNION ALL

SELECT '丌'

FROM DUAL

UNION ALL

SELECT '咔'

FROM DUAL

UNION ALL

SELECT '垃'

FROM DUAL

UNION ALL

SELECT '嘸'

FROM DUAL

UNION ALL

SELECT '拏'

FROM DUAL

UNION ALL

SELECT '噢'

FROM DUAL

UNION ALL

SELECT '妑'

FROM DUAL

UNION ALL

SELECT '七'

FROM DUAL

UNION ALL

SELECT '呥'

FROM DUAL

UNION ALL

SELECT '仨'

FROM DUAL

UNION ALL

SELECT '他'

FROM DUAL

UNION ALL

SELECT '屲'

FROM DUAL

UNION ALL

SELECT '屲'

FROM DUAL

UNION ALL

SELECT '屲'

FROM DUAL

UNION ALL

SELECT '夕'

FROM DUAL

UNION ALL

SELECT '丫'

FROM DUAL

UNION ALL

SELECT '帀'

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                                                      --英文直接返回

v_c := v_chn;

END IF;

v_return := v_return || v_c;

v_return := lower(v_return);

END LOOP;

RETURN v_return;

END fgetpy;