Use of table functions in Oracle __c language

a table function can accept a query statement or a cursor as an input parameter and can output multiple rows of data. The function can be executed in parallel, and the data stream can be continuously output, known as pipeline output. The table function can be used to process data in stages, eliminating the storage and buffering tables of intermediate results.

1. Passing Data with cursors
The use of cursor REF CURSOR to pass Datasets (multiline records) to the Pl/sql function:
SELECT *
From TABLE (MyFunction (CURSOR) (SELECT *
From Mytab)));

2. Use two materialized views (or tables) as boilerplate data
CREATE materialized VIEW SUM_SALES_COUNTRY_MV
Build IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
As
SELECT SUBSTR (S.calendar_month_desc, 1, 4) year, c.country_id country,
SUM (Sum_amount_sold) sum_amount_sold
From SUM_SALES_MONTH_MV S, customers C
WHERE s.cust_id = c.cust_id
and c.country_id in (' US ', ' UK ', ' FR ', ' ES ', ' JP ', ' AU ')
GROUP by SUBSTR (S.calendar_month_desc, 1, 4), c.country_id;

CREATE materialized VIEW SUM_ES_GEND_MV
Build DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
As
SELECT SUBSTR (S.calendar_month_desc, 1, 4) year,
S.calendar_month_desc Cal_month, C.cust_gender,
SUM (Sum_amount_sold) sum_amount_sold
From SUM_SALES_MONTH_MV s, customer C
WHERE s.cust_id = c.cust_id
and c.country_id = ' ES '
and Sunstr (S.calendar_month_desc, 1, 4) = ' 2000 '
GROUP by SUBSTR (S.calendar_month_desc, 1, 4),
S.calendar_month_desc,
C.cust_gender;

3. Defining the object type and the type of table based on the object type
Define object types and prepare for further references.
(1) Defining the Object type: Type sales_country_t
CREATE TYPE sales_country_t as OBJECT (
Year VARCHAR2 (4),
Country CHAR (2),
Sum_amount_sold number
);
(2) Defining the table type: Type Sum_sales_country_t_tab
CREATE TYPE Sum_sales_country_t_tab as TABLE of sales_country_t;
(3) Defining the Object type: Type sales_gender_t
CREATE TYPE sales_gender_t as OBJECT (
Year VARCHAR2 (4),
country_id CHAR (2),
Cust_gender CHAR (1),
Sum_amount_sold number
);
(4) Defining the table type: Type Sum_sales_gender_t_tab
CREATE TYPE Sum_sales_gender_t_tab as TABLE of sales_gender_t;
(5) Defining the object type: Type sales_roll_t
CREATE TYPE sales_roll_t as OBJECT (
Channel_desc VARCHAR2 (20),
country_id CHAR (2),
Sum_amount_sold number
);
(6) Defining the table type: Type Sum_sales_roll_t_tab
CREATE TYPE Sum_sales_roll_t_tab as TABLE of sales_roll_t;
(7) Check the established type
SELECT object_name, object_type, status
From User_objects
WHERE object_type = ' type ';

4. Definition Package: Create package and define REF CURSOR
CREATE OR REPLACE PACKAGE cursor_pkg
Is
TYPE Sales_country_t_rec is record (
Year VARCHAR (4),
Country CHAR (2),
Sum_amount_sold number
);

TYPE Sales_gender_t_rec is record (
Year VARCHAR2 (4),
country_id CHAR (2),
Cust_gender CHAR (1),
Sum_amount_sold number
);

TYPE Sales_roll_t_rec is record (
Channel_desc VARCHAR2 (20),
country_id CHAR (2),
Sum_amount_sold number
);

TYPE Sales_country_t_rectab is TABLE of Sales_country_t_rec;

TYPE Sales_roll_t_rectab is TABLE of Sales_roll_t_rec;

TYPE strong_refcur_t is REF CURSOR
return sales_country_t_rec;

TYPE row_refcur_t is REF CURSOR
return sum_sales_country_mv%rowtype;

TYPE roll_refcur_t is REF CURSOR
return sales_roll_t_rec;

TYPE refcur_t is REF CURSOR;
End corsor_pkg;

5. Define table Functions
(1) Defining table functions: Function Table_ref_cur_week
CREATE OR REPLACE FUNCTION table_ref_cur_week (cur cursor.refcur_t)
Return Sum_sales_country_t_tab
Is
Year VARCHAR (4);
Country CHAR (2);
Sum_amount_sold number;
Objset Sum_sales_country_t_tab: = Sum_sales_country_t_tab ();
I number: = 0;
BEGIN
LOOP
--Fetch from cursor variable
FETCH cur
Into the year, country, Sum_amount_sold;

EXIT when Cur%notfound;
---Exit when the last row is fetched
--Append to Collection
I: = i + 1;
Objset. EXTEND;
Objset (i): = sales_country_t (year, country, Sum_amount_sold);
End LOOP;

Close cur;

   return Objset;
End;
/
(2) defines a table function: function Table_ref_cur_strong
CREATE OR REPLACE FUNCTION table_ref_cur_strong (Cur cursor_ pkg.strong_refcur_t)
   return sum_sales_country_t_tab pipelined
is
    Year              varchar (4);
   country           char (2);
   sum_amount_sold   NUMBER;
   i                  number      := 0;
BEGIN
   loop
      fetch cur
        into year, country, Sum_amount_sold;

EXIT when Cur%notfound; --Exit when last row fetched
PIPE ROW (sales_country_t (year, country, Sum_amount_sold));
End LOOP;

Close cur;

return;
End;
/
(3) Defining table functions: Function Table_ref_cur_row
CREATE OR REPLACE FUNCTION table_ref_cur_row (cur cursor_pkg.row_refcur_t)
return Sum_sales_country_t_tab pipelined
Is
In_rec Cur%rowtype;
Out_rec sales_country_t: = sales_country_t (null, NULL, NULL);
BEGIN
LOOP
FETCH cur
into In_rec;

EXIT when Cur%notfound; ---Exit when the last row is fetched
Out_rec. Year: = In_rec. year;
Out_rec.country: = In_rec.country;
Out_rec.sum_amount_sold: = In_rec.sum_amount_sold;
PIPE ROW (OUT_REC);
End LOOP;

Close cur;

return;
End;
/
(4) Defining table functions: Function Gender_table_ref_cur_week
CREATE OR REPLACE FUNCTION gender_table_ref_cur_week (cur cursor_pkg.refcur_t)
Return Sum_sales_gender_t_tab
Is
Year VARCHAR2 (4);
country_id CHAR (2);
Cust_gender CHAR (1);
Sum_amount_sold number;
Objset Sum_sales_gender_t_tab: = Sum_sales_gender_t_tab ();
I number: = 0;
BEGIN
LOOP
FETCH cur
Into the year, country_id, Cust_gender, Sum_amount_sold;

EXIT when Cur%notfound; ---Exit when the last row is fetched
I: = i + 1;
Objset. EXTEND;
Objset (i): =
sum_sales_gender_t (year, country_id, Cust_gender, sum_amount_sold);
End LOOP;

Close cur;

return objset;
End;
/

6. Call table function
The following SQL query statement invokes a table function that has already been defined.
SELECT *
From TABLE (Table_ref_cur_week (CURSOR) (SELECT *
From SUM_SALES_COUNTRY_MV)));

SELECT *
From TABLE (Table_ref_cur_strong (CURSOR) (SELECT *
From SUM_SALES_COUNTRY_MV)));

Select *
  from TABLE (Table_ref_cur_row (CURSOR) (SELECT *
      & nbsp;                                      from sum_sales_country_mv));

Select *
  from TABLE (Table_ref_cur_week (CURSOR) (SELECT *
      & nbsp;                                       from SUM_SALES_COUNTRY_MV
                                             where country = ' AU '));