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

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

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;