Oracle解析复杂json的方法实例详解

bfcady

2019-04-06

问题背景：

当前在Oracle数据库(11G之前的版本)解析json没有可以直接使用的系统方法，网上流传的PLSQL脚本大多也只可以解析结构较单一的json串，对于结构复杂的json串还无法解析。如此一来导致即便可以在PL/SQL中调用远程接口，但返回结果仍需传给前台js或java等其它代码进行处理，不太方便。

分析思路:

1、在PL/SQL中写json串，无需声明json对象，只需直接拼接成格式正确的json字符串即可，因此数据库对象间json串的传递完全可以用varchar2或clob来代替。

2、结构复杂的json串节点元素值基本上可以分为两类：①仍为json串②json数组，因此我们只需对这两种类型的json对象进行处理即可。

Oracle解析复杂json的方法实例详解

3、在PL/SQL中处理json数组时，由于json元素是无序且可以重复的，因此我们需要对数组成员进行索引并能够获取数组长度才可以对其循环处理。

4、java中有很多可以方便解析json串的开源jar包，且数据库支持导入java类文件，因此是否可以将java代码编译生成的class导入数据处理json呢？

解决方案:

方法1:loadjava导入java类进行解析

1、查看当前数据库已导入的java类文件

2、执行loadjava命令导入处理json所需jar文件，在此选择org.json而不选择fastjson或jackson的原因是该jar包没有外部依赖且满足功能的同时所需导入类文件较少。

--向数据库导入json相关jar包
loadjava -r -f -u scott/tiger@xxx.xxx.xxx.xxx:1521/orcl json.jar
--删除指定jar
#dropjava -u scott/tiger@xxx.xxx.xxx.xxx:1521/orcl json.jar

在此，我们执行导入,如下:

Oracle解析复杂json的方法实例详解

细节:如果导入的jar包存在外部依赖，会报如下异常，继续导入外部依赖只会使要导入的jar包越来越多，最终还不一定可以导入成功,得不偿失，如下:

Oracle解析复杂json的方法实例详解

3、导入json.jar成功后再次查看当前已导入的类文件如下

Oracle解析复杂json的方法实例详解

4、在数据库SQL窗口执行以下脚本,创建java源码对象

create or replace and compile java source named "JsonUtil" as
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import java.lang.Integer;
public class JsonUtil {
 //取json串单个节点值
 public static String getValue(String jsonStr,String nodeName){
 String nodeValue="";
 try {
  if(jsonStr==null||!jsonStr.startsWith("{")||!jsonStr.endsWith("}")){
  nodeValue="";
  }else{
  JSONObject obj =new JSONObject(jsonStr);
  nodeValue = obj.getString(nodeName);
  }
 } catch (JSONException e) {
  nodeValue="";
 }
 return nodeValue;
 }
 //取json数组长度便于循环处理
 public static Integer getArrayLength(String jsonArrayStr){
 Integer length=0;
 try {
  if(jsonArrayStr==null||!jsonArrayStr.startsWith("[")||!jsonArrayStr.endsWith("]")){
  length=0;
  }else{
  JSONArray jsonArr = new JSONArray(jsonArrayStr);
  length=jsonArr.length();
  }
 } catch (JSONException e) {
  length=0;
 }
 return length;
 }
 //取json数组第index个元素
 public static String getArrayValue(String jsonStr,Integer index){
 String nodeValue="";
 try {
  if(jsonStr==null||!jsonStr.startsWith("[")||!jsonStr.endsWith("]")){
  nodeValue="";
  }else{
  JSONArray jsonArr = new JSONArray(jsonStr);
  nodeValue=jsonArr.getString(index);
  }
 } catch (JSONException e) {
  nodeValue="";
 }
 return nodeValue;
 }
}

创建成功后再次查询可以看到对应的class文件:

Oracle解析复杂json的方法实例详解

5、利用步骤4创建的class创建function(或procedure),在此为了使其跟目前数据库已存在的json处理方法区分开，我们创建一个package，如下:

create or replace package jsonpkg
as
function getval(jsonstr varchar2,nodename varchar2) return varchar2;
function getarrval(jsonArrayStr varchar2,seqNo number) return varchar2;
function getarrlen(jsonArrayStr varchar2) return number;
end jsonpkg;
/
create or replace package body jsonpkg
as
function getval(jsonstr varchar2,nodename varchar2) return varchar2
as language java name 'JsonUtil.getValue(java.lang.String,java.lang.String) return java.lang.String';
function getarrval(jsonArrayStr varchar2,seqNo number) return varchar2
as language java name 'JsonUtil.getArrayValue(java.lang.String, java.lang.Integer) return java.lang.String';
function getarrlen(jsonArrayStr varchar2) return number
as language java name 'JsonUtil.getArrayLength(java.lang.String) return java.lang.Integer';
end jsonpkg;
/

创建成功后可以查看包说明和主体:

Oracle解析复杂json的方法实例详解