CREATE FUNCTION Statement (create_function_statement)
The CREATE FUNCTION
statement (create_function_statement
)
defines a database function.
Structure
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 the data types BOOLEAN
, CHAR[ACTER]
, DATE
, FIXED
, FLOAT
, INT[EGER]
, NUMBER
, REAL
, SMALLINT
, TIME
, TIMESTAMP
and VARCHAR
are permitted as a data type
(data_type
) of the 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 the data types BOOLEAN
, CHAR[ACTER]
, DATE
, DEC[IMAL]
, DOUBLE
, FLOAT
, INT[EGER]
, NUMBER
, NUMERIC
, REAL
, SMALLINT
, TIME
, TIMESTAMP
and VARCHAR
are
permitted as a data type of the return value.
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.