Generate a table with number sequence in sql

最新推荐文章于 2025-06-30 08:28:24 发布

最新推荐文章于 2025-06-30 08:28:24 发布 · 132 阅读

文章标签：

#SQL #Oracle #IDEA #C #C++

本文介绍了在 Oracle 数据库中使用 SQL 生成数字序列的方法，包括预定义集合类型、纯 SQL 连接以及通过自定义函数实现动态数字序列生成的技术细节。

Generate a table with number sequence in sql
=========

1 Pre-defined collection types in Oracle
---------

select to_number(column_value) as num from
   table(sys.dbms_debug_vc2coll(1,2,3,4,5,6,7,8,9));

select to_number(column_value) as num from
   table(sys.KU$_VCNT(1,2,3,4,5,6,7,8,9));

select column_value as num from
   table(sys.KU$_OBJNUMSET(1,2,3,4,5,6,7,8,9));

2 Join: pure sql
---------

select a.i+b.i+c.i+d.i+1 as num
from
   (select 0 i from dual union all select 1 from dual) a,
   (select 0 i from dual union all select 2 from dual) b,
   (select 0 i from dual union all select 4 from dual) c,
   (select 0 i from dual union all select 8 from dual) d
order by num;

3 How to make number dynamic?
---------
?

3.1 Define function with collection type as return value

-----------------

It's not only sql.

And myaybe it's not correct or appropriate for dynamic.

However, I like it.

First, define a type:

create or replace type T_NUM_TAB as TABLE OF NUMBER;

Then, define the function:

CREATE OR REPLACE FUNCTION NUM_COLLECT (
       NUM_START IN NUMBER, 
       NUM_END IN NUMBER,
       NUM_STEP IN NUMBER DEFAULT 1)
  RETURN T_NUM_TAB
AS
  l_tab   T_NUM_TAB := T_NUM_TAB();
  l_idx   NUMBER;
BEGIN
  l_idx := NUM_START;
  WHILE l_idx <= NUM_END LOOP
    l_tab.extend;
    l_tab(l_tab.last) := l_idx;
    l_idx := l_idx + NUM_STEP;
  END LOOP;

  RETURN l_tab;
END;

Now, use it as follows:

SQL> select * from table(num_collect(2,10,2));

COLUMN_VALUE
------------
           2
           4
           6
           8
          10

Is it a good idea to take sql string as parameter for the function?