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