6 Using PL/SQL Stored Procedures and REF CURSORs

This chapter contains:

Introduction to PL/SQL Stored Procedures
Introduction to PL/SQL Packages and Package Bodies
Introduction to REF CURSORs
Creating a PL/SQL Stored Procedure that Uses REF CURSORs
Modifying an ODP.NET Application to Run Stored Procedures
Running a PL/SQL Stored Procedure Using an ODP.NET Application

Introduction to PL/SQL Stored Procedures

A stored procedure is a named set of PL/SQL statements designed to perform an action. Stored procedures are stored inside the database. They define a programming interface for the database rather than allowing the client application to interact with database objects directly. Stored procedures are typically used for data validation or to encapsulate large, complex processing instructions that combine several SQL queries.

Stored functions have a single return value parameter. Unlike functions, procedures may or may not return values.

Introduction to PL/SQL Packages and Package Bodies

A PL/SQL package stores related items as a single logical entity. A package is composed of two distinct pieces:

The package specification defines what is contained in the package; it is analogous to a header file in a language such as C++. The specification defines all public items. The specification is the published interface to a package.
The package body contains the code for the procedures and functions defined in the specification, and the code for private procedures and functions that are not declared in the specification. This private code is only visible within the package body.

The package specification and body are stored as separate objects in the data dictionary and can be seen in the user_source view. The specification is stored as the PACKAGE type, and the body is stored as the PACKAGE BODY type.

While it is possible to have a specification without a body, as when declaring a set of public constants, it is not possible to have a body with no specification.

Introduction to REF CURSORs

Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSORs are represented through the OracleRefCursor ODP.NET class.

REF CURSORs have the following characteristics:

A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR in order to access it.
A REF CURSOR involves an additional database round-trip. While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Note that data is not be retrieved until the user attempts to read it.
A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.
A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.
A REF CURSOR is a PL/SQL data type. You create and return a REF CURSOR inside a PL/SQL code block.

Creating a PL/SQL Stored Procedure that Uses REF CURSORs

This section demonstrates how to create a PL/SQL stored procedure.

To create a stored procedure:

Open Server Explorer and double-click HR to open the connection to the HR schema created in "Connecting to the Oracle Database".

Description of the illustration connect_se.gif

If you have not previously saved the password, the Oracle Server Login opens and you can enter the password now. If you have saved the password, then the connection expands immediately.
In Server Explorer, right-click Packages and select New Package.

Description of the illustration procedure1.gif

The New Package window appears.
In the New Package window, change the Package Name to HR_DATA.
Under the Methods area, click Add.

Description of the illustration procedure2.gif

The Add Method window appears.
In the Add Method window, enter Method Name GETCURSORS, and change Method Type to Procedure.
Under Parameters, click Add.

This starts the process of adding parameters.

Under the Parameter Details group on the right, enter these three parameters. Click Add before each parameter that you need to add.
- Name: DEP_ID, Direction: select IN , Data Type: select NUMBER.
- Name: EMPLOYEES_C, Direction: select OUT, Data Type: select SYS_REFCURSOR.
- Name: DEPENDENTS_C , Direction: OUT, Data Type: select SYS_REFCURSOR.
Description of the illustration procedure3.gif
Click OK when you finish adding parameters.

The New Package window reappears.

In the New Package window, click Preview SQL to see the SQL code created.

A Preview SQL window appears, containing code similar to the following. Note that this code has been abbreviated by removing most of the comments.


CREATE PACKAGE "HR"."HR_DATA" IS  -- Declare types, variables, constants, exceptions, cursors, 
  -- and subprograms that can be referenced from outside the package.

  PROCEDURE "GETCURSORS" (
    "DEP_ID" IN NUMBER, 
    "EMPLOYEES_C" OUT SYS_REFCURSOR, 
    "DEPENDENTS_C" OUT SYS_REFCURSOR);

END "HR_DATA";

CREATE PACKAGE BODY "HR"."HR_DATA" IS

  -- Implement subprograms, initialize variables declared in package
  -- specification.

  -- Make private declarations of types and items, that are not accessible 
  -- outside the package
   PROCEDURE "GETCURSORS" (
    "DEP_ID" IN NUMBER, 
    "EMPLOYEES_C" OUT SYS_REFCURSOR, 
    "DEPENDENTS_C" OUT SYS_REFCURSOR) IS

  -- Declare constants and variables in this section.

     BEGIN -- executable part starts here

     NULL;

  -- EXCEPTION -- exception-handling part starts here
 
    END "GETCURSORS";

END "HR_DATA";

Click OK to close the Preview SQL window.
In the New Package window, click OK to save the new package.

The new package, HR_DATA, now appears in the Server Explorer.
In the Server Explorer, right-click the package HR_DATA, and select Edit Package Body.

Description of the illustration procedure4.gif

The code for the package appears.

Scroll down to the body of the GETCURSORS procedure, and after BEGIN, replace the line NULL; with the following code:


OPEN EMPLOYEES_C FOR SELECT * FROM EMPLOYEES 
   WHERE DEP_ID=DEPARTMENT_ID;
OPEN DEPENDENTS_C FOR SELECT * FROM DEPENDENTS;

Save the changes to the package.
To run the stored procedure, in Server Explorer, expand the HR_DATA package.

Right-click the GETCURSORS method, and select Run.

Description of the illustration procedure5.gif

The Run Procedure window appears.
In the Run Procedure window, enter a Value of 60 for dep_id.

Description of the illustration procedure6.gif
Click OK.

The Output window appears, showing that the run was successful.

In the result window, the following message appears:
```
Procedure <HR.HR_DATA.GETCURSORS@hr.database> was run successfully.
```
Under this message, note two output parameters (together with DEP_ID): EMPLOYEES_C and DEPENDENTS_C.
Select the Value column entry for EMPLOYEES_C.

The Parameter Details area appears, showing the employees in department 60. The value for DEP_ID is 60.

Description of the illustration procedure7.gif
Select the Value column entry for DEPENDENTS_C.

The Parameter Details area appears, showing the value of the DEPENDENTS_C.

Description of the illustration procedure8.gif

Modifying an ODP.NET Application to Run Stored Procedures

This section demonstrates how to modify your Oracle Data Provider for .NET application to run a PL/SQL stored procedure, using the GETCURSORS stored procedure as a sample.

To modify your application to run a stored procedure:

Open the application HR_Connect_CS or HR_Connect_VB.
Make a copy of Form3.xx, which you finished at the end of Chapter 4 and name it Form4.xx, following the instructions in Appendix B, "Copying a Form".
With Form1 selected, switch to code view.

In the try block of the connect_Click() method, replace the two command assignment lines, starting with cmd = New OracleCommand... with the code indicated.

Visual C#:

cmd = new OracleCommand("HR_DATA.GETCURSORS", conn);
cmd.CommandType = CommandType.StoredProcedure;

Visual Basic:

cmd = new OracleCommand("HR_DATA.GETCURSORS", conn)
cmd.CommandType = CommandType.StoredProcedure

Under the code added in Step 3, add definitions and bindings for the three parameters of the GETCURSORS stored procedure as OracleParameter objects, calling them dep_id, employees_c and dependents_c.

Visual C#:

OracleParameter dep_id = new OracleParameter();
dep_id.OracleDbType = OracleDbType.Decimal;
dep_id.Direction = ParameterDirection.Input;
dep_id.Value = 60;
cmd.Parameters.Add(dep_id);

OracleParameter employees_c = new OracleParameter();
employees_c.OracleDbType = OracleDbType.RefCursor;
employees_c.Direction = ParameterDirection.Output;
cmd.Parameters.Add(employees_c);

OracleParameter dependents_c = new OracleParameter();
dependents_c.OracleDbType = OracleDbType.RefCursor;
dependents_c.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dependents_c);

Visual Basic:

Dim dep_id As OracleParameter = New OracleParameter
dep_id.OracleDbType = OracleDbType.Decimal
dep_id.Direction = ParameterDirection.Input
dep_id.Value = 60
cmd.Parameters.Add(dep_id)

Dim employees_c As OracleParameter = New OracleParameter
employees_c.OracleDbType = OracleDbType.RefCursor
employees_c.Direction = ParameterDirection.Output
cmd.Parameters.Add(employees_c)

Dim dependents_c As OracleParameter = New OracleParameter
dependents_c.OracleDbType = OracleDbType.RefCursor
dependents_c.Direction = ParameterDirection.Output
cmd.Parameters.Add(dependents_c)

Build the application.

Running a PL/SQL Stored Procedure Using an ODP.NET Application

This section demonstrates how to run a PL/SQL stored procedure, such as the GETCURSORS stored procedure, from your ODP application.

To run a stored procedure:

Run the application.

A Form1 window appears.
In the Form1 window, enter the connection information, and click Connect.
In the DataGrid object, scroll horizontally to verify that the values in the last column, DEPARTMENT_ID are only 60.

Note that the DataGrid contains the first result set from the stored procedure, which matches the query of the EMPLOYEES table.

Description of the illustration procedure9.gif
Close the application.