Oracle解析复杂json的方法

Oracle 2020-07-03

问题背景：

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

分析思路:

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

2、结构复杂的json串节点元素值基本上可以分为两类：①仍为json串②json数组，因此我们只需对这两种类型的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-uscott/tiger@xxx.xxx.xxx.xxx:1521/orcljson.jar--删除指定jar#dropjava-uscott/tiger@xxx.xxx.xxx.xxx:1521/orcljson.jar

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

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

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

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

1createorreplaceandcompilejavasourcenamed"JsonUtil"as23importorg.json.JSONArray;4importorg.json.JSONException;5importorg.json.JSONObject;6importjava.lang.Integer;78publicclassJsonUtil{910//取json串单个节点值11publicstaticStringgetValue(StringjsonStr,StringnodeName){12StringnodeValue="";13try{14if(jsonStr==null||!jsonStr.startsWith("{")||!jsonStr.endsWith("}")){15nodeValue="";16}else{17JSONObjectobj=newJSONObject(jsonStr);18nodeValue=obj.getString(nodeName);19}20}catch(JSONExceptione){21nodeValue="";22}23returnnodeValue;24}25//取json数组长度便于循环处理26publicstaticIntegergetArrayLength(StringjsonArrayStr){27Integerlength=0;28try{29if(jsonArrayStr==null||!jsonArrayStr.startsWith("[")||!jsonArrayStr.endsWith("]")){30length=0;31}else{32JSONArrayjsonArr=newJSONArray(jsonArrayStr);33length=jsonArr.length();34}35}catch(JSONExceptione){36length=0;37}38returnlength;39}4041//取json数组第index个元素42publicstaticStringgetArrayValue(StringjsonStr,Integerindex){43StringnodeValue="";44try{45if(jsonStr==null||!jsonStr.startsWith("[")||!jsonStr.endsWith("]")){46nodeValue="";47}else{48JSONArrayjsonArr=newJSONArray(jsonStr);49nodeValue=jsonArr.getString(index);50}51}catch(JSONExceptione){52nodeValue="";53}54returnnodeValue;55}56}

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

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

1createorreplacepackagejsonpkg2as3functiongetval(jsonstrvarchar2,nodenamevarchar2)returnvarchar2;4functiongetarrval(jsonArrayStrvarchar2,seqNonumber)returnvarchar2;5functiongetarrlen(jsonArrayStrvarchar2)returnnumber;6endjsonpkg;7/8createorreplacepackagebodyjsonpkg9as10functiongetval(jsonstrvarchar2,nodenamevarchar2)returnvarchar211aslanguagejavaname'JsonUtil.getValue(java.lang.String,java.lang.String)returnjava.lang.String';1213functiongetarrval(jsonArrayStrvarchar2,seqNonumber)returnvarchar214aslanguagejavaname'JsonUtil.getArrayValue(java.lang.String,java.lang.Integer)returnjava.lang.String';1516functiongetarrlen(jsonArrayStrvarchar2)returnnumber17aslanguagejavaname'JsonUtil.getArrayLength(java.lang.String)returnjava.lang.Integer';1819endjsonpkg;20/

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