MySQL implements a distinct mechanism for managing exceptions compared to other database engines such as Oracle. The foundation of this system lies in the DECLARE ... HANDLER construct, which allows developers to specify precisely how a stored routine should respond to runtime errors. This capability ensures data integrity by allowing the application to react predictively when operations fail.

The DECLARE HANDLER Construct

To begin capturing exceptions, the DECLARE keyword is utilized within the stored program block. The fundamental syntax defines the reaction type and the specific trigger condition:

DECLARE [CONTINUE | EXIT] HANDLER FOR {condition_value} DO statement_list;

This statement cosnists of three primary components:

1. Handler Type: Determines flow control upon error detection. It accepts either CONTINUE (proceed to the next statement) or EXIT (terminate the current block immediately).
2. Condition Value: Specifies the exact error or condition triggering the handler. Options include SQLSTATE codes, MySQL-specific error numbers, or predefined classes like SQLWARNING.
3. Action Statement: A single DML or DDL statement executed when the condition is met. Complex actions require wrapping logic inside BEGIN...END blocks.

In most production environments, immediate termination (EXIT) followed by a rollback is preferred over continuing execution, as proceeding after failure can lead to inconsistent data states.

Defining Condition Triggers

Error conditions in MySQL handlers fall into several categories:

• Specific Errors: Defined by SQLSTATE 'value' (standard ANSI codes) or mysql_error_code (native numeric IDs).
• Standard Classes:
  • SQLWARNING: Captures any warning condition where the SQLSTATE starts with '01'.
  • NOT FOUND: Captures condition warnings starting with '02' (typically used for missing rows).
  • SQLEXCEPTION: Captures all remaining unspecified SQL errors.
• Named Conditions: You can define a custom alias for an error code using DECLARE condition_name CONDITION FOR condition_value, then reference the name in the handler definition.

Implementation Examples

The following examples demonstrate various approaches to declaring and invoking these handlers. Note the variation in logic and variable naming compared to standard patterns.

Capturing Specific State Codes and Error Numbers

You may target specific SQLSTATE values directly or map MySQL error codes. Below are implementations targeting duplicate entries.

-- Example A: Targeting SQLSTATE
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' 
    SET @status_message = 'Constraint violation detected'; 

-- Example B: Targeting Native Error Code
DECLARE CONTINUE HANDLER FOR 1062 
    SET @status_message = 'Constraint violation detected'; 

-- Example C: Using a Named Alias
DECLARE dup_entry_condition CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR dup_entry_condition
BEGIN
    SET @status_message = 'Duplicate record found';
END;

Handling Warnings and Missing Data

The NOT FOUND handler is particularly useful for cursor loops or SELECT INTO statements that fail to retrieve records.

DECLARE EXIT HANDLER FOR NOT FOUND
    SET row_count = 0;

Grouped Statements via BEGIN...END

If the recovery logic requires multiple steps (e.g., logging errors and rolling back transactinos), encapsulate them in a compound statement block.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    INSERT INTO error_log (error_detail, timestamp) 
    VALUES ('Critical failure occurred', NOW());
END;

Raising Custom Exceptions

While handlers catch errors from existing operations, you can actively generate errors using the SIGNAL statement. This forces a callout to the outer layer with specific information regarding the failure.

SIGNAL SQLSTATE value_name
SET message_text = 'Description of error',
      condition_number = 40000;

The SIGNAL command can appear anywhere within the procedure logic. Conversely, RESIGNAL is used only inside an active handler block to pass the error up to the caller, often modifying the details before rethrowing.

Practical Workflow Scenarios

The scenarios below illustrate full procedures validating inputs and handling concurrency issues.

Scenario 1: Validating Entity Existence

This procedure checks if an order number exists before processing. If not, it raises a custom signal.

DELIMITER $$

CREATE PROCEDURE process_new_order(
    IN p_order_id INT,
    IN p_item_qty INT
)
BEGIN
    DECLARE v_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO v_exists
    FROM orders_header
    WHERE order_id = p_order_id;

    IF v_exists != 1 THEN 
        SIGNAL SQLSTATE '45001' 
        SET MESSAGE_TEXT = 'Order ID is not present in the header table';
    END IF;

    -- Proceed with valid business logic here
    INSERT INTO order_lines (order_id, quantity) VALUES (p_order_id, p_item_qty);
    
END$$

DELIMITER ;

Scenario 2: Arithmetic Validation with Resignal

This example demonstrates catching a division attempt and re-raising it with a clearer message to the client.

DELIMITER $$

CREATE PROCEDURE calculate_ratio(
    IN numerator INT,
    IN denominator INT,
    OUT ratio_result DOUBLE
)
BEGIN
    -- Define a custom condition for clarity
    DECLARE div_zero_error CONDITION FOR SQLSTATE '22012';
    
    -- Set up the handler to modify and rethrow the error
    DECLARE CONTINUE HANDLER FOR div_zero_error 
        BEGIN
            SET @custom_msg = 'Division operation rejected due to invalid divisor';
        END;

    IF denominator = 0 THEN
        SIGNAL div_zero_error;
    ELSE
        SET ratio_result := numerator / denominator;
    END IF;
END$$

DELIMITER ;

Scenario 3: Silent Logging on Conflict

Sometimes, preventing the transaction from stopping is desirable. Here, we log duplicates to a separate audit table and allow execution to continue, albeit with a modified return count.

CREATE DEFINER=`db_user`@`localhost` PROCEDURE safe_insert_variant(
    IN p_sku VARCHAR(45),
    IN p_supplier INT
)
BEGIN
    DECLARE v_conflict_flag BOOLEAN DEFAULT FALSE;
    
    -- Capture conflict error but do not exit
    DECLARE CONTINUE HANDLER FOR 1062 
    BEGIN
        SET v_conflict_flag = TRUE;
        INSERT INTO audit_conflicts (sku, supplier, action_time) 
        VALUES (p_sku, p_supplier, NOW());
    END;

    INSERT INTO product_variants (sku, supplier_id) 
    VALUES (p_sku, p_supplier);

    SELECT 
        CASE WHEN v_conflict_flag 
            THEN 'Inserted with conflict logged' 
            ELSE 'Insert successful' 
        END AS result_status;
END;

By utilizing these techniques—combining SIGNAL for validation and HANDLER for recovery—you can build MySQL stored routines that maintain high data consistency even under unexpected runtime conditions.