oracle11g数据库中的json工具

灰太狼 2022-06-08 03:47 715阅读 0赞

### 导入依赖库到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" as
    package 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;