VALUES(supplierId,productId) Code language: SQL (Structured Query Language) ( sql ) INSERT INTO SupplierProducts(supplierId,productId) If a duplicate key occurs, the code in the handler section will execute. This statement inserts a row into the SupplierProducts table. DECLARE EXIT HANDLER FOR 1062 BEGIN SELECT CONCAT( 'Duplicate key (',supplierId, ',',productId, ') occurred') AS message In addition, it returns an error message. The following exit handler terminates the stored procedure whenever a duplicate key occurs (with code 1062). return the products supplied by the supplier id SELECT COUNT(*)ĭELIMITER Code language: SQL (Structured Query Language) ( sql ) insert a new row into the SupplierProducts INSERT INTO SupplierProducts(supplierId,productId) Second, create a stored procedure that inserts product id and supplier id into the SupplierProducts table: CREATE PROCEDURE InsertSupplierProduct(īEGIN - exit if the duplicate key occurs DECLARE EXIT HANDLER FOR 1062 BEGIN SELECT CONCAT( 'Duplicate key (',inSupplierId, ',',inProductId, ') occurred') AS message For the sake of simplicity, we don’t create Products and Suppliers tables, as well as the foreign keys in the SupplierProducts table. Each supplier may provide many products and each product can be provided by many suppliers. The table SupplierProducts stores the relationships between the table suppliers and products. ) Code language: SQL (Structured Query Language) ( sql ) DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Error, duplicate key occurred' Code language: SQL (Structured Query Language) ( sql ) MySQL handler example in stored proceduresįirst, create a new table named SupplierProductsfor the demonstration: CREATE TABLE SupplierProducts ( If a duplicate key error occurs, the following handler issues an error message and continues execution. The following handler sets the value of the 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 Code language: SQL (Structured Query Language) ( sql ) SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated' ĮND Code language: SQL (Structured Query Language) ( sql ) If you declare it inside the BEGIN END block of a stored procedure, it will terminate the stored procedure immediately. The following handler rolls back the previous operations, issues an error message, and exit the current code block in case an error occurs. SET hasError = 1 Code language: SQL (Structured Query Language) ( sql ) The following handler set the value of the hasError variable to 1 and continue the execution if an SQLEXCEPTION occurs DECLARE CONTINUE HANDLER FOR SQLEXCEPTION Let’s take some examples of declaring handlers. The statement could be a simple statement or a compound statement enclosing by the BEGIN and END keywords. A named condition associated with either a MySQL error code or SQLSTATE value.The NOTFOUND condition is used for a cursor or SELECT INTO variable_list statement. Or it can be an SQLWARNING, NOTFOUND or SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE values. The condition_value accepts one of the following values: The condition_value specifies a particular condition or a class of conditions that activate the handler. EXIT : the execution of the enclosing code block, where the handler is declared, terminates.CONTINUE : the execution of the enclosing code block ( BEGIN … END ) continues. ![]() ![]() The action accepts one of the following values: 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. To declare a handler, you use the DECLARE HANDLER statement as follows: DECLARE action HANDLER FOR condition_value statement Code language: SQL (Structured Query Language) ( sql ) 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. 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. ![]() Summary: in this tutorial, you will learn how to use MySQL handler to handle errors encountered in stored procedures.
0 Comments
Leave a Reply. |