Procedure documentationCreating Database Procedures Locate this document in the navigation structure

 

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

Database procedures are also known as Stored Procedures.

Prerequisites

  • 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.

Procedure

  1. Open the SQL editor.

    More information: Working with SQL Statements: Overview

  2. Create the database procedure with the CREATE DBPROCEDURE SQL statement.

    More information: SQL Reference Manual, CREATE DBPROC[EDURE] Statement (create_dbproc_statement)

Result

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

Example

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

More Information

SQL Tutorial, Database Procedures