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.
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development