Oracle产生序列的6种方法
Oracle产生序列的6种方法
[sql]
方法一：
sys@ORCL> select level from dual connect by level<=5;
LEVEL
----------
1
2
3
4
5
方法二：
sys@ORCL> select rownum from dba_objects where rownum<=5;
ROWNUM
----------
1
2
3
4
5
方法三：
sys@ORCL> select to_number(column_value) n from xmltable('1 to 5');
N
----------
1
2
3
4
5
方法四：
select n
from (select 1 v from dual)
model
dimension by (v)
measures (v n)
rules
(
n[for v from 2 to 5 increment 1] = n[cv(v)-1] + 1
)
sys@ORCL> /
N
----------
1
2
3
4
5
方法五：
with t(n) as
(
select 1 from dual
union all
select n + 1 from t where n < 5
)
select n from t
sys@ORCL> /
N
----------
1
2
3
4
5
方法六：
create or replace function generator (n pls_integer) return sys.odcinumberlist pipelined is
m pls_integer := trunc(n / 10);
r pls_integer := n - 10 * m;
begin
for i in 1 .. m loop
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
end loop;
for i in 1 .. r loop
pipe row (null);
end loop;
end;
/
alter function generator compile plsql_code_type = native;
sys@ORCL> select * from table(generator(5));
N
----------
1
2
3
4
5
