新增解析json报文，获取key的value
建立如下两种类型
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;新建json截取通用方法
CREATE OR REPLACE FUNCTION fun_split(p_str IN VARCHAR2,p_delimiter IN VARCHAR2)RETURN ty_tbl_str_split IS--名称：json截取通用方法--传入参数：--p_str json报文内容--p_delimiter json报文中的key值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--获取json长度lenlen := LENGTH(p_str);--获取key长度len1len1 := LENGTH(p_delimiter);WHILE j < len LOOPj := INSTR(p_str, p_delimiter, i);IF j = 0 THENj := len;str := SUBSTR(p_str, i);str_split.EXTEND;str_split(str_split.COUNT) := ty_row_str_split(strValue => str);IF i >= len THENEXIT;END IF;ELSEstr := 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 fun_split;解析json通用方法
create or replace FUNCTION fun_parsejson(p_jsonstr varchar2, p_key varchar2)RETURN VARCHAR2 AS--名称：解析json通用方法rtnVal VARCHAR2(1000);i NUMBER(2);jsonkey VARCHAR2(500);jsonvalue VARCHAR2(1000);json VARCHAR2(3000);
BEGINIF 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(fun_split(json, ','))) LOOPIF temprow.VALUE IS NOT NULL THENi := 0;jsonkey := '';jsonvalue := '';FOR tem2 IN (SELECT strvalue AS VALUEFROM TABLE(fun_split(temprow.value, ':'))) LOOPIF i = 0 THENjsonkey := tem2.VALUE;END IF;IF i = 1 THENjsonvalue := tem2.VALUE;END IF;i := i + 1;END LOOP;IF (jsonkey = p_key) THENrtnVal := jsonvalue;END if;END IF;END LOOP;END IF;RETURN rtnVal;
END fun_parsejson;测试
--更新
update t_confirm_letter tset t.d_confirm_date = to_date(fun_parsejson(t.report_data, 'confirmDate'),'yyyy-MM-dd')where 1=1and t.d_confirm_date is nulland t.report_data is not null;
