|
效果： select fgetpy('中國(guó)') FROM dual; 結(jié)果 zg 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); /************************************************************************* 生成漢字拼音碼的函數(shù)。 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 '發(fā)' 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; 此函數(shù)有一個(gè)BUG，不知道為什么會(huì)這樣 select fgetpy('她們') FROM dual 的結(jié)果是 jm,'她'字的拼音首字母在函數(shù)里被轉(zhuǎn)成了j，應(yīng)該是t
|
|
|
