Stored Functions

A stored function is a stored program that returns a value. While stored procedures may return values via OUT or INOUT variables, a function canand mustreturn 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 SELECT, UPDATE, DELETE, and INSERT.

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.