如何通過準備/執行在儲存過程中使用 MySQL 錯誤處理程序

我有一個這樣定義的過程：

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE CONTINUE HANDLER FOR 1064
select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
   table_schema=db and table_name like pattern and
   create_time <= @owa;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end$$

這個想法是刪除與特定模式匹配的表，這些表具有 create_time <= 指定日期。

執行時，我得到一個錯誤：

mysql> call drop_tables_like(7, 'monkey%', 'daweb2012_civicrm');
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to     EXECUTE
mysql>

如何在使用預準備語句的過程中聲明異常處理程序？

EXECUTE問題是，當您嘗試DYNAMIC查詢NULL時，查詢會在未找到行時返回。

測試：

mysql> CALL drop_tables_like(
   -> '6', #myint INT, 
   -> 'onare', #pattern VARCHAR(255), 
   -> 'PRUEBA'); #db VARCHAR(255))
+--------+
| sqlSTR |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

因此，首先嘗試COUNT驗證您的模式是否存在任何行：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `drop_tables_like`(
myint INT, 
pattern VARCHAR(255), 
db VARCHAR(255))
BEGIN
DECLARE CONTINUE HANDLER FOR 1064
SELECT (now() - interval  myint day) into @owa;
SET group_concat_max_len = 65535;
# [DINAMYC QUERY]
SET @TotalRows=(select COUNT(*)
from information_schema.tables where table_schema=db and table_name like pattern and create_time <= @owa);
IF (@TotalRows>0) THEN
   SELECT concat('drop table ', group_concat(table_name)) into @str_sql
   FROM information_schema.tables
   WHERE
       table_schema=db and table_name like pattern and
       create_time <= @owa;
   PREPARE stmt FROM @str_sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
ELSE
   SELECT 'There\'s not rows to update.' as sDESC_ERROR;
END IF;
END$$

如果沒有要更新的行的結果：

mysql> CALL drop_tables_like( '6',  'onare',  'PRUEBA');
+-----------------------------+
| sDESC_ERROR                 |
+-----------------------------+
| There's not rows to update. |
+-----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

引用自：https://dba.stackexchange.com/questions/105972