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