How Can I Return Multiple Result Sets from Oracle PL/SQL Stored Procedures?

Returning Multiple Record Sets from Stored Procedures in Oracle PL/SQL

When working with stored procedures in Oracle PL/SQL, returning multiple sets of records based on a provided argument can be a common requirement. Here's how you can achieve this:

Creating a Function to Return a Result Set

To create a function that returns a result set, we can define a custom type to represent the objects we want to return and then declare a table of that type within the function. This table will be populated dynamically based on the argument passed to the function.

Consider the following example:

CREATE TYPE emp_obj AS OBJECT (empno NUMBER, ename VARCHAR2(10));
CREATE TYPE emp_tab AS TABLE OF emp_obj;

CREATE OR REPLACE FUNCTION all_emps
RETURN emp_tab
IS
    l_emp_tab emp_tab := emp_tab();
    n INTEGER := 0;
BEGIN
    FOR r IN (SELECT empno, ename FROM emp)
    LOOP
        l_emp_tab.EXTEND;
        n := n + 1;
        l_emp_tab(n) := emp_obj(r.empno, r.ename);
    END LOOP;
    RETURN l_emp_tab;
END;

Querying the Result Set

Once the function is created, you can query the returned result set as if it were a table. For instance, the following query will display all employees:

SELECT * FROM table (all_emps);

This will return a list of all employees, including their employee numbers and names.

Calling the Function from Plain SQL

To call the function from plain SQL, simply use the following syntax:

SELECT * FROM all_emps;

This will execute the function and return the result set.

The above is the detailed content of How Can I Return Multiple Result Sets from Oracle PL/SQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!