2020年12月18日 周五 09:22 (北京) 晴 0℃~-9℃ 3-4级转<3级风
|
---1.创建一个type，返回table类型 CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2(6000); / ---2.创建一个分割的函数，主要对json进行按照固定割串分割 CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); str_split ty_str_split := ty_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) := 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) := str; END IF; END LOOP;
RETURN str_split; END fn_split; / ---3.再创建一个函数，解析json对像{} CREATE OR REPLACE FUNCTION parsejson(p_jsonstr varchar2, p_key varchar2) RETURN VARCHAR2 IS rtnVal VARCHAR2(4000); i NUMBER(2); jsonkey VARCHAR2(4000); jsonvalue VARCHAR2(4000); json VARCHAR2(4000); BEGIN IF p_jsonstr IS NOT NULL THEN json := REPLACE(p_jsonstr, '{', ''); json := REPLACE(json, '}', ''); json := replace(json, '"', ''); FOR temprow IN (SELECT * FROM TABLE(fn_split(json, ','))) LOOP IF temprow.column_value IS NOT NULL THEN i := 0; jsonkey := ''; jsonvalue := ''; FOR tem2 IN (SELECT * FROM TABLE(fn_split(temprow.column_value, ':'))) LOOP IF i = 0 THEN jsonkey := tem2.column_value; END IF; IF i = 1 THEN jsonvalue := tem2.column_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; / ---4 解析JSON数组[{},{}] CREATE OR REPLACE FUNCTION parsejsontwo(p_jsonstr varchar2, p_key varchar2) RETURN VARCHAR2 IS rtnVal VARCHAR2(4000); i NUMBER(2); jsonkey VARCHAR2(4000); jsonvalue VARCHAR2(4000); json VARCHAR2(4000); json2 VARCHAR2(4000); BEGIN IF p_jsonstr IS NOT NULL THEN json := REPLACE(p_jsonstr, '[', ''); json := REPLACE(json, ']', ''); FOR temprow IN (SELECT * FROM TABLE(fn_split(json, ','))) LOOP IF temprow.column_value IS NOT NULL THEN json2 := REPLACE(temprow.column_value, '{', ''); json2 := REPLACE(json2, '}', ''); json2 := replace(json2, '"', ''); FOR temprow2 IN (SELECT * FROM TABLE(fn_split(json2, ','))) LOOP i:=0; jsonkey:= ''; jsonvalue:= ''; FOR tmpe2 IN (SELECT * FROM TABLE(fn_split(temprow2.column_value, ':'))) LOOP IF i = 0 THEN jsonkey := tmpe2.column_value; END IF; IF i = 1 THEN jsonvalue := tmpe2.column_value; END IF; i := i + 1; END LOOP; IF (jsonkey = p_key) THEN IF rtnVal IS NOT NULL THEN rtnVal :=rtnVal||','||jsonvalue; ELSE rtnVal :=jsonvalue; END IF; END if; END LOOP; END IF; END LOOP; END IF; RETURN rtnVal; END parsejsontwo; / -----示例： -- SELECT parsejsontwo('[{"applyno":792,"extend1":"2019-WT-280","customerpart":"xxx","contactman":"茆XX","projectname":"XXXXX","projecttype":"XXX样"},{"applyno":793,"extend1":"2019-WT-281","customerpart":"江苏XXX集团有限公司","contactman":"茆XX","projectname":"XX区XX镇延XX大道南侧XX路XX地块","projecttype":"XXX量"}]','extend1') as extend1 FROM dual;
|
|
|