CREATE OR REPLACE FUNCTION F_GET_FRO_JSON(
KREC_ID VARCHAR2, --唯一主键值
JSON VARCHAR2 --想要获取的key
) RETURN VARCHAR2 IS
JSON_VALUE VARCHAR(30);
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_INS0 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;
创建测试表
代码语言：javascript
复制
create table TESTGETJSON
(
ID NUMBER,
JSON VARCHAR2(4000)
)
测试数据
代码语言：javascript
复制
insert into testgetjson (ID, JSON)
values (1, '{"OF_BIZ_FINISH_FLAG" :"1",
"OF_DEF_DIV_METHOD" :"",
"OF_ERROR_DETAIL" :"0000",
"OF_FROM_TA_FLAG" :"0",
"OF_RETURN_CODE" :"0000"}');
insert into testgetjson (ID, JSON)
values (2, '{"OF_BIZ_FINISH_FLAG":"1","OF_DEF_DIV_METHOD":"","OF_ERROR_DETAIL":"逐笔人工处理为无效","OF_FROM_TA_FLAG":"0","OF_RETURN_CODE":"0355"}');
insert into testgetjson (ID, JSON)
values (3, '{"OF_BIZ_FINISH_FLAG":"1","OF_DEF_DIV_METHOD":"","OF_ERROR_DETAIL":"0000","OF_FROM_TA_FLAG":"0","OF_RETURN_CODE":"0000"}');
insert into testgetjson (ID, JSON)
values (4, '{"OF_BIZ_FINISH_FLAG":"1","OF_DEF_DIV_METHOD":"","OF_ERROR_DETAIL":"逐笔人工处理为无效","OF_FROM_TA_FLAG":"0","OF_RETURN_CODE":"0355"}');
insert into testgetjson (ID, JSON)
values (5, '{"OF_BIZ_FINISH_FLAG":"1","OF_DEF_DIV_METHOD":"","OF_ERROR_DETAIL":"0000","OF_FROM_TA_FLAG":"0","OF_RETURN_CODE":"0000"}');
insert into testgetjson (ID, JSON)
values (6, '{"OF_BIZ_FINISH_FLAG":"1","OF_DEF_DIV_METHOD":"","OF_ERROR_DETAIL":"0000","OF_FROM_TA_FLAG":"0","OF_RETURN_CODE":"0000"}');
insert into testgetjson (ID, JSON)
values (7, '{"OF_BIZ_FINISH_FLAG":"1","OF_DEF_DIV_METHOD":"","OF_ERROR_DETAIL":"0000","OF_FROM_TA_FLAG":"0","OF_RETURN_CODE":"0000"}');
commit;
替换函数
代码语言：javascript
复制
CREATE OR REPLACE FUNCTION TEST_GET_FRO_JSON(
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 TESTGETJSON T
WHERE T.ID=KREC_ID;
--获取json后第一个冒号
select instr(TO_CHAR(T.JSON),':',JSON_INS) INTO M_INS from TESTGETJSON T
WHERE T.ID=KREC_ID;
--获取json后第一个逗号
select instr(TO_CHAR(T.JSON),',',JSON_INS) INTO D_INS from TESTGETJSON T
WHERE T.ID=KREC_ID;
--最后一位json无逗号，直接取长度
if JSON_INS0 AND D_INS =0 then
select length(TO_CHAR(T.JSON)) INTO D_INS from TESTGETJSON 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 TESTGETJSON T
WHERE T.ID=KREC_ID;
RETURN JSON_VALUE;
END;
就可以使用了
代码语言：javascript
复制
SELECT T.ID,
T.JSON,
TEST_GET_FRO_JSON(T.ID,'OF_RETURN_CODE') AS OF_RETURN_CODE,
TEST_GET_FRO_JSON(T.ID,'OF_BIZ_FINISH_FLAG') AS OF_BIZ_FINISH_FLAG ,
TEST_GET_FRO_JSON(T.ID,'OF_ERROR_DETAIL') AS OF_ERROR_DETAIL
FROM TESTGETJSON T
11g以上可以直接用oracle自带的函数
代码语言：javascript
复制
select json_value(目标字段,'$.key值') from ‘EMP’
