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

  • 相关阅读:
    [Java5新特性]可变参数
    [Java5新特性]自动装箱/拆箱
    CentOS6.5下安装apache2.2和PHP 5.5.28
    VMware Workstation(虚拟机)v10.0.1 简体中文破解版
    [Android]通过js方法回调部分native报错 Web Console: Uncaught TypeError: Object [object Object] has no method 'xxx'
    Android WebView与JavaScript交互操作(Demo)
    【CSS3 入门教程系列】CSS3 Media Queries 实现响应式设计
    Bootstrap 3支持IE 8遇到的一个小问题
    php文件大小单位转换GB MB KB
    2个比较经典的PHP加密解密函数分享
  • 原文地址:https://www.cnblogs.com/l3985/p/4766468.html
Copyright © 2020-2023  润新知