Creates a user-defined function by adding a SQL wrapper to a Java static method. Can return a value defined by the method.
create function [owner.]sql_function_name ([ sql_parameter_name sql_datatype [(length)| (precision[, scale ])] [[, sql_parameter_name sql_datatype [(length)| (precision[, scale])]] ...]]) returns sql_datatype [(length)| (precision[, scale])] [modifies sql data] [returns null on null input | called on null input] [deterministic | not deterministic] [exportable] language java parameter style java external name 'java_method_name [([java_datatype[, java_datatype ...]])] '
is the Transact-SQL name of the function, must conform to the rules for identifiers, and cannot be a variable.
is the name of an argument to the function. The value of each input parameter is supplied when the function is executed. Parameters are optional; a SQLJ function need not take arguments.
Parameter names must conform to the rules for identifiers. If the value of a parameter contains nonalphanumeric characters, it must be enclosed in quotes. This includes object names qualified by a database name or owner name, since they include a period. If the value of the parameter begins with a numeric character, it also must be enclosed in quotes.
is the Transact-SQL datatype of the parameter. See create procedure for more information about these parameters.
sql_datatype is the SQL procedure signature.
specifies the result datatype of the function.
indicates that the Java method invokes SQL operations, reads, and modifies SQL data in the database. This is the default and only implementation. It is included for syntactic compatibility with the ANSI standard.
included for syntactic compatibility with the ANSI standard. Not currently implemented.
specifies that the procedure is to be run on a remote server using the Adaptive Server OmniConnect™ feature. Both the procedure and the method it is built on must reside on the remote server.
specifies that the external routine is written in Java. This is a required clause for SQLJ functions.
specifies that the parameters passed to the external routine at runtime are Java parameters. This is a required clause for SQLJ functions.
indicates that create function defines a SQL name for an external routine written in a programming language other than SQL.
specifies the name of the external routine (Java method). The specified name—‘java_method_name [ java_datatype[{, java_datatype} ...]]’—is a character-string literal and must be enclosed in single quotes.
specifies the name of the external Java method.
specifies a Java datatype that is mappable or result-set mappable. This is the Java method signature.
Creates a function square_root that invokes the java.lang.Math.sqrt() method:
create function square_root (input_number double precision) returns double precision language java parameter style java external name 'java.lang.Math.sqrt'
You cannot create a SQLJ function with the same name as an Adaptive Server built-in function.
You can create user-defined functions (based on Java static methods) and SQLJ functions with the same class and method names.
Adaptive Server searching order ensures that the SQLJ function is always found first.
You can include a maximum of 31 parameters in a create function statement.
When a function is created, Adaptive Server checks to see if it is a SQL user-defined function or a SQLJ user-defined function. If it is the latter, Adaptive Server checks for “sa” permissions. If it is a SQL function Adaptive Server checks for create function privileges.
Only the database owner or user with sa role can execute create function. The database owner or sa cannot transfer permission for create function.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
---|---|---|---|
97 |
install |
create function |
|
See Java in Adaptive Server Enterprise for more information about create function.
Commands create function (SQLJ), drop function (SQLJ)
System procedures sp_depends, sp_help, sp_helpjava, sp_helprotect