我们知道,每当MySQL存储过程中发生异常时,通过抛出适当的错误消息来处理它是非常重要的,因为如果我们不处理该异常,则有可能使存储过程中具有该特定异常的应用程序失败。MySQL提供了一个设置变量特定值并继续执行的处理程序。为了说明这一点,我们使用下面的示例,在该示例中,我们尝试在主键列中插入重复的值。mysql> DELIMITER //
mysql> Create Procedure Insert_Studentdetails2(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20),OUT got_error INT)
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error=1;
-> INSERT INTO Student_detail
-> (Studentid, StudentName, Address)
-> Values(S_Studentid,S_StudentName,S_Address);
-> Select * from Student_detail;
-> END //
mysql> Delimiter ;
mysql> CALL Insert_Studentdetails2(104,'Ram',‘Chandigarh',@got_error);
+-----------+-------------+------------+
| Studentid | StudentName | address |
+-----------+-------------+------------+
| 100 | Gaurav | Delhi |
| 101 | Raman | Shimla |
| 103 | Rahul | Jaipur |
| 104 | Ram | Chandigarh |
+-----------+-------------+------------+
4 rows in set (0.04 sec)
现在,如果我们尝试添加列'studentid'的任何重复值,则它将继续执行,它将给出在过程'select * from student_detail'中编写的查询的结果集,并将got_error变量的值设置为1 。mysql> CALL Insert_Studentdetails2(104,'Shyam','Hisar',@got_error);
+-----------+-------------+------------+
| Studentid | StudentName | address |
+-----------+-------------+------------+
| 100 | Gaurav | Delhi |
| 101 | Raman | Shimla |
| 103 | Rahul | Jaipur |
| 104 | Ram | Chandigarh |
+-----------+-------------+------------+
4 rows in set (0.00 sec)
mysql> Select @got_error;
+------------+
| @got_error |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)