oracale解析json对象

create or replace function JsonParam (v_param varchar2,v_key varchar2)
return varchar2
is
       temp varchar2(50);
       i number(2);
       jsonkey varchar2(50);
       jsonvalue varchar2(50);
       json varchar2(1000);
      
begin
       if v_param is not null then
            json:=replace(v_param,'{','') ;
             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=v_key) then
                   temp:=jsonvalue;
                end if;
                end if;
             end loop;
       end if;
return temp;
      
end JsonParam;