| Oracle® Database Express Edition 2 Day Plus .NET Developer Guide 10g Release 2 (10.2) Part Number B25312-01 |
|
|
View PDF |
| Oracle® Database Express Edition 2 Day Plus .NET Developer Guide 10g Release 2 (10.2) Part Number B25312-01 |
|
|
View PDF |
This chapter contains the following sections:
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.
A stored procedure is a named set of PL/SQL statements designed to perform an action. Stored functions have a single return value parameter. Unlike functions, procedures may or may not return values.
Ref cursors are one of the most powerful, flexible, and scalable methods for returning query results from an Oracle Database to a client application.
A ref cursor is a PL/SQL datatype whose value is the address of the memory location of a query work area on the database server. A query work area can be thought of as the result set, or a row set, on the server; it is the location where the results of a query are stored in server memory. In essence, a ref cursor is a handle to a result set on the server. A ref cursor is represented through the OracleRefCursor ODP.NET class.
Ref cursors have the following characteristics:
A ref cursor refers to server memory. The memory address represented by a ref cursor "lives" on the database server, not on the client machine. Therefore, the client's connection to the database must be in place during the lifetime of the ref cursor. If the underlying connection to the database is closed, the ref cursor will become inaccessible to the client.
A ref cursor involves an additional database round trip. Because a ref cursor is a pointer to memory on the server that is returned to the client, the actual data contained in the ref cursor is not initially returned to the client. The client must request the data contained in the ref cursor after it has opened the ref cursor. Note that data will 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 datatype. You create and return a ref cursor inside a PL/SQL code block.
Follow the instructions in Section "Copying a Project" to create a new copy of the HR_DataSet_ODP_CS project. Name the new project HR_StoredProcedure_CS. If using VB, name it HR_StoredProcedure_VB.
In Oracle Explorer, right-click Packages and select New Package.
In the New Package window, change the Package Name to HR_DATA.
In the Add Method window, enter Method Name GETCURSORS, and change Method Type to Procedure.
In the Add Method window, under Parameter Details, enter DEPARTMENTID for Name, IN for Direction, and NUMBER for Data Type.
Under Parameters, click Add.
Enter a second parameter under Parameter Details, with EMPLOYEESCUR for Name, OUT for Direction, and SYS_REFCURSOR for Data Type.
Under Parameters, click Add.
Enter a third parameter under Parameter Details, with DEPENDENTSCUR for Name, OUT for Direction, and SYS_REFCURSOR for Data Type.
Click OK.
In the New Package window, click Preview SQL to see the SQL code created.
A Preview SQL window appears, containing code in Example 6-1. Note that this example has been abbreviated by removing most of the comments.
Example 6-1 PL/SQL Code for Package HR_DATA
CREATE PACKAGE "HR"."HR_DATA" IS -- Declare types, variables, constants, exceptions, cursors,
-- and subprograms that can be referenced from outside the package.
PROCEDURE "GETCURSORS" (
"DEPARTMENTID" IN NUMBER,
"EMPLOYEESCUR" OUT SYS_REFCURSOR,
"DEPENDENTSCUR" 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" (
"DEPARTMENTID" IN NUMBER,
"EMPLOYEESCUR" OUT SYS_REFCURSOR,
"DEPENDENTSCUR" 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 window.
In the New Package window, click OK.
Note that a new package, HR_DATA, now appears in Oracle Explorer.
In Oracle Explorer, right-click package HR_DATA, and select Edit Package Body.
The code for the package is displayed.
Scroll down to the body of the GETCURSORS procedure, and replace NULL; with code in Example 6-2:
Example 6-2 Assigning Reference Cursors
OPEN EMPLOYEESCUR FOR SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=DEPARTMENTID; OPEN DEPENDENTSCUR FOR SELECT * FROM DEPENDENTS;
Save the changes to the package; use the Ctrl+S keyboard shortcut.
To run the stored procedure, in Oracle Explorer, expand package HR_DATA.
Right-click the GETCURSORS method, and select Run.
In the Run Procedure window, enter a Value of 60 for DEPARTMENTID. Click OK.
The Output window appears, showing that the run was successful. Close the Output Window.
In the design view, the following message appears:
Procedure <HR.HR_DATA.GETCURSORS@hr.database> was run successfully.
Under this message, note two new parameters (together with DEPARTENTID): EMPLOYEESCUR and DEPENDENTSCUR.
In the design view, select the Value column entry for EMPLOYEESCUR.
The Parameter Details area appears, showing the result of the EMPLOYEESCUR for DEPARTMENTID 60.
In the design view, select the Value column entry for DEPENDENTSCUR.
The Parameter Details area appears, showing the result of the DEPENDENTSCUR.
With Form1 selected, switch to code view using the F7 keyboard shortcut.
In the try block of the connect_Click() method, replace the two command assignment lines, starting with cmd = New OracleCommand... with code in Example 6-3 or Example 6-4.
Under the code added in Step 2, add definitions and bindings for the three parameters of the GETCURSORS stored procedure as OracleParameter objects, calling them dept_id, emp_cur and dnt_cur.
Example 6-5 Defining and Binding OracleParameter Objects for Stored Procedure: C#
OracleParameter dept_id = new OracleParameter(); dept_id.OracleDbType = OracleDbType.Decimal; dept_id.Direction = ParameterDirection.Input; dept_id.Value = 60; cmd.Parameters.Add(dept_id); OracleParameter emp_cur = new OracleParameter(); emp_cur.OracleDbType = OracleDbType.RefCursor; emp_cur.Direction = ParameterDirection.Output; cmd.Parameters.Add(emp_cur); OracleParameter dnt_cur = new OracleParameter(); dnt_cur.OracleDbType = OracleDbType.RefCursor; dnt_cur.Direction = ParameterDirection.Output; cmd.Parameters.Add(dnt_cur);
Example 6-6 Defining and Binding OracleParameter Objects for Stored Procedure: VB
Dim dept_id As OracleParameter = New OracleParameter dept_id.OracleDbType = OracleDbType.Decimal dept_id.Direction = ParameterDirection.Input dept_id.Value = 60 cmd.Parameters.Add(dept_id) Dim emp_cur As OracleParameter = New OracleParameter emp_cur.OracleDbType = OracleDbType.RefCursor emp_cur.Direction = ParameterDirection.Output cmd.Parameters.Add(emp_cur) Dim dnt_cur As OracleParameter = New OracleParameter dnt_cur.OracleDbType = OracleDbType.RefCursor dnt_cur.Direction = ParameterDirection.Output cmd.Parameters.Add(dnt_cur)
Run the application using the F7 keyboard shortcut.
A Form1 window appears.
In the Form1 window, enter the connection information, and click Connect.
In the DataGrid object, scroll horizontally to note that the last column, DEPARTMENT_ID, is equal to 60.
Note that the DataGrid contains the first result set from the stored procedure, which matches the query of the EMPLOYEES table.
Close the application.
