What are the different ways to generate rows?
January 26th, 2006 By Eddie Awad
Sometimes you need to generate rows while working on a SQL problem. I have come across three different ways to accomplish this:
HR@XE> variable num_of_rows number HR@XE> exec :num_of_rows := 11 PL/SQL procedure successfully completed.
Using cube with dual:
HR@XE> select rownum
2 from (
3 select null from dual
4 group by cube (1,2,3,4) -- Generates 2*2*2*2 rows
5 )
6 where rownum <= :num_of_rows
7 /
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
11 rows selected.
Using connect by level with dual (9i and above):
HR@XE> select *
2 from (
3 select level recnum from dual
4 connect by level <= :num_of_rows
5 )
6 /
RECNUM
----------
1
2
3
4
5
6
7
8
9
10
11
11 rows selected.
Using just any table you can select from having the right number of rows:
HR@XE> select rownum recnum
2 from all_objects
3 where rownum <= :num_of_rows
4 /
RECNUM
----------
1
2
3
4
5
6
7
8
9
10
11
11 rows selected.
January 30th, 2006 at 8:00 am
Another way to generate rows is to use pipelined function
create or replace type t_number as table of number
/
create or replace function generate(v_inf number, v_sup number) return t_number
pipelined
as
begin
for i in v_inf..v_sup loop
pipe row (i);
end loop;
return;
end;
/
select * from table(cast(generate(1,11) as t_number))
/
Best regards,
Claudiu Ariton
February 8th, 2006 at 11:56 am
Seen on asktom website, you may use this:
create or replace function vtable( n in number default null ) return array PIPELINED as begin for i in 1 .. nvl(n,999999999) loop pipe row(i); end loop; return; end; /Sample usage:
February 9th, 2006 at 8:41 pm
10G, MODEL clause, just plug in the ITERATE :
SELECT x from dual
MODEL DIMENSION BY (1 AS z) MEASURES (1 x)
RULES ITERATE (7) (x[ITERATION_NUMBER]=ITERATION_NUMBER+1);
February 11th, 2006 at 11:00 am
Igor,
your example with model clause ist very impressing for me! Did not worke with 10G model clause yet
karl
February 23rd, 2006 at 1:37 pm
Hi,
The Date variant of igors model example :
Greetings
Karl