CREATE FUNCTION statement

The CREATE FUNCTION statement allows you to create Java functions, which you can then use in an expression.

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. ] SQL92Identifier

If 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.

Note: 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 }
 }

LANGUAGE

JAVA- the database manager will call the function as a public static method in a Java class.

EXTERNAL NAME string

String describes the Java method to be called when the function is executed, and takes the following form:

class_name.method_name

The External Name cannot have any extraneous spaces.

PARAMETER STYLE

JAVA - 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.

Derby 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 INPUT

Specifies 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

Example

CREATE FUNCTION TO_DEGREES(RADIANS DOUBLE) RETURNS DOUBLE
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'java.lang.Math.toDegrees'