Generate a table with number sequence in 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 numfrom (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) dorder 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_TABASl_tab T_NUM_TAB := T_NUM_TAB();l_idx NUMBER;BEGINl_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?
页:
[1]