SQL Reference - SQLEXCEPTION

SQLEXCEPTION

SQLEXCEPTION is a predefined subclass in SQL utilized to handle exceptions. It is a part of exception handling in SQL and it's typically activated when a SQL statement encounters an error during its execution process. These errors could be syntactical errors, issues with data or database integrity, or other SQL-related issues. SQLEXCEPTION enables the program to proficiently manage such situations, preventing it from crashing and providing a way to address and rectify the problem.

Example

DROP PROCEDURE IF EXISTS sampleProcedure;
DELIMITER //
CREATE PROCEDURE sampleProcedure()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT 'An SQL exception has occurred.';
  DROP TABLE nonExistentTable;
END//
DELIMITER ;
CALL sampleProcedure();

Output

'An SQL exception has occurred.'

Explanation

In the example, a stored procedure named sampleProcedure is created. Inside this procedure, an exit handler for SQLEXCEPTION is declared. An SQL statement that will definitely result in an exception (dropping a non-existent table) is issued. When the procedure is called and the exception is raised, the exit handler is invoked and the message ‘An SQL exception has occurred.’ is displayed.

Example

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Output

ErrorNumber | ErrorMessage
----------- | ------------------------------
8134        | Divide by zero error encountered.

Explanation

In this example, an attempt is made to divide by zero which will throw a SQL exception. The TRY...CATCH block captures this, and the ERROR_NUMBER() and ERROR_MESSAGE() functions are used to fetch the details of the exception. These details are then outputted, showing the error number (8134) and the error message (“Divide by zero error encountered.

Example

DECLARE
  sample_exception EXCEPTION;
BEGIN
  RAISE sample_exception;
EXCEPTION
  WHEN sample_exception THEN
    dbms_output.put_line('SQLException encountered.');
END;

Output

SQLException encountered.

Explanation

If an exception is encountered in the SQL, it will be caught by the exception handler and a customized message will be displayed as per the exception block. In this case, a custom exception named sample_exception is initialized and then immediately raised. The exception block catches the exception and outputs the specified error message ‘SQLException encountered.’