User defined function
A user-defined function ( english function user-defined , acronym UDF ) in a programming environment refers to a function that will create and the user himself can integrate into its projects. It fulfills a task like a macro , but is implemented via a function call. User-defined functions are available in general programming languages such as Visual Objects , scripting languages such as PHP , special macro software such as AutoIt and in database languages such as SQL and Firebird .
The syntax of a user-defined function must correspond to the syntax of the underlying programming language , and predefined standard functions and other user-defined functions can be used in the definition. A user-defined function must provide exactly one return value.
Unlike a stored procedure , a user-defined function cannot be started as a program .
Examples
Scalar user-defined function in SQL
The following scalar user-defined function is passed the foreign key @PersNr of the person of data type Integer . The function returns the lowest lecture number for this person.
CREATE FUNCTION GibMinVorlesungsnummer(@PersNr INT)
RETURNS INT
AS
RETURN
AS
(
RETURN
SELECT MIN(VorlNr)
FROM Vorlesung
WHERE PersNr = @PersNr
);
After this user-defined function is stored in the database , it can be used within a SQL query. The query
SELECT Professor.PersNr, Professor.Name, GibMinVorlesungsnummer(PersNr) AS MinVorlNr,
(SELECT Titel
FROM Vorlesung
WHERE VorlNr = GibMinVorlesungsnummer(PersNr)
) AS Titel
FROM Professor;
could return the following table as a result:
PersNo | Surname | MinVorl | title |
---|---|---|---|
12 | Wirth | 1000 | Software development 1 |
15th | Tesla | 1001 | Databases |
20th | Vacationers | 1200 | Networks 1 |
External Table Function in SQL
In this variant, exactly one unnamed SQL table is supplied as the return value. It can be anywhere within an SQL statement where an SQL table can be. A control entry that refers to an external program is defined at the SQL level. The database system usually supports several programming languages in which the program can be written.
The following user-defined function in SQL has two parameters @VorlNr , @PersNr of the data type Integer :
CREATE FUNCTION GibVorlesungen(@VorlNr INT, @PersNr INT)
RETURNS TABLE
AS
RETURN
AS
(
SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.PersNr, Professor.Name
FROM Professor INNER JOIN Vorlesung
ON Professor.PersNr = Vorlesung.PersNr
WHERE VorlNr = @VorlNr AND PersNr = @PersNr
);
The query
SELECT * FROM GibVorlesungen(1001, 15);
could return the following result (see SQL view ):
Template no | title | PersNo | Surname |
---|---|---|---|
1001 | Databases | 15th | Vacationers |
Web links
- Microsoft Docs: [ https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-ver15
Create User-defined Functions (Database Engine)]