Background documentationDatabase Procedures Locate this document in the navigation structure

 

Database procedures are programs that can be called up from an application program in the same way as an SQL statement. A database procedure can contain several SQL statements. A series of control structures are also available for the application programmer. You can program loops or branches, for example, within a database procedure.

You can define both input and output parameters in order to transfer individual values to a database procedure or to obtain results.

You can use database procedures, for example, to formulate complex integrity rules that check the permissibility of values. In database procedures, operations can be provided for application objects. Any changes to these rules or operations can be made at a central location, namely in the database procedure. This means that they do not have to be made individually in each application. The programs are more clearly organized and, as a result, more user friendly.

Using database structures also makes it easier to grant privileges. All you have to do is grant the call privilege; you do not need to grant privileges for the addressed database objects.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Creating a Database Procedure

To create a database procedure, use the CREATE DBPROC statement.

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

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

Explanation

  • IN zip CHAR(5), OUT avg_price FIXED(6,2) : The input parameter is the zip code zip, the output parameter is the average price avg_price.

  • The SQL statements (in this example, a SELECT statement) and control structures (in this example, a WHILE and an IF statement) that are required for executing the desired procedure are defined after the keyword AS.

  • The variables required within the database procedure are defined after the keyword VAR.

  • Error Handling: If an SQL error occurs in the statement list between TRY and CATCH, the system branches directly to the statement that follows CATCH. The actual troubleshooting routine can be programmed in this statement.

  • SET can be used to assign values to the sum and hotels variables.

  • The DECLARE CURSOR statement is used to define the named result table dbproccursor.

  • The tables in the SELECT statement must always be specified completely, that is, with the owner specified. Note that the zip code is transferred as the parameter: :zip.

  • The WHILE statement enables statements to be repeated in response to certain conditions. The statement is executed as long as the search condition specified is met.

  • The FETCH statement assigns the values from the current row of the result table dbproccursor to parameters.

  • The $rc variable returns a numeric error code after a SELECT statement has been executed. The value 0 means that the SELECT statement was successfully executed.

  • An IF statement first evaluates the search condition. If this is met, the statement specified in the THEN branch is executed. Otherwise, the statement in the ELSE branch (if defined) is executed.

  • The CLOSE statement deletes the result table dbproccursor.

See also:

SQL Reference Manual, CREATE DBPROC[EDURE] Statement (create_dbproc_statement), Routine (routine)

Evaluating System Tables, DBPROCEDURES

Calling Up a Database Procedure

To call up a database procedure, use the CALL statement.

When the hotel table with the SQL Statements for the HOTEL Demo Schema has been created and filled with data, you can try the following CALL statement:

CALL hotel.avg_price ('20005', :avg_price)

Result

Out(1)

135

If the mode of a formal parameter in the database procedure is OUT, the corresponding expression must be a parameter specification. The CALL statement contains the parameter specification :avg_price.

See also: SQL Reference Manual, CALL Statement (call_statement)

Deleting a Database Procedure

To delete a database procedure, use the DROP DBPROC statement.

DROP DBPROC hotel.avg_price

You can use this SQL statement to delete the database procedure definition.

See also: SQL Reference Manual, DROP DBPROC[EDURE] Statement (drop_dbproc_statement)

More Information

More examples for Data Definition