MySQL Error Handling in Stored Procedures PDF
Document Details
Uploaded by AffordableHyperbola
University of San Jose-Recoletos
Tags
Summary
This document explains how to handle errors in MySQL stored procedures. It covers general and specific error conditions, providing examples of how to declare handlers, and how to use specific error codes or standards SQLSTATE values. The document includes practical examples demonstrating error handling mechanisms using the CONTINUE and EXIT handlers.
Full Transcript
MySQL Error Handling in Stored Procedures MySQL handler is used to handle errors encountered in stored procedures. When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful e...
MySQL Error Handling in Stored Procedures MySQL handler is used to handle errors encountered in stored procedures. When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful error message. MySQL provides an easy way to define handlers that handle from general conditions such as warnings or exceptions to specific conditions e.g., specific error codes. Declaring a handler: DECLARE action HANDLER FOR condition_value statement; If a condition whose value matches the condition_value, MySQL will execute the statement and continue or exit the current code block based on the action. The action accepts one of the following values: CONTINUE : the execution of the enclosing code block (BEGIN … END) continues. EXIT : the execution of the enclosing code block, where the handler is declared, terminates. The condition_value specifies a particular condition or a class of conditions that activates the handler. The condition_value accepts one of the following values: a) A MySQL error code. b) A standard SQLSTATE value. Or it can be an SQLWARNING, NOTFOUND or SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE values. The NOTFOUND condition is used for a cursor or SELECT INTO variable_list statement. c) A named condition associated with either a MySQL error code or SQLSTATE value. The statement could be a simple statement or a compound statement enclosing by the BEGIN and END keywords. Example 1: The following handler sets the value of hasError variable to 1 and continues the execution if an SQLEXCEPTION occurs. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasError = 1; Example 2: The following handler rolls back the previous operations, issues an error message, and exits the current code block in case an error occurs. If you declare it inside the BEGIN END block of a stored procedure, it will terminate the stored procedure immediately. DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'An error has occurred. Operation was rolled back and the stored procedure was terminated'; END; Example 3: The following handler sets the value of rowNotFound variable to 1 and continues execution if there is no more row to fetch in case of a cursor or SELECT INTO statement. DECLARE CONTINUE HANDLER FOR NOT FOUND SET rowNotFound = 1; Example 4: If a duplicate key error occurs, the following handler issues an error message and continues execution. DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Error, duplicate key occurred'; Example 5: DELIMITER $$ CREATE PROCEDURE AddCustomer(IN custID CHAR(3), IN lastName VARCHAR(15), IN firstName VARCHAR(15), IN address VARCHAR(30), IN gender CHAR, IN contact VARCHAR(11)) BEGIN DECLARE EXIT HANDLER FOR 1062 BEGIN SELECT CONCAT("Duplicate key (", custID,") occured") AS "Error"; END; INSERT INTO customer VALUES (custID, lastName, firstName, address, gender, contact); SELECT "one row inserted" AS "Message"; END $$ DELIMITER ;
