Here is an example from Chapter 8 of Get It Done with MySQL 5&6:
DROP PROCEDURE IF EXISTS signaldemo;
DELIMITER go
CREATE PROCEDURE signaldemo( pSignal BOOL )
BEGIN
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
IF pSignal THEN
SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT='Null primary key';
ELSE
SELECT 'Null primary key' AS Error;
END IF;
END;
SELECT 'Running' AS 'Block 1';
INSERT INTO t VALUES(NULL,NULL);
END;
SELECT 'Running' AS 'Block 2';
END;
go
DELIMITER ;
CALL signaldemo(1);
+---------+
| Block 1 |
+---------+
| Running |
+---------+
show errors;
+-------+------+------------------+
| Level | Code | Message |
+-------+------+------------------+
| Error | 1644 | Null primary key |
+-------+------+------------------+
And here is a RESIGNAL example from the same chapter:
DROP PROCEDURE IF EXISTS resignaldemo;
DELIMITER go
CREATE PROCEDURE resignaldemo(pResignal BOOL)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'Error handler' AS 'WhoAmI';
IF pResignal THEN RESIGNAL; END IF;
END;
INSERT INTO t VALUES(NULL,NULL);
END;
go
DELIMITER ;
|
|