CREATE FUNCTION Statement (create_function_statement) 

The CREATE FUNCTION statement (create_function_statement) defines a database function.

Syntax

<create_function_statement> ::= CREATE [PUBLIC] FUNCTION <dbfunction_name> [(<formal_parameter1>,..)] RETURNS <data_type> AS <routine>

<formal_parameter1> ::= <argument1> <data_type>
<argument1> ::= <identifier>

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

CREATE FUNCTION avg_price (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;

Explanation

If a schema is not specified in the database function name, the current schema is implicitly assumed.

The database function is assigned to the schema that has been determined implicitly or specified explicitly. The current user must have the CREATEIN privilege for this schema. The function name must differ from the names of the database functions already existing in the schema.

The current user is the owner of a database function. This user has the EXECUTE privilege to execute the database function and assign this authorization to other users.

If a results table is created in the database function, its name must be different to the results table of the SQL statement it is called by.

Argument

By specifying an argument  argument1 , you assign a name to a formal parameter of the database function. This parameter name can then be used as a variable in expressions and assignments in the database function.

Data Type

Only BOOLEAN, CHAR[ACTER], DATE, FIXED, FLOAT, INT[EGER], NUMBER, REAL, SMALLINT, TIME, TIMESTAMP, and VARCHAR can be used as the data type  data_type  for a formal parameter of a database function.

Data types are normally defined with both length and precision. However, if you specify VARCHAR or NUMBER without length or precision, the database system derives these properties automatically depending on the context of the function call.

PUBLIC

By specifying PUBLIC, you generate a global database function that can be called without specifying a schema name. In this case, the function name must not contain a schema name and must not be the same as the name of another global database function.

RETURNS

Only BOOLEAN, CHAR[ACTER], DATE, DEC[IMAL], DOUBLE, FLOAT, INT[EGER], NUMBER, NUMERIC, REAL, SMALLINT,TIME, TIMESTAMP, and VARCHAR can be used as the data type of the return code.

See also:

SQL Tutorial, Database Functions