Conclusion
A stored function is a special type of stored program that returns a single result. Stored functions can be used in SQL statements or within other stored programs wherever an expression that returns a corresponding data type can be used.
Stored functions have the following limitations when compared to stored procedures:
- They may not include OUT or INOUT parameters.
- They may not return result sets.
A stored function terminates when a RETURN statement is encountered. In general, it is good practice to include a single RETURN statement at the end of the function rather than including multiple RETURN statements inside flow control statements. If a stored function terminates without issuing a RETURN statement, an error will be raised.
You can use stored functions within standard SQL. Doing so can improve the readability and maintainability of the SQL by centralizing the definition of complex calculations, decodes, or other application logic.
Be careful, however, when using stored functions inside SQL statements if those functions embed SQL statements. Stored functions that include SQL can often perform badly when included within standard SQL statements.