Some experiences on MySQL Stored Procedure Exception Handling, mysql Stored Procedure

Some experiences on MySQL Stored Procedure Exception Handling, mysql Stored Procedure

Drop procedure if exists 'SP _ model ';

DELIMITER ;;
Create procedure 'SP _ model' (IN V_TYPE INT)
BEGIN
/********** Stored procedure template, combined with the function of returning custom error information, error exit, and transaction rollback ***********/
DECLARE V_TEST int default 0;

DECLARE V_ERR_NO int default 0;
DECLARE V_ERR_MSG VARCHAR (100) DEFAULT 'execution successfully ';
Declare continue handler for sqlexception, SQLWARNING, not found set V_ERR_NO = 1;

-- Create table 'test _ error '(
-- 'A' int (11) not null,
-- 'B' int (11) DEFAULT NULL,
-- Primary key ('A ')
--) ENGINE = InnoDB default charset = utf8;

-- Truncate table TEST_ERROR;
-- CALL SP_MODEL (1 );
-- SELECT * FROM TEST_ERROR;

Start transaction;

IF V_TYPE = 1 THEN
Insert into TEST_ERROR (a, B) VALUES (1, 1 );
Insert into TEST_ERROR (a, B) VALUES (1, 1 );

IF V_ERR_NO = 1 THEN
SET V_ERR_MSG = 'You have already inserted it. Do not insert it again ~ ';
-- LEAVE Label;
End if;

SET V_TEST = 1;

Insert into TEST_ERROR (a, B) VALUES (3, 'A ');
IF V_ERR_NO = 1 THEN
SET V_ERR_MSG = 'the size is incorrect and cannot be inserted ~ ';
LEAVE Label;
End if;

Insert into TEST_ERROR (a, B) VALUES (4, 1 );

SET V_TEST = 2;

ELSE
SET V_ERR_MSG = 'incorrect passed V_TYPE ';
SET V_ERR_NO = 1; -- directly SET the value of this variable to 1 and throw an exception.
LEAVE Label;
End if;

END Label;

IF V_ERR_NO = 1 THEN
ROLLBACK;
ELSE
COMMIT;
End if;

SELECT V_ERR_NO AS ERR_NO, V_ERR_MSG AS ERR_MSG, V_TEST as test; -- return the execution result
END

;;

DELIMITER;