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);