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

  • 相关阅读:
    POJ-3176 Cow Bowling
    01背包、完全背包、多重背包
    最后的几天暑假学习
    暑假的学习
    凸包算法(Graham扫描法)详解
    微软版的SqlHelper.cs类
    SQL语句分组排序,多表关联排序
    SQL存储过程分页(通用的拼接SQL语句思路实现)
    增加删除字段修改字段名,修改表结构,非常用SQL语句技巧总结
    Asp.net mvc返回Xml结果,扩展Controller实现XmlResult以返回XML格式数据
  • 原文地址:https://www.cnblogs.com/l3985/p/4766468.html
Copyright © 2020-2023  润新知