Contents Index CREATE EXTERNLOGIN statement CREATE INDEX statement

ASA SQL Reference
  SQL Statements

CREATE FUNCTION statement

Description 

Use this statement to create 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 }

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; ...'

operating-system :
Windows95 | WindowsNT | NetWare | 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;

Parameters 

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 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, .so on UNIX, and .nlm on NetWare. In the absence of the extension, the software appends the platform-specific default file extension for libraries. On NetWare, if no NLM name is given, the NLM containing the symbol must already be loaded when the function is called.

For information about external library calls, see Calling external libraries from procedures.

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. Future versions of the software may use this declaration to allow optimizations that are unsafe for functions that could return different values for the same input.

Usage 

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.

Adaptive Server Anywhere treats all user-defined functions as idempotent unless they are declared NOT DETERMINISTIC. Idempotent 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.

Permissions 

Must have RESOURCE authority.

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

Side effects 

Automatic commit.

See also 

ALTER FUNCTION statement

DROP statement

BEGIN statement

CREATE PROCEDURE statement

RETURN statement

Using Procedures, Triggers, and Batches

Standards and compatibility 
Examples 

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
...

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

Contents Index CREATE EXTERNLOGIN statement CREATE INDEX statement