我们如何在存储过程中编写MySQL处理程序,该处理程序会引发错误消息并继续执行?
我们知道,每当MySQL存储过程中发生异常时,通过抛出适当的错误消息来处理它就非常重要,因为如果我们不处理该异常,则有可能使存储过程中存在该特定异常的应用程序失败。MySQL提供了一个引发错误消息并继续执行的处理程序。为了说明这一点,我们使用下面的示例,在该示例中,我们尝试在主键列中插入重复的值。
例
mysql> DELIMITER //mysql> Create Procedure Insert_Studentdetails(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20))
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'Got an error';
-> INSERT INTO Student_detail
-> (Studentid, StudentName, Address)
-> Values(S_Studentid,S_StudentName,S_Address);
-> Select * from Student_detail;
-> END //
调用上面的过程,如果我们尝试在“ studentid”列中输入重复的值,它将抛出一条错误消息“ get an error”并继续执行。
mysql> Delimiter ;mysql> CALL Insert_Studentdetails(100, 'Gaurav', 'Delhi');
+-----------+-------------+---------+
| Studentid | StudentName | address |
+-----------+-------------+---------+
| 100 | Gaurav | Delhi |
+-----------+-------------+---------+
1 row in set (0.11 sec)
mysql> CALL Insert_Studentdetails(101, 'Raman', 'Shimla');
+-----------+-------------+---------+
| Studentid | StudentName | address |
+-----------+-------------+---------+
| 100 | Gaurav | Delhi |
| 101 | Raman | Shimla |
+-----------+-------------+---------+
2 rows in set (0.06 sec)
mysql> CALL Insert_Studentdetails(101, 'Rahul', 'Jaipur');
+--------------+
| Got an error |
+--------------+
| Got an error |
+--------------+
1 row in set (0.03 sec)
+-----------+-------------+---------+
| Studentid | StudentName | address |
+-----------+-------------+---------+
| 100 | Gaurav | Delhi |
| 101 | Raman | Shimla |
+-----------+-------------+---------+
2 rows in set (0.04 sec)
mysql> CALL Insert_Studentdetails(103, 'Rahul', 'Jaipur');
+-----------+-------------+---------+
| Studentid | StudentName | address |
+-----------+-------------+---------+
| 100 | Gaurav | Delhi |
| 101 | Raman | Shimla |
| 103 | Rahul | Jaipur |
+-----------+-------------+---------+
3 rows in set (0.08 sec)
以上是 我们如何在存储过程中编写MySQL处理程序,该处理程序会引发错误消息并继续执行? 的全部内容, 来源链接: utcz.com/z/338314.html