oracle 解析json格式

標籤：

1、

CREATE OR REPLACE PACKAGE PKG_ANALYSE_COMMON IS

-- AUTHOR : YZCHEN
-- CREATED : 2013/11/26 14:12:43
-- PURPOSE : 公用預存程序包

/*
* AUTHOR: YZCHEN DATETIME: 2013-11-26 14:20:36
* DESC: 根據P_SEQ分割字串，並返回資料格式，預設以,分割
*/
-- 分割後的字串臨時儲存類型
TYPE TYPE_SPLIT IS TABLE OF VARCHAR2(1024);
-- 分割函數
FUNCTION FUNC_SPLIT(P_LIST VARCHAR2, P_SEP VARCHAR2 := ‘,‘)
RETURN TYPE_SPLIT
PIPELINED;

/*
* AUTHOR: YZCHEN DATETIME: 2013-11-27 17:20:36
* DESC: 解析指定的JSON格式字串
*/
-- 解析函數
FUNCTION FUNC_PARSEJSON(P_JSONSTR VARCHAR2) RETURN TYPE_SPLIT
PIPELINED;

-- 解析函數，並擷取指定KEY的VALUE值
FUNCTION FUNC_PARSEJSON_BYKEY(P_JSONSTR VARCHAR2, P_KEY VARCHAR2)
RETURN VARCHAR2;

END PKG_ANALYSE_COMMON;

 

2、

CREATE OR REPLACE PACKAGE BODY PKG_ANALYSE_COMMON IS

/*
* @SEE DECLARETION
*/
FUNCTION FUNC_SPLIT(P_LIST VARCHAR2, P_SEP VARCHAR2 := ‘,‘)
RETURN TYPE_SPLIT
PIPELINED IS
L_IDX PLS_INTEGER;
V_LIST VARCHAR2(4000) := P_LIST;
BEGIN
LOOP
L_IDX := INSTR(V_LIST, P_SEP);
IF L_IDX > 0 THEN
PIPE ROW(SUBSTR(V_LIST, 1, L_IDX - 1));
V_LIST := SUBSTR(V_LIST, L_IDX + LENGTH(P_SEP));
ELSE
PIPE ROW(V_LIST);
EXIT;
END IF;
END LOOP;
END FUNC_SPLIT;

/*
* @SEE DECLARETION
*/
FUNCTION FUNC_PARSEJSON(P_JSONSTR VARCHAR2) RETURN TYPE_SPLIT
PIPELINED IS
V_JSONSTR VARCHAR2(1000) := P_JSONSTR;
JSONKEY VARCHAR2(50);
JSONVALUE VARCHAR2(50);
JSON VARCHAR2(1000);
TEMPCHAR VARCHAR2(1);
TEMPSTR1 VARCHAR2(1000);
TEMPSTR2 VARCHAR2(1000);
CUR_JSON1 SYS_REFCURSOR;
CUR_JSON2 SYS_REFCURSOR;
BEGIN
IF V_JSONSTR IS NOT NULL THEN
-- 先去掉前面的 [ 和後面的 ] 符號
TEMPCHAR := SUBSTR(V_JSONSTR, 1, 1);
IF ‘[‘ = TEMPCHAR THEN
V_JSONSTR := SUBSTR(V_JSONSTR, 2, LENGTH(V_JSONSTR));
END IF;
TEMPCHAR := SUBSTR(V_JSONSTR, LENGTH(V_JSONSTR), 1);
IF ‘]‘ = TEMPCHAR THEN
V_JSONSTR := SUBSTR(V_JSONSTR, 1, LENGTH(V_JSONSTR) - 1);
END IF;

-- 開始解析
JSON := REPLACE(V_JSONSTR, ‘{‘, ‘‘);
JSON := REPLACE(JSON, ‘}‘, ‘‘);
JSON := REPLACE(JSON, ‘"‘, ‘‘);
OPEN CUR_JSON1 FOR
SELECT * FROM TABLE(PKG_ANALYSE_COMMON.FUNC_SPLIT(JSON, ‘,‘));
LOOP
FETCH CUR_JSON1
INTO TEMPSTR1;
EXIT WHEN CUR_JSON1%NOTFOUND;

IF TEMPSTR1 IS NOT NULL THEN
JSONKEY := ‘‘;
JSONVALUE := ‘‘;
OPEN CUR_JSON2 FOR
SELECT * FROM TABLE(PKG_ANALYSE_COMMON.FUNC_SPLIT(TEMPSTR1, ‘:‘));
LOOP
FETCH CUR_JSON2
INTO TEMPSTR2;
EXIT WHEN CUR_JSON2%NOTFOUND;
PIPE ROW(TEMPSTR2);
END LOOP;
END IF;

END LOOP;
END IF;
END FUNC_PARSEJSON;

/*
* @SEE DECLARETION
*/
FUNCTION FUNC_PARSEJSON_BYKEY(P_JSONSTR VARCHAR2, P_KEY VARCHAR2)
RETURN VARCHAR2 IS
V_JSONSTR VARCHAR2(4000) := P_JSONSTR;
JSONKEY VARCHAR2(50);
JSONVALUE VARCHAR2(50);
JSON VARCHAR2(4000);
TEMPCHAR VARCHAR2(1);
TEMPSTR1 VARCHAR2(4000);
TEMPSTR2 VARCHAR2(4000);
CUR_JSON1 SYS_REFCURSOR;
CUR_JSON2 SYS_REFCURSOR;
IDX NUMBER := 0;
BEGIN
IF V_JSONSTR IS NOT NULL THEN
-- 先去掉前面的 [ 和後面的 ] 符號
TEMPCHAR := SUBSTR(V_JSONSTR, 1, 1);
IF ‘[‘ = TEMPCHAR THEN
V_JSONSTR := SUBSTR(V_JSONSTR, 2, LENGTH(V_JSONSTR));
END IF;
TEMPCHAR := SUBSTR(V_JSONSTR, LENGTH(V_JSONSTR), 1);
IF ‘]‘ = TEMPCHAR THEN
V_JSONSTR := SUBSTR(V_JSONSTR, 1, LENGTH(V_JSONSTR) - 1);
END IF;

-- 開始解析
JSON := REPLACE(V_JSONSTR, ‘{‘, ‘‘);
JSON := REPLACE(JSON, ‘}‘, ‘‘);
JSON := REPLACE(JSON, ‘"‘, ‘‘);
OPEN CUR_JSON1 FOR
SELECT * FROM TABLE(PKG_ANALYSE_COMMON.FUNC_SPLIT(JSON, ‘,‘));
LOOP
FETCH CUR_JSON1
INTO TEMPSTR1;
EXIT WHEN CUR_JSON1%NOTFOUND;
IDX := 0;
IF TEMPSTR1 IS NOT NULL THEN
JSONKEY := ‘‘;
JSONVALUE := ‘‘;
OPEN CUR_JSON2 FOR
SELECT * FROM TABLE(PKG_ANALYSE_COMMON.FUNC_SPLIT(TEMPSTR1, ‘:‘));
LOOP
FETCH CUR_JSON2
INTO TEMPSTR2;
EXIT WHEN CUR_JSON2%NOTFOUND;
IF IDX > 0 THEN
RETURN TEMPSTR2;
END IF;
IF TEMPSTR2 = P_KEY THEN
IDX := IDX + 1;
END IF;
END LOOP;
END IF;

END LOOP;
END IF;
RETURN ‘‘;
END FUNC_PARSEJSON_BYKEY;

END PKG_ANALYSE_COMMON;

oracle 解析json格式