Oracle编写解析字段为json字符串的函数

2019-3-21 16:00| 发布者: zhaojun917| 查看: 1365| 评论: 0|原作者: 梦想之都-俊月星空

摘要: create or replaceTYPE ty_row_str_split as object (strValue VARCHAR2 (4000));create or replaceTYPE ty_tbl_str_split AS TABLE OF ty_row_str_split;create or replaceFUNCTION fn_split(p_str IN VARCHAR2, ...

create or replace

TYPE ty_row_str_split as object (strValue VARCHAR2 (4000));

create or replace

TYPE ty_tbl_str_split AS TABLE OF ty_row_str_split;

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

AS

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;

select parsejson('{"name":"test","age":10,"key":"Y"}','key') from dual where parsejson('{"name":"test","age":10,"key":"Y"}','key')='Y'

收藏分享邀请

上一篇：Oracle 中 union与union all下一篇：Oracle中的instr()函数详解及应用

账号		自动登录	找回密码
密码			立即注册

Oracle编写解析字段为json字符串的函数

相关分类

站长推荐 /6