CREATE FUNCTION statement

Description

Creates a new function in the database.

Syntax

CREATE FUNCTION [ owner.]function-name ( [ parameter, … ] ) RETURNS data-type routine-characteristics 
{ compound-statement 
   | AS tsql-compound-statement 
   | external-name }

Parameters

parameter:: IN parameter-name data-type

routine-characteristics:: ON EXCEPTION RESUME | [ NOT ] DETERMINISTIC

tsql-compound-statement:: sql-statement sql-statement ...

external-name:: EXTERNAL NAME library-call | EXTERNAL NAME java-call LANGUAGE JAVA

library-call:: '[operating-system:]function-name@library.dll; ...'

operating-system:: WindowsNT | UNIX

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

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

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

Examples

Example 1

Concatenates a firstname string and a lastname string:

CREATE FUNCTION fullname (
	firstname CHAR(30),
	lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
	DECLARE name CHAR(61);
	SET name = firstname || ' ' || lastname;
	RETURN (name);
END

The following examples illustrate the use of the fullname function.

To return a full name from two supplied strings, enter:
```
SELECT fullname ('joe','smith')
```

fullname('joe', 'smith')
joe smith

To list the names of all employees, enter:

SELECT fullname (emp_fname, emp_lname)
FROM employee

fullname (emp_fname, emp_lname)
Fran Whitney
Matthew Cobb
Philip Chin
Julie Jordan
Robert Breault
...

Example 2

Uses Transact-SQL syntax:

CREATE FUNCTION DoubleIt ( @Input INT )
RETURNS INT 
AS 
DECLARE @Result INT  
SELECT @Result = @Input * 2 
RETURN @Result

The statement SELECT DoubleIt( 5 ) returns a value of 10.

Example 3

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

CREATE FUNCTION creates a user-defined function in the database. A function can be created for another user by specifying an owner name. Subject to permissions, a user-defined function can be used in exactly the same way as other nonaggregate functions.

The following describes each clause of the CREATE FUNCTION statement.

CREATE FUNCTION Parameter names must conform to the rules for database identifiers. They must have a valid SQL data type, and must be prefixed by IN, signifying that the argument is an expression that provides a value to the function.

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. See “Transact-SQL batch overview” and CREATE PROCEDURE statement [T-SQL].

EXTERNAL NAME A function using the EXTERNAL NAME clause is a wrapper around a call to a function in an external library. A function using EXTERNAL NAME can have no other clauses following the RETURNS clause. The library name may include the file extension, which is typically .dll on Windows, and .so on UNIX. In the absence of the extension, the software appends the platform-specific default file extension for libraries.

EXTERNAL NAME LANGUAGE JAVA A function that uses EXTERNAL NAME with a LANGUAGE JAVA clause is a wrapper around a Java method.

For information on calling Java procedures, see CREATE PROCEDURE statement.

ON EXCEPTION RESUME Use Transact-SQL -like error handling. For more information, see CREATE PROCEDURE statement.

NOT DETERMINISTIC A function specified as NOT DETERMINISTIC 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

Functions may be declared as DETERMINISTIC if they always return the same value for given input parameters.

Unless they are declared NOT DETERMINISTIC, all user-defined functions return a consistent result for the same parameters and are free of side effects. That is, the server assumes that two successive calls to the same function with the same parameters will return the same result, and will not have any unwanted side effects on the query's semantics.

User-defined functions are processed by Adaptive Server Anywhere. They do not take advantage of the performance features of Sybase IQ. Queries that include user-defined functions run at least 10 times slower than queries without them.

In certain cases, differences in semantics between ASA and Sybase IQ can produce different results for a query if it is issued in a user-defined function. For example, Sybase IQ treats the CHAR and VARCHAR data types as distinct and different, while Anywhere treats CHAR data as if it were VARCHAR.

To modify a user-defined function, or to hide the contents of a function by scrambling its definition, use the ALTER FUNCTION statement. For more information, see the Adaptive Server Anywhere SQL Reference.

Side effects

Automatic commit.

Standards

SQL/92 Persistent Stored Module feature.

Sybase Not supported by Adaptive Server Enterprise.

Permissions

Must have RESOURCE authority.

External functions, including Java functions, must have DBA authority.