Stored Functions
Explore the creation and use of stored functions in SQL to write reusable, single-value return programs. Understand how stored functions differ from stored procedures, their syntax, parameter types, and performance considerations. Learn to view, call, and manage stored functions in your database.
We'll cover the following...
Stored Functions
A stored function is a kind of a stored program that can only return one value. It can be used in SQL statements in place of an expression. Common formulas or expressions that are used over and over again can be written in stored functions to make the code reusable. However, if a stored function that queries data from tables is used in a SQL statement, then it may slow down the speed of the query.
There are a number of differences when we compare stored functions to stored procedures. Stored procedures can call stored functions but the opposite is not possible. Stored functions can be used in SQL statements but stored procedures can only be called with the CALL keyword. That is why stored procedures are stored in compiled form where as stored functions are parsed and compiled at runtime. Return value is optional in stored procedures but a must in stored functions. Moreover, stored functions can only return one value but there is no such restriction on the number of return values in stored procedures. Stored functions only support IN parameter type while stored procedures can have IN, OUT and INOUT parameters. Error handling is not possible in stored functions.
The CREATE FUNCTION statement is used to create a stored function. The parameter list contains all the parameters of the function. Unlike stored procedures where the parameters could be IN, OUT or INOUT type, a stored function only takes IN parameters so there is no need to specify the type of parameters in ...