This chapter contains:
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.
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.
CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.
CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a
CURSOR is a pointer or a handle to a result set on the database.
CURSORs are represented through the
OracleRefCursor ODP.NET class.
CURSORs have the following characteristics:
CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the
CURSOR in order to access it.
CURSOR involves an additional database round-trip. While the
CURSOR is returned to the client, the actual data is not returned until the client opens the
CURSOR and requests the data. Note that data is not be retrieved until the user attempts to read it.
CURSOR is not updatable. The result set represented by the
CURSOR is read-only. You cannot update the database by using a
CURSOR is not backward scrollable. The data represented by the
CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the
CURSOR to point to random records in the result set.
CURSOR is a PL/SQL data type. You create and return a
CURSOR inside a PL/SQL code block.
This section demonstrates how to create a PL/SQL stored procedure.
Open Server Explorer and double-click HR to open the connection to the HR schema created in "Connecting to the Oracle Database".
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.
The New Package window appears.
In the New Package window, change the Package Name to
Under the Methods area, click Add.
The Add Method window appears.
In the Add Method window, enter Method Name
GETCURSORS, and change Method Type to
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.
DEP_ID, Direction: select
IN , Data Type: select
EMPLOYEES_C, Direction: select
OUT, Data Type: select
DEPENDENTS_C , Direction:
OUT, Data Type: select
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.
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:
Save the changes to the package.
To run the stored procedure, in Server Explorer, expand the
GETCURSORS method, and select Run.
The Run Procedure window appears.
In the Run Procedure window, enter a Value of
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
Select the Value column entry for
The Parameter Details area appears, showing the employees in department 60. The value for
Select the Value column entry for
The Parameter Details area appears, showing the value of the
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.
Open the application
With Form1 selected, switch to code view.
try block of the
connect_Click() method, replace the two command assignment lines, starting with
cmd = New OracleCommand... with the code indicated.
cmd = new OracleCommand("HR_DATA.GETCURSORS", conn); cmd.CommandType = CommandType.StoredProcedure;
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
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);
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.
This section demonstrates how to run a PL/SQL stored procedure, such as the
GETCURSORS stored procedure, from your ODP application.
Run the application.
A Form1 window appears.
In the Form1 window, enter the connection information, and click Connect.
DataGrid object, scroll horizontally to verify that the values in the last column,
DEPARTMENT_ID are only
Note that the
DataGrid contains the first result set from the stored procedure, which matches the query of the
Close the application.