Oracle 中文转拼音码 

 1 --创建函数
 2 CREATE OR REPLACE FUNCTION F_PINYIN(P_NAME IN VARCHAR2) RETURN VARCHAR2 AS
 3 V_COMPARE VARCHAR2(100);
 4 V_RETURN VARCHAR2(4000);
 5 
 6 FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
 7 BEGIN
 8   RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
 9   END;
10   BEGIN
11 FOR I IN 1..LENGTH(P_NAME) LOOP
12 V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
13 IF V_COMPARE >= F_NLSSORT('吖') AND V_COMPARE <= F_NLSSORT('驁') THEN
14 V_RETURN := V_RETURN || 'A';
15 ELSIF V_COMPARE >= F_NLSSORT('八') AND V_COMPARE <= F_NLSSORT('簿') THEN
16 V_RETURN := V_RETURN || 'B';
17 ELSIF V_COMPARE >= F_NLSSORT('嚓') AND V_COMPARE <= F_NLSSORT('錯') THEN
18 V_RETURN := V_RETURN || 'C';
19 ELSIF V_COMPARE >= F_NLSSORT('咑') AND V_COMPARE <= F_NLSSORT('鵽') THEN
20 V_RETURN := V_RETURN || 'D';
21 ELSIF V_COMPARE >= F_NLSSORT('妸') AND V_COMPARE <= F_NLSSORT('樲') THEN
22 V_RETURN := V_RETURN || 'E';
23 ELSIF V_COMPARE >= F_NLSSORT('发') AND V_COMPARE <= F_NLSSORT('猤') THEN
24 V_RETURN := V_RETURN || 'F';
25 ELSIF V_COMPARE >= F_NLSSORT('旮') AND V_COMPARE <= F_NLSSORT('腂') THEN
26 V_RETURN := V_RETURN || 'G';
27 ELSIF V_COMPARE >= F_NLSSORT('妎') AND V_COMPARE <= F_NLSSORT('夻') THEN
28 V_RETURN := V_RETURN || 'H';
29 ELSIF V_COMPARE >= F_NLSSORT('丌') AND V_COMPARE <= F_NLSSORT('攈') THEN
30 V_RETURN := V_RETURN || 'J';
31 ELSIF V_COMPARE >= F_NLSSORT('咔') AND V_COMPARE <= F_NLSSORT('穒') THEN
32 V_RETURN := V_RETURN || 'K';
33 ELSIF V_COMPARE >= F_NLSSORT('垃') AND V_COMPARE <= F_NLSSORT('擽') THEN
34 V_RETURN := V_RETURN || 'L';
35 ELSIF V_COMPARE >= F_NLSSORT('嘸') AND V_COMPARE <= F_NLSSORT('椧') THEN
36 V_RETURN := V_RETURN || 'M';
37 ELSIF V_COMPARE >= F_NLSSORT('拏') AND V_COMPARE <= F_NLSSORT('瘧') THEN
38 V_RETURN := V_RETURN || 'N';
39 ELSIF V_COMPARE >= F_NLSSORT('筽') AND V_COMPARE <= F_NLSSORT('漚') THEN
40 V_RETURN := V_RETURN || 'O';
41 ELSIF V_COMPARE >= F_NLSSORT('妑') AND V_COMPARE <= F_NLSSORT('曝') THEN
42 V_RETURN := V_RETURN || 'P';
43 ELSIF V_COMPARE >= F_NLSSORT('七') AND V_COMPARE <= F_NLSSORT('裠') THEN
44 V_RETURN := V_RETURN || 'Q';
45 ELSIF V_COMPARE >= F_NLSSORT('亽') AND V_COMPARE <= F_NLSSORT('鶸') THEN
46 V_RETURN := V_RETURN || 'R';
47 ELSIF V_COMPARE >= F_NLSSORT('仨') AND V_COMPARE <= F_NLSSORT('蜶') THEN
48 V_RETURN := V_RETURN || 'S';
49 ELSIF V_COMPARE >= F_NLSSORT('侤') AND V_COMPARE <= F_NLSSORT('籜') THEN
50 V_RETURN := V_RETURN || 'T';
51 ELSIF V_COMPARE >= F_NLSSORT('屲') AND V_COMPARE <= F_NLSSORT('鶩') THEN
52 V_RETURN := V_RETURN || 'W';
53 ELSIF V_COMPARE >= F_NLSSORT('夕') AND V_COMPARE <= F_NLSSORT('鑂') THEN
54 V_RETURN := V_RETURN || 'X';
55 ELSIF V_COMPARE >= F_NLSSORT('丫') AND V_COMPARE <= F_NLSSORT('韻') THEN
56 V_RETURN := V_RETURN || 'Y';
57 ELSIF V_COMPARE >= F_NLSSORT('帀') AND V_COMPARE <= F_NLSSORT('咗') THEN
58 V_RETURN := V_RETURN || 'Z';
59 END IF;
60 END LOOP;
61 RETURN V_RETURN;
62 END;
63 
64 --测试
65 select F_PINYIN(中文) test from dual

 

posted @ 2018-08-11 00:35  Jarman  阅读(2346)  评论(0)    收藏  举报