Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

• Lecture:
  • Error Handling and Exceptions
  • Types of Handler Actions
  • How to Write Handler
  • Defining and Handling Exceptions in Stored Procedures and Functions
• Lab:
  • Exception Handling in Stored Procedures
  • Exception Handling with Various Handler Actions

Lecture Notes (In-Depth)

1. Error Handling and Exceptions

• Purpose: Error handling in MySQL ensures that stored procedures and functions can gracefully manage unexpected conditions (e.g., constraint violations, invalid inputs) to prevent silent failures and provide meaningful feedback.
• Exceptions in MySQL:
  • Errors (e.g., duplicate key, foreign key violation) are identified by SQLSTATE codes (5-character strings).
  • Common SQLSTATE categories:
    • '00xxx': Success.
    • '01xxx': Warnings (e.g., data truncated).
    • '02xxx': No data (e.g., NOT FOUND for cursors).
    • '45xxx': User-defined errors.
    • '23xxx': Constraint violations (e.g., duplicate key, foreign key).
  • Exceptions can be raised explicitly (SIGNAL) or caught and managed using handlers.
• Why Needed:
  • Ensure data integrity (e.g., reject invalid data).
  • Log errors for auditing or debugging.
  • Provide user-friendly error messages to applications.
  • Control program flow (e.g., continue or exit after an error).

2. Types of Handler Actions

• Handlers: Special constructs in stored procedures and functions that define actions to take when specific conditions (errors, warnings, or no data) occur.
• Types of Handler Actions:
  • CONTINUE:
    • Executes the handler’s logic and continues execution after the statement that caused the error.
    • Use case: Log an error but allow the program to proceed.
    • Example: Log a duplicate key error but continue processing other statements.
  • EXIT:
    • Executes the handler’s logic and exits the current BEGIN ... END block (e.g., terminates the procedure or function).
    • Use case: Stop execution on critical errors.
    • Example: Exit a procedure if a foreign key violation occurs.
  • UNDO:
    • Not supported in MySQL (mentioned in some SQL standards but not implemented).
    • MySQL uses ROLLBACK in transactions for similar functionality.
• Key Notes:
  • Handlers are scoped to the BEGIN ... END block where they are declared.
  • Multiple handlers can be defined for different conditions in the same block.

3. How to Write Handler

• Syntax:

  DECLARE {CONTINUE | EXIT} HANDLER FOR condition_value
  BEGIN
      -- Handler logic
  END;
  
  

  • condition_value can be:
    • SQLSTATE 'value': Specific error code (e.g., '23000' for constraint violation).
    • SQLWARNING: Catches all warnings (SQLSTATE starting with '01').
    • NOT FOUND: Catches no-data conditions (SQLSTATE '02000', e.g., cursor fetch).
    • SQLEXCEPTION: Catches all errors except warnings (SQLSTATE not starting with '00' or '01').
    • Custom condition: DECLARE condition_name CONDITION FOR SQLSTATE 'value';.

• Example:

  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
  BEGIN
      INSERT INTO error_log (error_message) VALUES ('Duplicate key error');
  END;
  
  

• Steps:

  1. Declare the handler at the start of a BEGIN ... END block.
  2. Specify the action (CONTINUE or EXIT).
  3. Define the condition to catch (e.g., SQLSTATE, SQLEXCEPTION).
  4. Write logic to handle the condition (e.g., log error, raise custom error).

4. Defining and Handling Exceptions in Stored Procedures and Functions

• Defining Custom Conditions:
  • Use DECLARE ... CONDITION to name a specific SQLSTATE for clarity.
  • Syntax: DECLARE condition_name CONDITION FOR SQLSTATE 'value';.
  • Example: DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000';.