Oracle 获取json字符串指定内容_oracle sql取json中的字段值

1.创建FN_SPLIT切割函数

CREATE OR REPLACE TYPE ty_row_str_split  as object (strValue VARCHAR2 (4000));
CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split;
 
CREATE OR REPLACE FUNCTION FN_SPLIT ( p_str IN CLOB, p_delimiter IN VARCHAR2 ) RETURN ty_tbl_str_split IS j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 ( 5000 );
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;

2.创建 JSON_KEY（）函数

CREATE OR REPLACE function "JSON_KEY"(p_jsonstr CLOB,p_key varchar2) 
return varchar2
is
  rtnval varchar2(1000);
  i number(2);
  jsonkey varchar2(5000);
  jsonvalue varchar2(3000);
  json CLOB;
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 JSON_KEY;

用法如下：
select JSON_KEY('{"name":"zhangsan","tel":"138888888"}','tel') from dual