我已经创建了一个MySQL函数,如果为参数传递的值无效,则会引发一个错误。在MySQL函数中引发错误的选项有哪些?
发布于 2012-08-06 21:39:45
MySQL 5.5引入了信号,类似于其他语言中的异常:
http://dev.mysql.com/doc/refman/5.5/en/signal.html
例如,在mysql
命令行客户端中:
mysql> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error';
ERROR 1644 (45000): Custom error
发布于 2010-04-23 02:31:03
这实际上是所有三个答案的组合。您调用一个不存在的过程来引发错误,然后声明一个用于捕获您生成的错误的退出处理程序。下面是一个示例,如果要删除的行设置了外键id,则使用SQLSTATE 42000 (过程不存在)在删除前抛出错误:
DROP PROCEDURE IF EXISTS decount_test;
DELIMITER //
CREATE DEFINER = 'root'@'localhost' PROCEDURE decount_test ( p_id bigint )
DETERMINISTIC MODIFIES SQL DATA
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
SELECT 'Invoiced barcodes may not have accounting removed.';
IF (SELECT invoice_id
FROM accounted_barcodes
WHERE id = p_id
) THEN
CALL raise_error;
END IF;
DELETE FROM accounted_barcodes WHERE id = p_id;
END //
DELIMITER ;
输出:
call decount_test(123456);
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
发布于 2009-03-31 23:45:43
在MySQL 5中,您可以通过调用不存在的存储过程(调用raise_error)或向查询传递无效值(如null到非NULL约束字段)来引发错误。下面是Roland Bouman关于在MySQL函数中引发错误的一个有趣的帖子:
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html
https://stackoverflow.com/questions/465727
复制相似问题