如何在存储过程中编写 MySQL 处理程序，设置变量的特定值并继续执行?
mysqlmysqli database
众所周知，每当 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 // Query OK, 0 rows affected (0.00 sec) 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) Query OK, 0 rows affected (0.06 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) Query OK, 0 rows affected (0.03 sec) mysql> Select @got_error; +------------+ | @got_error | +------------+ | 1 | +------------+ 1 row in set (0.00 sec)
