MySQL 存储过程错误处理

Question

我相信目前 MySQL 中没有任何东西可以访问 MySQL 存储过程中最后执行的语句的 SQLSTATE。这意味着当在存储过程中引发通用 SQLException 时，很难/不可能得出错误的确切性质。

是否有人有解决方法来导出 MySQL 存储过程中的错误的 SQLSTATE，而不涉及为每个可能的 SQLSTATE 声明处理程序？

例如 - 假设我试图返回一个 error_status，它超出了以下通用的“SQLException 发生在此 BEGIN....END block 中的某处”:

DELIMITER $$

CREATE PROCEDURE `myProcedure`(OUT o_error_status varchar(50))
MY_BLOCK: BEGIN

 DECLARE EXIT handler for 1062 set o_error_status := "Duplicate entry in table";
 DECLARE EXIT handler for 1048 set o_error_status := "Trying to populate a non-null column with null value"; 
-- declare handlers ad nauseum here....

 DECLARE EXIT handler for sqlexception set o_error_status:= "Generic SQLException. You'll just have to figure out the SQLSTATE yourself...." ;

-- Procedure logic that might error to follow here...

END MY_BLOCK$$

有什么建议吗？

PS 我正在运行 MySQL 5.1.49

Answer 1

I believe there is nothing currently available in MySQL that allows access to the SQLSTATE of the last executed statement within a MySQL stored procedure. This means that ... it is hard/impossible to derive the exact nature of the error.

幸运的是，事实并非如此。

SHOW ERRORS LIMIT 1   -- for SQL-state > 2
SHOW WARNINGS LIMIT 1 -- for SQL-state 1,2

将显示最后的错误或警告。

为了避免列出每一个错误，您可以像这样处理一类 SQL 错误:

SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.

NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). An example is shown in Section 12.7.5, “Cursors”. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.

SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.

所以要处理异常，你需要只做:

DECLARE EXIT HANDLER FOR SQLSTATE SQLEXCEPTION .....;

链接:
http://dev.mysql.com/doc/refman/5.5/en/signal.html
http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html