Oracle實現POSTGRESQL的generate_series功能

標籤：data   pipelined   rac   turn   replace   line   track   null   from   

PostgreSQL 有產生序列的函數，能夠方便的進行造資料。這裡我用ORACLE實現了PGSQL的generate_series函數功能。
POSTGRESQL.

t_girl=# select * from generate_series(1,10); generate_series-----------------               1               2               3               4               5               6               7               8               9              10(10 行記錄)時間：1.290 mst_girl=# select * from generate_series(1,10,2); generate_series-----------------               1               3               5               7               9(5 行記錄)時間：0.431 mst_girl=# select * from generate_series(1,10,3); generate_series-----------------               1               4               7              10(4 行記錄)時間：0.879 mst_girl=# select * from generate_series(2,10,3); generate_series-----------------               2               5               8(3 行記錄)時間：0.867 mst_girl=# select count(*) from generate_series(1,1000); count-------  1000(1 行記錄)時間：1.142 mst_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

ORACLE：

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.0212:10:36 SQL> select * from table(ytt_generate_series(1,10,2));COLUMN_VALUE                                                                    ------------                                                                               1                                                                               3                                                                               5                                                                               7                                                                               9                                                                    經過時間:  00: 00: 00.0012:10:54 SQL> select * from table(ytt_generate_series(1,10,3));COLUMN_VALUE                                                                    ------------                                                                               1                                                                               4                                                                               7                                                                              10                                                                    經過時間:  00: 00: 00.0012:10:56 SQL> select * from table(ytt_generate_series(2,10,3));COLUMN_VALUE                                                                    ------------                                                                               2                                                                               5                                                                               8                                                                    經過時間:  00: 00: 00.0212:11:04 SQL> select count(*) from table(ytt_generate_series(1,1000));  COUNT(*)                                                                      ----------                                                                            1000                                                                      經過時間:  00: 00: 00.1313:32:13 SQL> select to_char(sysdate - n,‘YYYY-MM-DD‘) from table(ytt_generate_series(1,10));TO_CHAR(SY----------2015-12-082015-12-072015-12-062015-12-052015-12-042015-12-032015-12-022015-12-012015-11-302015-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/9create 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 pipelinedis   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;/

Oracle實現POSTGRESQL的generate_series功能