|
|
|
The CREATE FUNCTION command creates a new User Defined Scalar Function in the current database
function_name is the name that is to be assigned to the function. Different owners can have functions with the same name in the same database
Each parameter_decl is of the form:
[ARG_TYPE] ARG_NAME DATA_TYPE
and is separated by a comma (‘,’)
The return value of the function is specified with the RETURNS key word followed by the data_type declaration.
The Java IMPORT statements which are needed for the correct functioning of the Java snippet (the code between BEGIN and END -- see below) need to be placed in the section between the IMPORT and BEGIN keywords. This section need not be present if there are no import statements.
The function cannot be created without specifying all the import statements needed by the snippet. (For details refer to any Java manual or book)
The Function body, referred to as the Java snippet, needs to be placed between the BEGIN and END keywords. Any valid Java code other than class or function definitions can be present here. The snippet must have a return statement.
Note: Each of the keywords IMPORT, BEGIN and END need to be upper case and be present on a separate line. Once the IMPORT or BEGIN are specified, the statement is terminated by an END but not a semicolon (‘;’).
Creating a User Defined Scalar Function
ISQL> CREATE FUNCTION str_cat(IN org_string VARCHAR(20), IN string_to_concat VARCHAR(20))
RETURNS VARCHAR(40)
IMPORT
import java.math.*;
BEGIN
String new_str = org_string + string_to_concat ;
return new_str;
END
The above example creates a User Defined Scalar Function, str_cat, that takes two input arguments and returns the concatenated string.
