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;
来源：https://blog.csdn.net/tstcylq/article/details/49861865