![]() 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 enclosed 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |