- 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;
- -------------------------------------------------------------
- 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
- AS
- 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;
来源: http://www.bubuko.com/infodetail-2987122.html