Functions
Go Up to Supported Objects
Functions are subroutines that you define. Functions are useful for reusable application logic. You can use functions to determine the best methods for controlling access and manipulation of the underlying data contained in an object.
The table below describes the types of user-defined functions you can create:
| Function | Description |
|---|---|
|
Column or External Table Function |
You can write in a host programming language, such as C. This function can act on a table and returns a table value rather than a scalar value. |
|
External Scalar Function |
You can write in a language other than SQL, such as C++ or Java and returns a scalar value to the program. This type of function is referenced by the CREATE FUNCTION statement and can be used to perform computations on data contained in the database but cannot directly reference the data. |
|
OLEDB Function |
Accesses OLE DB data in user-defined OLE DB external tables. |
|
Sourced Function |
Inherits the semantics of another function and can be an operator. |
|
Template Function |
Partial functions that do not contain any executable code. Mainly used in a federated database to map the template function to a data source function -Oracle, SQL Server, Sybase ASE, etc. A function mapping needs to be created in conjunction with the template function. |
Creating and editing
- Functions Wizard (DB2 LUW) and Functions Editor (IBM DB2 LUW)
- Functions Wizard (DB2 Z/OS) and Functions Editor (IBM DB2 Z/OS)
- Functions wizard (MySQL) and Functions editor (MySQL)
- Functions Wizard (Oracle) and Functions Editor (Oracle)
- Functions Wizard (PostgreSQL) and Functions Editor (PostgreSQL)
- Functions Wizard (SQL Server) and Functions Editor (SQL Server)
- Functions Wizard (Sybase ASE) and Functions Editor (Sybase ASE)
- Note: Creation and editing of objects of this type is not supported against Sybase IQ datasources.
DBMS platform availability and object actions/operations supported
The following table lists object actions available for this object type. For an introduction to object actions and details on usage of specific actions, see Object actions.
| DB2 LUW | DB2 z/OS | MySQL | ORCL | PSTGRS * | SQL SVR | SYB ASE | SYB IQ | |
|---|---|---|---|---|---|---|---|---|
|
✓ |
||||||||
|
✓ |
||||||||
|
✓ |
||||||||
|
✓ |
||||||||
|
✓ |
✓ |
✓ |
||||||
|
✓ |
✓ |
✓ |
✓ |
✓ |
✓ |
✓ |
✓ | |
|
✓ |
||||||||
|
✓ |
✓ |
✓ |
✓ |
✓ |
✓ |
✓ |
✓ | |
|
✓ |
||||||||
|
✓ |
✓ |
✓ |
✓ |
✓ |
✓ |
|||
|
✓ | ||||||||
|
✓ |
✓ |
✓ |
||||||
|
✓ |
✓ |
✓ |
✓ |
✓ |
✓ |
|||
|
✓ |
Important Notes
With respect to functions and stored procedures, it can be necessary to have statements executed before and after creation of the procedure or function. This can be useful for example, if you need to create or drop temporary tables used by the function or procedure. Two tag pairs, ETStart and ETEnd, let you embed statements in the first comment block of a stored procedure or function. The following shows the expected syntax:
create procedure dbo.procname(@a numeric) as
/*
<ETStart>SQL Statement</ETStart>
<ETEnd>SQL Statement</ETEnd>
*/
begin
...