Oracle下获取字符串拼音码-优快云博客

create or replace function Fun_GetPingYin(inputStr varchar2)
return varchar2 --返回字符类型
-----------------------------
--获取字符串拼音码，暂时未解决字符串里有空格问题
--yyf 2011-11-03
----------------------------
AS
v_inputStr varchar2(500); --输入字符串
v_strLength number; --输入字符长度
v_return varchar2(200); --返回字符
v_start number; --开始翻译拼音字符位置
v_subStr varchar2(2); --每次截取的字符
v_translataStr varchar2(2);--转换的字符串
v_Code number;--字符的数字编码
begin
v_inputStr:= inputStr;
v_strLength := length(inputStr);
v_return := '';
v_start := 0;

for i in 1..v_strLength loop
--此句需放到循环里面。重新赋值
v_Code:=63;--ASCII码从63位开始才是拼音字符，大写
--每次截取一个字符翻译
select substr(v_inputStr,i,1) into v_subStr
from dual;

--查询中文字符码
--查询到当前的中文字符所属的行数加上63即使相应的英文字符
SELECT v_Code + MAX (rw)
        INTO v_Code
        FROM (SELECT chn, ROWNUM rw
                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
                                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_subStr
                                  FROM DUAL) a
                      ORDER BY NLSSORT (chn, 'NLS_SORT=SCHINESE_PINYIN_M')) c) b
       WHERE chn = v_subStr;
       dbms_output.put_line(v_Code);
       v_translataStr:=chr(v_Code);--转为字符
IF CHR (v_Code) = '@' --空格或英文
THEN           --直接返回
   v_Code := v_subStr;
END IF;

v_return:=v_return||v_translataStr;
end loop;
return v_return;
end;