User Defined Function
A User Defined Function is a routine that can be called just like built-in functions. A function has some input parameters, and returns a single value.
Functions create local scopes. Variables, cursors, temporary tables, and functions declared in user defined functions can be refered only within the functions.
Scalar Function
Declaration
scalar_function_declaration
: DECLARE function_name FUNCTION ([parameter [, parameter ...] [, optional_parameter ...]])
AS
BEGIN
statements
END;
optional_parameter
: parameter DEFAULT value
- function_name
- identifier
- statements
- Statements
- parameter
- Variable
- value
- value
A scalar function takes some arguments, and returns a value. In the statements, arguments are set to variables specified in the declaration as parameters.
Usage
function_name([argument, [, argument ...]])
- function_name
- identifier
- argument
- value
Aggregate Function
Declaration
aggregate_function_declaration
: DECLARE function_name AGGREGATE (cursor_name [, parameter ...] [, optional_parameter ...])
AS
BEGIN
statements
END;
optional_parameter
: parameter DEFAULT value
- function_name
- identifier
- cursor_name
- identifier
- statements
- Statements
- parameter
- Variable
- value
- value
An aggregate function takes at least one argument, and returns a value. The first argument is a representation of grouped values, and the following arguments are parameters.
In the statements, grouped values represented by the first argument can be referred with a pseudo cursor named as cursor_name, and the second argument and the followings are set to variables specified in the declaration as parameters. You can use the Fetch Statement, While In Statement or the Cursor Status Expressions against the pseudo cursor.
Usage
You can use a user defined aggregate function as an Aggregate Function or an Analytic Function.
As an Aggregate Function
function_name([DISTINCT] expr [, argument ...])
- function_name
- identifier
- expr
- value
- argument
- value
As an Analytic Function
function_name([DISTINCT] expr [, argument ...]) OVER ([partition_clause] [order_by_clause [windowing_clause]])
- function_name
- identifier
- expr
- value
- argument
- value
- partition_clause
- Partition Clause
- order_by_clause
- Order By Clause
- windowing_clause
- Windowing Clause
Example:
DECLARE product AGGREGATE (list, @default DEFAULT 0)
AS
BEGIN
VAR @value, @fetch;
WHILE @fetch IN list
DO
VAR @floatVal := FLOAT(@fetch);
IF @floatVal IS NULL THEN
CONTINUE;
END IF;
IF @value IS NULL THEN
@value := @floatVal;
CONTINUE;
END IF;
@value := @value * @floatVal;
END WHILE;
IF @value IS NULL THEN
@value := @default;
END IF;
RETURN @value;
END;
SELECT product(i) FROM numbers;
SELECT product(i, NULL) FROM numbers;
SELECT i, product(i) OVER (order by i) FROM numbers;
DISPOSE FUNCTION Statement
A DISPOSE FUNCTION statement disposes user defined function named as function_name.
DISPOSE FUNCTION function_name;
- function_name
- identifier
RETURN Statement
A RETURN statement terminates executing function, then returns a value. If the return value is not specified, then returns a null.
When there is no return statement, the function executes all the statements and returns a null.
RETURN [value];
- value
- value