How to Return Multiple Values and Rows with PL/SQL Function

STEP – 1 à Create Object Type EMP_DETAILS (COLLECTION)

CREATE OR REPLACE TYPE emp_details -- Collection

AS

   OBJECT (first_name VARCHAR2 (30),

           last_name VARCHAR2 (30),

           date_of_birth DATE);

STEP – 2 à Create Type EMP  as TABLE

create type emp is table of emp_details

STEP – 3 à Create Function to Return Type as EMP

CREATE OR REPLACE FUNCTION xx_return_multiple_rows_f

   RETURN emp

AS

       l_emp_details    emp := emp();

BEGIN

    l_emp_details.extend;

    l_emp_details(1) := emp_details ('BIJOY', 'JOSEPH', to_date('12-MAR-1986','DD-MON-YYYY'));

    l_emp_details.extend;

    l_emp_details(2) := emp_details ('SUBHU', 'NAYAK', to_date('12-MAR-1986','DD-MON-YYYY'));

--    commit;

   RETURN l_emp_details;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN NULL;

END;

STEP – 3 à Call Function which Returns Data Set

select * from table (xx_return_multiple_rows_f)

OR

select xx_return_multiple_rows_f from dual

STEP – 4 à If Incase, want to print or use the Output

DECLARE

   CURSOR c1

   IS

      SELECT   * FROM table (xx_return_multiple_rows_f);

BEGIN

   FOR i IN c1

   LOOP

      DBMS_OUTPUT.put_line (i.first_name);

   END LOOP;

END;