t_girl=# select * from generate_series(1,10);
generate_series
-----------------
1
2
3
4
5
6
7
8
9
10
(10 行記錄)
時間：1.290 ms
t_girl=# select * from generate_series(1,10,2);
generate_series
-----------------
1
3
5
7
9
(5 行記錄)
時間：0.431 ms
t_girl=# select * from generate_series(1,10,3);
generate_series
-----------------
1
4
7
10
(4 行記錄)
時間：0.879 ms
t_girl=# select * from generate_series(2,10,3);
generate_series
-----------------
2
5
8
(3 行記錄)
時間：0.867 ms
t_girl=# select count(*) from generate_series(1,1000);
count
-------
1000
(1 行記錄)
時間：1.142 ms
t_girl=# select to_char(now() - '1 day'::interval * n,'YYYY-MM-DD') as result from generate_series(1,10) g(n);
result
------------
2015-12-08
2015-12-07
2015-12-06
2015-12-05
2015-12-04
2015-12-03
2015-12-02
2015-12-01
2015-11-30
2015-11-29
(10 行記錄)
時間：17.284 ms
12:10:34 SQL> select * from table(ytt_generate_series(1,10));
COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10
已選擇 10 行。
已用時間: 00: 00: 00.02
12:10:36 SQL> select * from table(ytt_generate_series(1,10,2));
COLUMN_VALUE
------------
1
3
5
7
9
已用時間: 00: 00: 00.00
12:10:54 SQL> select * from table(ytt_generate_series(1,10,3));
COLUMN_VALUE
------------
1
4
7
10
已用時間: 00: 00: 00.00
12:10:56 SQL> select * from table(ytt_generate_series(2,10,3));
COLUMN_VALUE
------------
2
5
8
已用時間: 00: 00: 00.02
12:11:04 SQL> select count(*) from table(ytt_generate_series(1,1000));
COUNT(*)
----------
1000
已用時間: 00: 00: 00.13
13:32:13 SQL> select to_char(sysdate - n,'YYYY-MM-DD') from table(ytt_generate_series(1,10));
TO_CHAR(SY
----------
2015-12-08
2015-12-07
2015-12-06
2015-12-05
2015-12-04
2015-12-03
2015-12-02
2015-12-01
2015-11-30
2015-11-29
已選擇 10 行。
已用時間: 00: 00: 00.01
-- Declare result set.
create or replace type ytt_num is object
( n number);
/
create or replace type numbers_table is table of ytt_num;
/
-- Function body.
-- Created by ytt.
-- 2015/12/9
create or replace function ytt_generate_series
(
f_start_num number := 1, -- Start number.
f_end_num number, -- Finish number.
f_step_num number := 1 -- Step.
)
return numbers_table pipelined
is
list numbers_table := numbers_table();
i number := 0;
j number := 1;
begin
i := f_start_num;
j := 1;
-- Increase nested table's size.
list.extend(f_end_num);
-- Loop begin.
while i <= f_end_num loop
-- Initlization.
list(j) := ytt_num(null);
list(j).n := i;
pipe row(list(j));
i := i + f_step_num;
j := j + 1;
end loop;
return;
end;
/
