Developer Reference : Data Access Methods : SQL Engine Reference : SQL Syntax Reference : CREATE FUNCTION
 
CREATE FUNCTION
The CREATE FUNCTION statement creates a scalar user-defined function (UDF) in the database. You can invoke the user-defined functions from a query.
Syntax
CREATE FUNCTION function-name ( [ [ IN ]
{ :parameter_name scalar_parameter_data_type [ DEFAULT value | = value ] } [...] ] )
RETURNS scalar_return_data_type
[AS]
BEGIN
body_of_function
RETURN scalar_expression
END;
 
function_name ::= name of the scalar UDF. UDF names must conform to the rules for identifiers and must be unique within the database.
 
parameter_name ::= a parameter in the scalar UDF. A maximum of 300 parameters is allowed. The value must be supplied when the function is invoked.
 
scalar_parameter_data_type ::= the data type for the specified parameter name.
 
scalar_return_data_type ::= the return value of a scalar UDF. Only scalar types are supported.
 
value ::= a value to assign to scalar_return_data_type, either with the DEFAULT keyword or with the equals sign.
 
body_of_function ::= the statements that compose the scalar function.
 
scalar_expression ::= specifies the scalar value that the scalar function returns.
Remarks
You must have the CREATE FUNCTION privilege when you create a function in a secured database.
Each fully qualified UDF name (database-name.function-name) must be unique within a database. The UDF name cannot be the same as any of the following in the same database:
•a built-in function name
•any other UDF name
•a stored procedure name
Restrictions
You cannot use the CREATE DATABASE or the DROP DATABASE statement in a user-defined function. The table actions CREATE, ALTER, UPDATE, DELETE, and INSERT are not permitted within a user-defined function.
Only scalar input parameters are supported. No OUTPUT and INOUT parameters are allowed. By default, all parameters are input. You need not specify the IN keyword.
Limits
Observe the following limitations when you create user-defined functions.
Attribute
Limit
Number of parameters
300
Size of the UDF body
64 KB
Maximum length of UDF name
See Table 1, Identifier Restrictions by Identifier Type, in Advanced Operations Guide
Maximum length of UDF variable name
128 characters
Supported Scalar Input Parameters and Returned Data Types
Zen supports the data types for input scalar parameters and returned values shown in the following table.
Table 20 Supported Input Parameters and Returned Data Types
AUTOTIMESTAMP
BIGDENTITY
BIGINT
BINARY
BIT
BLOB
CHAR
CHARACTER
CLOB
CURRENCY
DATE
DATETIME
DEC
DECIMAL
DOUBLE
FLOAT
IDENTITY
INT
INTEGER
LONG
LONGVARBINARY
LONGVARCHAR
NCHAR
NLONGVARCHAR
NUMERIC
NVARCHAR
REAL
SMALLIDENTITY
SMALLINT
TIME
TIMESTAMP
TIMESTAMP2
TINYINT
UBIGINT
UINT
UINTEGER
UNIQUEIDENTIFIER
USMALLINT
UTINYINT
VARBINARY
VARCHAR
 
Examples
This topic provides a number of examples of CREATE FUNCTION.
The following example creates a function that calculates the area of a rectangular box whose details are stored in the Box table:
CREATE FUNCTION CalculateBoxArea(:boxName char(20))
RETURNS REAL
AS
BEGIN
DECLARE :len REAL;
DECLARE :breadth REAL;
SELECT len, breadth INTO :len, :breadth FROM box
WHERE name = :boxName;
RETURN(:len * :breadth);
END;
============ 
The following example creates a function that compares two integers and returns the smaller of the two:
CREATE FUNCTION GetSmallest(:A integer, :B Integer)
RETURNS Integer
AS
BEGIN
DECLARE :smallest INTEGER
IF (:A < :B ) THEN
SET :smallest = :A;
ELSE
SET :smallest = :B;
END IF;
RETURN :smallest;
END;
============ 
The following example creates a function that calculates simple interest using the formula SI = PTR/100, where P is the Principle, T is the period, and R is the rate of interest.
CREATE FUNCTION CalculateInterest(IN :principle float, IN :period real, IN :rate double)
RETURNS DOUBLE
AS
BEGIN
DECLARE :interest DOUBLE;
SET :interest = ((:principle * :period * :rate) / 100);
RETURN (:interest);
END;
Invoking a Scalar User-Defined Function
You can invoke a user-defined function wherever scalar expressions are supported, by specifying the function name followed by a comma-separated list of arguments. The list of arguments is enclosed in parentheses.
A UDF can be invoked with or without a database qualifier prefix. When a database qualifier is not prefixed, the UDF is executed from the current database context. If a database qualifier is prefixed, the UDF is executed in the context of the specified database. (In the examples below, some use a database qualifier prefix and some do not.)
Limits
Parameter names cannot be specified in the arguments, when invoking a function.
The argument values for all parameters (also known as actual parameters) must be in the same sequence in which the parameters are defined in the CREATE FUNCTION statement (also known as formal parameters).
Examples of User-Defined Functions
UDF in Procedures
CREATE PROCEDURE procTestUdfInvoke() AS
BEGIN
DECLARE :a integer;
SET :a = 99 + (222 + Demodata.GetSmallest(10, 9)) + 10;
PRINT :a;
END;
CALL procTestUdfInvoke()
============ 
The following example is similar to the previous one, except that the database qualifier is omitted.
CREATE PROCEDURE procTestUdfInvoke2() AS
BEGIN
DECLARE :a INTEGER;
SET :a = 99 + (222 + GetSmallest(10, 9)) +10;
PRINT :a;
END;
CALL procTestUdfInvoke2
============ 
UDF in Select list
SELECT GetSmallest(100,99)
============ 
UDF in Where clause
SELECT name FROM class WHERE id <= GetSmallest(10,20)
============ 
UDF within UDF
CREATE FUNCTION funcTestUdfInvoke() RETURNS INTEGER as
BEGIN
DECLARE :a INTEGER;
SET :a = 99 + (222 - Demodata.GetSmallest(10, 9));
RETURN :a;
END;
============ 
UDF in INSERT statement
CREATE TABLE t1(col1 integer, col2 integer, col3 float)
INSERT INTO t1 VALUES (GetSmallest(10,20), 20 , 2.0)
INSERT INTO t1 (SELECT * FROM t1 WHERE col1 = getSmallest(10,20))
============ 
UDF in UPDATE statement
UPDATE t1 SET col2 = Demodata.GetSmallest(2,10) WHERE col1 = 2
UPDATE t1 set col1 = 3 WHERE col2 = Demodata.GetSmallest(10, 5)
============ 
UDF in GROUP BY statement
SELECT col2 FROM t1 GROUP BY getSmallest(10,2), col2
============ 
UDF in ORDER BY statement
SELECT col2 FROM t1 ORDER BY Demodata.getSmallest(10,2), col2
============ 
Recursive UDF
CREATE FUNCTION factorial(in :n integer) RETURNS double AS BEGIN
DECLARE :fact double;
IF (:n <= 0) THEN
SET :fact = 1;
ELSE
SET :fact = (:n * Demodata.factorial(:n - 1));
END IF;
RETURN :fact;
END;
select Demodata.factorial(20) can be used to get the factorial value of 20.
============ 
UDF with default value
CREATE FUNCTION testUdfDefault1(:z INTEGER DEFAULT 10) RETURNS INTEGER AS
BEGIN
RETURN :z-1;
END;
select Demodata.testUdfDefault1(). This function uses the default value specified (10) if a parameter is not provided.
CREATE FUNCTION testUdfDefault2(:a VARCHAR(20) = 'Accounting Report' ) RETURNS VARCHAR(20) as
BEGIN
RETURN :a;
END;
select Demodata.testUdfDefault2(). This function takes the default value specified (Accounting Report) if a parameter is not provided
============ 
UDF with dynamic parameters
SELECT name FROM class WHERE id <= GetSmallest(?,?)
============ 
UDF as an expression
SELECT 10 + Demodata.Getsmallest(10,20) + 15
============ 
UDF used as parameters
SELECT demodata.calculateinterest (10+demodata.getsmallest(3000, 2000), demodata.factorial(2), demodata.testUdfDefault(3))
See Also
DECLARE
DROP FUNCTION