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.
