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

参考：https://www..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>