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

Returning Multiple Result Sets with Oracle PL/SQL Stored Procedures

Question:

How can we create a stored procedure that retrieves various datasets based on a passed argument and subsequently call it from plain SQL?

Answer:

To return multiple result sets from an Oracle PL/SQL stored procedure, we can employ the following approach:

1. Create Object and Table Types:

   First, we define object and table types to represent the data structure of our result sets. In this example, we create an emp_obj object type with two attributes: empno (number) and ename (varchar2). We then create a emp_tab table type as a collection of emp_obj objects.

2. Build the Stored Procedure:

   We define a function (all_emps in this case) that returns a emp_tab collection. Inside the function:

   • Initialize an empty emp_tab variable.
   • Perform a loop to iterate through the desired rows (in this example, retrieving empno and ename from the emp table).
   • Extend the emp_tab collection and assign each row's values to an emp_obj instance.
   • Return the populated emp_tab collection.

3. Call the Stored Procedure from SQL:

   To call the all_emps function from SQL, we use the table operator. This allows us to query the returned collection as a regular table.

Example:

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

SQL> create type emp_tab is table of emp_obj;

SQL> 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;

SQL> select * from table (all_emps);

Result:

     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

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