- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we write MySQL handler, in a stored procedure, that sets the particular value of a variable and continues the execution?
As we know that whenever an exception occurred in MySQL stored procedure, it is very important to handle it by throwing proper error message because if we do not handle the exception, there would be a chance to fail application with that certain exception in a stored procedure. MySQL provides a handler that sets the particular value of a variable and continues the execution. To demonstrate it, we are using the following example in which we are trying to insert a duplicate value in a Primary key column.
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)
Now, if we will try to add any duplicate value of column ‘studentid’ then it will continue the execution, it gives the result set of the query written in procedure ‘select * from student_detail’ and sets the value of the got_error variable to 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)
143 Views
