Oracle 如何生成连续的数字/字符/时间序列Tony.Dong的专栏-oracle 循环字母

大家好，我是只谈技术不剪发的 Tony 老师。今天我们继续介绍如何在 Oracle 数据库中生成连续的数字/字符/时间序列。

📝如果你使用的是 MySQL 数据库，实现相同的功能可以参考这篇文章。
📝如果你使用的是 PostgreSQL，实现相同的功能可以参考这篇文章。
📝如果你使用的是 SQL Server，实现相同的功能可以参考这篇文章。

在 Oracle 中实现数据行生成的方法有很多，本文只介绍几种常用的语法。

使用层次查询生成序列

Oracle 提供了 CONNECT BY 层次查询，可以用于生成数字序列。

生成一个连接的数字序列

以下语句使用 CONNECT BY 和 LEVEL 伪列生成了一个连续的数字序列：

SELECT LEVEL AS n FROM dual WHERE LEVEL >= 11 CONNECT BY LEVEL <= 15; N | --| 11| 12| 13| 14| 15| 

通过修改查询条件中的 LEVEL 范围，可以返回不同的数字序列。

除了使用 LEVEL 伪列之外，也可以使用 ROWNUM 伪列实现相同的功能：

SELECT rownum AS n FROM dual CONNECT BY LEVEL <= 5; N| -| 1| 2| 3| 4| 5| 

生成一个间隔的数字序列

以下查询利用 mod 函数返回了一个从 2 到 15 之间、增量为 3 的数字序列：

SELECT LEVEL AS n FROM dual WHERE LEVEL >= 2 AND MOD(LEVEL-2, 3)=0 CONNECT BY LEVEL <= 15; N | --| 2| 5| 8| 11| 14| 

以下查询返回了一个增量为 -2.5、范围从 15 到 1.4 之间的降序数字序列：

SELECT (LEVEL-1) * -2.5 + 15 AS n FROM dual WHERE LEVEL >= 1 CONNECT BY (LEVEL-1) * -2.5 + 15 >= 1.4; N   | ----| 15| 12.5| 10| 7.5| 5| 2.5| 

生成一个连续的字符序列

基于上面的层次查询和 chr(n) 函数可以生成连续的字符序列。例如：

SELECT CHR(LEVEL-1+65) AS letter FROM dual  CONNECT BY LEVEL-1 <= 70-65; LETTER| ------| A     | B     | C     | D     | E     | F     | 

该查询返回了字符 A 到 F 的序列，chr(n) 函数用于将 ASCII 编码转化为相应的字符。

生成一个间隔的时间序列

同样基于以上层次查询和时间加减法可以生成间隔的时间序列。例如：

SELECT TIMESTAMP '2020-01-01 00:00:00' + (LEVEL-1)/24 AS ts FROM dual  CONNECT BY LEVEL <= 12; TS                 | -------------------| 2020-01-01 00:00:00| 2020-01-01 01:00:00| 2020-01-01 02:00:00| 2020-01-01 03:00:00| 2020-01-01 04:00:00| 2020-01-01 05:00:00| 2020-01-01 06:00:00| 2020-01-01 07:00:00| 2020-01-01 08:00:00| 2020-01-01 09:00:00| 2020-01-01 10:00:00| 2020-01-01 11:00:00| 

该查询返回了 2020-01-01 00:00:00 到 2020-01-01 12:00:00、间隔为 1 小时的所有时间点。

使用表函数生成序列

Oracle 支持表函数（table function），也就是返回结果为集合（表）的函数，可以用于模拟 PostgreSQL 中的 generate_series 函数。

创建模拟的 generate_series 函数

我们创建一个 PL/SQL 函数 generate_series：

CREATE OR REPLACE FUNCTION generate_series (pstart IN NUMBER, pstop IN NUMBER, pstep IN NUMBER DEFAULT 1) RETURN sys.odcinumberlist DETERMINISTIC PIPELINED AS BEGIN IF (pstep = 0) THEN     raise_application_error(-20001, 'step size cannot equal zero!'); END IF; IF (pstart > pstop AND pstep > 0) OR (pstart < pstop AND pstep < 0) THEN RETURN; END IF; FOR i IN 0 .. floor(abs((pstop-pstart)/pstep)) LOOP     PIPE ROW (pstart + i * pstep); END LOOP; RETURN; END generate_series; 

其中，sys.odcinumberlist 是 Oracle 预定义的变长数组类型；PIPELINED 表示定义管道表函数；pstart 表示数据序列的起点，pstop 表示数据序列的终点，pstep 表示每次的增量，不允许为 0，默认为 1。

使用 generate_series 函数生成序列

创建了 generate_series 函数之后，我们就可以用它来生成各种序列值。例如：

SELECT * FROM TABLE(generate_series(11, 15)); COLUMN_VALUE| ------------| 11| 12| 13| 14| 15| SELECT * FROM TABLE(generate_series(15, 1.4, -2.5)); COLUMN_VALUE| ------------| 15| 12.5| 10| 7.5| 5| 2.5| 

其中，TABLE 函数用于将数组转换为表；第一个函数返回了 11 到 15 的连续整数；第二个函数返回了 15 到 1.4 之间增量为 -2.5 的降序序列。

我们同样可以使用 generate_series 函数生成字符序列和时间序列：

SELECT chr(column_value) FROM TABLE(generate_series(65, 70)); CHR(COLUMN_VALUE)| -----------------| A                | B                | C                | D                | E                | F                | SELECT TIMESTAMP '2020-01-01 00:00:00' + (column_value-1)/24 AS ts FROM TABLE(generate_series(1, 12)); TS                 | -------------------| 2020-01-01 00:00:00| 2020-01-01 01:00:00| 2020-01-01 02:00:00| 2020-01-01 03:00:00| 2020-01-01 04:00:00| 2020-01-01 05:00:00| 2020-01-01 06:00:00| 2020-01-01 07:00:00| 2020-01-01 08:00:00| 2020-01-01 09:00:00| 2020-01-01 10:00:00| 2020-01-01 11:00:00| 

使用通用表表达式生成序列

生成一个等差数字序列

通用表表达式（Common Table Expression）的递归调用可以用于生成各种数列。例如：

WITH t(n) AS ( SELECT 1 FROM dual   UNION ALL SELECT n+2 FROM t WHERE n < 9 ) SELECT n FROM t; N| -| 1| 3| 5| 7| 9| 

以上语句生成了一个从 1 递增到 9、增量为 2 的数列，执行过程如下：

• 首先，执行 CTE 中的初始化查询，生成一行数据（1）；
• 然后，第一次执行递归查询，判断 n < 9，生成一行数据 3（n+2）；
• 接着，重复执行递归查询，生成更多的数据；直到 n = 9 时不满足条件终止递归；此时临时表 t 中包含 5 条数据；
• 最后，执行主查询，返回所有的数据。

生成一个等比数字序列

上文模拟的 generate_series 函数只能生成等差数列，通用表表达式则可以生成更复杂的数列，例如等比数列：

WITH t(n) AS ( SELECT 1 FROM dual   UNION ALL SELECT n*3 FROM t WHERE n < 100 ) SELECT n FROM t; N  | ---| 1| 3| 9| 27| 81| 243| 

从第二行开始，每个数字都是上一行的 3 倍。

生成斐波那契数列

斐波那契数列（Fibonacci series）是指从数字 0 和 1（或者从 1 和 1）开始，后面的每个数字等于它前面两个数字之和（0、1、1、2、3、5、8、13、21、…）。使用通用表表达式可以很容易地生成斐波那契数列：

WITH fibonacci (n, fib_n, next_fib_n) AS ( SELECT 1, 0, 1 FROM dual   UNION ALL SELECT n + 1, next_fib_n, fib_n + next_fib_n   FROM fibonacci   WHERE n < 10 ) SELECT * FROM fibonacci; N |FIB_N|NEXT_FIB_N| --|-----|----------| 1| 0| 1| 2| 1| 1| 3| 1| 2| 4| 2| 3| 5| 3| 5| 6| 5| 8| 7| 8| 13| 8| 13| 21| 9| 21| 34| 10| 34| 55| 

其中，字段 n 表示该行包含了第 n 个斐波那契数列值；字段 fib_n 表示斐波那契数列值；字段 next_fib_n 表示下一个斐波那契数列值。

生成一个连续的字符序列

基于通用表表达式和 CHR(n) 函数同样可以生成连续的字符序列，例如：

WITH t(n) AS ( SELECT 65 FROM dual   UNION ALL SELECT n+1 FROM t WHERE n <= 70 ) SELECT chr(n) FROM t; CHR(N)| ------| A     | B     | C     | D     | E     | F     | G     | 

生成一个间隔的时间序列

以下语句使用递归通用表表达式生成一个时间序列：

WITH ts(v) AS ( SELECT TIMESTAMP '2020-01-01 00:00:00' FROM dual   UNION ALL SELECT v + 1/24 FROM ts WHERE v < TIMESTAMP '2020-01-01 12:00:00' ) SELECT v FROM ts; V                  | -------------------| 2020-01-01 00:00:00| 2020-01-01 01:00:00| 2020-01-01 02:00:00| 2020-01-01 03:00:00| 2020-01-01 04:00:00| 2020-01-01 05:00:00| 2020-01-01 06:00:00| 2020-01-01 07:00:00| 2020-01-01 08:00:00| 2020-01-01 09:00:00| 2020-01-01 10:00:00| 2020-01-01 11:00:00| 2020-01-01 12:00:00| 

以上查询返回了一个表，数据为 2020-01-01 00:00:00 到 2020-01-01 12:00:00，间隔为 1 小时的时间点。

如果觉得文章对你有用，欢迎关注❤️、评论📝、赞👍