Creating Stored Procedures
Create stored procedures by calling the
createProcedure() method of
RWDBDatabase. The DB Interface Module will create the parameter list from a schema you supply, while the body of the stored procedure is supplied by the application. 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:
BEGIN
<declarations>
<statements>
END;
This simple example looks up a royalty percentage and returns the royalty schedule based on title and sales level:
RWCString body =
"begin"
" declare total int \n"
" select titles.total_sales into total \n"
" from titles where title_id = title_id; \n"
" if ( total > 0 ) \n"
" set total = total + newsales; \n"
" select royalty into pc from roysched \n"
" where total >= roysched.lowrange and \n"
" total < roysched.highrange \n"
" and roysched.title_id = title_id; \n"
" select royalty,lowrange,highrange from roysched \n"
" where roysched.title_id = @title_id; \n"
"end; "
RWDBSchema params;
RWDBColumn aParameter;
aParameter.name("title_id").type(RWDBValue::Int);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);
aParameter.name("newsales").type(RWDBValue::Int);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);
aParameter.name("pc").type(RWDBValue::Float);
aParameter.paramType(RWDBColumn::outParameter);
params.appendColumn(aParameter);
aDb.createProcedure("royalty", body, params);
