oracle自定义函数解析json获取字段值

PARSEJSON函数

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 STRVALUE AS VALUE FROM TABLE(FN_SPLIT(JSON, ','))) LOOP
        IF TEMPROW.VALUE IS NOT NULL THEN
           I := 0;
           JSONKEY := '';
           JSONVALUE := '';
           FOR TEM2 IN(SELECT STRVALUE AS VALUE FROM TABLE(FN_SPLIT(TEMPROW.VALUE, ':'))) LOOP
               IF I = 0 THEN
                  JSONKEY := TEM2.VALUE;
               END IF;
               IF I = 1 THEN
                  JSONVALUE := TEM2.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;

FN_SPLIT函数：

CREATE OR REPLACE FUNCTION "FN_SPLIT" (P_STR       IN VARCHAR2,
                                    P_DELIMITER IN VARCHAR2)
  RETURN TY_TBL_STR_SPLIT IS
  J         INT := 0;
  I         INT := 1;
  LEN       INT := 0;
  LEN1      INT := 0;
  STR       VARCHAR2(4000);
  STR_SPLIT TY_TBL_STR_SPLIT := TY_TBL_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) := TY_ROW_STR_SPLIT(STRVALUE => 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) := TY_ROW_STR_SPLIT(STRVALUE => STR);
    END IF;
  END LOOP;


  RETURN STR_SPLIT;
END FN_SPLIT;