Using Stored Functions in SQL

So far, we have looked at stored functions as though they were simply a variant on the stored procedure syntaxa 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 statementsoften with embedded expressions far more complex than the one shown in Example 10-11result 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 of SQL statements, affecting many different modules.

Stored functions can help us minimize this problem, by centralizing the complex code in one program unit, and then deploying that program wherever needed. Example 10-12 shows the result of transferring the logic in the previous query's CASE expression into a stored function.

Example 10-12. Stored function for use in our SQL statement

CREATE FUNCTION cust_status(IN in_status CHAR(1))
 RETURNS VARCHAR(20)
BEGIN
 DECLARE long_status VARCHAR(20);

 IF in_status = 'O' THEN
 SET long_status='Overdue';
 ELSEIF in_status = 'U' THEN
 SET long_status='Up to date';
 ELSEIF in_status = 'N' THEN
 SET long_status='New';
 END IF;

 RETURN(long_status);
END;

We can now use this function in our SQL statement, as shown in Example 10-13.

Example 10-13. Stored function in a SQL statement

SELECT cust_status(customer_status) as Status, count(*) as Count
 FROM customers
 GROUP BY customer_status
 ORDER BY cust_status(customer_status);

Notice that the repetition has been removed and the query is also much more readable, since it is hiding the details of the customer status formula. If and when a programmer needs to understand the logic used to determine customer status, she can open up the stored function and take a look.

10.4.1. Using SQL in Stored Functions

You can include SQL statements inside of stored functions that are themselves used within SQL statements as user-defined functions. However, be careful when doing so, since functions calling SQL inside of SQL statements can lead to unpredictable and often poor performance.

For instance, consider the stored function shown in Example 10-14.

Example 10-14. Stored function to return customer count for a sales rep

CREATE FUNCTION customers_for_rep(in_rep_id INT)
 RETURNS INT
 READS SQL DATA
BEGIN
 DECLARE customer_count INT;

 SELECT COUNT(*)
 INTO customer_count
 FROM customers
 WHERE sales_rep_id=in_rep_id;

 RETURN(customer_count);

END;

This function returns the number of customers assigned to a given sales representative. We might use this function in a stored program when calculating a commission, as shown in Example 10-15.

Example 10-15. Using the sales rep function in a stored program

IF customers_for_rep(in_employee_id) > 0 THEN
 CALL calc_sales_rep_bonus(in_employee_id);
ELSE
 CALL calc_nonrep_bonus(in_employee_id);
END IF;

If this stored function is called for a single employee, then the use of the stored function is probably appropriateit improves the clarity of the business logic, and performance would be no worse than it would be with an embedded SQL statement.

However, consider the case where we want to issue a query listing all the sales representatives with more than 10 customers together with their customer counts. In standard SQL, the query might look like that shown in Example 10-16.

Example 10-16. Standard SQL to retrieve sales reps with more than 10 customers

SELECT employee_id,COUNT(*)
 FROM employees JOIN customers
 ON (employee_id=sales_rep_id)
 GROUP BY employee_id
 HAVING COUNT(*) > 10
 ORDER BY COUNT(*) desc;

Alternately, we can use our stored function, which willapparentlyavoid the join between employees and customers and also avoid a GROUP BY. The stored function version of the query is shown in Example 10-17.

Example 10-17. Function-based query to retrieve sales reps with more than 10 customers

SELECT employee_id,customers_for_rep(employee_id)
 FROM employees
 WHERE customers_for_rep(employee_id)>10
 ORDER BY customers_for_rep(employee_id) desc

Although the stored function solution looks a lot simpler, it actually takes much longer to run than the standard SQL. For every row retrieved from the employees table, the stored function must be called three times (once for the SELECT, once for the WHERE, and once for the ORDER BY). Furthermore, each invocation of the stored function performs a full table scan of the customers tableresulting in three such full scans for each employee row. In contrast, the standard SQL performs just one scan of the customers table and then joins that to the employees table using the primary key (employee_id).

For our sample data, the standard SQL returned the required results almost instantaneously, while the stored function solution took almost half a minute. Figure 10-1 compares the execution times for the two solutions.

Using a stored function inside of a SQL statement that, in turn, contains SQL will not always cause such extreme response time degradation. In general, though, you should think twice about using a function that contains SQL inside of another SQL statement unless the embedded SQL is very efficientsuch as a SQL statement that retrieves data via a quick index lookup.

Figure 10-1. Comparison of performance between standard SQL and SQL using a stored function containing embedded SQL

Be careful using SQL inside of stored functions that are called by other SQL statements. The resulting performance can be very poor unless the stored function is extremely efficient.