return a table from Function in Oracle

Hello Guys,

Today I am going to explain how to write a function in oracle which return a table as result set.

We have to follow 3 steps to do so.

Step 1: We are going to create a object type that contains the fields that are going to be returned from function.

CREATE OR REPLACE TYPE emp_type as object 

(

id                     number,

name               varchar2(30),

designation      varchar2(50),

salary              number(16,4)

);

/

execute this statement

Step 2: Now we create a nested table type from above Type

CREATE OR REPLACE TYPE emp_type_Table as table of  emp_type;

execute this statement

Step 3:  So It's time to do our real work i.e. create a function

CREATE OR REPLACE FUNCTION fn_get_emp_salary (Pyear  IN  number,
                                                P id   IN number)
   RETURN emp_type_Table
AS
   v_ret   emp_type_Table ;
BEGIN
   SELECT   CAST (
               MULTISET(

                                      -- your query goes here

                                       select id, name, designation, salary from emp 

                                       where id=Pid and year=Pyear
                              )
     INTO   v_ret
     FROM   DUAL;

   RETURN v_ret;
END fn_get_emp_salary ;
/

execute this function.


Now your function is ready and you can call it as shoen below


select * from table( fn_get_emp_salary(2012,25))

I call the function by sending parameter year as 2012 and id as 25


Hope this post help you in writing function in oracle.........