在Oracle 11g中解析JSON类型字段：获取key-value对的方法

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_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;

create table TESTGETJSON
(
  ID   NUMBER,
  JSON VARCHAR2(4000)
)

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;

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_INS<>0 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;

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

select json_value(目标字段,'$.key值') from ‘EMP’