User-defined Functions
IN SQL, we may also create our own functions, so-called user-defined functions.
A user-defined function is a routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table. Use this statement to create a reusable routine that can be used in other queries.
In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements. The SQL standard distinguishes between scalar and table functions. A scalar function returns only a single value (or NULL), whereas a table function returns a (relational) table comprising zero or more rows, each row with one or more columns.
Stored Procedures vs. Functions:
- Only functions can return a value (using the RETURN keyword).
- Stored procedures can use RETURN keyword but without any value being passed[1]
- Functions could be used in SELECT statements, provided they don’t do any data
manipulation and also should not have any OUT or IN OUT parameters.
- Functions must return a value, but for stored procedures this is not
- A function can have only IN parameters, while stored procedures may have OUT or IN OUT parameters.
- A function is a subprogram written to perform certain computations and return a single value.
- A stored procedure is a subprogram written to perform a set of actions, and can return multiple values using the OUT parameter or return no value at all.
User-defined functions in SQL are declared using the CREATE FUNCTION statement.
When we have created the function, we can use the function the same way we use built-in functions.