Creating Stored Procedures
Stored procedures can be created by calling the
createProcedure() method of
RWDBDatabase. The DB Interface Module creates the parameter list from a schema you supply, while the body of the stored procedure is supplied by the application. Here is a simple SQL procedure example, which returns the average employee salary as an output parameter, returns the total of all salaries as a return value, and returns a table of all the employees whose salary is greater than the average:
RWCString body =
"LANGUAGE SQL \n"
"DYNAMIC RESULT SETS 1 \n"
"BEGIN \n"
"DECLARE entries INTEGER; \n"
"DECLARE counter INTEGER default 0; \n"
"DECLARE total DOUBLE default 0.0; \n"
"DECLARE aSalary DOUBLE default 0.0; \n"
"DECLARE c1 CURSOR FOR SELECT salary FROM employees; \n"
"DECLARE c2 CURSOR WITH RETURN TO CALLER FOR \n"
" SELECT * FROM employees WHERE salary > aSalary; \n"
"SELECT COUNT(*) INTO entries FROM employees; \n"
"OPEN c1; \n"
"WHILE counter < entries \n"
" DO FETCH c1 INTO aSalary; \n"
" SET total = total + aSalary; \n"
" SET counter = counter + 1; \n"
"END WHILE; \n"
"IF entries = 0 \n"
" THEN SET average = 0; \n"
"ELSE \n"
" SET average = total / entries; \n"
" SET aSalary = average; \n"
"END IF; \n"
"OPEN c2; \n"
"RETURN CAST(total as INTEGER); \n"
"END \n";
RWDBSchema params;
RWDBColumn aParameter;
aParameter.name("average").type(RWDBValue::Double);
aParameter.paramType(RWDBColumn::outParameter);
params.appendColumn(aParameter);
aDB.createProcedure("averageSalary", body, params, conn);
The createProcedure() method uses the DB2 SQL statement CREATE PROCEDURE. For this reason, it can also register stored procedures with the application server when they are written in languages other than SQL. Please see the DB2 documentation for more information.
