[计算机软件及应用]oracle中文字符转换拼音.doc

该函数利用了oracle提供的NLSSORT，具有以下特点： 1. 不需要中文字库 2. 与数据库字符集无关 3. 支持Oracle 9i 及以上版本 4. 根据参数可分别返回全拼小写、全拼大写、全拼首字母大写，首字母小写、首字母大写 5. 不考虑多音字拼音上下文语义的正确性 6. 在2G内存、XP、10.2.0.1 上对3万中文返回拼音用时1.2秒 CREATE OR REPLACE FUNCTION fn_getpy (p_str IN VARCHAR2, p_flag NUMBER DEFAULT NULL) RETURN VARCHAR2 AS v_compare VARCHAR2 (4); v_return VARCHAR2 (4000); v_length INT := 0; v_substr VARCHAR2 (4); FUNCTION fn_nlssort (p_word IN VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN SUBSTR (NLSSORT (p_word, nls_sort=schinese_pinyin_m), 1, 4); END fn_nlssort; BEGIN IF p_str IS NULL THEN RETURN ; END IF; v_length := LENGTH (p_str); CASE p_flag WHEN 1 THEN --全拼大写 FOR i IN 1 .. v_length LOOP v_substr := SUBSTR (p_str, i, 1); v_compare := fn_nlssort (v_substr); CASE WHEN v_compare BETWEEN 3B29 AND 3B30 THEN v_return := v_return || A; WHEN v_compare = 3B31 THEN v_return := v_return || AES; WHEN v_compare BETWEEN 3B32 AND 3B9E THEN v_return := v_return || AI; WHEN v_compare BETWEEN 3BA0 AND 3BFE THEN v_return := v_return || AN; WHEN v_compare BETWEEN 3C01 AND 3C14 THEN v_return := v_return || ANG; WHEN v_compare BETWEEN 3C15 AND 3C82 THEN v_return := v_return || AO; WHEN v_compare BETWEEN 3C84 AND 3CE9 THEN v_return := v_return || BA; WHEN v_compare BETWEEN 3CED AND 3D1D THEN v_return := v_return || BAI; WHEN v_compare BETWEEN 3D20 AND 3D64 THEN v_return := v_return || BAN; WHEN v_