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> [DETERMINISTIC] AS <routine>

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

Examples

SQL-Tutorial, Database Functions

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. If VARCHAR is used, then the length is not specified; if NUMBER is used, then the precision is not specified. In these cases, the database system automatically derives the length or precision, 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.

DETERMINISTIC

Specifying DETERMINISTIC ensures that the function exhibits a deterministic behavior. This means that the same values are always returned for the same input values.

Specifying DETERMINISTIC is absolutely necessary if you want to use the function to generate a function index.