【原创】Oracle实现PGSQL的generate_series-创新互联

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 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

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.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; /

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

标题名称：【原创】Oracle实现PGSQL的generate_series-创新互联
转载源于：http://6mz.cn/article/dsdsps.html