CREATE FUNCTION Statement (Java UDF)

Creates a new external Java table UDF function in the database.

Syntax

CREATE [ OR REPLACE | TEMPORARY ] FUNCTION [ owner.]function-name  
   ( [ parameter, … ] )
   [ SQL SECURITY { INVOKER | DEFINER } ]
   RETURNS data-type
   ON EXCEPTION RESUME
   | [ NOT ] DETERMINISTIC
   { compound-statement | AS tsql-compound-statement
   | EXTERNAL NAME 'java-call' LANGUAGE JAVA [ ALLOW | DISALLOW SERVER SIDE REQUESTS ] environment-name}

parameter:
   IN parameter-name data-type [ DEFAULT expression ]

tsql-compound-statement: 
   sql-statement 
    sql-statement …

java-call:
   '[ package-name.]class-name.method-name method-signature'

method-signature:
   ( [ field-descriptor, ….] ) return-descriptor

field-descriptor and return-descriptor:
   Z | B | S | I | J | F | D | C | V | [ descriptor | L class-name;

Parameters

CREATE [ OR REPLACE ] – parameter names must conform to the rules for database identifiers. They must have a valid SQL data type and be prefixed by the keyword IN, signifying that the argument is an expression that provides a value to the function.
The CREATE clause creates a new function, while the OR REPLACE clause replaces an existing function with the same name. When a function is replaced, the definition of the function is changed but the existing permissions are preserved. You cannot use the OR REPLACE clause with temporary functions.

TEMPORARY – the function is visible only by the connection that created it, and that it is automatically dropped when the connection is dropped. Temporary functions can also be explicitly dropped. You cannot perform ALTER, GRANT, or REVOKE operations on them, and unlike other functions, temporary functions are not recorded in the catalog or transaction log.
Temporary functions execute with the permissions of their creator (current user), and can only be owned by their creator. Therefore, do not specify owner when creating a temporary function. They can be created and dropped when connected to a read-only database.

SQL SECURITY – defines whether the function is executed as the INVOKER, the user who is calling the function, or as the DEFINER, the user who owns the function. The default is DEFINER.
When INVOKER is specified, more memory is used because annotation must be done for each user that calls the procedure. Also, name resolution is done as the invoker as well. Therefore, take care to qualify all object names (tables, procedures, and so on) with their appropriate owner.

data-type – LONG BINARY and LONG VARCHAR are not permitted as return-value data types.

compound-statement – a set of SQL statements bracketed by BEGIN and END, and separated by semicolons. See BEGIN … END Statement.

tsql-compound-statement – a batch of Transact-SQL statements.

[NOT] DETERMINISTIC – function is re-evaluated each time it is called in a query. The results of functions not specified in this manner may be cached for better performance, and re-used each time the function is called with the same parameters during query evaluation.
Functions that have side effects, such as modifying the underlying data, should be declared as NOT DETERMINISTIC. For example, a function that generates primary key values and is used in an INSERT … SELECT statement should be declared NOT DETERMINISTIC:
```
CREATE FUNCTION keygen( increment INTEGER ) 
RETURNS INTEGER 
NOT DETERMINISTIC 
BEGIN   
  DECLARE keyval INTEGER;  
  UPDATE counter SET x = x + increment;  
  SELECT counter.x INTO keyval FROM counter;   
  RETURN keyval 
END 
INSERT INTO new_table 
SELECT keygen(1), ... 
FROM old_table
```
```
CREATE FUNCTION keygen( increment INTEGER ) 
RETURNS INTEGER 
NOT DETERMINISTIC 
BEGIN   
  DECLARE keyval INTEGER;  
  UPDATE counter SET x = x + increment;  
  SELECT counter.x INTO keyval FROM counter;   
  RETURN keyval 
END 
INSERT INTO new_table 
SELECT keygen(1), ... 
FROM old_table
```
Functions may be declared as DETERMINISTIC if they always return the same value for given input parameters. All user-defined functions are treated as deterministic unless they are declared NOT DETERMINISTIC. Deterministic functions return a consistent result for the same parameters and are free of side effects. That is, the database server assumes that two successive calls to the same function with the same parameters will return the same result without unwanted side-effects on the semantics of the query.

LANGUAGE JAVA – a wrapper around a Java method. For information on calling Java procedures, see CREATE PROCEDURE Statement.

environment-name – a wrapper around a Java method.
The DISALLOW clause is the default. The ALLOW clause indicates that server-side connections are allowed.

Note: Do not specify the ALLOW clause unless necessary. ALLOW slows down certain types of SAP Sybase IQ table joins. Do not use UDFs with both the ALLOW and DISALLOW SERVER SIDE REQUESTS clauses in the same query.

Examples

Example 1 – creates an external function written in Java:

CREATE FUNCTION dba.encrypt( IN name char(254) )
RETURNS VARCHAR 
EXTERNAL NAME
'Scramble.encrypt (Ljava/lang/String;)Ljava/lang/String;'
LANGUAGE JAVA

Usage

When functions are executed, not all parameters need to be specified. If a default value is provided in the CREATE FUNCTION statement, missing parameters are assigned the default values. If an argument is not provided by the caller and no default is set, an error is given.

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—Not supported by Adaptive Server Enterprise.

Permissions

For function to be owned by self – Requires the CREATE PROCEDURE system privilege

For function to be owned by any user – Requires one of:

CREATE ANY PROCEDURES system privilege.
CREATE ANY OBJECT system privilege.

To create a function containing an external reference, regardless of whether or not they are the owner of the function, also requires the CREATE EXTERNAL REFERENCE system privilege.