Returning Result Sets From a Procedure

So far, you've only seen how to retrieve individual values from stored procedures using output parameters. It's also possible to return a whole result set from a procedure (or even, in the case of DB2, multiple result sets). In the case of SQL Server and Access, this involves nothing new—you just include a SELECT statement in the body of the procedure. However, for Oracle and DB2, you need to use cursors to do this, so now that you understand cursors, you're in a position to see how to return a result set from a stored procedure.

In DB2, you need to open a cursor WITH RETURN and leave it open at the end of the procedure. Things are trickier in Oracle, though: You need to create a special object called a package, which is used to associate multiple objects into a single object. You can place a cursor and your procedure into a single package and return the cursor as an output parameter from your procedure.

Let's start by looking quickly at the SQL Server and Access code.

SQL Server and Access

This is probably the simplest example in the chapter!

RETURNING ROWS (SQL SERVER AND ACCESS)

Execute the following CREATE PROCEDURE statement against the InstantUniversity database:

CREATE PROCEDURE GetStudents
AS
SELECT StudentID, Name FROM Student;

You can execute this using the EXEC or EXECUTE keyword:

EXEC GetStudents;

The output from this query is as follows:

   StudentID  Name

   ---------- --------------
   1          John Jones
   2          Gary Burton
   3          Emily Scarlett
   ...       ...

DB2

One feature of DB2 is that it allows you to return more than one result set from a stored procedure, simply by leaving more than one cursor open at the end of the procedure. Because of this, it also allows you to specify how many result sets are returned from a procedure using the RESULT SETS clause, which is placed before the LANGUAGE SQL clause.

RETURNING ROWS (DB2)

Build the following procedure in Development Center:

CREATE PROCEDURE GetStudents()
RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
   DECLARE cur_Students CURSOR WITH RETURN FOR
      SELECT StudentID, Name FROM Student;
   OPEN cur_Students;
END P1

You're just returning one cursor, so you add the clause RESULT SETS 1 to your CREATE PROCEDURE statement. Within the body of the procedure, you simply declare and open the cursor for the appropriate SELECT statement. Notice that you declare the cursor WITH RETURN because you want to return the result set from the procedure.

You can execute this using the standard CALL keyword:

CALL GetStudents;

The output from this query is as follows:

   StudentID  Name
   ---------- --------------
   1           John Jones
   2           Gary Burton
   3           Emily Scarlett
   ...        ...

Oracle

As mentioned previously, you have to do a bit more work with Oracle. First, you need to create a package that will hold your cursor and the stored procedure. To create a package, you need to use the CREATE PACKAGE statement:

CREATE [OR REPLACE] PACKAGE PackageName
AS
-- Types, procedures, etc., in the package
END PackageName;

Next, you have to define the body for the package. The package body is where the actual code for any functions, procedures, and so on in the package is defined. As expected, you create this using the CREATE PACKAGE BODY statement:

CREATE [OR REPLACE] PACKAGE BODY PackageName
AS
-- Definitions of procedures and functions
END PackageName;

Note that the package body must be given the same name as the package with which it is associated.

It's also possible for the stored procedure to be defined outside the package; you'll see examples of this in the first case study.

Let's see how this works in practice by implementing the GetStudents procedure in Oracle.

RETURNING ROWS (ORACLE)

First, you have to define the package:

CREATE OR REPLACE PACKAGE student_pkg
AS
TYPE studCur IS REF CURSOR;
PROCEDURE GetStudents(o_StudCur OUT studCur);
END student_pkg;
/

As well as the signature of the GetStudents procedure, the package defines a cursor type called studCur. This is a special type of cursor, called a REF CURSOR. Whereas the implicit and explicit cursors you used in the previous example needed to be defined when the procedure was written, REF CURSORs can be used even if the cursor spec isn't known until runtime.

Second, you create the package body:

CREATE OR REPLACE PACKAGE BODY student_pkg
AS
   PROCEDURE GetStudents(o_StudCur OUT studCur)
   IS
   BEGIN
      OPEN o_StudCur FOR
         SELECT StudentID, Name FROM Student;
   END GetStudents;
END student_pkg;
/

In this case, the package body contains just one item—the definition of the GetStudents stored procedure. This has one output parameter, o_StudCur, which is an instance of your REF CURSOR type, studCur. Within the body of the procedure, you open this cursor, specifying the cursor spec in the OPEN statement. Notice that you can't declare the cursor because it's a parameter—this is why you need to use a REF CURSOR rather than a standard explicit cursor.

Now you can test the procedure. Because this procedure is intended to return a result set to a client application, rather than being accessed from SQL code, you need to retrieve the cursor from the output parameter and loop through the rows manually:

SET SERVEROUT ON
DECLARE
   TYPE studCurType IS REF CURSOR;
   mycur studCurType;
   studrow Student%ROWTYPE;
BEGIN
   student_pkg.GetStudents(mycur);
   FETCH mycur INTO studrow;
   WHILE mycur%FOUND
   LOOP
      dbms_output.put_line(studrow.StudentID || '  ' ||
                             studrow.Name);
      FETCH mycur INTO studrow;
   END LOOP;
END;
/

The output from this query is as follows:

   1   John Jones
   2   Gary Burton
   3   Emily Scarlett
   ... ...