Oracle Table Function

The Oracle Table function was introduced at Oracle9i, which is perfectly compatible with the benefits of view and stored procedures:

Application Examples:

1.Table () function:

Set feedback offcreate or replace type Type_int_date_varchar2 as Object (I integer, d date, v varchar2 ())/create or rep Lace type table_int_date_varchar2 as table of type_int_date_varchar2/create or replace function f_int_date_varchar2 retur n 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 * FROMTable(CAST (F_INT_DATE_VARCHAR2 () as TABLE_INT_DATE_VARCHAR2));d ROP 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 Repla Ce 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));