oracle 11g 版本之前，创建自定义函数--解析JSON字符串.sql

2020年12月18日 周五 09:22  (北京) 晴 0℃~-9℃ 3-4级转<3级风

 ---1.创建一个type，返回table类型

 CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2(6000);

 /

 ---2.创建一个分割的函数，主要对json进行按照固定割串分割

 CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)

   RETURN ty_str_split

 IS

   j INT := 0;

   i INT := 1;       

   len INT := 0;

   len1 INT := 0;

   str VARCHAR2 (4000);

   str_split ty_str_split := ty_str_split ();

 BEGIN

   len := LENGTH (p_str);

   len1 := LENGTH (p_delimiter);

 

   WHILE j < len

   LOOP

     j := INSTR (p_str, p_delimiter, i);

 

     IF j = 0

     THEN

         j := len;

         str := SUBSTR (p_str, i);

         str_split.EXTEND;

         str_split (str_split.COUNT) := str;

 

         IF i >= len

         THEN

           EXIT;

         END IF;

     ELSE

         str := SUBSTR (p_str, i, j - i);

         i := j + len1;

         str_split.EXTEND;

         str_split (str_split.COUNT) := str;

     END IF;

   END LOOP;

 

   RETURN str_split;

 END fn_split;

 /

 ---3.再创建一个函数，解析json对像{}

 CREATE OR REPLACE FUNCTION parsejson(p_jsonstr varchar2, p_key varchar2)

   RETURN VARCHAR2 IS

   rtnVal    VARCHAR2(4000);

   i         NUMBER(2);

   jsonkey   VARCHAR2(4000);

   jsonvalue VARCHAR2(4000);

   json      VARCHAR2(4000);

 BEGIN

   IF p_jsonstr IS NOT NULL THEN

     json := REPLACE(p_jsonstr, '{', '');

     json := REPLACE(json, '}', '');

     json := replace(json, '"', '');

     FOR temprow IN (SELECT * FROM TABLE(fn_split(json, ','))) LOOP

       IF temprow.column_value IS NOT NULL THEN

         i         := 0;

         jsonkey   := '';

         jsonvalue := '';

         FOR tem2 IN (SELECT *

                        FROM TABLE(fn_split(temprow.column_value, ':'))) LOOP

           IF i = 0 THEN

             jsonkey := tem2.column_value;

           END IF;

           IF i = 1 THEN

             jsonvalue := tem2.column_value;

           END IF;

           i := i + 1;

         END LOOP;

         IF (jsonkey = p_key) THEN

           rtnVal := jsonvalue;

         END if;

       END IF;

     END LOOP;

   END IF;

   RETURN rtnVal;

 END parsejson;

 /

 ---4 解析JSON数组[{},{}]

 CREATE OR REPLACE

 FUNCTION parsejsontwo(p_jsonstr varchar2, p_key varchar2)

   RETURN VARCHAR2 IS

   rtnVal    VARCHAR2(4000);

   i        NUMBER(2);

   jsonkey   VARCHAR2(4000);

   jsonvalue VARCHAR2(4000);

   json      VARCHAR2(4000);

   json2      VARCHAR2(4000);

 BEGIN

   IF p_jsonstr IS NOT NULL THEN

     json := REPLACE(p_jsonstr, '[', '');

     json := REPLACE(json, ']', '');

     FOR temprow IN (SELECT * FROM TABLE(fn_split(json, ','))) LOOP

       IF temprow.column_value IS NOT NULL THEN

         json2 := REPLACE(temprow.column_value, '{', '');

         json2 := REPLACE(json2, '}', '');

         json2 := replace(json2, '"', '');

         FOR temprow2 IN (SELECT * FROM TABLE(fn_split(json2, ','))) LOOP

           i:=0;

           jsonkey:= '';

           jsonvalue:= '';

           FOR tmpe2 IN (SELECT * FROM TABLE(fn_split(temprow2.column_value, ':'))) LOOP

               IF i = 0 THEN

                 jsonkey := tmpe2.column_value;

               END IF;

               IF i = 1 THEN

                 jsonvalue := tmpe2.column_value;

               END IF;

               i := i + 1;

           END LOOP;

           IF (jsonkey = p_key) THEN

             IF rtnVal IS NOT NULL THEN

               rtnVal :=rtnVal||','||jsonvalue;

              ELSE

               rtnVal :=jsonvalue;

             END IF;

           END if;

         END LOOP;

       END IF;

     END LOOP;

   END IF;

  RETURN rtnVal;

 END parsejsontwo;

 /

 -----示例：

 -- SELECT parsejsontwo('[{"applyno":792,"extend1":"2019-WT-280","customerpart":"xxx","contactman":"茆XX","projectname":"XXXXX","projecttype":"XXX样"},{"applyno":793,"extend1":"2019-WT-281","customerpart":"江苏XXX集团有限公司","contactman":"茆XX","projectname":"XX区XX镇延XX大道南侧XX路XX地块","projecttype":"XXX量"}]','extend1') as extend1  FROM dual;