MySQL 5 Stored Procedures, Views, and Triggers

mysql> SELECT
-> w.name as “List”,
-> ws.stock as “Stock”,
-> ws.quantity_owned as “Qty”,
-> sph.price as “Cur Price”,
-> (ws.quantity_owned * sph.price) as “Cur Value”,
-> ROUND((ws.quantity_owned
-> / TotalWatchlist.total_shares) * 100, 2) as “% List”
-> FROM Watchlist w
-> INNER JOIN WatchlistStock ws
-> ON w.watchlist_id = ws.watchlist
-> INNER JOIN (
-> SELECT stock, MAX(time_taken) as last_time
-> FROM StockPriceHistory
-> GROUP BY stock
-> ) as CurrentStockTime
-> ON ws.stock = CurrentStockTime.stock
-> INNER JOIN StockPriceHistory sph
-> ON sph.stock = CurrentStockTime.stock
-> AND sph.time_taken = CurrentStockTime.last_time
-> INNER JOIN (
-> SELECT watchlist, SUM(quantity_owned) as total_shares
-> FROM WatchlistStock
-> GROUP BY watchlist
-> ) as TotalWatchlist
-> ON w.watchlist_id = TotalWatchlist.watchlist
-> ORDER BY “List”, “Stock”, “% List” DESC;
+——————–+——-+—–+———–+————+——–+
| List | Stock | Qty | Cur Price | Cur Value | % List |
+——————–+——-+—–+———–+————+——–+
| List #1 (Mix) | AMD | 50 | 24.0000 | 1200.0000 | 20.00 |
| List #1 (Mix) | DELL | 50 | 32.5900 | 1629.5000 | 20.00 |
| List #1 (Mix) | INTC | 50 | 24.0700 | 1203.5000 | 20.00 |
| List #1 (Mix) | MSFT | 50 | 24.6700 | 1233.5000 | 20.00 |
| List #1 (Mix) | RHAT | 50 | 20.4800 | 1024.0000 | 20.00 |
| List #2 (Hvy Chip) | AMD | 300 | 24.0000 | 7200.0000 | 37.50 |
| List #2 (Hvy Chip) | INTC | 450 | 24.0700 | 10831.5000 | 56.25 |
| List #2 (Hvy Chip) | RHAT | 50 | 20.4800 | 1024.0000 | 6.25 |
+——————–+——-+—–+———–+————+——–+
8 rows in set (0.00 sec)

The SELECT statement in Figure One uses a series of JOIN ed regular and derived tables (a special kind of subquery useful in aggregation) to produce the output.

Clearly, executing this SQL statement from the command line (or even cluttering the application code with this size of statement) is cumbersome. Until MySQL 5, however, there was little a developer could do to encapsulate this kind of complex statement. But, with MySQL 5, you have two options for encapsulating this statement into a cleaner container. The first method, using a stored procedure, has already been explained, and would be a fine option for separating this code into a database-level routine.

A second option would be to use a new MySQL 5 view. A view is simply a SELECT statement that can be used like a regular table, but that need not be defined as a separate table.

A major disadvantage of stored procedures is that they cannot be “embedded” in other SELECT statements, meaning code such as the following is illegal:

SELECT * FROM MyStoredProcedure();

A view, on the other hand, can be defined to represent a selection of various fields from various tables, and then used as a “virtual table” in other SELECT statements in a format such as:

SELECT * FROM MyView;

The basics of creating a view in MySQL 5 is simple: prepend CREATE VIEW before the SELECT statement that should populate the view. A simple view might be created with:

CREATE VIEW MyView AS SELECT
field1, field2 FROM MyTable;

(Some caveats apply, as you’ll see in a moment.) Once created, you can SELECT from the view as normal:

SELECT * FROM MyView

(Internally, MySQL replaces the MyView with the SELECT statement defined by CREATE VIEW.)

To return to the stock application, you want to encapsulate the complex SQL statement in Listing Seven into a simple, easy to call, view. However, simply adding CREATE VIEW causes an error, because MySQL has some restrictions on what kind of SELECT statements can be defined as a view.

The most important restriction is that views cannot contain SELECT statements that involve derived tables. While that may seem odd, this restriction actually encourages componentization of your SQL statements. To define a view containing all the SQL code in Listing Seven, you must first define separate views representing the derived tables. In this way, MySQL actually forces you to create smaller, more re-usable pieces of code.

Listing Seven shows the three views created from the original SQL code in Figure One.