2.8 Stored Procedures

The Oracle access library includes support for RWDBStoredProc that is analogous to PL/SQL procedures and functions. (See the Oracle PL/SQL User's Guide and Reference, Chapter 6.) Procedures and functions are implemented using the OCI call odessp() to get parameter information. SQL statements are made to create procedures and functions, to get their text, and to execute them.

Please note that PL/SQL differentiates between stored functions and procedures. PL/SQL functions cannot be created with the createProcedure() method of RWDBDatabase that we describe in the next section. Instead, you must use the executeSql() method of RWDBConnection. PL/SQL functions can be executed and the return value can be obtained using the RWDBStoredProc::returnValue() call.

2.8.1 Creating a Stored Procedure

A stored procedure is created using the CREATE OR REPLACE PROCEDURE... SQL statement. You can create the procedure by using the RWDBDatabase::createProcedure() call. In this case, the parameter list and body of the stored procedure must be supplied. The body should contain the following:

<declarations>
BEGIN
<statements>
END;

The following simple procedure to debit an account provides an example. The procedure takes two input parameters and one output parameter. The input parameters are the amount and the acct_id, and the output parameter is the new balance, new_bal.

RWCString body(" acct_bal REAL; \n"
               " service_charge CONSTANT REAL :=1.5;\n"
               " BEGIN \n"
               "  SELECT bal INTO acct_bal FROM accts "
               "  WHERE acct_no = acct_id; \n"
               " IF acct_bal < 500.00 THEN \n"
               "  acct_bal := acct_bal service_charge; \n"
               "  END IF;\n"
               "  acct_bal := acct_bal - amount; \n "
               "  UPDATE accts SET bal = acct_bal \n "
               "   WHERE acct_no = acct_id;\n"
               " new_bal :=acct_bal; \n"
               " END; \n");
RWDBSchema params;
RWDBColumn aParameter;
aParameter.name("acct_id").type(RWDBValue::Int).paramType
       (RWDBColumn::inParameter);
params.appendColumn(aParameter);
aParameter.name("amount").type(RWDBValue::Float).paramType
       (RWDBColumn::inParameter); 
// Describe aParameter as input parameter of  
// ORACLE type FLOAT.
params.appendColumn(aParameter);
aParameter.name("new_bal").type
 (RWDBValue::Float).paramType(RWDBColumn::outParameter); 
// Describe the aParameter as output parameter of 
// ORACLE type FLOAT
  params.appendColumn(aParameter);
  adb.createProcedure("debit",body,params);
  RWDBStoredProc aProc = adb.storedProc("debit"); 
  float new_bal;
  aProc << 1 << 1250.90 << &new_bal;
  aProc.execute(); 
  aProc.fetchReturnParams(); // actually loads the 
                             // output parameters

After a stored procedure is created, it can be executed by instantiating an RWDBStoredProc and calling the execute() method. See the DBTools.h++ User's Guide and Reference for more information. It is the responsibility of the programmer to make sure that his or her stored procedure can be compiled by the PL/SQL compiler.

2.8.2 Input/Output Parameters and Result Sets

Input and output parameters can be passed to stored procedures. DBTools.h++ uses RWDBColumn to describe a parameter to a stored procedure.

The RWDBStoredProcedure::fetchReturnParams() must be used to load the output parameters.

There is no limit on the number of input/output parameters that can be exchanged between an RWDBStoredProc and a PL/SQL procedure. There is, however, a default value of 32 parameters.

To specify the number of parameters, for example, 40, you would proceed as follows:

#include <rw/db/orasrc/orasysh.h>

RWDBConnection oraConnect = myDbase.connection();

RWDBOracleSystemHandle* handle =
 (RWDBOracleSystemHandle*)oraConnect.systemHandle();

handle->maximumNumberOfParameters(40);

// One should then use the explicit connection oraConnect.

DBTools.h++ provides support for stored procedures that contain cursor variables. A cursor variable is a reference to a cursor that is defined and opened on an Oracle server. In the sample stored procedure below, written in PL/SQL, myCursor is a cursor variable.

create package myProcPkg as 
  type cursvar is ref cursor return employees%ROWTYPE;
end myProcPkg;

create procedure myProc(myDept in number, 
                        myCursor in out myProcPkg.cursvar) as
begin
    open myCursor for select * from employees where DEPTNO = myDept; 
end myProc;

DBTools.h++ enables your application to receive an RWDBResult containing the result sets returned from the execution of a stored procedure, as in the following code fragment:

RWDBStoredProc myStoredProc = aDatabase.storedProc("myProcPkg.myProc");
int myDept = 30;
myStoredProc << myDept;
RWDBResult result = myStoredProc.execute();

2.8.3 Instantiating an RWDBStoredProcedure Using Schema Data

The member function:

RWDBDatabase::storedProc(const RWCString& aName, 
                         const RWDBConnection& aConn, 
                         const RWDBSchema& aSchema)

increases the performance of stored procedures by eliminating the usual parameter fetch required for instantiations. The schema can come from previously cached procedure schema, through RWDBStoredProc::params(), or from user-created schema. The user-created schema must include the name and the type of each parameter in the stored procedure. When the type is unknown, the native type should be used. The native type is used first to avoid type mapping, unless the native type is set to the default -1.

Here is an example that uses the procedure created in Section 2.8.2:

RWDBSchema params;
params.appendColumn("myDept", 
  RWDBValue::Long, 
  0, 
  -1,
  -1, 
  -1, 
  TRUE,
  RWDBColumn::inParameter);
  params.appendColumn("myCursor", 
  RWDBValue::NoType, 
  0, 
  102,  // pl/sql cursor type
  -1, 
  -1, 
  TRUE, 
  RWDBColumn::inOutParameter);
RWDBStoredProc  sp = db.storedProc("myProc", params);