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

《[计算机软件及应用]oracle中文字符转换拼音.doc》由会员分享，可在线阅读，更多相关《[计算机软件及应用]oracle中文字符转换拼音.doc（143页珍藏版）》请在三一办公上搜索。

1、该函数利用了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_re

2、turn 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 WH

3、EN 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_ret

4、urn | 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_compar

5、e 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_ret

6、urn := 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

7、; 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

8、 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 :

9、= 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; WH

10、EN 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

11、:= 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

12、 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

13、_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_re

14、turn | 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;

15、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 = 4

16、A50 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 :=

17、 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; WHE

18、N 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

19、 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_r

20、eturn | 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

21、 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;

22、 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 51

23、4E 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; W

24、HEN 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_compar

25、e 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 54

26、B2 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 :=

27、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