Use of table functions in Oracle

Table functions can use query statements or cursors as input parameters and output multiple rows of data. This function can be executed in parallel and continuously output data streams, known as pipeline output. The table function can be used to convert data in stages and save the storage and buffering of intermediate results. 1. Use a cursor to pass data. Use the cursor REFCURSOR to record a dataset (multiple rows

Table functions can use query statements or cursors as input parameters and output multiple rows of data. This function can be executed in parallel and continuously output data streams, known as pipeline output. The table function can be used to convert data in stages and save the storage and buffering of intermediate results. 1. Use the CURSOR to pass data. Use the cursor ref cursor to transfer a dataset (multiple rows of records

Table functions can use query statements or cursors as input parameters and output multiple rows of data. This function can be executed in parallel and continuously output data streams, known as pipeline output. The table function can be used to convert data in stages and save the storage and buffering of intermediate results.

1. Use a cursor to pass data
The cursor ref cursor can be used to pass a dataset (multiple rows of records) to the PL/SQL function:
SELECT *
From table (myfunction (CURSOR (SELECT *
FROM mytab )));

2. Use two materialized views (or tables) as the sample 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 ('U', 'uk', 'Fr ', 'els', '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) = '20140901'
Group by substr (s. calendar_month_desc, 1, 4 ),
S. calendar_month_desc,
C. cust_gender;

3. Define object types and table Types Based on Object Types
Define the object type and prepare for further reference.
(1) define 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) define the table TYPE: TYPE SUM_SALES_COUNTRY_T_TAB
Create type sum_sales_country_t_tab as table of sales_country_t;
(3) define 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) define the table TYPE: TYPE SUM_SALES_GENDER_T_TAB
Create type sum_sales_gender_t_tab as table of sales_gender_t;
(5) define 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) define 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 created 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 maid is table of sales_country_t_rec;

TYPE maid 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) define the table FUNCTION: 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 year, country, sum_amount_sold;

Exit when cur % NOTFOUND;
-- Exit when 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) define the 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) define the table FUNCTION: 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 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) define the table FUNCTION: 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 year, country_id, cust_gender, sum_amount_sold;

Exit when cur % NOTFOUND; -- Exit when 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 functions
The following SQL query statement calls a defined table function.
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 *
FROM sum_sales_country_mv )));

SELECT *
From table (table_ref_cur_week (CURSOR (SELECT *
FROM sum_sales_country_mv
WHERE country = 'au ')));