|
Using MySql Stored Functions
by Guy Harrison
MySQL 5 introduced stored procedures, allowing us to write programs that are stored in, and execute in, the database serverr. A stored function is a special type of stored program that returns a value. Unlike procedures, stored functions can be used in expressions wherever you can use a built-in function or inside of SQL statements such as SELECT, UPDATE, DELETE or INSERT.
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 sub-queries or joins, although you need to be careful to avoid possible performance problems that can occur if a function called from an SQL statement itself calls other SQL statements.
Stored functions may not return result sets and may not include dynamic SQL.
Creating Stored Functions
You can create a stored function using the following syntax:
CREATE FUNCTION function_name (parameter_list)
RETURNS datatype
[LANGUAGE SQL]
[ {DETERMINISTIC | NOT DETERMINISTIC} ]
[ {CONTAINS SQL | NO SQL | MODIFIES SQL DATA | READS SQL DATA} ]
[ SQL SECURITY {DEFINER|INVOKER} ]
[ COMMENT comment ]
Function statements
The following clauses are unique to stored functions:
RETURNS
|
The RETURNS clause is mandatory and defines the data type that the function will return. |
RETURN
|
The function body must contain one or more RETURN statements, which terminate function execution and return the specified result to the calling program. We discuss RETURN in detail below. |
These clauses are common to all types of stored programs:
LANGUAGE SQL
|
Indicates that the stored procedure uses the ANSI standard stored procedure language. Since MySQL currently only supports stored procedures written in this language, specifying this keyword is currently unnecessary. However, in future versions, MySQL might support stored procedures written in other languages (Java, for instance), and when this occurs, you may need to specify this keyword.
|
SQL SECURITY
{DEFINER|INVOKER}
|
Determines whether the stored procedure should execute using the permissions of the user who created the stored procedure (DEFINER) or the permissions of the user who is currently executing the stored procedure (INVOKER). The default is DEFINER. |
[NOT] DETERMINISTIC
|
Indicates whether the stored function will always return the same results if the same inputs are provided. For instance, a square root function is deterministic because the square root of a number never changes, while an AGE function is non-deterministic because people are getting older all the time (sigh). By default MySQL will assume a stored procedure or function is NOT DETERMINISTIC. The only time this keyword is important is when binary logging is enabled: you then need to specify either DETERMINISTIC or either NO SQL or READS SQL DATA to create your function. |
NO SQL|CONTAINS
SQL|READS SQL DATA|
MODIFIES SQL DATA
|
Indicates the type of access to database data that the stored function will perform. If a program reads data from the database, you may specify the READS SQL DATA keyword. If the program modifies data in the database, you could specify MODIFIES SQL DATA. If the function performs no database accesses, you may specify NO SQL |
COMMENT comment_string
|
Specifies a comment that is stored in the database along with the procedure definition. You can see these comments in the INFORMATION_SCHEMA.ROUTINES table, in the output of SHOW PROCEDURE/FUNCTION STATUS, and in a SHOW CREATE PROCEDURE/FUNCTION statement.
|
The RETURN Statement
The RETURN statement terminates stored function execution and returns the specified value to the calling program. You can have as many RETURN statements in your stored function as makes sense. Example 1 shows an example of a stored procedure that has multiple RETURN statements.
Example 1: Simple stored function with multiple RETURN statements
CREATE FUNCTION cust_status(in_status CHAR(1))
RETURNS VARCHAR(20)
BEGIN
IF in_status = 'O' THEN
RETURN('Overdue');
ELSEIF in_status = 'U' THEN
RETURN('Up to date');
ELSEIF in_status = 'N' THEN
RETURN('New');
END IF;
END;
However, it’s usually regarded as good practice to include only a single RETURN statement (“one way in and one way out”), and to use variable assignments within conditional statements to change the return value. Aside from arguably resulting in more comprehensible program flow, using a single RETURN statement can avoid the situation in which none of the RETURN statements get executed. “Falling out” of a function, rather than exiting cleanly via a RETURN statement, will cause a runtime error, as shown in Example 2.
Example 2: “Falling out” of a function without executing a RETURN statement
mysql> select cust_status('X');
ERROR 1321 (2F005): FUNCTION cust_status ended without RETURN
Example 3 shows our previous example recoded to include only a single RETURN statement.
Example 3: Simple stored function with single RETURN statement
CREATE FUNCTION cust_status(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;
|
Note: It is good practice to include only a single RETURN statement – as the last line of executable code - in your stored functions. Avoid any flow control that could allow the stored function to terminate without calling a RETURN statement.
|
Parameters to Stored Functions
Stored functions can include multiple parameters, but these may only be IN parameters. That is, you can specify neither the OUT nor INOUT clause when defining your parameters .So, for instance, the function defined in Example 4 will not compile.
Example 4: This function will not compile due to the INOUT clause
CREATE FUNCTION f_inout(INOUT x INT) RETURNS INT
BEGIN
SET x=1;
RETURN(1);
END;
|
Note: Stored functions cannot include OUT or INOUT parameters; if you need to return multiple variables from your stored program then a procedure is possibly more appropriate than a function.
|
SQL Statements in Stored Functions
You can include SQL statements within stored functions, although you should be very careful about including SQL statements in stored functions that might itself be used inside a SQL statement (more on that later).
However, you cannot return a result set from a stored function: trying to create a stored function that contains a SELECT statement without an INTO clause will result in a 1415 error, as shown in Example 5.
Example 5: Stored functions cannot return result sets
mysql> CREATE FUNCTION test_func()
-> RETURNS INT
-> BEGIN
-> SELECT 'Hello World';
-> RETURN 1;
-> END;$$
ERROR 1415 (0A000): Not allowed to return a result set from a function
Calling Stored Functions
A function can be called by specifying its name and parameter list wherever an expression of the appropriate data type may be used. To show how stored functions can be called, we’ll use the simple stored function shown in Example 6.
Example 6: Simple stored function
CREATE FUNCTION isodd(input_number int)
RETURNS int
BEGIN
DECLARE v_isodd INT;
IF MOD(input_number,2)=0 THEN
SET v_isodd=FALSE;
ELSE
SET v_isodd=TRUE;
END IF;
RETURN(v_isodd);
END ;
From the MySQL command line, we can invoke our simple stored function in a number of ways. Example 7 shows how to call the stored function from a SET statement and from a SELECT statement.
Example 7: Calling a stored function from the MySQL command line
mysql> SET @x=isodd(42);
Query OK, 0 rows affected (0.00 sec)
mysql> select @x;
------
| @x |
------
| 0 |
------
1 row in set (0.02 sec)
mysql> SELECT isodd(42)
-> ;
-----------
| isodd(42) |
-----------
| 0 |
-----------
From within a stored procedure, we can invoke the function both within a SET clause and within a variety of flow control statements. Example 8 shows how to call a stored function from within a SET statement, as well as from an IF statement.
Example 8: Calling a stored function from within a stored procedure
SET l_isodd=isodd(aNumber);
IF (isodd(aNumber)) THEN
SELECT CONCAT(aNumber," is odd") as isodd;
ELSE
SELECT CONCAT(aNumber," is even") AS isodd;
END IF;
Programming languages support a variety of methods for calling a stored function. Java and .NET languages (VB.NET and C#) provide methods to call stored functions directly. However, in many of the dynamic languages (PHP, Perl, Python) there is no API for directly accessing a stored function.
If a language does not support a method for directly calling a stored function, you should embed the call in a SELECT statement without a FROM clause and retrieve the function result from the subsequent result set. For instance, in PHP with the mysqli interface, we can retrieve a stored function result as shown in Example 9.
Example 9: Calling a stored function from PHP
$stmt=$my_db->prepare("SELECT isodd(?)") or die($my_db->error);
$stmt->bind_param('i',$aNumber) or die($stmt->error);
$stmt->execute() or die($stmt->error);
$stmt->bind_result($isodd);
$stmt->fetch();
if ($isodd == 1 )
printf("%d is an odd number\n",$aNumber);
else
printf("%d is an even number\n",$aNumber);
Some languages specifically support calling stored functions. For instance, Java JDBC allows a stored function to be called directly, as shown in Example 10.
Example 10: JDBC support for stored functions
CallableStatement PreparedFunc =
MyConnect.prepareCall("{ ? = call isodd( ? ) }");
PreparedFunc.registerOutParameter(1, Types.INTEGER);
PreparedFunc.setInt(1, aNumber);
PreparedFunc.execute();
if (PreparedFunc.getInt(1) == 1)
System.out.println(aNumber " is odd");
else
System.out.println(aNumber " is even");
Using Stored Functions in SQL
So far, we’ve 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 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 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 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 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 12 shows the result of transferring the logic in the previous query's CASE expression into a stored function.
Example 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 13.
Example 13: Stored function in an 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 or he can open up the stored function and take a look.
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 14.
Example 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 procedure when calculating a commission, as shown in Example 15.
Example 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 appropriate – it improves the clarity of the business logic, and performance would be no worse than it would be with an embedded SQL.
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 16.
Example 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 will – apparently – avoid the join between employees and customers and also avoid a GROUP BY. The stored function version of the query is shown in Example 17.
Example 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 tables – resulting 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 1 compares the execution times for the two solutions.
Figure 1: Comparison of performance between standard SQL and SQL using a stored function containing embedded SQL
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 efficient – such as a SQL statement that retrieves data via a quick index lookup.
|
Note: 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.
|
Conclusion
A stored function is a special type of stored procedure 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 functions inside SQL statements if those functions embed SQL statements. Functions that include SQL can often perform badly when included within standard SQL statements.
Guy Harrison is a chief architect for database solutions at Quest Software, and is a recognized expert with almost 20 years experience in application and database administration, performance tuning and software development. Guy is the author of Oracle SQL High Performance Tuning (Prentice Hall), Oracle Desk Reference (Prentice Hall) and MySQL Stored Procedure Programming (O’Reilly with Steven Feuerstein), and is a regular speaker at trade shows and events. He is chief architect of Quest's Spotlight® family of diagnostic products as well as contributing to the design and development of many other Quest products. Guy’s articles on Oracle contention can also be found at Toad World.
|