ORACLE_JSON字符解析_o002

CREATE OR REPLACE PACKAGE PKG_COMMON IS

  -- AUTHOR  : chenjj
  -- CREATED :
  -- PURPOSE : 公共存储过程包


  -- 分割后的字符串临时存储类型
  TYPE TYPE_SPLIT IS TABLE OF VARCHAR2(1024);
  -- 分割函数
  FUNCTION FUNC_SPLIT(P_LIST VARCHAR2, P_SEP VARCHAR2 := ',')
    RETURN TYPE_SPLIT
    PIPELINED;


  -- 解析函数
  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_COMMON;

CREATE OR REPLACE PACKAGE BODY PKG_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_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_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(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;
    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_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_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_COMMON;

使用实例：

select PKG_COMMON.FUNC_PARSEJSON_BYKEY('{"a":"1ccc","b":"2"}','a') from dual;