Function Return Multiple Values and Rows

Return multiple values and rows in a Function:
Steps:

• Create a object type , with rwquired column attributes.
•  Create a nested table based on the object created.
•  Create a function that returns the nested table type.
•  Call the function.

1. Create a object type:

 CREATE OR REPLACE TYPE EMP_OBJ_TYP

 AS OBJET

(ENAME VARCHAR2(100),

 EMPNO NUMBER,

 DEPTNO NUMBER);

 

 Objecty will give the ability to hold the multiple attributes like ename,empno,deptno.

 

 2. Create a nested table:

 CREATE OR REPLACE TYPE EMP_TBL_TYP

 IS TABLE OF EMP_OBJ_TYP;

 

 

 Nested table will be perfect to srore the fetched data, as nested table is made fform object type, so it will hold multiple columns datain a single key value pair.

 

 3. Create a function:

 CREATE OR REPLACE FUNCTION RETURN_MULTIPLE_VAL_ROW(V_DEPTNO NUMBER)

 RETURN EMP_TBL_TYP

 AS

 EMP_DATA EMP_TBL_TYP:=EMP_TBL_TYP();

 BEGIN

 SELECT EMP_OBJ_TYP(ENAME,EMPNO,DEPTNO) 

 BULK COLLECT 

 INTO EMP_DATA 

 FROM EMP WHERE EMPNO=V_DEPTNO;

 

 RETURN EMP_DATA;

 END;

 

 4. Call the function

  SELECT * FROM TABLE (RETURN_MULTIPLE_VAL_ROW(20));