Stored Procedures
The DB Access Module for Oracle OCI includes support for
RWDBStoredProc that is analogous to PL/SQL procedures and functions. (See the Oracle PL/SQL Language Reference.) OCI call
OCIDescribeAny() is used to get parameter information for procedures and functions. SQL statements are made to create procedures and functions, to get their text, and to execute them.
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 using class
RWDBStoredProc and the return value can be obtained using
RWDBStoredProc::returnValue().
NOTE >> It is currently not possible to use
RWDBStoredProc to encapsulate a PL/SQL procedure or function that has parameters of the Oracle datatypes CLOB and BLOB.
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;
// Describe aParameter as input parameter of
// Oracle type INTEGER.
aParameter.name("acct_id").type(RWDBValue::Int)
.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);
// Describe aParameter as input parameter of
// Oracle type FLOAT.
aParameter.name("amount").type(RWDBValue::Float)
.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);
// Describe the aParameter as output parameter of
// Oracle type FLOAT
aParameter.name("new_bal").type(RWDBValue::Float)
.paramType(RWDBColumn::outParameter);
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. It is the responsibility of the programmer to make sure that his or her stored procedure can be compiled by the PL/SQL compiler.
Input/Output Parameters and Result Sets
Input and output parameters can be passed to stored procedures. The DB Interface Module uses
RWDBColumn to describe a parameter to a stored procedure.
The RWDBStoredProc::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.
The DB Interface Module 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 following sample stored procedure, 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,
mySalary in number) as
begin
open myCursor for select * from employees where DEPTNO =
myDept and SALARY <= mySalary;
end myProc;
The DB Interface Module enables your application to receive an
RWDBResult containing the result sets returned by the reference cursors 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("myProc");
int myDept = 30, int mySalary = 50000;
myStoredProc["myDept"] << myDept;
myStoredProc["mySalary"] << mySalary;
RWDBResult result = myStoredProc.execute();
Instantiating an RWDBStoredProc Using Schema Data
The member functions:
RWDBDatabase::storedProc(RWCString, RWDBSchema, RWDBColumn)
RWDBDatabase::storedProc(RWCString, RWDBConnection, RWDBSchema,
RWDBColumn)
increase the performance of stored procedures by accepting a stored procedure schema from the user while constructing
RWDBStoredProc objects. This eliminates the usual parameter fetch required for instantiations. The schema to be supplied to these methods 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, if set, is used first to avoid type mapping. The column should be supplied for any return value and must include a name and the datatype.