dcsimg

MySQL 5 Stored Functions and Cursors

Take a hands-on tour of MySQL 5’s new stored functions and cursors in MySQL 5.

As part of Linux Magazine’s coverage of the release of MySQL 5, developers Michael Kruckenberg and Jay Pipes continue their survey of the software’s many new features. December 2005’s feature story on MySQL (available online at http://www.linux-mag.com/2005-12/mysql.html) laid the foundation of a stock tracking application, using stored procedures, views, and triggers. This feature, the second of three, expands the applications’s capabilities using MySQL 5’s stored functions and cursors.

December’s “MySQL 5 Stored Procedures, Views, and Triggers” implemented a stored procedure to encapsulate inserts into a stock tracking table, created a view to generate a stock report, and added a trigger to calculate stock price history averages over time.
If you followed along with that article, you can use the tables and data you created previously. Otherwise, use Listing One to set up the database schema and data records necessary to get going.[ You can download the SQL scripts and code used throughout this article from http://www.linux-mag.com/downloads/2006-02/mysql5/source.tgz.]
LISTING ONE: A database schema and data for mining stock price history data

DROP DATABASE IF EXISTS stock_app;
CREATE DATABASE stock_app;

USE stock_app;

CREATE TABLE IF NOT EXISTS StockPriceHistory
(
stock CHAR(5) NOT NULL,
time_taken DATETIME NOT NULL,
price DECIMAL(9, 4) NOT NULL,
PRIMARY KEY pk_stock_price_history (stock, time_taken)
);

INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 100 minute), ’23.70’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 90 minute), ’23.75’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 80 minute), ’23.76’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 70 minute), ’23.73’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 60 minute), ’23.74’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 50 minute), ’23.79’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 40 minute), ’23.81’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 30 minute), ’23.83’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 20 minute), ’23.84’);
INSERT INTO StockPriceHistory VALUES (’AMD’, date_sub(now(), interval 10 minute), ’23.82’);

Introduction to Stored Functions

MySQL 5 includes support for stored functions, or sets of SQL statements that encapsulate small operations. Unlike a stored procedure, which gets executed with an explicit CALL statement, a stored function is used within a SQL statement in the same way that built-in MySQL functions are used.
For example, the built-in MySQL length() function calculates the length of a string:
SELECT length(body) FROM document;
If length() isn’t suited to your needs, you can create and use your own mylength():
SELECT mylength(body) FROM document;
Stored functions are built from SQL statements and are stored in a special table named mysql.proc within a database’s namespace. A stored function is defined using the CREATE FUNCTION statement:
CREATE FUNCTION function_name
name ( [func_parameter[, … ]] )
RETURNS type
[characteristic … ] routine_body
CREATE FUNCTION requires a function name and the parentheses, but doesn’t require any input parameters. By default, a stored function is created in the current database; to create it in another database, prepend the name of the database to the function name, as in CREATE FUNCTION database.function_name. Unlike stored procedures, which can return nothing or a large set of data, stored functions must have a RETURN statement and can only return one value. routine_body is a set of SQL statements to produce the desired return value. (The “MySQL Manual” chapter on stored procedures and functions can be found at http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html.)
To demonstrate a stored function, suppose you start with the data shown in Figure One, a sample set of data from a table that keeps track of stock prices over time.
Figure One: Data in the StockPriceHistory table
mysql> SELECT stock, price, time_taken FROM 
-> StockPriceHistory WHERE stock = ’AMD’;
+-------+---------------------+---------+
| stock | time_taken | price |
+-------+---------------------+---------+
| AMD | 2005-12-06 16:17:41 | 23.7000 |
| AMD | 2005-12-06 16:27:41 | 23.7500 |
| AMD | 2005-12-06 16:37:41 | 23.7600 |
| AMD | 2005-12-06 16:47:41 | 23.7300 |
| AMD | 2005-12-06 16:57:41 | 23.7400 |
| AMD | 2005-12-06 17:07:41 | 23.7900 |
| AMD | 2005-12-06 17:17:41 | 23.8100 |
| AMD | 2005-12-06 17:27:41 | 23.8300 |
| AMD | 2005-12-06 17:37:41 | 23.8400 |
| AMD | 2005-12-06 17:47:41 | 23.8200 |
+-------+---------------------+---------+
10 rows in set (0.00 sec)
As you can see, over the course of the past hour, the stock has peaked and dropped. Suppose that you’ve been asked to provide a quick way to yield the change in the last hour for a given stock. You’d like to easily get at this information in a query to potentially use it to insert point-in-time data into another table. Using a stored function, you can easily encapsulate this requirement.
Listing One shows how to create the hourly_stock_change() function which finds the correct records and returns the price difference between them.
Listing One: Creating the hourly_stock_change() function
01USE stock_app;
02
03DROP FUNCTION IF
04 EXISTS hourly_stock_change;
05
06DELIMITER //
07CREATE FUNCTION hourly_stock_change (stock_name CHAR(5))
08 RETURNS DECIMAL(9,4)
09 SQL SECURITY DEFINER
10
11BEGIN
12
13DECLARE price_then DECIMAL(9,4);
14DECLARE price_now DECIMAL(9,4);
15
16SELECT price INTO price_then
17 FROM StockPriceHistory
18 WHERE stock = stock_name
19 AND unix_timestamp(now()) – unix_timestamp(time_taken) > 3600
20 ORDER BY time_taken DESC LIMIT 1;
21
22SELECT price INTO price_now
23 FROM StockPriceHistory
24 WHERE stock = stock_name
25 ORDER BY time_taken DESC LIMIT 1;
26
27RETURN price_now – price_then;
28
29END
Listing One starts by issuing the DROP FUNCTION IF EXISTS command to remove the hourly_stock_change() function if it exists. Next, the DELIMITER statement on Line 5 sets the delimiter to // to allow the semi-colon to be used within the function.
The definition of the function begins at Line 6, starting with the CREATE FUNCTION statement. It includes the function name, hourly_stock_change, and a stock_name parameter, typed to accept a string of up to five characters. Line 7 indicates that the function returns a DECIMAL(10,2). On Line 8, the SQL SECURITY is set to DEFINER, which means that when this function is executed, it uses the permissions of the user that defined the function, not the user that’s executing the query. (See the sidebar “Stored Function Privileges” for more information about new access rights defined in MySQL 5.)
The body of the function starts at Line 10 with BEGIN. (BEGIN is not required if your body contains only one SQL statement.) Lines 10-11 define two variables, price_then and price_now, to store the stock price of an hour ago and the current stock price, respectively.
The function then gets the price of the stock from one hour ago, grabbing the first record that is more than an hour old. You’ll notice that the SELECT statement uses the keyword INTO (on Line 15) to put the value retrieved from the database into price_then. After getting the price from one hour ago, the function gets the curent price, and in Line 26, returns the difference between the two.
Once this function is created you can use it in a SELECT statement to easily find the amount of change that’s occurred in the past hour:
mysql> SELECT hourly_stock_change(’AMD’);
+----------------------------+
| hourly_stock_change(’AMD’) |
+----------------------------+
| 0.0800 |
+----------------------------+
1 row in set (0.01 sec)
The value returned from the function is the difference between the most current stock price and the price that was closest to, but not less than one hour ago. If the stock price has dropped lower than the price from one hour ago, the function will return a negative value.
If you want to store the hourly stock change, you can create a table with stock and hourly_change fields and use the function to generate the value directly in a INSERT statement, such as:
mysql> INSERT INTO hourly_stock_change SET stock=’AMD’,   
-> hourly_change=hourly_stock_change(’AMD’);
Functions must be called with the expected number of arguments or MySQL returns an error:
mysql> SELECT hourly_stock_change();
ERROR 1318 (42000): Incorrect number of arguments for
FUNCTION stock_app.hourly_stock_change; expected 1, got 0
This small example provides just the slightest hint of the possibilities available with stored functions. There are a number of things that could be done to customize the hourly_stock_change() function further. Maybe you’d rather have a stock_change() function that takes in a time as a parameter and returns the change since that time. The ability to create stored functions with SQL in the client puts you in power to organize, optimize, and secure your applications and databases.

Database Cursors

So far, you’ve built routines that grab individual pieces of data, store the data n local variables, and use those variables to generate output. The hourly_stock_exchange() demonstrates how to compare single pieces of data, but how you would tackle a routine that requires looking at many rows of data? If you want to look at an entire set of records within your routine and make decisions or create new data based on some or all of those records, you need to use a cursor.
Cursors are new to MySQL in version 5. A cursor operates within a stored procedure, stored function, or trigger, and provides a mechanism for retrieving a set of records to work with in the routine. (“Routine” is an umbrella term that describes both stored procedures and stored functions.) MySQL’s cursors are server-side, cursors, where a server-side cursor can only be used within routines on the server, and cannot be passed back to the client for use in the calling program.)
In MySQL, the cursor can only be moved forward, is read-only, and can only move one record at a time (controlled by the FETCH statement). MySQL’s cursors can only read the data in the record; writing is not allowed. The cursor is also asensitive, meaning that it points at the real data. Hence, the data being used by the cursor may be changed by another process while the cursor is moving through the records. (The converse is an insensitive cursor, which takes a temporary copy of the data to be used by the cursor.)
A cursor is defined with the DECLARE… CURSOR FOR SELECT… statement. The SELECT part of the definition is a typical SQL query that extracts data from one or more tables. When the routine is ready to start working with the records, an OPEN is issued. At that point, the query is executed and the cursor points at the recordset, ready for you to begin FETCH- ing records. A FETCH statement gives you the current record and moves the cursor to the next record to await further instructions.
To illustrate use of a cursor, let’s build on the previous example. Suppose that you want to provide another piece of summary data from the StockPriceHistory table. This time, you want to allow a user to quickly see how much a stock has fluxuated during the day, showing a summary of the absolute value of increases and decreases in price throughout the history of the stock. This may be useful to determining how much a stock jumps up and down during a period of time.
You can work with the same set of data used in the first example shown in Figure One. Listing Two creates a price_fluxuation() function that uses a cursor to iterate over all the changes in the day and tally up the absolute value of each fluxuation.
Listing Two: Creating the stock_fluxuation() function
01USE stock_app;
02
03DROP FUNCTION IF EXISTS price_fluxuation;
04
05DELIMITER //
06
07CREATE FUNCTION price_fluxuation (stock_name CHAR(5))
08RETURNS DECIMAL(9,4)
09SQL SECURITY DEFINER
10
11BEGIN
12
13DECLARE curr_price DECIMAL(9,4) DEFAULT 0;
14DECLARE last_price DECIMAL(9,4) DEFAULT 0;
15DECLARE price_change DECIMAL(9,4) DEFAULT 0;
16DECLARE finished INT(1) DEFAULT 0;
17DECLARE price_cur CURSOR FOR
18 SELECT price FROM StockPriceHistory
19 WHERE stock = stock_name
20 ORDER BY time_taken;
21DECLARE CONTINUE HANDLER
22 FOR NOT FOUND SET finished = 1;
23
24OPEN price_cur;
25
26calc_change: LOOP
27FETCH price_cur INTO curr_price;
28IF finished THEN
29 LEAVE calc_change;
30END IF;
31
32IF last_price THEN
33 SET price_change = price_change + abs(curr_price – last_price);
34 SET last_price = curr_price;
35ELSE
36 SET last_price = curr_price;
37END IF;
38
39END LOOP calc_change;
40
41CLOSE price_cur;
42
43RETURN price_change;
44
45END;
46
47//
48
49DELIMITER ;
Since you’ve already created a stored function in the first example, much of the price_fluxuation() function should be familiar. Listing Two starts with DROP IF EXISTS and DELIMITER statements and then moves to the CREATE FUNCTION statement that includes the same input parameter and return value as the first example.
The cursor portion of the function starts on Line 16 with a finished variable used to indicate when the cursor has reached the end of the records. Line 17 defines the price_cur cursor with a SELECT statement that gets all records in the StockPriceHistory table that match the stock_name. The last declaration on Line 21 is a HANDLER that watches for a condition and reacts when that condition is raised. In this example, the condition is when the cursor is moved to the next row but there is no next row, which happens after the cursor has reached the last record returned from the defining query. When the condition is raised, the finished variable is set to 1 (which is checked after every FETCH).
The cursor is put into action with the OPEN statement on Line 22. The calc_loop provides the flow control for looping through the records. On Line 23, a FETCH statement assigns the value in the currently active record to curr_price. This is followed immediately by a check of the finished variable on Line 24 to determine if there was no record to FETCH.
If the finished variable is set to 1, the loop ends immediately. In this example, the query that defines the cursor retrieves only one column, the price. However, you can put more than one field in the cursor’s SELECT statement, but the number of fields must match a comma-separated list of variables to SELECT into.
For instance, if you wanted to add the stock column to the query on Line 18 you’d add a curr_stock variable and your FETCH would look something like this:
FETCH price_cur 
INTO curr_price, curr_stock;
Lines 28-33 contain a bit of logic to keep track of the absolute change in the stock from record to record. After each record is processed and the finished flag is set to exit the loop, the cursor is closed using CLOSE.
Using this stored function is as simple as:
mysql> SELECT price_fluxuation(’AMD’);
+-------------------------+
| price_fluxuation(’AMD’) |
+-------------------------+
| 0.2200 |
+-------------------------+
1 row in set (0.00 sec)
As with the first example, there is ample room for improvement and expansion. For instance, perhaps you’d like to add another parameter to the function to give the user control over the date range of records used in the calculation.

Viewing Existing Functions

Once you’ve started using stored functions in your database, you’ll likely want to audit what’s defined in your database. To get a quick view, MySQL provides a SHOW CREATE FUNCTION command. Using the SHOW command requires that you know the function database and name.
Another place to get information about routines in your database are the INFORMATION_SCHEMA tables. A quick list of function names and the associated databases can be found in the ROUTINES table, as show in Figure Two.
Figure Two: Use INFORMATION_SCHEMA to see existing functions
mysql> SELECT SPECIFIC_NAME, ROUTINE_SCHEMA
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ’FUNCTION’;
+---------------------+----------------+
| SPECIFIC_NAME | ROUTINE_SCHEMA |
+---------------------+----------------+
| hourly_stock_change | stock_app |
| price_fluxuation | stock_app |
+---------------------+----------------+
2 rows in set (0.00 sec)
The amount of information available from the INFORMATION_SCHEMA is staggering. That’ll be the focus of the final article in this series.

An Ambitious Database

Stored functions and cursors are another example of MySQL’s growing enterprise strength. Add these two features to previously covered stored procedures, views, and triggers and it becomes clear why MySQL AB promotes version 5 as the most ambitous release in the history of MySQL.
It’s important to remember that while MySQL 5’s new features are being welcomed by many, that doesn’t mean the new release is always the right answer for your particular application or database problem. Before jumping into the excitement of these enterprise features you should carefully consider how they fit with your requirements. But if the new features are a good match for your database needs, you’re in good hands.
The next Linux Magazine installment in this series will focus solely on the wealth of information stashed in the INFORMATION_SCHEMA. Stay tuned.

Michael Kruckenberg and Jay Pipes are co-authors of Pro MySQL, recently published by Apress (http://www.apress.com/). Written for both current and potential MySQL users, Pro MySQL offers an exclusive in-depth look at MySQL 5, including design, development, source code, architecture, and administration.

Comments are closed.