|
Creates a user-defined SQL function in the database.
CREATE [ OR REPLACE | TEMPORARY ] FUNCTION [ owner.]function-name ( [ parameter, ... ] ) RETURNS data-type [ SQL SECURITY { INVOKER | DEFINER } ] [ ON EXCEPTION RESUME ] [ [ NOT ] DETERMINISTIC ] compound-statement | AS tsql-compound-statement | AT location-string
parameter : [ IN ] parameter-name data-type [ DEFAULT expression ]
tsql-compound-statement : sql-statement sql-statement ...
Specifying CREATE OR REPLACE FUNCTION creates a new function, or replaces an existing function with the same name. When a function is replaced, the definition of the function is changed but the existing privileges are preserved.
You cannot use the OR REPLACE clause with temporary functions.
Specifying CREATE TEMPORARY FUNCTION means that 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 on them, and, unlike other functions, temporary functions are not recorded in the catalog or transaction log.
Temporary functions execute with the privileges of their creator (current user) or specified owner. You can specify an owner for a temporary function when:
the temporary function is created within a permanent stored procedure
the owner of the temporary function and permanent stored procedure is the same
To drop the owner of a temporary function, you must drop the temporary function first.
Temporary functions can be created and dropped when connected to a read-only database.
You cannot use the OR REPLACE clause with temporary functions.
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. However, function parameters are IN by default.
The data type of the parameter. Set the data type explicitly, or specify the %TYPE or %ROWTYPE attribute to set the data type to the data type of another object in the database. Use %TYPE to set it to the data type of a column in a table or view. Use %ROWTYPE to set the data type to a composite data type derived from a row in a table or view.
Use the RETURNS clause to specify the data type for the result of the function. The RETURNS clause must be the first clause of the statement.
The SQL SECURITY clause 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.
Use Transact-SQL-like error handling.
Use this clause to indicate whether functions are deterministic or non-deterministic. If this clause is omitted, then the deterministic behavior of the function is unspecified (the default).
If a function is declared as DETERMINISTIC, it should return the same value every time it is invoked with the same set of parameters.
If a function is declared as NOT DETERMINISTIC, then it is not guaranteed to return the same value for the same set of parameters. A function declared as NOT DETERMINISTIC is re-evaluated each time it is called in a query. This clause must be used when it is known that the function result for a given set of parameters can vary.
Also, functions that have side effects such as modifying the underlying data should be declared as NOT DETERMINISTIC. For example, here is an example of a function that generates primary key values and is used in an INSERT...SELECT statement; it is declared NOT DETERMINISTIC. The tables that are referenced are fictitious.
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 can be declared as DETERMINISTIC if they always return the same value for given input parameters.
A set of SQL statements bracketed by BEGIN and END, and separated by semicolons.
tsql-compound-statement is a batch of Transact-SQL statements.
Create a proxy function on the current database for a remote function specified by location-string. The AT clause supports the semicolon (;) as a field delimiter in location-string. If no semicolon is present, a period is the field delimiter. The use of semicolons allows file names and extensions to be used in the database and owner fields.
The string in the AT clause can also contain local or global variable names enclosed in braces ({variable-name}). The SQL variable name must be of type CHAR, VARCHAR, or LONG VARCHAR. For example, an AT clause that contains 'bostonase.master.dbo.{@myfunction}' indicates that @myfunction is a SQL variable and that the current contents of the @myfunction variable should be substituted when the remote procedure is used.
A proxy function can return any data type except DECIMAL, NUMERIC, LONG VARCHAR, LONG NVARCHAR, LONG BINARY, XML, or any spatial data type.
The CREATE FUNCTION statement creates a function in the database. A function can be created for another user by specifying an owner name. Subject to privileges, a function can be used in exactly the same way as other non-aggregate functions.
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.
When SQL SECURITY INVOKER is specified, more memory is used because annotation must be done for each user that calls the procedure. Also, when SQL SECURITY INVOKER is specified, name resolution is done as the invoker. Therefore, make sure to qualify all object names (tables, procedures, and so on) with their appropriate owner.
All 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 returns the same result, and does not have any unwanted side effects on the query's semantics.
If a function returns a result set, it cannot also set output parameters or return a return value.
You must have the CREATE PROCEDURE system privilege to create functions owned by you.
You must have the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privilege to create functions owned by others.
You must also have the CREATE EXTERNAL REFERENCE system privilege to create an external function.
No privilege is required to create temporary functions.
To replace an existing function, you must be the owner of the function, or have one of the following:
CREATE ANY PROCEDURE and DROP ANY PROCEDURE system privileges.
CREATE ANY OBJECT and DROP ANY OBJECT system privileges.
ALTER ANY OBJECT or ALTER ANY PROCEDURE system privileges.
Automatic commit, even for temporary functions.
CREATE FUNCTION is a core feature of ANSI/ISO SQL Standard, though some of its components supported in the software are optional SQL Language Features. A subset of these features includes:
The SQL SECURITY clause is optional Language Feature T324.
The ability to pass a LONG VARCHAR, LONG NVARCHAR, or LONG BINARY value to a SQL function is Language Feature T041.
The ability to create or modify a schema object within a SQL function, using statements such as CREATE TABLE or DROP TRIGGER, is Language Feature T651.
The ability to use a dynamic-SQL statement within a SQL function, including statements such as EXECUTE IMMEDIATE, PREPARE, and DESCRIBE, is Language Feature T652.
Several clauses of the CREATE FUNCTION statement are not in the standard. These include:
The TEMPORARY clause.
The ON EXCEPTION RESUME clause.
The optional DEFAULT clause for a specific routine parameter.
The specification of a Transact-SQL function using the AS clause.
The optional OR REPLACE clause.
CREATE FUNCTION is supported by Adaptive Server Enterprise. Adaptive Server Enterprise does not support the optional IN keyword for function parameters.
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 example replaces the fullname function created in the first example. After replacing the function, the local variable name is removed:
CREATE OR REPLACE FUNCTION fullname( firstname CHAR(30), lastname CHAR(30) ) RETURNS CHAR(61) BEGIN RETURN ( firstname || ' ' || lastname ); 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 ( GivenName, Surname ) FROM GROUPO.Employees;
| fullname (GivenName, Surname) |
|---|
| Fran Whitney |
| Matthew Cobb |
| Philip Chin |
| Julie Jordan |
| ... |
The following function uses Transact-SQL syntax:
CREATE FUNCTION DoubleIt( @Input INT ) RETURNS INT AS BEGIN DECLARE @Result INT SELECT @Result = @Input * 2 RETURN @Result END;
The statement SELECT DoubleIt( 5 ); returns a value of 10.
The following example creates a function called fullname and sets the data types of the firstname and lastname parameters to the data types of the Surname and Givenname column of the Employees table by using a %TYPE attribute:
CREATE OR REPLACE FUNCTION fullname( firstname Employees.Surname%TYPE, lastname Employees.GivenName%TYPE ) RETURNS LONG VARCHAR BEGIN RETURN ( firstname || ' ' || lastname ); END; SELECT fullname ( Surname, GivenName )FROM GROUPO.Employees;