Creating Database Functions

A database function is a user-defined function. To create a database function, you define a database procedure with only one output parameter. You call a database function in the same way that you would call a predefined function.

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 function.
More information: SQL Reference Manual, Privilege Specification for a Schema (schema_priv_spec)
The database is in the ONLINE operational state.

Procedure

Open the SQL editor.
More information: Entering and Executing SQL Statements
Create the database function using the CREATE FUNCTION statement.
More information: SQL Reference Manual, CREATE FUNCTION Statement (create_function_statement)

Result

The database function is displayed in the Functions folder in the explorer window (read-only).

Example

The database function avgprice determines the average price for single rooms in hotels located within a specified zip code range.

CREATE FUNCTION hotel.avgprice (zip CHAR(5)) RETURNS FIXED(6,2) AS

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

hotels INTEGER; avg_price FIXED(6,2);

TRY

SET sum = 0; SET hotels = 0;

DECLARE functionresult 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 functionresult INTO :price;

SET sum = sum + price;

SET hotels = hotels + 1;

END;

CATCH

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

CLOSE functionresult;

IF hotels > 0 THEN RETURN sum/hotels

ELSE RETURN NULL;

More Information

SQL Tutorial, Database Functions