oracle split 以及 简单json解析存储过程

由于之前工作上需要在oracle中做split功能以及json格分解。然后经过一番google和优化整合，最后整理到一个存储过程包中，易于管理，代码如下：

1、包定义：

[sql]  view plain copy

1. CREATE OR REPLACE PACKAGE PKG_COMMON IS  
2.   
3.   -- AUTHOR  : YZCHEN  
4.   -- CREATED : 2013/11/26 14:12:43  
5.   -- PURPOSE : 公共存储过程包  
6.   
7.   /*  
8.   * AUTHOR: YZCHEN  DATETIME: 2013-11-26 14:20:36  
9.   * DESC: 根据P_SEQ分割字符串，并返回数据格式，默认以,分割  
10.   */  
11.   -- 分割后的字符串临时存储类型  
12.   TYPE TYPE_SPLIT IS TABLE OF VARCHAR2(1024);  
13.   -- 分割函数  
14.   FUNCTION FUNC_SPLIT(P_LIST VARCHAR2, P_SEP VARCHAR2 := ',')  
15.     RETURN TYPE_SPLIT  
16.     PIPELINED;  
17.   
18.   /*  
19.   * AUTHOR: YZCHEN  DATETIME: 2013-11-27 17:20:36  
20.   * DESC: 解析指定的JSON格式字符串  
21.   */  
22.   -- 解析函数  
23.   FUNCTION FUNC_PARSEJSON(P_JSONSTR VARCHAR2) RETURN TYPE_SPLIT  
24.     PIPELINED;  
25.   
26.   -- 解析函数，并获取指定KEY的VALUE值  
27.   FUNCTION FUNC_PARSEJSON_BYKEY(P_JSONSTR VARCHAR2, P_KEY VARCHAR2)  
28.     RETURN VARCHAR2;  
29.   
30. END PKG_COMMON;  

2、包体：

[sql]  view plain copy

1. CREATE OR REPLACE PACKAGE BODY PKG_COMMON IS  
2.   
3.   /*  
4.   * @SEE DECLARETION  
5.   */  
6.   FUNCTION FUNC_SPLIT(P_LIST VARCHAR2, P_SEP VARCHAR2 := ',')  
7.     RETURN TYPE_SPLIT  
8.     PIPELINED IS  
9.     L_IDX  PLS_INTEGER;  
10.     V_LIST VARCHAR2(4000) := P_LIST;  
11.   BEGIN  
12.     LOOP  
13.       L_IDX := INSTR(V_LIST, P_SEP);  
14.       IF L_IDX > 0 THEN  
15.         PIPE ROW(SUBSTR(V_LIST, 1, L_IDX - 1));  
16.         V_LIST := SUBSTR(V_LIST, L_IDX + LENGTH(P_SEP));  
17.       ELSE  
18.         PIPE ROW(V_LIST);  
19.         EXIT;  
20.       END IF;  
21.     END LOOP;  
22.   END FUNC_SPLIT;  
23.   
24.   /*  
25.   * @SEE DECLARETION  
26.   */  
27.   FUNCTION FUNC_PARSEJSON(P_JSONSTR VARCHAR2) RETURN TYPE_SPLIT  
28.     PIPELINED IS  
29.     V_JSONSTR VARCHAR2(1000) := P_JSONSTR;  
30.     JSONKEY   VARCHAR2(50);  
31.     JSONVALUE VARCHAR2(50);  
32.     JSON      VARCHAR2(1000);  
33.     TEMPCHAR  VARCHAR2(1);  
34.     TEMPSTR1  VARCHAR2(1000);  
35.     TEMPSTR2  VARCHAR2(1000);  
36.     CUR_JSON1 SYS_REFCURSOR;  
37.     CUR_JSON2 SYS_REFCURSOR;  
38.   BEGIN  
39.     IF V_JSONSTR IS NOT NULL THEN  
40.       -- 先去掉前面的 [ 和后面的 ] 符号  
41.       TEMPCHAR := SUBSTR(V_JSONSTR, 1, 1);  
42.       IF '[' = TEMPCHAR THEN  
43.         V_JSONSTR := SUBSTR(V_JSONSTR, 2, LENGTH(V_JSONSTR));  
44.       END IF;  
45.       TEMPCHAR := SUBSTR(V_JSONSTR, LENGTH(V_JSONSTR), 1);  
46.       IF ']' = TEMPCHAR THEN  
47.         V_JSONSTR := SUBSTR(V_JSONSTR, 1, LENGTH(V_JSONSTR) - 1);  
48.       END IF;  
49.   
50.       -- 开始解析  
51.       JSON := REPLACE(V_JSONSTR, '{', '');  
52.       JSON := REPLACE(JSON, '}', '');  
53.       JSON := REPLACE(JSON, '"', '');  
54.       OPEN CUR_JSON1 FOR  
55.         SELECT * FROM TABLE(PKG_COMMON.FUNC_SPLIT(JSON, ','));  
56.       LOOP  
57.         FETCH CUR_JSON1  
58.           INTO TEMPSTR1;  
59.         EXIT WHEN CUR_JSON1%NOTFOUND;  
60.   
61.         IF TEMPSTR1 IS NOT NULL THEN  
62.           JSONKEY   := '';  
63.           JSONVALUE := '';  
64.           OPEN CUR_JSON2 FOR  
65.             SELECT * FROM TABLE(PKG_COMMON.FUNC_SPLIT(TEMPSTR1, ':'));  
66.           LOOP  
67.             FETCH CUR_JSON2  
68.               INTO TEMPSTR2;  
69.             EXIT WHEN CUR_JSON2%NOTFOUND;  
70.             PIPE ROW(TEMPSTR2);  
71.           END LOOP;  
72.         END IF;  
73.   
74.       END LOOP;  
75.     END IF;  
76.   END FUNC_PARSEJSON;  
77.   
78.   /*  
79.   * @SEE DECLARETION  
80.   */  
81.   FUNCTION FUNC_PARSEJSON_BYKEY(P_JSONSTR VARCHAR2, P_KEY VARCHAR2)  
82.     RETURN VARCHAR2 IS  
83.     V_JSONSTR VARCHAR2(1000) := P_JSONSTR;  
84.     JSONKEY   VARCHAR2(50);  
85.     JSONVALUE VARCHAR2(50);  
86.     JSON      VARCHAR2(1000);  
87.     TEMPCHAR  VARCHAR2(1);  
88.     TEMPSTR1  VARCHAR2(1000);  
89.     TEMPSTR2  VARCHAR2(1000);  
90.     CUR_JSON1 SYS_REFCURSOR;  
91.     CUR_JSON2 SYS_REFCURSOR;  
92.     IDX       NUMBER := 0;  
93.   BEGIN  
94.     IF V_JSONSTR IS NOT NULL THEN  
95.       -- 先去掉前面的 [ 和后面的 ] 符号  
96.       TEMPCHAR := SUBSTR(V_JSONSTR, 1, 1);  
97.       IF '[' = TEMPCHAR THEN  
98.         V_JSONSTR := SUBSTR(V_JSONSTR, 2, LENGTH(V_JSONSTR));  
99.       END IF;  
100.       TEMPCHAR := SUBSTR(V_JSONSTR, LENGTH(V_JSONSTR), 1);  
101.       IF ']' = TEMPCHAR THEN  
102.         V_JSONSTR := SUBSTR(V_JSONSTR, 1, LENGTH(V_JSONSTR) - 1);  
103.       END IF;  
104.   
105.       -- 开始解析  
106.       JSON := REPLACE(V_JSONSTR, '{', '');  
107.       JSON := REPLACE(JSON, '}', '');  
108.       JSON := REPLACE(JSON, '"', '');  
109.       OPEN CUR_JSON1 FOR  
110.         SELECT * FROM TABLE(PKG_COMMON.FUNC_SPLIT(JSON, ','));  
111.       LOOP  
112.         FETCH CUR_JSON1  
113.           INTO TEMPSTR1;  
114.         EXIT WHEN CUR_JSON1%NOTFOUND;  
115.         IDX := 0;  
116.         IF TEMPSTR1 IS NOT NULL THEN  
117.           JSONKEY   := '';  
118.           JSONVALUE := '';  
119.           OPEN CUR_JSON2 FOR  
120.             SELECT * FROM TABLE(PKG_COMMON.FUNC_SPLIT(TEMPSTR1, ':'));  
121.           LOOP  
122.             FETCH CUR_JSON2  
123.               INTO TEMPSTR2;  
124.             EXIT WHEN CUR_JSON2%NOTFOUND;  
125.             IF IDX > 0 THEN  
126.               RETURN TEMPSTR2;  
127.             END IF;  
128.             IF TEMPSTR2 = P_KEY THEN  
129.               IDX := IDX + 1;  
130.             END IF;  
131.           END LOOP;  
132.         END IF;  
133.   
134.       END LOOP;  
135.     END IF;  
136.     RETURN '';  
137.   END FUNC_PARSEJSON_BYKEY;  
138.   
139. END PKG_COMMON;  

检验结果：

select * from table(pkg_common.FUNC_SPLIT('sadasd,asd,asd,s', ','));

注意： 是 from table()。 否则，会只有一条 collection 记录。