Oracle解析json字符串 获取指定值自定义函数代码

WBOY
Release: 2016-06-07 14:57:53
Original
2662 people have browsed it

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字符串 获取指定值自定义函数代码 Oracle
CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split
Copy after login
CREATE OR REPLACE TYPE ty_row_str_split as object (strValue VARCHAR2 (4000))
Copy after login
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;
Copy after login
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;
Copy after login
select parsejson('{"rta":"0.19","status":"0","msg":"PING OK - Packet loss \u003d 0%, RTA \u003d 0.19 ms","packetloss":"0"}','rta') from dual;
Copy after login
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!