Skip Headers
Oracle® Database 2 Day + .NET Developer's Guide
11g Release 2 (11.2)

Part Number E10767-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Using PL/SQL Stored Procedures and REF CURSORs

This chapter contains:

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 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:

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:

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

    Description of connect_se.gif follows
    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.

  2. In Server Explorer, right-click Packages and select New Package.

    Description of procedure1.gif follows
    Description of the illustration procedure1.gif

    The New Package window appears.

  3. In the New Package window, change the Package Name to HR_DATA.

  4. Under the Methods area, click Add.

    Description of procedure2.gif follows
    Description of the illustration procedure2.gif

    The Add Method window appears.

  5. In the Add Method window, enter Method Name GETCURSORS, and change Method Type to Procedure.

  6. 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 procedure3.gif follows
    Description of the illustration procedure3.gif

  7. Click OK when you finish adding parameters.

    The New Package window reappears.

  8. 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"; 
    
  9. Click OK to close the Preview SQL window.

  10. In the New Package window, click OK to save the new package.

    The new package, HR_DATA, now appears in the Server Explorer.

  11. In the Server Explorer, right-click the package HR_DATA, and select Edit Package Body.

    Description of procedure4.gif follows
    Description of the illustration procedure4.gif

    The code for the package appears.

  12. 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;
    
  13. Save the changes to the package.

  14. To run the stored procedure, in Server Explorer, expand the HR_DATA package.

    Right-click the GETCURSORS method, and select Run.

    Description of procedure5.gif follows
    Description of the illustration procedure5.gif

    The Run Procedure window appears.

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

    Description of procedure6.gif follows
    Description of the illustration procedure6.gif

  16. 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.

  17. 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 procedure7.gif follows
    Description of the illustration procedure7.gif

  18. Select the Value column entry for DEPENDENTS_C.

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

    Description of procedure8.gif follows
    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:

  1. Open the application HR_Connect_CS or HR_Connect_VB.

  2. 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".

  3. With Form1 selected, switch to code view.

  4. 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
    
  5. 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)
    
  6. 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:

  1. Run the application.

    A Form1 window appears.

  2. In the Form1 window, enter the connection information, and click Connect.

  3. 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 procedure9.gif follows
    Description of the illustration procedure9.gif

  4. Close the application.