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().
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.
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. See the SourcePro C++ API Reference Guide 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.
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();
|
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.
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.