oracle split 以及 简单json解析存储过程

原创 2013年12月02日 15:46:03

BEGIN;

由于之前工作上需要在oracle中做split功能以及json格分解。然后经过一番google和优化整合,最后整理到一个存储过程包中,易于管理,代码如下:


1、包定义:

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

2、包体:

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 * from table(pkg_common.FUNC_SPLIT('sadasd,asd,asd,s', ','));

注意: 是 from table()。 否则,会只有一条 collection 记录。



END;

--- --- --- ---> 点击查看更多最新原创博文<--- --- --- ---

相关文章推荐

Oracle 中解析Json数据 PLJson的安装及使用

将数据以json的格式传到数据库中,那么怎么解析呢?有办法,需要用到一个 PLJSON 的开源项目 下载下来安装 https://github.com/pljson/pljson安装方法:下载后,...

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

存储过程解析表菜单并输出JSON格式字符串

存储过程解析表菜单并输出JSON格式字符串,可用来实现权限菜单控制。
  • 5653325
  • 5653325
  • 2014年10月09日 16:56
  • 3676

sql(Oracle)读取json_list数据和多层嵌套的json数据

最近在开发一个管理系统的时候整理出一个多层嵌套的逻辑,并且在嵌套的最里层存在一对多的业务关系: 简单举例说明:一家水果店出售多种水果(为了简化业务结构,就单纯一苹果为主线),苹果又分为多种类型(比如...

oracle 存储过程及REF CURSOR的使用

摘要:主要记录了oracle的stored procedure相关知识、从定义到使用。从最简单的例子入手、逐渐深入。同时后面结合了ref cursor的使用。顺便补充了在java中如何使用。...

oracle json 解析函数

oracle json 解析函数
  • tstcylq
  • tstcylq
  • 2015年11月16日 09:46
  • 1786

oracle 解析JSON字符串

---------------------- --  解析JSON字符串  -- ---------------------- --p_jsonstr json字符串 --p_key 键 --返回p_...

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

Oracle解析json字符串 获取指定值自定义函数代码

http://www.oschina.net/code/snippet_1162040_48289 [1].[代码] ty_tbl_str_split 跳至 [1] [2] [3...
  • e_wsq
  • e_wsq
  • 2016年03月07日 14:06
  • 2181

Oracle解析复杂json的方法

问题背景:          当前在Oracle数据库(11G之前的版本)解析json没有可以直接使用的系统方法,网上流传的PLSQL脚本大多也只可以解析结构较单一的json串,对于结构复杂的j...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle split 以及 简单json解析存储过程
举报原因:
原因补充:

(最多只允许输入30个字)