Use a transaction instance in the Mysql Stored Procedure

Copy codeThe Code is as follows:
Create definer = 'root' @ 'localhost' PROCEDURE 'createbusiness '(parameter1 int)
BEGIN
# Routine body goes here...
DECLARE flag int DEFAULT parameter1; # DECLARE the variable flag and assign the parameter value to the variable
DECLARE uuidStr VARCHAR (32); # DECLARE a 32-Bit String
DECLARE currentTime TIMESTAMP; # DECLARE a variable whose type is TIMESTAMP

Declare err INT default 0; # declare an integer variable err. The default value is 0.
Declare continue handler for sqlexception set err = 1; # When sqlexception handler catches an exception, set err = 1

Start transaction; # START the TRANSACTION

WHILE flag> 0 DO # Note: while cannot be empty (the while block must contain statements)

# Uuid () function to get the string is '6cd780c-baba-1026-9564-0040f4311e29 ', remove the-inside, get a 32-Bit String
SET uuidStr = REPLACE (UUID (),'-','');
# Obtain the current time
SET currentTime = CURRENT_TIMESTAMP ();
# Execute the insert statement. Pay attention to the concat (str1, str2,...) function connecting strings. In this example, str can also be a number.
INSERT
Table Name
(Id, title, keyword, hasImage, isTodayHead, isShowInHome, isBigness, publishTime, originId, modify_time, isAnalysis)
VALUE
(UuidStr, CONCAT ('event title', flag), CONCAT ('keyword', flag), currentTime, CONCAT ('xxxxxxx', flag), currentTime, 1 );
# Once every cycle, flag minus 1. Note that there is no flag syntax.
Set flag = flag-1;

# Test whether the transaction has been rolled back when err = 1. Test OK.
# IF flag = 7 THEN # note that set is used to assign values to variables in procedure, or default is used to assign values to parent and child variables during variable declaration. Therefore, the = sign can be used to compare whether the values on both sides are equal, <=> yes. The difference should not be resolved first.
# Set err = 1;
# END if;
End while;

IF (err = 0) THEN
Commit;
Select 'OK ';
ELSE
Rollback;
Select 'err ';
End if;

END;