Я хочу написать SQL-запрос, который принимает переменную связывания (скажем: NUM), и ее вывод состоит из одного столбца и: NUM число строк, каждая строка имеет номер строки. т.е. если мы пройдем: NUM как 7, выход должен быть:
VAL
====
1
2
3
4
5
6
7
В запросе не должно быть никаких фактических таблиц БД, и код PL/SQL не должен использоваться. т.е. в запросе следует использовать только двойное выражение
Есть ли способ достичь этого?
Вы можете использовать:
WHERE ROWNUM <= :NUM
... но таблица должна содержать строку, равную или превышающую предел в переменной привязки. Эта ссылка демонстрирует различные методы генерации числа строк в Oracle.
Используя CONNECT BY, Oracle 10g +:
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= :NUM
Подтверждено monojohnny, что можно использовать переменную связывания. Попытки запуска на Oracle 9i, хотя поддерживается синтаксис CONNECT BY, приводят к ошибке ORA-01436.
Единственное, на что я не на 100%, - это если CONNECT BY примет ограничение от переменной привязки.
Ссылка:
Попробуйте что-то вроде:
SELECT 1 AS Val FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 5 FROM dual
UNION ALL SELECT 6 FROM dual
UNION ALL SELECT 7 FROM dual;
Это беспорядочно, но это будет трюк.
Отредактировано: Ah - вам нужно передать переменную, чтобы вы знали, как высоко идти...
Итак, как насчет чего-то вроде:
SELECT t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 AS Val
FROM
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t1,
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t2,
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t3,
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t4
WHERE t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 <= 7;
ОК... редактирование снова, теперь используя WITH:
WiTH
A0 AS (SELECT 0 as N FROM DUAL UNION ALL SELECT 0 FROM DUAL),
A1 AS (SELECT 0 as N FROM A0, A0 AS B),
A2 AS (SELECT 0 as N FROM A1, A1 AS B),
A3 AS (SELECT 0 as N FROM A2, A2 AS B),
A4 AS (SELECT 0 as N FROM A3, A3 AS B),
A5 AS (SELECT 0 as N FROM A4, A4 AS B),
A6 AS (SELECT 0 as N FROM A5, A5 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY N) AS Val FROM A6)
SELECT *
FROM Nums
WHERE Val <= :NUM
;
Я не придумал этот ответ [так что убедитесь, что все голоса идут правильно!], это просто мои тестовые заметки, основанные на "OMG Ponies" [кто не был уверен, будет ли метод работать со связыванием переменная] выше для справки:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> var num_rows number
SQL> begin select 20 into :num_rows from dual;
2 end;
3 /
PL/SQL procedure successfully completed.
SQL> select level from dual
2 connect by level <=:num_rows;
LEVEL
----------
1
2
3
4
...
Запрос без подключения
WITH num(n) as(select 1 from dual union all
select n+1 from num where n <= :num_limit)
select * from num
Я отмечаю эту вики сообщества, поскольку на самом деле она не отвечает вашим требованиям без таблиц, но одна из первых вещей, которые мы делаем при установке базы данных, - это создать набор таблиц для этой цели.
Таким образом, мы значительно уменьшаем сложность и увеличиваем скорость большого количества наших запросов за счет (минимального и дешевого) дискового пространства.
Вам следует подумать над этим. Помимо сохранения таблицы дат, вам не нужно много внимания.
Другое решение потребует некоторого PL/SQL для создания функции для возврата коллекции со строками... Не так просто, как подход select level from dual connect by level <= :b1, но он полезен в нескольких ситуациях:
1) Создайте тип объекта таблицы номеров (number_tbl, в этом примере):
create or replace type number_tbl as table of number;
2) Создайте функцию, которая получит количество строк, которые будут сгенерированы, а затем вернет объект number_tbl с результатами:
create or replace function get_rows( i_num_rows number ) return number_tbl as
t number_tbl := number_tbl();
begin
if i_num_rows < 1 then
return null;
end if;
t.extend( i_num_rows );
for i in 1..i_num_rows loop
t(i) := i;
end loop;
return t;
end get_rows;
3) выберите из своей функции функцию table( ... ), чтобы превратить ваш объект number_tbl в что-то по выбору:
select * from table( cast ( get_rows( :b1 ) as number_tbl ) );
connect by - такая замечательная вещь. Это поможет вам сгенерировать несколько строк с одним набором данных, доступных в двойной таблице. Это может помочь вам создать огромное количество строк для ваших фиктивных данных. Например
insert into test select a.* from test1 a,(select * from dual connect by level <=100000) b;
или вы можете сделать что-то вроде этого
Пример 2: Вы хотите напечатать квадрат и куб чисел от 1 до 10.
SQL> select level "No", power(level,2) "Square", power(level,3) "Cube" from dual connect by level <= 10;
No Square Cube
---------- ---------- ----------
1 1 1
2 4 8
3 9 27
4 16 64
5 25 125
6 36 216
7 49 343
8 64 512
9 81 729
10 100 1000
Следовательно, вы можете манипулировать им в любой форме. Так вы можете возвращать несколько строк из двойной таблицы. Ссылки: http://www.oraclebin.com/2012/12/multipe-rows-from-dual-table.html
Другой способ - использовать выражение диапазона XQuery, например:
select column_value from xmltable(:a||' to '||:b);
1
2
3
4
5
6
7
8
9
10
Это решение довольно гибкое, например:
select column_value from xmltable('5 to 10, 15 to 20');
5
6
7
8
9
10
15
16
17
18
19
20
WITH cte_numbers(n)
AS (
SELECT 0
UNION ALL
SELECT n + 1
FROM cte_numbers
WHERE n < 10
)
SELECT n
FROM cte_numbers;
Возвращенные строки 0 1 2 3 4 5 6 7 8 9 10
В зависимости от базы данных может использоваться различный метод.
PostgreSQL имеет приятную особенность - series.
Чтобы получить то, что вы хотите просто хотите:
SELECT * FROM generate_series(1, NUM);