The function owner and the
database owner
automatically gain the
EXECUTE privilege on the function, and are able to grant this privilege to
other users. The EXECUTE privileges cannot be revoked from the function and
database owners.
Syntax CREATE FUNCTION function-name ( [ FunctionParameter
[, FunctionParameter] ] * ) RETURNS DataType [ FunctionElement ] *
function-Name[ schemaName. ] SQL92IdentifierIf
schema-Name is not provided, the current schema is the default schema. If
a qualified procedure name is specified, the schema name cannot begin with
SYS.
FunctionParameter[ parameter-Name ] DataType
PararameterName must be unique within a function.
The syntax
of DataType is described in .
Data-types
such as BLOB, CLOB, LONG VARCHAR, LONG VARCHAR FOR BIT DATA, and XML are not
allowed as parameters in a CREATE FUNCTION statement.
FunctionElement {
| LANGUAGE { JAVA }
| EXTERNAL NAME string
| PARAMETER STYLE JAVA
| { NO SQL | CONTAINS SQL | READS SQL DATA }
| { RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT }
}
LANGUAGEJAVA- the database manager will
call the function as a public static method in a Java class.
EXTERNAL NAME stringString describes
the Java method to be called when the function is executed, and takes the
following form: class_name.method_nameThe External
Name cannot have any extraneous spaces.
PARAMETER STYLEJAVA - The function will
use a parameter-passing convention that conforms to the Java language and
SQL Routines specification. INOUT and OUT parameters will be passed as single
entry arrays to facilitate returning values. Result sets are returned through
additional parameters to the Java method of type java.sql.ResultSet[] that
are passed single entry arrays.
does
not support long column types (for example Long Varchar, BLOB, and so on).
An error will occur if you try to use one of these long column types.
NO SQL, CONTAINS SQL, READS SQL DATA Indicates
whether the function issues any SQL statements and, if so, what type.
- CONTAINS SQL
- Indicates that SQL statements that neither read nor modify SQL data can
be executed by the function. Statements that are not supported in any function
return a different error.
- NO SQL
- Indicates that the function cannot execute any SQL statements
- READS SQL DATA
- Indicates that some SQL statements that do not modify SQL data can be
included in the function. Statements that are not supported in any stored
function return a different error. This is the default value.
RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUTSpecifies
whether the function is called if any of the input arguments is null. The
result is the null value.
- RETURNS NULL ON NULL INPUT
- Specifies that the function is not invoked if any of the input arguments
is null. The result is the null value.
- CALLED ON NULL INPUT
- Specifies that the function is invoked if any or all input arguments are
null. This specification means that the function must be coded to test for
null argument values. The function can return a null or non-null value. This
is the default setting.
The function elements may appear in any order, but each type of
element can only appear once. A function definition must contain these elements:
- LANGUAGE
- PARAMETER STYLE
- EXTERNAL NAME
ExampleCREATE FUNCTION TO_DEGREES(RADIANS DOUBLE) RETURNS DOUBLE
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'java.lang.Math.toDegrees'