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

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 <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms

–> <!—ecms
• $velocityCount

–> <!—ecms

–>

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  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 VARCHAR2IS  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   <p style="color:transparent">本文来源gao!%daima.com搞$代*!码$网3</p>     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;

喜欢 (0)赏

[搞代码]

分享 (0)