oracle json_value,oracle json 解析函数

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

内容来源于网络如有侵权请私信删除