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

Stored Procedures with Multiple Result Sets in Oracle PL/SQL

In Oracle PL/SQL, stored procedures can be crafted to return multiple result sets based on different arguments. This can enhance query flexibility and enable efficient data retrieval. To achieve this, the concept of nested tables, also known as collection types, comes into play.

Consider a scenario where you require a stored procedure to dynamically retrieve employee records based on user-supplied criteria. Here's how you can approach it:

1. Define Nested Table Types: Create nested table types that will hold the result set of employee objects. These types serve as templates to structure the returned data.
2. Create the Stored Procedure: Construct the stored procedure with input parameters and declare variables of the defined nested table type.
3. Populate the Nested Table: Within the stored procedure, iterate through the source data using a cursor, and add employee objects to the nested table variable.
4. Return the Nested Table: Once the nested table is populated, return it as the result of the stored procedure.
5. Invoke from Plain SQL: The stored procedure can be invoked from plain SQL simply by calling its name, passing arguments if necessary.

Here's an example implementation:

TYPE emp_obj IS OBJECT (empno NUMBER, ename VARCHAR2(10));
TYPE emp_tab IS 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;

Now, you can execute this stored procedure in plain SQL:

SELECT * FROM TABLE(all_emps);

This query will return a dynamically generated result set with multiple rows, where each row represents an employee object.

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