The built-in functions are default to the database, whereas user-defined functions are created by users.
Built-In Functions
- Scalar functions- A scalar function returns a single value each time it is invoked and is generally valid wherever an SQL expression is valid.
- Table functions- A table function can be used in a FROM clause and returns a table
- Row function- A row function can be used as a transform function and returns a row
Two Types of UDFs
- Sourced functions– Functions that are based on existing functions.
- External functions– Functions that are developed by users.
Example:
[code language=”sql”]
Create Function DAYSINMONTH(DATE)
RETURNS Integer
EXTERNAL NAME ‘DAYMYTH’
LANGUAGE COBOL;
[/code]
Once UDF is created, DBA has to give Grant access to use this function in COBOL program. In the COBOL program the code should be as follows:
[code language=”sql”]
Select Empno, Lastname, Birthdate, Daysinmonth(Birthdate)
From DSN8810.Temp
Where Daysinmonth <31
[/code]
Related:
- Why SQL functions are faster than UDF (stackoverflow.com)
