Oracle 产生序列的 6 种方法

来源：互联网发布：南风知我意1百度云编辑：程序博客网时间：2024/06/01 14:49

方法一：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 tsys@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