raise error mysql引发异常

一、MySQL 5.5

mysql> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error';
ERROR 1644 (45000): Custom error

 

二、MySQL 早期版本

DROP PROCEDURE IF EXISTS raise_application_error;
DROP PROCEDURE IF EXISTS get_last_custom_error;
DROP TABLE IF EXISTS RAISE_ERROR;

DELIMITER $$
CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR(F1 INT NOT NULL);

  SELECT CODE, MESSAGE INTO @error_code, @error_message;
  INSERT INTO RAISE_ERROR VALUES(NULL);
END;
$$

CREATE PROCEDURE get_last_custom_error() SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  SELECT @error_code, @error_message;
END;
$$
DELIMITER ; 

使用如下:
CALL raise_application_error(1234, 'Custom message');
CALL get_last_custom_error();

例子:
DROP TABLE IF EXISTS ex1;
DROP TRIGGER IF EXISTS ex1_bi;
DROP TRIGGER IF EXISTS ex1_bu;

CREATE TABLE ex1(only_odd_numbers INT UNSIGNED);

DELIMITER $$

CREATE TRIGGER ex1_bi BEFORE INSERT ON ex1 FOR EACH ROW
BEGIN 
  IF NEW.only_odd_numbers%2 != 0 THEN
    CALL raise_application_error(3001, 'Not odd number!');
  END IF;
END
$$

CREATE TRIGGER ex1_bu BEFORE UPDATE ON ex1 FOR EACH ROW
BEGIN 
  IF NEW.only_odd_numbers%2 != 0 THEN
    CALL raise_application_error(3001, 'Not odd number!');
  END IF;
END
$$

DELIMITER ;


使用:
mysql> INSERT INTO ex1 VALUES(2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ex1 VALUES(3);
ERROR 1048 (23000): Column 'F1' cannot be null
mysql> CALL get_last_custom_error();
+-------------+-----------------+
| @error_code | @error_message  |
+-------------+-----------------+
| 3001        | Not odd number! | 
+-------------+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT \* FROM ex1;
+------------------+
| only_odd_numbers |
+------------------+
|                2 | 
+------------------+
1 row in set (0.00 sec) 


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值