RESIGNAL with New Signal Information

RESIGNAL with a SET clause provides new signal information, so the statement means "pass on the error with changes":

RESIGNAL SET signal_information_item [, signal_information_item] ...;

As with RESIGNAL alone, the idea is to pop the diagnostics area stack so that the original information will go out. Unlike RESIGNAL alone, anything specified in the SET clause changes.

Example:

DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
 SET @error_count = @error_count + 1;
 IF @a = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF;
 END;
 DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
CALL p();

Remember from the previous discussion that RESIGNAL alone results in a diagnostics area stack like this:

1. ERROR 1051 (42S02): Unknown table 'xx'

The RESIGNAL SET MYSQL_ERRNO = 5 statement results in this stack instead:

1. ERROR 5 (42S02): Unknown table 'xx'

In other words, it changes the error number, and nothing else.

The RESIGNAL statement can change any or all of the signal information items, making the first condition area of the diagnostics area look quite different.

Retornar