Creates a new function in the database.
CREATE FUNCTION [ owner.]function-name ( [ parameter, … ] ) RETURNS data-type routine-characteristics
{ compound-statement
| AS tsql-compound-statement
| external-name }
IN parameter-name data-type
ON EXCEPTION RESUME | [ NOT ] DETERMINISTIC
sql-statement sql-statement ...
EXTERNAL NAME library-call | EXTERNAL NAME java-call LANGUAGE JAVA
'[operating-system:]function-name@library.dll; ...'
WindowsNT | UNIX
'[package-name.]class-name.method-name method-signature'
( [ field-descriptor, ... ] ) return-descriptor
Z | B | S | I | J | F | D | C | V | [descriptor | Lclass-name;
The following function 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.
Return a full name from two supplied strings:
SELECT fullname ('joe','smith')
fullname('joe', 'smith') |
|---|
joe smith |
List the names of all employees:
SELECT fullname (emp_fname, emp_lname) FROM employee
fullname (emp_fname, emp_lname) |
|---|
Fran Whitney |
Matthew Cobb |
Philip Chin |
Julie Jordan |
Robert Breault |
... |
The following function 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.
The following statement 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
The CREATE FUNCTION statement 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 non-aggregate functions.
The following describes each of the clauses of the CREATE FUNCTION statement.
CREATE FUNCTION clause Parameter names must conform to the rules for database identifiers. They must have a valid SQL data type, and must be prefixed by the keyword 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 clause 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.
For information about external library calls, see “Calling external libraries from procedures” in Chapter 8, “Using Procedures and Batches” of the Sybase IQ System Administration Guide.
EXTERNAL NAME LANGUAGE JAVA clause 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 clause Use Transact-SQL -like error handling. For more information, see CREATE PROCEDURE statement.
NOT DETERMINISTIC clause 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 the Adaptive
Server Anywhere portion of the product. They do not take advantage
of the performance features of Sybase IQ. Queries that include
user-defined functions will 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, 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.
Automatic commit.
Must have RESOURCE authority.
External functions, including Java functions, must have DBA authority.
Chapter 8, “Using Procedures and Batches” in the Sybase IQ System Administration Guide
ALTER FUNCTION statement in the Adaptive Server Anywhere SQL Reference.
