Oracle函数返回结果集(数字序列、时间序列)

最新推荐文章于 2023-05-10 19:35:56 发布

Mentses

最新推荐文章于 2023-05-10 19:35:56 发布

阅读量638

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： Oracle Oracle函数 Oracle返回结果集 Oracle生成序列

本文链接：https://blog.youkuaiyun.com/yuxiao_1989/article/details/89847934

Oracle 专栏收录该内容

7 篇文章

订阅专栏

博客给出参考链接https://www.cnblogs.com/senyier/p/6611952.html ，包含Oracle相关代码及运行效果内容，涉及Oracle函数、返回结果集、生成序列等信息技术知识。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考：https://www.cnblogs.com/senyier/p/6611952.html

代码：

CREATE OR REPLACE PACKAGE PKG_TOOL IS

  TYPE DATE_RECORD_NUM IS RECORD(COL_NUM NUMBER);

  TYPE DATE_RECORD_DATE IS RECORD(COL_DATE DATE);

  TYPE DATE_TABLE_NUM IS TABLE OF DATE_RECORD_NUM;

  TYPE DATE_TABLE_DATE IS TABLE OF DATE_RECORD_DATE;

  FUNCTION F_GET_SEQ_NUM(NUM1 NUMBER, NUM2 NUMBER, GAP NUMBER DEFAULT 1) RETURN DATE_TABLE_NUM PIPELINED;
  
  FUNCTION F_GET_SEQ_DATE(DATE1 DATE, DATE2 DATE, GAP NUMBER DEFAULT 60) RETURN DATE_TABLE_DATE PIPELINED;
    
END PKG_TOOL;

CREATE OR REPLACE PACKAGE BODY PKG_TOOL IS

  FUNCTION F_GET_SEQ_NUM(NUM1 NUMBER, NUM2 NUMBER, GAP NUMBER DEFAULT 1)
    RETURN DATE_TABLE_NUM
    PIPELINED IS
    L_RESULT DATE_RECORD_NUM;
  BEGIN
    FOR REC IN (SELECT GAP * (LEVEL - 1) + LEAST(NUM1, NUM2) NN
                  FROM DUAL
                CONNECT BY ROWNUM <= CEIL((ABS(NUM2 - NUM1) + 1) / GAP)) LOOP
      L_RESULT.COL_NUM := REC.NN;
      PIPE ROW(L_RESULT);
    END LOOP;
  END F_GET_SEQ_NUM;

  FUNCTION F_GET_SEQ_DATE(DATE1 DATE, DATE2 DATE, GAP NUMBER DEFAULT 60)
    RETURN DATE_TABLE_DATE
    PIPELINED IS
    L_RESULT DATE_RECORD_DATE;
  BEGIN
    FOR REC IN (SELECT GAP * ((LEVEL - 1) / 24 / 3600) + LEAST(DATE1, DATE2) NN
                  FROM DUAL
                CONNECT BY ROWNUM <=
                           CEIL(ABS(DATE2 - DATE1) * 24 * 3600 + 1) / GAP) LOOP
      L_RESULT.COL_DATE := REC.NN;
      PIPE ROW(L_RESULT);
    END LOOP;
  END F_GET_SEQ_DATE;

END PKG_TOOL;

运行效果：

SQL> select * from table(PKG_TOOL.F_GET_SEQ_NUM(1,10));
   COL_NUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected

SQL> select * from table(PKG_TOOL.F_GET_SEQ_NUM(1,20,5));
   COL_NUM
----------
         1
         6
        11
        16

SQL> select TO_CHAR(COL_DATE,'yyyy-mm-dd hh24:mi:ss') from table(PKG_TOOL.F_GET_SEQ_DATE(to_date('2019-05-01 10:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-05-01 10:10:00','yyyy-mm-dd hh24:mi:ss'),60));
TO_CHAR(COL_DATE,'YYYY-MM-DDHH
------------------------------
2019-05-01 10:00:00
2019-05-01 10:01:00
2019-05-01 10:02:00
2019-05-01 10:03:00
2019-05-01 10:04:00
2019-05-01 10:05:00
2019-05-01 10:06:00
2019-05-01 10:07:00
2019-05-01 10:08:00
2019-05-01 10:09:00
10 rows selected

SQL> select TO_CHAR(COL_DATE,'yyyy-mm-dd hh24:mi:ss') from table(PKG_TOOL.F_GET_SEQ_DATE(to_date('2019-05-01 10:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2019-05-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),3600));
TO_CHAR(COL_DATE,'YYYY-MM-DDHH
------------------------------
2019-05-01 10:00:00
2019-05-01 11:00:00
2019-05-01 12:00:00
2019-05-01 13:00:00
2019-05-01 14:00:00
2019-05-01 15:00:00
2019-05-01 16:00:00
2019-05-01 17:00:00
2019-05-01 18:00:00
2019-05-01 19:00:00
2019-05-01 20:00:00
2019-05-01 21:00:00
2019-05-01 22:00:00
2019-05-01 23:00:00
14 rows selected

SQL>