oracle a table of,Oracle Table Function

Oracle Table Function在Oracle9i时引入，完美的兼容了view和存储过程的优点：

应用举例：

1.Table()函数:

set feedback off

create or replace type type_int_date_varchar2 as object (i integer, d date, v varchar2(99))

/

create or replace type table_int_date_varchar2 as table of

type_int_date_varchar2

/

create or replace function f_int_date_varchar2 return table_int_date_varchar2 as

a_type_int_date_varchar2 table_int_date_varchar2 := table_int_date_varchar2();

begin

a_type_int_date_varchar2.extend;

a_type_int_date_varchar2(a_type_int_date_varchar2.count) :=

type_int_date_varchar2(1,to_date(‘08.01.1947‘,‘dd.mm.yyyy‘), ‘David Bowie‘);

a_type_int_date_varchar2.extend;

a_type_int_date_varchar2(a_type_int_date_varchar2.count) :=

type_int_date_varchar2(2,to_date(‘27.01.1756‘,‘dd.mm.yyyy‘), ‘Mozart‘);

return a_type_int_date_varchar2;

end;

/

select * from table (cast (f_int_date_varchar2() as table_int_date_varchar2));

drop function f_int_date_varchar2;

drop type table_int_date_varchar2;

drop type type_int_date_varchar2;

2.A Function that returns a table of dates in a Range

create or replace type date_obj as object (dt date)

/

create or replace type date_table as table of date_obj

/

create or replace function date_range(from_dt in date, to_dt in date)

return date_table as

a_date_table date_table := date_table();

cur_dt date:=from_dt;

begin

while cur_dt <= to_dt loop

a_date_table.extend;

a_date_table(a_date_table.count) := date_obj(cur_dt);

cur_dt := cur_dt + 1;

end loop;

return a_date_table;

end date_range;

/

select * from table (

cast ( date_range(

to_date(‘01.01.2002‘,‘dd.mm.yyyy‘),

to_date(‘31.01.2002‘,‘dd.mm.yyyy‘)

)

as date_table

));

原文：http://blog.csdn.net/davidwang9527/article/details/25697611