Creating Database Procedures

A database procedure is a series of statements that forms an independent part of a program.

Database procedures are also called Stored Procedures.

You are logged on to the database as a database user.
You have the CREATEIN privilege for the schema in which you want to create the database procedure.
More information: SQL Reference Manual, Privilege Specification for a Schema (schema_priv_spec)
The database is in the ONLINE operational state.

Open the SQL editor.
More information: Entering and Executing SQL Statements
Create the database procedure using the CREATE DBPROCEDURE statement.
More information: SQL Reference Manual, CREATE DBPROC[EDURE] Statement (create_dbproc_statement)

The database procedure is displayed in the Procedures folder in the explorer window (read-only) .

The database procedure avg_price determines the average price for single rooms in hotels located within the specified zip code range.

CREATE DBPROCEDURE hotel.avg_price (IN zip CHAR(5), OUT avg_price FIXED(6,2)) AS

VAR sum FIXED(10,2); price FIXED(6,2); hotels INTEGER;

TRY

SET sum = 0; SET hotels = 0;

DECLARE dbproccursor CURSOR FOR

SELECT price FROM hotel.room,hotel.hotel WHERE zip = :zip AND

room.hno = hotel.hno AND type = 'single';

WHILE $rc = 0 DO BEGIN

FETCH dbproccursor INTO :price;

SET sum = sum + price;

SET hotels = hotels + 1;

END;

CATCH

IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

CLOSE dbproccursor;

IF hotels > 0 THEN SET avg_price = sum / hotels

ELSE STOP (100, 'no hotel found');