Return multiple rows from PL/SQL stored Function

Overview:

A function always return a value using the RETURN statement.

Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE.

 This article help you to show how to return multiple rows or values from Function.

 See below example:

For Example:

1) Create Object with Emp_id and First_name columns:

    CREATE TYPE XX_emp_obj IS OBJECT ( Emp_id NUMBER, First_name VARCHAR2(20) );

2) Create Table type depend on Object Type:

    CREATE TYPE XX_emp_tab IS TABLE OF XX_emp_obj;

3) Create PL/SQL stored Function, in this function use below Select script for display records:

        SELECT person_id, last_name

        FROM per_all_people_f

        WHERE rownum < 5;

    Function:

    CREATE OR REPLACE FUNCTION XX_get_emps

    RETURN XX_emp_tab

    IS

          l_emp_tab XX_emp_tab := XX_emp_tab();

          ln_num NUMBER :=0;

    BEGIN

       FOR lrec IN (SELECT person_id, last_name FROM per_all_people_f WHERE rownum < 5)

       LOOP

           l_emp_tab.EXTEND;

           ln_num := ln_num +1;

           l_emp_tab(ln_num ) := XX_emp_obj(lrec .person_id, lrec .last_name);

       END LOOP;

    RETURN l_emp_tab;

    END;

    /

4) After created function, call that function is Select statement:

    SELECT * FROM TABLE (XX_get_emps);

     -- (XX_get_emps -- function name).

5) Or call in PL/SQL block:

    SET SERVEROUTPUT ON;

    DECLARE

        CURSOR lcur_emp 

        IS

           SELECT empl_id, first_name 

           FROM TABLE(XX_get_emps);

    BEGIN

        dbms_output.put_line('EmpID: '||'         '||'FirstName: ');

        FOR lrec IN lcur_emp

        LOOP

               dbms_output.put_line(lrec.empl_id||'         '||lrec.first_name);

        END LOOP;

    END;

    /

** If you need to Drop above created objects then use below SQL scripts:

   1) DROP TYPE XX_emp_obj;

   2) DROP TYPE XX_emp_tab;

   3) DROP FUNCTION XX_get_emps;