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.
For details on how Derby matches functions to Java methods,
see Argument matching.
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.
The syntax
of DataType is described in Data types.
This is the return type of a table function. Currently, only Derby-style table functions are supported.
They are functions which return JDBC ResultSets. For more information, see
"Programming Derby-style table functions" in the
Derby Developer's Guide.
At run-time, as values are read out of the user-supplied ResultSet, Derby coerces those values
to the data types declared in the CREATE FUNCTION statement. This affects values typed as
CHAR, VARCHAR, LONG VARCHAR, CHAR FOR BIT DATA, VARCHAR FOR BIT DATA,
LONG VARCHAR FOR BIT DATA, and DECIMAL/NUMERIC. Values which are too long are truncated
to the maximum length declared in the CREATE FUNCTION statement. In addition,
if a String value is returned in the ResultSet for a column of CHAR type
and the String is shorter than the declared length of the CHAR column, Derby
pads the end of the String with blanks in order to stretch it out to the declared
length.
The syntax
of DataType is described in Data types.
Note: XML is not
allowed as the type of a column in the dataset returned by a table function.
FunctionElement
{
| LANGUAGE { JAVA }
| DeterministicCharacteristic
| EXTERNAL NAME string
| PARAMETER STYLE ParameterStyle
| { 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.
DeterministicCharacteristic
DETERMINISTIC | NOT DETERMINISTIC
Declares that the function is deterministic, meaning that with the same
set of input values, it always computes the same result. The default is NOT
DETERMINISTIC. Derby cannot
recognize whether an operation is actually deterministic, so you must take care
to specify the DeterministicCharacteristic correctly.
ParameterStyle
JAVA | DERBY_JDBC_RESULT_SET
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 can be 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.
The PARAMETER STYLE is DERBY_JDBC_RESULT_SET if and only if this
is a Derby-style table function, that is, a function which returns
TableType
and which is mapped to a method which returns a JDBC
ResultSet. Otherwise, the PARAMETER STYLE must be JAVA.
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 of declaring a scalar function
CREATE FUNCTION TO_DEGREES
( RADIANS DOUBLE )
RETURNS DOUBLE
PARAMETER STYLE JAVA
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'java.lang.Math.toDegrees'
Example of declaring a table function
CREATE FUNCTION PROPERTY_FILE_READER
( FILENAME VARCHAR( 32672 ) )
RETURNS TABLE
(
KEY_COL VARCHAR( 10 ),
VALUE_COL VARCHAR( 1000 )
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
NO SQL
EXTERNAL NAME 'vtis.example.PropertyFileVTI.propertyFileVTI'
