||
如何将存储过程执行错误记录到表中以便调试?
简单的方法是创建一个表来存储存储过程错误,如下所示
1.创建日志表以捕获程序错误:
<code >mysql> create table procedure_log( id serial, procedure_Name varchar(64), connection_Id int(11), user_Executed varchar(100), returned_Error text, date_Occurred timestamp ) ENGINE=INNODB; Query OK, 0 rows affected (0.18 sec) mysql>CREATE TABLE `andy` ( `id` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2.编写一个示例存储过程,在遇到SQLEXCEPTION时将错误插入到表中:
<code >mysql> DELIMITER $$ DROP PROCEDURE IF EXISTS test.test_procedure $$ CREATE PROCEDURE test_procedure(tablename varchar(25)) BEGIN /* This is the handler part to capture the exception and log it into a table. */ DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT; SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text); INSERT INTO test.procedure_log(procedure_Name, connection_Id, user_Executed, returned_Error, date_Occurred) SELECT 'test_procedure', CONNECTION_ID(), CURRENT_USER(), @full_error, NOW(); SELECT "Stored procedure failed with errors" as Errmsg; /* This error will be returned to application */ END; /* This part will just select the data from the tablename that is provided as a input for this procedure */ SET @test=CONCAT('Select * from ',tablename,' ;'); PREPARE STMT FROM @test; EXECUTE STMT; END $$ Query OK, 0 rows affected (0.00 sec) mysql> mysql> DELIMITER ;
3.使用当前数据库中存在的表名调用过程:
<code >mysql> call test_procedure('andy'); +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
这只是从现有的表中选择行并打印出来。它不打印procedure_log表中的任何条目,因为该过程没有引发异常。
<code >mysql> select * from procedure_log; Empty set (0.01 sec)
4.使用不存在的表名调用过程:
<code >mysql> call test_procedure('t1'); +---------------------------------------+ | Errmsg | +---------------------------------------+ | Stored procedure failed with errors | +---------------------------------------+ 1 row in set (0.03 sec) Query OK, 0 rows affected (0.03 sec)
选择程序日志表中的条目时,它包含以下内容:
<code >(Tue Sep 12 11:21:14 2023)[root@GreatSQL][test]>select * from procedure_log; +----+----------------+---------------+---------------+---------------------------------------------------+---------------------+ | id | procedure_Name | connection_Id | user_Executed | returned_Error | date_Occurred | +----+----------------+---------------+---------------+---------------------------------------------------+---------------------+ | 1 | test_procedure | 1976419 | root@% | ERROR 1146 (42S02): Table 'test.t1' doesn't exist | 2023-09-12 11:19:42 | +----+----------------+---------------+---------------+---------------------------------------------------+---------------------+ 1 row in set (0.00 sec)
除了测试用例中提到的SQLEXCEPTION之外,还有处理SQL语句返回的SQLWARNINGS和其他消息的选项。
合作电话:010-64087828
社区邮箱:greatsql@greatdb.com