Oracle解析json字符串 获取指定值自定义函数代码
程序员文章站
2024-01-28 16:41:28
...
Oracle解析json字符串获取指定值自定义函数代码 Oracle 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
Oracle解析json字符串 获取指定值自定义函数代码 OracleCREATE 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 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;
select parsejson('{"rta":"0.19","status":"0","msg":"PING OK - Packet loss \u003d 0%, RTA \u003d 0.19 ms","packetloss":"0"}','rta') from dual;