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

Returning Multiple Result Sets from Oracle PL/SQL

Consider the need to create a stored procedure that dynamically returns multiple result sets based on input arguments. Here's how to achieve this in Oracle PL/SQL:

Building the Stored Procedure

To return multiple result sets, first create an object type that represents the data in each result set. For example, to return employee data:

CREATE TYPE EMP_OBJ IS OBJECT (EMPNO NUMBER, ENAME VARCHAR2(10));

Next, create a table type that stores a collection of these objects:

CREATE TYPE EMP_TAB IS TABLE OF EMP_OBJ;

Now, create the stored procedure. Here's an example that assigns objects to an array and returns it:

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;

Calling from Plain SQL

Once the stored procedure is created, you can call it in plain SQL using the TABLE() constructor:

SELECT * FROM TABLE (ALL_EMPS);

This query will return all employees as a single result table.

Example

Consider the following stored procedure:

CREATE OR REPLACE PROCEDURE GET_EMPLOYEES (
  DEPT INTEGER,
  OUT RECORDS OUT SYS_REFCURSOR
) IS
...
END;

You can call this procedure and access the returned records using a cursor:

DECLARE
  EMP_CUR SYS_REFCURSOR;
BEGIN
  GET_EMPLOYEES(10, EMP_CUR);
  LOOP
    FETCH EMP_CUR INTO EMP_OBJ;
    EXIT WHEN EMP_CUR%NOTFOUND;
    ...
  END LOOP;
END;

By using object types, table types, and cursors, you can effectively return multiple result sets from Oracle PL/SQL stored procedures. This provides flexibility and efficiency when working with complex data in your applications.

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