Background documentationDatabase Functions Locate this document in the navigation structure

 

You can define user-specific database functions. In an SQL statement, you can then use these user-defined database functions in the same way as any other predefined functions.

A database function 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 function.

Database functions are specialized database procedures that contain any number of input parameters but only one output parameter. The output parameter represents the result of the database function.

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 Function

To create a database function, use the CREATE FUNCTION statement.

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

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

     hotels INTEGER;

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;

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

Explanation

  • The input parameter is the zip code zip; the data type of the output value is specified after the keyword RETURNS.

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

  • The variables required within the database function 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 functionresult.

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

  • The output parameter is defined after the keyword RETURN.

See also:

SQL Reference Manual, CREATE FUNCTION Statement (create_function_statement), Routine (routine)

Evaluating System Tables, FUNCTIONS

Calling Up a Database Function

To call up a database function, follow the procedure for calling up a predefined function. 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 SELECT statement:

SELECT hno, hotel.avgprice(zip) avgprice

  FROM hotel.hotel

    WHERE hno < 100

Result

HNO

AVGPRICE

10

135

20

70

30

45

40

87.5

50

105

60

120

70

115

80

87.5

90

90

See also: SQL Reference Manual, Database Function Call (dbfunction_call)

Deleting a Database Function

To delete a database function, use the DROP FUNCTION statement.

DROP FUNCTION hotel.avgprice

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

See also: SQL Reference Manual, DROP FUNCTION Statement (drop_function_statement)

More Information

More examples for Data Definition