oracle_function_生成汉字拼音码的函数

~~~
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;
   END LOOP;
   RETURN v_return;
END fgetpy;