MySQL Stored Procedure Programming

Using Stored Functions in SQL

So far, we have looked at stored functions as though they were simply a variant on the stored procedure syntax—a special type of stored procedure that can return a value. While this is certainly a valid use for a stored function, stored functions have an additional and significant role to play: as user-defined functions (UDFs ) within SQL statements.

Consider the SELECT statement shown in Example 10-11: it returns a count of customers by status, with the one-byte status code decoded into a meaningful description. It also sorts by the decoded customer status. Notice that we must repeat the rather awkward CASE statement in both the SELECT list and the ORDER BY clause.

Example 10-11. SQL statement with multiple CASE statements

SELECT CASE customer_status
            WHEN 'U' THEN 'Up to Date'
            WHEN 'N' THEN 'New'
            WHEN 'O' THEN 'Overdue'
       END  as Status, count(*) as Count
  FROM customers
 GROUP BY customer_status
 ORDER BY CASE customer_status
            WHEN 'U' THEN 'Up to Date'
            WHEN 'N' THEN 'New'
            WHEN 'O' THEN 'Overdue'
       END

Now imagine an application with many similar CASE statements, as well as complex calculations involving business accounting logic, scattered throughout our application. Such statements—often with embedded expressions far more complex than the one shown in Example 10-11—result in code that is difficult to understand and maintain. Whenever the CASE constructs or business calculations need to be modified, it will be necessary to find and then modify a large number ...