A Function that returns a Table

This is an Example of a Pipelined function that returns a Table of Results :-


DROP TYPE TYP_NAME_AND_AGES;

DROP TYPE OBJ_NAME_AND_AGES

DROP FUNCTION FN_NAME_AND_AGES;

DROP TABLE TBL_NAME_AND_AGES;

CREATE TABLE TBL_NAME_AND_AGES
(
NAME VARCHAR2(10),
AGE NUMBER
);

INSERT INTO TBL_NAME_AND_AGES VALUES ('SANDEEP',20);
INSERT INTO TBL_NAME_AND_AGES VALUES ('SCOTT',21);
INSERT INTO TBL_NAME_AND_AGES VALUES ('TIGER',22);

COMMIT;

CREATE OR REPLACE TYPE TYP_NAME_AND_AGES AS OBJECT
(
NAME VARCHAR2(10),
AGE NUMBER
);

CREATE OR REPLACE TYPE OBJ_NAME_AND_AGES AS TABLE OF TYP_NAME_AND_AGES;

CREATE OR REPLACE FUNCTION FN_NAME_AND_AGES
RETURN OBJ_NAME_AND_AGES
PIPELINED
AS
BEGIN

FOR rec IN ( SELECT * FROM TBL_NAME_AND_AGES )
LOOP
PIPE ROW ( TYP_NAME_AND_AGES( rec.NAME, rec.AGE ) );
END LOOP;

END FN_NAME_AND_AGES;

SELECT * FROM TABLE(FN_NAME_AND_AGES);

NAME AGE
---------- ----------
SANDEEP 20
SCOTT 21
TIGER 22