Oracle解析json字符串获取指定值自定义函数代码

CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split

CREATE OR REPLACE TYPE ty_row_str_split as object (strValue VARCHAR2 (4000))

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;

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;

select parsejson('{"rta":"0.19","status":"0","msg":"PING OK - Packet loss \u003d 0%, RTA \u003d 0.19 ms","packetloss":"0"}','rta') from dual;