按照下面步骤在oracle 执行sql----------创建自定义函数sql

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

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

—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(1000);
  i         NUMBER(2);
  jsonkey   VARCHAR2(500);
  jsonvalue VARCHAR2(1000);
  json      VARCHAR2(3000);
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(500);
  jsonvalue VARCHAR2(1000);
  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;