oracle11G解析json字符串函数
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;
