Using Stored Routines, Triggers, and Scheduled Events - MySQL - page 327

CREATE EVENT mark_expire
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM mark_log WHERE ts < NOW () - INTERVAL 2 DAY ;
If you adopt this strategy, you have cooperating events: one event that adds rows to
the mark_log table, and another that removes them. They act together to maintain
a log that contains recent rows but does not become too large.
9.9. Writing Helper Routines for Executing Dynamic SQL
Problem
Prepared SQL statements enable you to construct and execute SQL statements on the
fly, but the supporting mechanism can be tedious to use.
Solution
Write a helper procedure that handles the drudgery.
Discussion
Using a prepared SQL statement involves three steps: preparation, execution, and deal‐
location. For example, if the @tbl_name and @val variables hold a table name and a value
to insert into the table, you can create the table and insert the value like this:
SET @ stmt = CONCAT ( 'CREATE TABLE ' , @ tbl_name , ' (i INT)' );
PREPARE stmt FROM @ stmt ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
SET @ stmt = CONCAT ( 'INSERT INTO ' , @ tbl_name , ' (i) VALUES(' , @ val , ')' );
PREPARE stmt FROM @ stmt ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
To ease the burden of going through those steps for each dynamically created statement,
use a helper routine that, given a statement string, prepares, executes, and deallocates
it:
CREATE PROCEDURE exec_stmt ( stmt_str TEXT )
BEGIN
SET @ _stmt_str = stmt_str ;
PREPARE stmt FROM @ _stmt_str ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
END ;
The exec_stmt() routine enables the same statements to be executed much more
simply: