oracle11g数据库中的json工具

导入依赖库到oracle（org.json:json:20170516）

loadjava -r -f -u user/password@ip:1521/orasid json-20170516.jar

添加java source工具类

create or replace and compile java source named "JsonUtil" aspackage org.jws;import org.json.JSONObject;public class JsonUtil {    // 获取字符值    public static String getStringValue(String jsonObjStr, String key) {        try {            JSONObject obj = new JSONObject(jsonObjStr);            if (obj.has(key)) {                Object value = obj.get(key);                return value.toString();            }        } catch (Exception e) {        }        return null;    }    // 获取数字值    public static Double getNumberValue(String jsonObjStr, String key) throws RuntimeException {        try {            JSONObject obj = new JSONObject(jsonObjStr);            if (obj.has(key)) {                Object value = obj.get(key);                if (value instanceof Number) {                    return ((Number) value).doubleValue();                } else {                    return Double.parseDouble(value.toString());                }            }        } catch (Exception e) {        }        return null;    }}/

定义package

create or replace package pkg_json as  --获取字符  function getstr  (    jsonstr  varchar2,    nodename varchar2  ) return varchar2;  --获取数值  function getnum  (    jsonstr  varchar2,    nodename varchar2  ) return number;end pkg_json;/create or replace package body pkg_json as  function getstr  (    jsonstr  varchar2,    nodename varchar2  ) return varchar2 as    language java name 'org.jws.JsonUtil.getStringValue(java.lang.String,java.lang.String) return java.lang.String';  function getnum  (    jsonstr  varchar2,    nodename varchar2  ) return number as    language java name 'org.jws.JsonUtil.getNumberValue(java.lang.String,java.lang.String) return java.lang.Double';end pkg_json;/

测试

select pkg_json.getstr('{"name":"zhangsan","age":18}', 'name') strval,       pkg_json.getnum('{"name":"zhangsan","age":18}', 'age') numval  from dual;

tips

select * from user_java_classes a;