2.8 Stored Procedures

The Oracle8 access library includes support for RWDBStoredProc that is analogous to PL/SQL procedures and functions. (See the Oracle8 PL/SQL User's Guide and Reference, Chapter 6.) Procedures and functions are implemented using the OCI call OCIDescribeAny() 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  
// Oracle8 type FLOAT.
params.appendColumn(aParameter);
aParameter.name("new_bal").type
 (RWDBValue::Float).paramType(RWDBColumn::outParameter); 
// Describe the aParameter as output parameter of 
// Oracle8 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 and output parameters that can be exchanged between an RWDBStoredProc and a PL/SQL procedure.

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 Oracle8 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. Notice in the following code fragment that the user does not explicitly shift a cursor parameter into the procedure:

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);