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;
oracle function json,oracle json 解析函数
最新推荐文章于 2024-05-22 17:56:31 发布