SQL查询从双重返回N行 [英] SQL Query to return N rows from dual

问题描述

我想编写一个 SQL 查询，它接受一个绑定变量(比如:NUM)，它的输出包含一列 &:NUM 行数，每行都有自己的行号.即，如果我们将 :NUM 作为 7 传递，则输出应为:

I want to write a SQL query which accepts a bind variable (say :NUM) and its output consists of one column & :NUM number of rows, each row having its row number. i.e. if we pass :NUM as 7, the output should be:

VAL
====
1
2
3
4
5
6
7

查询中不应有任何实际的数据库表，也不应使用任何 PL/SQL 代码.即只应在查询中使用双重

There shouldn't be any actual DB tables in query and no PL/SQL code should be used. i.e. only dual should be used in the query

有什么办法可以做到这一点吗?

Is there any way to achieve this?

推荐答案

您可以使用:

 WHERE ROWNUM <= :NUM

...但表必须包含等于或大于绑定变量中的限制的行.此链接演示了 Oracle 中的各种行号生成技术.

...but the table has to contain row equal or greater to the limit in the bind variable. This link demonstrates various row number generation techniques in Oracle.

使用CONNECT BY，Oracle 10g+:

Using CONNECT BY, Oracle 10g+:

SELECT LEVEL
  FROM DUAL
CONNECT BY LEVEL <= :NUM

由 monojohnny 确认可以使用绑定变量.尝试在 Oracle 9i 上运行，但支持 CONNECT BY 语法会导致 ORA-01436 错误.

Confirmed by monojohnny that the bind variable can be used. Attempts to run on Oracle 9i, though CONNECT BY syntax is supported results in an ORA-01436 error.

我唯一不是 100% 的问题是 CONNECT BY 是否会接受绑定变量的限制.

The only thing I'm not 100% on is if the CONNECT BY will accept the limit from the bind variable.

参考:

• 整数系列生成器 - 按级别连接方法

这篇关于SQL查询从双重返回N行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！