Oracle11g处理JSON数据函数

通过自定义oracle函数来实现读取表中json类型数据

在开发系统调用第三方接口来实现本地特色功能时，对接口调用数据没有设计表保存业务数据，而是采用统一保存调用日志的方法，在后台用AOP截取这个业务所有接口请求，获固定参数做请求标记，然后数据库中保存所有接口调用请求和响应信息，调用和响应参数都是以json数据格式存储。这样做业务确实很方便，但是客户要统计一些业务详细的数据，处理起来很尴尬。
由于开发时把所有这类业务请求调用报文转换为json存储到数据库，没有详细表存储，一个大类业务调用多个接口都存到一张表中。在网上看了一些处理oracle json文件的方法，可以直接用oracl 自带的函数处理，但这是oracle 11g 的版本不支持JSON处理函数，需要oracle 12 及以上版本才能支持。看了一下这块可以通过Oracle自定义函数来处理，实际查询时调用这个函数根据json的key获取具体的vale值，这样就能获取到json 个数数据中想要的数据了。

通过自定义oracle函数来实现读取表中json类型数据，

Oracle数据库存储JSON字段

ORACLE 处理JSON格式字段函数模板

CREATE OR REPLACE FUNCTION F_GET_FRO_JSON(
KREC_ID VARCHAR2,    --唯一主键值
JSON VARCHAR2        --想要获取的key
) RETURN VARCHAR2 IS
JSON_VALUE VARCHAR(60); -- 根据自己JSON数据中值长度设置
JSON_INS INTEGER;
M_INS INTEGER;
D_INS INTEGER;
BEGIN

--获取json key位置
select instr(TO_CHAR("目标字段"),JSON) INTO JSON_INS from "目标表" T
WHERE "主键"=KREC_ID;

--获取json后第一个冒号
select instr(TO_CHAR("目标字段"),':',JSON_INS) INTO M_INS from "目标表" T
WHERE "主键"=KREC_ID;

--获取json后第一个逗号
select instr(TO_CHAR("目标字段"),',',JSON_INS) INTO D_INS from "目标表" T
WHERE "主键"=KREC_ID;

--最后一位json无逗号，直接取长度
if JSON_INS<>0 AND D_INS =0 then
select length(TO_CHAR("目标字段")) INTO D_INS from "目标表" T
WHERE "主键"=KREC_ID;
end if;

--截取,字符串类型去除引号
select replace(SUBSTR(TO_CHAR("目标字段"),M_INS+1,D_INS-M_INS-1),'"','') INTO JSON_VALUE from "目标表" T
WHERE "主键"=KREC_ID;

RETURN JSON_VALUE;

END;

实际应用

创建一个测用的试表

-- 创建JSON 表
create table ORACLE_JSON_TEST
(
  ID   NUMBER,
  JSON VARCHAR2(4000)
)

插入JSON数据

-- 插入JSION数据
insert into ORACLE_JSON_TEST (ID, JSON)
values (1, '{"STU_ID":"2301001","STU_NAME":"张三","STU_AGE":"24","STU_MAJOR":"计算机科学与技术","班级":"190034"}');

insert into ORACLE_JSON_TEST (ID, JSON)
values (2, '{"STU_ID":"2301002","STU_NAME":"张四","STU_AGE":"25,"STU_MAJOR":"计算机科学与技术","班级":"190034"}');

insert into ORACLE_JSON_TEST (ID, JSON)
values (3, '{"STU_ID":"2301003","STU_NAME":"李四","STU_AGE":"25,"STU_MAJOR":"计算机科学与技术","班级":"190034"}');

insert into ORACLE_JSON_TEST (ID, JSON)
values (4, '{"STU_ID":"2301003","STU_NAME":"李三","STU_AGE":"25,"STU_MAJOR":"计算机科学与技术","班级":"190034"}');

insert into ORACLE_JSON_TEST (ID, JSON)
values (5, '{"STU_ID":"2301003","STU_NAME":"李二","STU_AGE":"25,"STU_MAJOR":"计算机科学与技术","班级":"190034"}');

### 查询测试数据

select * from ORACLE_JSON_TEST;

根据JSON函数模板修改对应表处理函数

CREATE OR REPLACE FUNCTION ORACLE_JSON_GET(
KREC_ID VARCHAR2,
KJSON VARCHAR2

) RETURN VARCHAR2 IS
JSON_VALUE VARCHAR(30);
JSON_INS  INTEGER;
M_INS INTEGER;
D_INS INTEGER;
BEGIN

--获取json key位置
select instr(TO_CHAR(T.JSON),KJSON) INTO JSON_INS  from ORACLE_JSON_TEST T  --获取 json 的表
WHERE T.ID=KREC_ID;

--获取json后第一个冒号
select instr(TO_CHAR(T.JSON),':',JSON_INS) INTO M_INS  from ORACLE_JSON_TEST T
WHERE T.ID=KREC_ID;

--获取json后第一个逗号
select instr(TO_CHAR(T.JSON),',',JSON_INS) INTO D_INS  from ORACLE_JSON_TEST T
WHERE T.ID=KREC_ID;

--最后一位json无逗号，直接取长度
if JSON_INS<>0 AND D_INS =0 then
select length(TO_CHAR(T.JSON)) INTO D_INS  from ORACLE_JSON_TEST T
WHERE T.ID=KREC_ID;
end if;

--截取,字符串类型去除引号
select replace(SUBSTR(TO_CHAR(T.JSON),M_INS+1,D_INS-M_INS-1),'"','') INTO JSON_VALUE  from ORACLE_JSON_TEST T
WHERE T.ID=KREC_ID;

RETURN JSON_VALUE;

END;

用自定义函数查询，拆分JSON字符

用自定义函数查询，拆分JSON字符获取想要的JSON字符信息。

SELECT T.ID,
T.JSON,
ORACLE_JSON_GET(T.ID,'STU_ID') AS STU_ID,
ORACLE_JSON_GET(T.ID,'STU_NAME') AS STU_NAME ,
ORACLE_JSON_GET(T.ID,'STU_AGE') AS STU_AGE，
ORACLE_JSON_GET(T.ID,'STU_MAJOR') AS STU_MAJOR  
FROM ORACLE_JSON_TEST T

查询结果

https://cloud.tencent.com/developer/article/1934951