User-defined function: Difference between revisions

Revision as of 10:26, 13 July 2006

Revision as of 12:37, 17 August 2006

Line 1:

A '''User -Defined Function''', or '''UDF''', is a [[function (programming)|function]] provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment.

==BASIC language==

Line 7:

==Databases==

In SQL Databasesdatabases, a user-defined function provides a mechanism for [[extension (computing)|extending]] tothe functionality of the [[database server]] by adding a function that can be evaluated in SQL queriesstatements. User definedThe [[SQL:2003|SQL]] standard distinguishes between scalar and table functions. in SQLA arescalar declaredfunction usingreturns theonly <code>CREATEa FUNCTION</code>single keywords.value For(or example[[NULL (SQL)|NULL]]), whereas a table function thatreturns convertsa Celsius(relational) totable Fahrenheitcomprised mightof bezero declaredor likemore this:rows and 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 might be declared like this:

<pre>

CREATE FUNCTION CtoF(@Celsius FLOAT)

RETURNS FLOAT

return RETURN (@Celsius * 1.8) + 32

AS

BEGIN

return (@Celsius * 1.8) + 32

END

</pre>

Once created, thea user-defined function may be used as [[expression|expressions]] in SQL statements. For example, it can be invoked where most other intrinsic functions are availableallowed. This also includes SELECT statements, where the function can be used against data stored in tables in the database. In such usageConceptually, the function is evaluated once per row in such usage. For example, assume a table named 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. This query:

<pre>

Line 25 ⟶ 24:

</pre>

would retrieve the name and the boiling point from each row. It invokes the CtoF user-defined function we'veas declared above in order to convert the value in the column to a value in Fahrenheit.

Each user-defined function carries certain properties or characteristics. The SQL standard defines the following properties:

* language - defines the programming language in which the user-defined function is implemented; examples are SQL, C, or Java.

* parameter style - defines the conventions that are used to pass the function parameters and results between the implementation of the function and the database system (only applicable if language is not SQL).

* specific name - a name for the function that is unique within the database. Note that the function name does not have to be unique, considering overloaded functions.

* determinism - specifies whether the function is deterministic or not. The determinism characteristic has an influence on the [[query optimizer|query optimizer]] when compiling a SQL statement. The rewrite of SQL statements may not change the number of function invocations for non-deterministic functions.

* SQL-data access - tells the database management system whether the function contains no SQL statements (NO SQL), contains SQL statements but does not access any tables or views (CONTAINS SQL), reads data from tables or views (READS SQL DATA), or actually modifies data in the database (MODIFIES SQL DATA).

User-defined functions should not be confused with [[stored procedure]]s. Stored procedures allow the user to creategroup a set of SQL commands. thatA procedure can accept parameters and canexecute beits SQL statements executeddepending usingon those parameters. These proceduresA runprocedure asis anot setan [[expression|expression]] and, returnthus, acannot setbe ofused rows; they are not evaluated per-row in any circumstance, aslike user-defined functions are.

Some database management systems allow the creation of user defined functions in languages other than SQL. Mirosoft SQL Server, for example, allows the user to use .NET languages for this purpose. DB2 and Oracle support usel-defined functions written in C or Java programming languages.

==External links==