Oracle 產生序列的 6 種方法

方法一：

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    

By David Lin

2013-06-19

Always be a first-rate version of yourself,insted of a second-rate version of someone else