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

mysql> CALL us_population();
+-----------------------+
| Total U.S. Population |
+-----------------------+
| 308143815 |
+-----------------------+
mysql> SELECT SUM(pop) AS 'Total U.S. Population' FROM states;
+-----------------------+
| Total U.S. Population |
+-----------------------+
| 308143815 |
+-----------------------+
NOT FOUND handlers are also useful for checking whether SELECT … INTO var_name
statements return any results. Recipe 9.2 shows an example.
Catching and Ignoring Errors
If you consider an error benign, you can use a handler to ignore it. For example, many
DROP statements in MySQL have an IF EXISTS clause to suppress errors if objects to be
dropped do not exist. But some DROP statements have no such clause and thus no way
to suppress errors. DROP USER is one of these:
mysql> DROP USER 'bad-user'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'bad-user'@'localhost'
To prevent errors from occurring for nonexistent users, invoke DROP USER within a stored
procedure that catches code 1396 and ignores it:
CREATE PROCEDURE drop_user ( user TEXT , host TEXT )
BEGIN
DECLARE account TEXT ;
DECLARE CONTINUE HANDLER FOR 1396
SELECT CONCAT ( 'Unknown user: ' , account ) AS Message ;
SET account = CONCAT ( QUOTE ( user ), '@' , QUOTE ( host ));
CALL exec_stmt ( CONCAT ( 'DROP USER ' , account ));
END ;
If the user does not exist, drop_user() writes a message within the condition handler,
but no error occurs:
mysql> CALL drop_user('bad-user','localhost');
+--------------------------------------+
| Message |
+--------------------------------------+
| Unknown user: 'bad-user'@'localhost' |
+--------------------------------------+
To ignore the error completely, write the handler using an empty BEGIN … END block:
DECLARE CONTINUE HANDLER FOR 1396 BEGIN END ;
Another approach is to generate a warning, as demonstrated in the next section.