Reading Notes-Derby Reference Manual (2)

Iv. create function statement
In Derby, the create function statement is used to create a function. Of course, the function is actually implemented by calling a function in the Java class.

By default, the function creator and the database creator obtain the absolute control right of the function and cannot be deprived. In addition, other users can be granted the power to execute functions.

The function name can specify a schema, but when the processing process in the function belongs to a specific schema, the schema name of the function name cannot be a string starting with sys (if a qualified procedure name is specified, the schema name cannot begin with sys .).

Function parameter types cannot be blob, clob, long varchar, long varchar for BIT data, and XML.

The returned data type can be table type (that is, a common query result dataset, corresponding to a resultset in Java) or database data type. The data returned by the Java function is forcibly converted to the database data type described in the create function statement. If the data length is too long, it is truncated according to the maximum length of the corresponding database type. When the returned data is table data, the result set should not contain XML data (XML is not allowed as the type of a column in the dataset returned by a table function .), the length of char and string is shorter than the defined length in the table column. Derby fills in the corresponding space after it to reach the defined length.

The function body contains the following elements:
{
| 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}
}
The preceding elements are not ranked successively, but the three elements are required: language, parameter style, and external name.
If the language is set to Java, the database manager calls the function as a public static method.
External name specifies the complete name of the Java method to be called, that is, class name + method name.
Deterministiccharacteristic specifies whether the function is deterministic (the output result is always the same as that of the same input parameter, which is a deterministic function; otherwise, it is an uncertainty function ), this is mainly used to determine whether the function can be used for index calculation columns and index views. The default value is not deterministic. Derby does not determine the certainty or uncertainty of an operation, so be cautious when specifying a function as deterministic.
Parameterstyle specifies the parameter type. Java: Common Database type; derby_jdbc_result_set: Table data type.
You can specify the execution environment of SQL statements in the function: No SQL indicates that no SQL statements can be run; contains SQL indicates that SQL statements that do not read or modify SQL data can be included; the default value of reads SQL data indicates that SQL statements without modifying SQL data can be included.
Returns NULL on Null input indicates that when any parameter is null, the function is not called and null is directly returned. Called on Null input is set by default, that is, when the existence parameter is null, the function is still called for execution.

--------------------------------------

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'