oracle解析json字符串 获取指定值自定义函数代码 oracle 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 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 varchar2is 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;