A stored function is a stored program that
returns a value. While stored procedures may return values via
INOUT variables, a function can—and
must—return data only via a single
RETURN value. Unlike stored procedures, stored
functions can be used in expressions wherever you can use a
built-in function of the same return data type and can be used inside of
SQL statements such as
In this chapter we will look at how and when to use stored functions.
The use of stored functions can improve the readability and maintainability of stored program code by encapsulating commonly used business rules or formulas. You can also use stored function return values to control the overall program flow.
Using stored functions in standard SQL statements can simplify the syntax of the SQL by hiding complex calculations and avoiding the repetitive coding of these calculations throughout your code. Stored functions can also be used in SQL to implement operations that would otherwise require subqueries or joins, although you need to be careful to avoid possible performance problems that can occur if a function called from a SQL statement itself calls other SQL statements.
Stored functions may not return result sets and may not include dynamic SQL.
We provided an overview of the
CREATE FUNCTION statement in Chapter 7, but we will recap here. You
create a stored function using the following syntax:
CREATE FUNCTION ...