MySQL 存储过程错误处理

问题描述

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

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 when a generic SQLException is raised within a stored procedure it is hard/impossible to derive the exact nature of the error.

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

Does anybody have a workaround for deriving the SQLSTATE of an error in a MySQL stored procedure that does not involve declaring a handler for every possible SQLSTATE?

例如 - 假设我试图返回一个 error_status ，它超出了下面的通用SQLException发生在这个 BEGIN....END 块中的某处":

For example - imagine that I am trying to return an error_status that goes beyond the generic "SQLException happened somewhere in this BEGIN....END block" in the following:

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

PS I am running MySQL 5.1.49

推荐答案

GET DIAGNOSTICS 在 5.6.4 中可用

GET DIAGNOSTICS is available in 5.6.4

见http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

这篇关于MySQL 存储过程错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！