[计算机软件及应用]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 VARCHAR2AS 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_compare BETWEEN '3D66' AND '3DA2' THEN v_return := v_return | 'BANG' WHEN v_compare BETWEEN '3DA4' AND '3E10' THEN v_return := v_return | 'BAO' WHEN v_compare = '3E11' THEN v_return := v_return | 'BE' WHEN v_compare BETWEEN '3E12' AND '3E7A' THEN v_return := v_return | 'BEI' WHEN v_compare BETWEEN '3E7C' AND '3EA0' THEN v_return := v_return | 'BEN' WHEN v_compare BETWEEN '3EA1' AND '3ED5' THEN v_return := v_return | 'BENG' WHEN v_compare BETWEEN '3ED8' AND '3FE9' THEN v_return := v_return | 'BI' WHEN v_compare BETWEEN '3FEA' AND '4055' THEN v_return := v_return | 'BIAN' WHEN v_compare BETWEEN '4058' AND '40AE' THEN v_return := v_return | 'BIAO' WHEN v_compare = '4060' THEN v_return := v_return | 'BIA' WHEN v_compare BETWEEN '40B4' AND '40D4' THEN v_return := v_return | 'BIE' WHEN v_compare BETWEEN '40D6' AND '4116' THEN v_return := v_return | 'BIN' WHEN v_compare BETWEEN '4118' AND '4160' THEN v_return := v_return | 'BING' WHEN v_compare BETWEEN '4161' AND '4224' THEN v_return := v_return | 'BO' WHEN v_compare BETWEEN '4225' AND '427C' THEN v_return := v_return | 'BU' WHEN v_compare BETWEEN '427D' AND '4289' THEN v_return := v_return | 'CA' WHEN v_compare BETWEEN '428C' AND '42B5' THEN v_return := v_return | 'CAI' WHEN v_compare BETWEEN '42B9' AND '430C' THEN v_return := v_return | 'CAN' WHEN v_compare BETWEEN '430D' AND '4334' THEN v_return := v_return | 'CANG' WHEN v_compare BETWEEN '4335' AND '435C' THEN v_return := v_return | 'CAO' WHEN v_compare BETWEEN '435D' AND '438C' THEN v_return := v_return | 'CE' WHEN v_compare BETWEEN '4390' AND '4398' THEN v_return := v_return | 'CEN' WHEN v_compare BETWEEN '439D' AND '43AA' THEN v_return := v_return | 'CENG' WHEN v_compare = '43AC' THEN v_return := v_return | 'CEOK' WHEN v_compare = '43AE' THEN v_return := v_return | 'CEOM' WHEN v_compare = '43B0' THEN v_return := v_return | 'CEON' WHEN v_compare = '43B1' THEN v_return := v_return | 'CEOR' WHEN v_compare BETWEEN '43B2' AND '440A' THEN v_return := v_return | 'CHA' WHEN v_compare BETWEEN '440E' AND '442D' THEN v_return := v_return | 'CHAI' WHEN v_compare BETWEEN '4431' AND '44E1' THEN v_return := v_return | 'CHAN' WHEN v_compare BETWEEN '44E4' AND '4552' THEN v_return := v_return | 'CHANG' WHEN v_compare BETWEEN '4554' AND '458E' THEN v_return := v_return | 'CHAO' WHEN v_compare BETWEEN '4590' AND '45C8' THEN v_return := v_return | 'CHE' WHEN v_compare BETWEEN '45C9' AND '463D' THEN v_return := v_return | 'CHEN' WHEN v_compare BETWEEN '463E' AND '46CD' THEN v_return := v_return | 'CHENG' WHEN v_compare BETWEEN '46CE' AND '47A6' THEN v_return := v_return | 'CHI' WHEN v_compare BETWEEN '47A8' AND '47EC' THEN v_return := v_return | 'CHONG' WHEN v_compare BETWEEN '47ED' AND '484C' THEN v_return := v_return | 'CHOU' WHEN v_compare BETWEEN '484D' AND '48E2' THEN v_return := v_return | 'CHU' WHEN v_compare BETWEEN '48E9' AND '48F4' THEN v_return := v_return | 'CHUAI' WHEN v_compare BETWEEN '48F6' AND '4924' THEN v_return := v_return | 'CHUAN' WHEN v_compare BETWEEN '4925' AND '4951' THEN v_return := v_return | 'CHUANG' WHEN v_compare BETWEEN '4954' AND '496E' THEN v_return := v_return | 'CHUI' WHEN v_compare BETWEEN '4971' AND '49C6' THEN v_return := v_return | 'CHUN' WHEN v_compare BETWEEN '49C8' AND '49EA' THEN v_return := v_return | 'CHUO' WHEN v_compare BETWEEN '49EC' AND '4A4A' THEN v_return := v_return | 'CI' WHEN v_compare = '4A50' THEN v_return := v_return | 'CIS' WHEN v_compare BETWEEN '4A51' AND '4AB2' THEN v_return := v_return | 'CONG' WHEN v_compare BETWEEN '4AB4' AND '4ABA' THEN v_return := v_return | 'COU' WHEN v_compare BETWEEN '4ABC' AND '4AEA' THEN v_return := v_return | 'CU' WHEN v_compare BETWEEN '4AEE' AND '4B0C' THEN v_return := v_return | 'CUAN' WHEN v_compare BETWEEN '4B0D' AND '4B56' THEN v_return := v_return | 'CUI' WHEN v_compare BETWEEN '4B59' AND '4B6C' THEN v_return := v_return | 'CUN' WHEN v_compare BETWEEN '4B70' AND '4BA9' THEN v_return := v_return | 'CUO' WHEN v_compare BETWEEN '4BAD' AND '4BFE' THEN v_return := v_return | 'DA' WHEN v_compare BETWEEN '4C00' AND '4C4E' THEN v_return := v_return | 'DAI' WHEN v_compare BETWEEN '4C50' AND '4CDC' THEN v_return := v_return | 'DAN' WHEN v_compare BETWEEN '4CDE' AND '4D26' THEN v_return := v_return | 'DANG' WHEN v_compare BETWEEN '4D28' AND '4D76' THEN v_return := v_return | 'DAO' WHEN v_compare BETWEEN '4D7E' AND '4D8D' THEN v_return := v_return | 'DE' WHEN v_compare = '4D8E' THEN v_return := v_return | 'DEM' WHEN v_compare BETWEEN '4D90' AND '4D91' THEN v_return := v_return | 'DEN' WHEN v_compare BETWEEN '4D94' AND '4DC0' THEN v_return := v_return | 'DENG' WHEN v_compare BETWEEN '4DC4' AND '4E8A' THEN v_return := v_return | 'DI' WHEN v_compare = '4E8C' THEN v_return := v_return | 'DIA' WHEN v_compare BETWEEN '4E8D' AND '4EE8' THEN v_return := v_return | 'DIAN' WHEN v_compare BETWEEN '4EE9' AND '4F38' THEN v_return := v_return | 'DIAO' WHEN v_compare BETWEEN '4F39' AND '4F90' THEN v_return := v_return | 'DIE' WHEN v_compare = '4F8D' THEN v_return := v_return | 'DEI' WHEN v_compare = '4F91' THEN v_return := v_return | 'DIM' WHEN v_compare BETWEEN '4F92' AND '4FCD' THEN v_return := v_return | 'DING' WHEN v_compare BETWEEN '4FCD' AND '4FD4' THEN v_return := v_return | 'DIU' WHEN v_compare BETWEEN '4FD5' AND '5032' THEN v_return := v_return | 'DONG' WHEN v_compare BETWEEN '5034' AND '507C' THEN v_return := v_return | 'DOU' WHEN v_compare = '5044' THEN v_return := v_return | 'DUL' WHEN v_compare BETWEEN '507E' AND '50E9' THEN v_return := v_return | 'DU' WHEN v_compare BETWEEN '50EA' AND '5110' THEN v_return := v_return | 'DUAN' WHEN v_compare BETWEEN '5114' AND '514E' THEN v_return := v_return | 'DUI' WHEN v_compare BETWEEN '5152' AND '518D' THEN v_return := v_return | 'DUN' WHEN v_compare = '5160' THEN v_return := v_return | 'TON' WHEN v_compare BETWEEN '518E' AND '5200' THEN v_return := v_return | 'DUO' WHEN v_compare BETWEEN '5205' AND '52C2' THEN v_return := v_return | 'E' WHEN v_compare BETWEEN '52C4' AND '52CD' THEN v_return := v_return | 'EN' WHEN v_compare = '52D4' THEN v_return := v_return | 'ENG' WHEN v_compare = '52D5' THEN v_return := v_return | 'EO' WHEN v_compare = '52D6' THEN v_return := v_return | 'EOL' WHEN v_compare = '52D8' THEN v_return := v_return | 'EOS' WHEN v_compare BETWEEN '52D9' AND '5332' THEN v_return := v_return | 'ER' WHEN v_compare BETWEEN '5334' AND '5366' THEN v_return := v_return | 'FA' WHEN v_compare BETWEEN '536A' AND '53FA' THEN v_return := v_return | 'FAN' WHEN v_compare BETWEEN '53FD' AND '5438' THEN v_return := v_return | 'FANG' WHEN v_compare BETWEEN '5439' AND '54B2' THEN v_return := v_return | 'FEI' WHEN v_compare BETWEEN '54B4' AND '5528' THEN v_return := v_return | 'FEN' WHEN v_compare BETWEEN '5529' AND '55A9' THEN v_return := v_return | 'FENG' WHEN v_compare BETWEEN '55AA' AND '55AE' THEN v_return := v_return | 'FO' WHEN v_compare BETWEEN '55B1' AND '55BC' THEN v_return := v_return | 'FOU' WHEN v_compare BETWEEN '55BD' AND '5739' THEN v_return := v_return | 'FU' WHEN v_compare = '569D' THEN v_return := v_return | 'M' WHEN v_compare BETWEEN '573C' AND '574C' THEN v_return := v_return | 'GA' WHEN v_compare BETWEEN '574D' AND '578C' THEN v_return := v_return | 'GAI' WHEN v_compare BETWEEN '578D' AND '57F0' THEN v_return := v_return | 'GAN' WHEN v_compare BETWEEN '57F1' AND '582C' THEN v_return := v_return | 'GANG' WHEN v_compare BETWEEN '582E' AND '5884' THEN v_return := v_return | 'GAO