1. 异常处理器类型
– CONTINUE: 继续执行后续语句
– EXIT: 退出存储过程
– UNDO: 回滚事务（仅限事务块内）

2. 异常条件
– SQLSTATE值
– MySQL错误代码
– 命名条件
– SQLWARNING
– NOT FOUND
– SQLEXCEPTION

1. 基本语法
DECLARE handler_type HANDLER FOR condition_value
statement;

2. 处理器类型
– CONTINUE: 继续执行
– EXIT: 退出存储过程

3. 条件值
– SQLSTATE ‘45000’
– MySQL错误代码如1062
– 命名条件
– SQLWARNING
– NOT FOUND
– SQLEXCEPTION

1. 处理重复键错误
DELIMITER //

CREATE PROCEDURE insert_user_safe(
IN user_name VARCHAR(64),
IN email VARCHAR(128),
OUT result VARCHAR(100)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE ‘23000’
BEGIN
SET result = ‘插入失败：用户名或邮箱已存在’;
ROLLBACK;
END;

START TRANSACTION;

INSERT INTO users (user_name, email, status, created_at)
VALUES (user_name, email, 1, NOW());

COMMIT;

SET result = ‘用户创建成功’;
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 调用存储过程
CALL insert_user_safe(‘new_user’, ‘new@test.com’, @result);
SELECT @result;

输出示例：
Query OK, 0 rows affected (0.01 sec)

+——————-+
| @result |
+——————-+
| 用户创建成功 |
+——————-+
1 row in set (0.00 sec)

3. 测试重复插入
CALL insert_user_safe(‘user001’, ‘user001@test.com’, @result);
SELECT @result;

输出示例：
Query OK, 0 rows affected (0.00 sec)

+———————————+
| @result |
+———————————+
| 插入失败：用户名或邮箱已存在 |
+———————————+
1 row in set (0.00 sec)

1. 处理外键约束错误
DELIMITER //

CREATE PROCEDURE delete_order_safe(
IN order_id INT,
OUT result VARCHAR(100)
)
BEGIN
DECLARE EXIT HANDLER FOR 1451
BEGIN
SET result = ‘删除失败：订单存在关联数据’;
ROLLBACK;
END;

DECLARE EXIT HANDLER FOR 1452
BEGIN
SET result = ‘删除失败：外键约束错误’;
ROLLBACK;
END;

START TRANSACTION;

DELETE FROM orders WHERE id = order_id;

COMMIT;

SET result = ‘订单删除成功’;
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 调用存储过程
CALL delete_order_safe(100, @result);
SELECT @result;

输出示例：
Query OK, 0 rows affected (0.00 sec)

+——————-+
| @result |
+——————-+
| 订单删除成功 |
+——————-+
1 row in set (0.00 sec)

3. 测试外键约束
CALL delete_order_safe(1, @result);
SELECT @result;

输出示例：
Query OK, 0 rows affected (0.00 sec)

+———————————+
| @result |
+———————————+
| 删除失败：订单存在关联数据 |
+———————————+
1 row in set (0.00 sec)

1. 定义命名条件
DELIMITER //

CREATE PROCEDURE transfer_money(
IN from_account INT,
IN to_account INT,
IN amount DECIMAL(10,2),
OUT result VARCHAR(100)
)
BEGIN
DECLARE insufficient_funds CONDITION FOR 1644;
DECLARE account_not_found CONDITION FOR SQLSTATE ‘02000’;

DECLARE EXIT HANDLER FOR insufficient_funds
BEGIN
SET result = ‘转账失败：余额不足’;
ROLLBACK;
END;

DECLARE EXIT HANDLER FOR account_not_found
BEGIN
SET result = ‘转账失败：账户不存在’;
ROLLBACK;
END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET result = ‘转账失败：系统错误’;
ROLLBACK;
END;

DECLARE from_balance DECIMAL(10,2);

START TRANSACTION;

SELECT balance INTO from_balance
FROM accounts
WHERE id = from_account;

IF from_balance IS NULL THEN
SIGNAL account_not_found;
END IF;

IF from_balance < amount THEN
SIGNAL insufficient_funds
SET MESSAGE_TEXT = ‘余额不足’;
END IF;

UPDATE accounts SET balance = balance – amount WHERE id = from_account;
UPDATE accounts SET balance = balance + amount WHERE id = to_account;

COMMIT;

SET result = ‘转账成功’;
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 调用存储过程
CALL transfer_money(1, 2, 100.00, @result);
SELECT @result;

输出示例：
Query OK, 0 rows affected (0.01 sec)

+—————+
| @result |
+—————+
| 转账成功 |
+—————+
1 row in set (0.00 sec)

1. 处理警告信息
DELIMITER //

CREATE PROCEDURE batch_insert_with_warning(
IN data_count INT,
OUT success_count INT,
OUT warning_count INT
)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE warning_flag INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
SET warning_flag = 1;
SET warning_count = warning_count + 1;
END;

SET success_count = 0;
SET warning_count = 0;

WHILE i <= data_count DO
INSERT INTO test_data (name, value, created_at)
VALUES (CONCAT(‘test_’, i), i, NOW());

IF warning_flag = 0 THEN
SET success_count = success_count + 1;
END IF;

SET warning_flag = 0;
SET i = i + 1;
END WHILE;
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 调用存储过程
CALL batch_insert_with_warning(10, @success, @warnings);
SELECT @success AS success_count, @warnings AS warning_count;

输出示例：
+—————+—————-+
| success_count | warning_count |
+—————+—————-+
| 10 | 0 |
+—————+—————-+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

1. 处理游标未找到数据
DELIMITER //

CREATE PROCEDURE get_user_orders(
IN user_id INT,
OUT has_orders BOOLEAN
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE order_count INT DEFAULT 0;

DECLARE cur CURSOR FOR
SELECT COUNT(*) FROM orders WHERE user_id = user_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

FETCH cur INTO order_count;

IF done THEN
SET has_orders = FALSE;
ELSE
SET has_orders = order_count > 0;
END IF;

CLOSE cur;
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 调用存储过程
CALL get_user_orders(1, @has_orders);
SELECT @has_orders;

输出示例：
+————+
| @has_orders |
+————+
| 1 |
+————+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

3. 测试无订单用户
CALL get_user_orders(9999, @has_orders);
SELECT @has_orders;

输出示例：
+————+
| @has_orders |
+————+
| 0 |
+————+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

1. 异常处理原则
– 预期可能发生的错误
– 提供有意义的错误信息
– 确保资源正确释放
– 维护数据一致性

2. 事务管理
– 使用事务保护关键操作
– 异常时回滚事务
– 确保事务完整性

3. 错误日志
– 记录异常信息
– 包含足够的上下文
– 便于问题排查

4. 代码结构
– 集中处理异常
– 避免嵌套过深
– 保持代码清晰