如何将存储过程执行错误记录到表中以便调试？
简单的方法是创建一个表来存储存储过程错误，如下所示
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和其他消息的选项。
