Creates a new function.

Syntax

---

CREATE FUNCTION <Name>
([<ParamDefinition>[,ParamDefinition]])
RETURNS <DataType>
EXTERNAL NAME <ModuleName> | <BodyDefinition>
[DESCRIPTION <Description>]
[VERSION <VersionNumber>]
[ATTRIBUTES <CustomAttributes>]

<ParamDefinition> =

<Mode> <Name> <DataType> [<Description>]

<Mode> =

IN|OUT|INOUT

<DataType> =

CHARACTER|CHAR [(<Length>]) [<CollationName>]
CHARACTER VARYING|VARCHAR [(<Length>]) [<CollationName>]
GUID
BYTE [(<LengthInBytes>])
BYTE VARYING|VARBYTE [(<LengthInBytes>])
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB [<CollationName>]
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT [(<Precision>,<Scale>)]
DECIMAL|NUMERIC [(<Precision>,<Scale>)]
DATE
TIME
TIMESTAMP
INTERVAL YEAR [TO MONTH]
INTERVAL MONTH
INTERVAL DAY [TO HOUR|MINUTE|SECOND|MSECOND]
INTERVAL HOUR [TO MINUTE|SECOND|MSECOND]
INTERVAL MINUTE [TO SECOND|MSECOND]
INTERVAL SECOND [TO MSECOND]
INTERVAL MSECOND

<BodyDefinition> =

BEGIN
   [<Declaration>;]
   [<Declaration>;]
   [<Statement>;]
   [<Statement>;]
   RETURN <Expression>
[EXCEPTION
   [<Statement>;]]
END

Usage

---

Use this statement to create a new function in a given database. Functions can be used in jobs, other functions and procedures, triggers, DML statements, and catalog-bound expressions such as table column default expressions and table constraint check expressions.

Examples

---

-- This function looks up the sales tax
-- rate for a given state and county

CREATE FUNCTION LookupSalesTaxRate(IN State CHAR(2), IN County VARCHAR)
RETURNS DECIMAL(19,2)
BEGIN
   DECLARE TempCursor CURSOR FOR stmt;
   DECLARE Result DECIMAL(19,2) DEFAULT 0;

   PREPARE stmt FROM 'SELECT * FROM SalesTaxes WHERE State = ? AND County = ?';

   OPEN TempCursor USING State, County;

   IF (ROWCOUNT(TempCursor) > 0) THEN
      FETCH FIRST FROM TempCursor ('TaxRate') INTO Result;
   END IF;

   CLOSE TempCursor;

   RETURN Result;
END

Required Privileges

---

The current user must be granted the CREATE privilege on the current database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: