How Can I Retrieve Multiple Result Sets from an Oracle PL/SQL Stored Procedure?

Retrieving Multiple Result Sets from an Oracle PL/SQL Stored Procedure

In Oracle PL/SQL, stored procedures provide a structured way to encapsulate SQL queries and database logic. One common requirement is the need to return multiple sets of records from a stored procedure based on a given input argument. This can be accomplished by constructing a PL/SQL function that returns a table or a collection of user-defined objects (UDOs).

To create a function that returns a result set, follow these steps:

1. Define a table type or a collection type to hold the data.
2. Create a function that accepts an argument and declares a local variable of the specified data type.
3. Execute a cursor or loop through records in the database, populating the variable and extending its size accordingly.
4. Return the data structure containing the result sets.

Here's an example using a table type:

SQL> create type emp_obj is object (empno number, ename varchar2(10));
  2  /

Type created.

SQL> create type emp_tab is table of emp_obj;
  2  /

Type created.

SQL> create or replace function all_emps return emp_tab
  2  is
  3     l_emp_tab emp_tab := emp_tab();
  4     n integer := 0;
  5  begin
  6     for r in (select empno, ename from emp)
  7     loop
  8        l_emp_tab.extend;
  9        n := n + 1;
 10       l_emp_tab(n) := emp_obj(r.empno, r.ename);
 11     end loop;
 12     return l_emp_tab;
 13  end;
 14  /

Function created.

To retrieve the result sets from a plain SQL query, simply query the function:

SQL> select * from table (all_emps);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7902 FORD
      7934 MILLER

This method allows you to efficiently retrieve multiple result sets from a stored procedure and manipulate them as regular tables within your SQL queries.

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