请选择 进入手机版 | 继续访问电脑版
查看: 676|回复: 0

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

3万

主题

3万

帖子

10万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
100197
发表于 2015-11-19 19:09:04
Oracle解析json字符串 获取指定值自定义函数代码
  1. CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split
复制代码
  1. CREATE OR REPLACE TYPE ty_row_str_split as object (strValue VARCHAR2 (4000))
复制代码
  1. CREATE OR REPLACE FUNCTION fn_split(p_str IN VARCHAR2,
  2. p_delimiter IN VARCHAR2)
  3. RETURN ty_tbl_str_split IS
  4. j INT := 0;
  5. i INT := 1;
  6. len INT := 0;
  7. len1 INT := 0;
  8. str VARCHAR2(4000);
  9. str_split ty_tbl_str_split := ty_tbl_str_split();
  10. BEGIN
  11. len := LENGTH(p_str);
  12. len1 := LENGTH(p_delimiter);
  13. WHILE j < len LOOP
  14. j := INSTR(p_str, p_delimiter, i);
  15. IF j = 0 THEN
  16. j := len;
  17. str := SUBSTR(p_str, i);
  18. str_split.EXTEND;
  19. str_split(str_split.COUNT) := ty_row_str_split(strValue => str);
  20. IF i >= len THEN
  21. EXIT;
  22. END IF;
  23. ELSE
  24. str := SUBSTR(p_str, i, j - i);
  25. i := j + len1;
  26. str_split.EXTEND;
  27. str_split(str_split.COUNT) := ty_row_str_split(strValue => str);
  28. END IF;
  29. END LOOP;
  30. RETURN str_split;
  31. END fn_split;
复制代码
  1. CREATE OR REPLACE FUNCTION parsejson(p_jsonstr varchar2,p_key varchar2) RETURN VARCHAR2
  2. IS
  3. rtnVal VARCHAR2(1000);
  4. i NUMBER(2);
  5. jsonkey VARCHAR2(500);
  6. jsonvalue VARCHAR2(1000);
  7. json VARCHAR2(3000);
  8. BEGIN
  9. IF p_jsonstr IS NOT NULL THEN
  10. json := REPLACE(p_jsonstr,'{','') ;
  11. json := REPLACE(json,'}','') ;
  12. json := replace(json,'"','') ;
  13. FOR temprow IN(SELECT strvalue AS VALUE FROM TABLE(fn_split(json, ','))) LOOP
  14. IF temprow.VALUE IS NOT NULL THEN
  15. i := 0;
  16. jsonkey := '';
  17. jsonvalue := '';
  18. FOR tem2 IN(SELECT strvalue AS VALUE FROM TABLE(fn_split(temprow.value, ':'))) LOOP
  19. IF i = 0 THEN
  20. jsonkey := tem2.VALUE;
  21. END IF;
  22. IF i = 1 THEN
  23. jsonvalue := tem2.VALUE;
  24. END IF;
  25. i := i + 1;
  26. END LOOP;
  27. IF(jsonkey = p_key) THEN
  28. rtnVal := jsonvalue;
  29. END if;
  30. END IF;
  31. END LOOP;
  32. END IF;
  33. RETURN rtnVal;
  34. END parsejson;
复制代码
  1. select parsejson('{"rta":"0.19","status":"0","msg":"PING OK - Packet loss \u003d 0%, RTA \u003d 0.19 ms","packetloss":"0"}','rta') from dual;
复制代码


回复

使用道具 举报