How to return multiple values in function without using OUT parameter

 Steps:

     1. Create a object type with required column attributes

         2.  Create a nested table based on the object created

         3.  Create a function that returns a nested table type

Explanation:

   Why object type?

                ·       Object will give the ability to hold the multiple attributes like

                 First_name, last_name, dept_name, salary..etc

    Nested table:

                ·       Nested table will be perfect to store the fetched data.

                ·       Nested table is made from object type, so it will hold multiple                         columns data in a single key value pair.

      Example:

      First_name, last_name, dept_name, salary

    Function:

   ·     Function can returns multiple values through the object type and     nested table type.

Example:

  Following function can return Multiple values (Ename,job,sal)

Step 1: Object Creation

1.   Create or replace TYPE Emp_Obj_type

     As object

        (ename varchar2(50),

        job       varchar2(50),

        sal      number

   );

Step 2: Nested table creation based on the object

1.   Create or replace TYPE Emp_tab_type

     Is table of Emp_Obj_type;

Step 3: Function

1. Create or replace function fun_ret_val (p_empno number)

   Return Emp_tab_type

   Is

        P_ename  varchar2(50),

        P_job       varchar2(50),

        P_sal        varchar2(50)

   / * nested table variable declaration and initialization */

   Emp_details Emp_tab_type := Emp_tab_type();

   /* extending the nested table */

   Begin

      Emp_details.Extend ();

    /* getting required data into variable */

   Select ename,job,sal

   Into P_ename, P_job, P_sal

   From emp e,dept d

   Where e.deptno=d.deptno

   And e.empno= p_empno;

   /* using the object constructor, to insert the data into

   the nested table */

   Emp_details(1) := Emp_tab_type(P_ename, P_job,P_sal);

   Return Emp_details;

   End;

Calling method:

Select * from table (fun_ret_val(100));

  Here,

     No database objects in the function so we can easily call the function in select statement.

 

Following function for return all employee records:

1. Create or replace function fun_ret_val

   Return Emp_tab_type

   Is

        P_ename  varchar2(50),

        P_job       varchar2(50),

        P_sal        varchar2(50)

   / * nested table variable declaration and initialization */

        Emp_details Emp_tab_type := Emp_tab_type();

   Begin

       /* extending the nested table */

         Emp_details.Extend ();

        Select emp_obj_type(P_ename, P_job, P_sal)

        Bulk collect into Emp_details

        From emp e , dept d

        Where e.deptno=d.deptno

   Return Emp_details;

   End;

Calling method:

Select * from table (fun_ret_val);

Bulk collect:

·       This is used to fetch the many rows at once and place them in a  collection.

Share: