效果:
select fgetpy('中国') FROM dual;
结果 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);
/*************************************************************************
生成汉字拼音码的函数。 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;
此函数有一个BUG,不知道为什么会这样
select fgetpy('她们') FROM dual
的结果是 jm,'她'字的拼音首字母在函数里被转成了j,应该是t
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。