PL/SQL - Create an Object | create table function

Object Creation:

CREATE OR REPLACE TYPE SALES_ROW AS OBJECT

(

S_DATE DATE,

S_ORDERID NUMBER,

S_PRODUCTID NUMBER,

S_CUSTOMERID NUMBER,

S_TOTALAMOUNT NUMBER

);

CREATE a table of  type  SALES_ROW

CREATE TYPE SALES_TABLE IS TABLE OF SALES_ROW;

Table function:

CREATE OR REPLACE FUNCTION FETCH_SALES_TABLE (S_ORDERID NUMBER)

RETURN SALES_TABLE

IS

L_TAB SALES_TABLE := SALES_TABLE();

BEGIN

FOR C IN 

(

SELECT SALES_DATE,ORDER_ID,PRODUCT_ID, CUSTOMER_ID, TOTAL_AMOUNT FROM SALES WHERE ORDER_ID = S_ORDERED

)

LOOP

L_TAB.EXTEND;

L_TAB(L_TAB.LAST) := SALES_ROW(C.SALES_DATE,C.ORDER_ID,C.PRODUCT_ID,C.CUSTOMER_ID,C.TOTAL_AMOUNT);

END LOOP;

RETURN L_TAB;

END;

NOW we can run this function just like querying from a regular table:

Select * from table (fetch_sales_table(1267));

Pipelined functions:

Pipeline negates the need to build huge collections by piping rows out of the function as they are created  saving memory and allowing subsequent processing to start before all the rows are generated.

CREATE OR REPLACE FUNCTION FETCH_SALES_TABLE (S_ORDERID NUMBER)

RETURN SALES_TABLE

PIPELINED

IS

BEGIN

FOR C IN 

(

SELECT SALES_DATE,ORDER_ID,PRODUCT_ID, CUSTOMER_ID, TOTAL_AMOUNT FROM SALES WHERE ORDER_ID = S_ORDERED

)

LOOP

PIPE ROW( SALES_ROW(C.SALES_DATE,C.ORDER_ID,C.PRODUCT_ID,C.CUSTOMER_ID,C.TOTAL_AMOUNT));

END LOOP;

END;

To execute:

Select * from table(fetch_sales_table(1221));