User-defined function: Difference between revisions

Revision as of 14:24, 8 April 2023

Revision as of 10:05, 20 May 2023

Line 10:

==Databases==

In [[relational database management system]]s, a user-defined function provides a mechanism for extending the functionality of the [[database server]] by adding a function, that can be evaluated in standard query language (usually [[SQL]]) statements. The [[SQL:2003ISO/IEC 9075|SQL standard]] standard distinguishes between [[Scalar (computing)|scalar]] and table functions. A scalar function returns only a single value (or [[null (SQL)|NULL]]), whereas a table function returns a (relational) table comprising zero or more rows, each row with one or more columns.

User-defined functions in SQL are declared using the <code>CREATE FUNCTION</code> statement. For example, a function that converts Celsius to Fahrenheit (a temperature scale used in USA) might be declared like this:

Line 19:

</syntaxhighlight>

Once created, a user-defined function may be used in [[expression (programming)|expressions]] in SQL statements. For example, it can be invoked where most other intrinsic functions are allowed. This also includes [[Select (SQL)|SELECT statements]], where the function can be used against data stored in tables in the database. Conceptually, the function is evaluated once per row in such usage. For example, assume a table named {{Code|Elements}}, with a row for each known chemical element. The table has a column named BoilingPoint for the boiling point of that element, in Celsius. The query

<syntaxhighlight lang="sql">