Use the transaction instance in the Mysql stored procedure
- 2020-06-15 10:22:23
- OfStack
CREATE DEFINER=`root`@`localhost` PROCEDURE `createBusiness`(parameter1 int)
BEGIN
#Routine body goes here...
DECLARE flag int DEFAULT parameter1;# Declare a variable flag , assigns the parameter value to the variable
DECLARE uuidStr VARCHAR(32);# The statement 1 A length of 32 Bit string
DECLARE currentTime TIMESTAMP;# The statement 1 Three variables of type timestamp
declare err INT default 0;# The statement 1 Three shaping variables err , the default value is 0
declare continue handler for sqlexception set err=1;# when sqlexception handler Set when an exception is caught err=1
START TRANSACTION;# Start the transaction
WHILE flag>0 DO # Pay attention to : while Cannot empty implementation ( in while Block, which must have a statement in it )
#uuid() The string of the function is '6ccd780c-baba-1026-9564-0040f4311e29' Take out the inside - , 1 a 32 Bit string
SET uuidStr = REPLACE(UUID(),'-','') ;
# Get the current time
SET currentTime = CURRENT_TIMESTAMP();
# Execute the insert statement and notice the function that concatenates the string concat(str1,str2,...); Among them str.. It can also be a numeric type
INSERT INTO
The name of the table
(id,title,keyword,hasImage,isTodayHead,isShowInHome,isBigness,publishTime,originId,modify_time,isAnalysis)
VALUE
(uuidStr,CONCAT(' Event title ',flag),CONCAT(' The keyword ',flag),1,1,0,0,currentTime,CONCAT('xxxxxxx',flag),currentTime,1);
# Each cycle 1 Time, flag To lose 1 , notice no flag-- The grammar of the
set flag = flag-1;
# Test when here err=1 Test if the transaction has been rolled back ok
#IF flag=7 THEN # Note that in procedure To assign a value to a variable set, Or in variable declarations default Father and son, so = The sign can be used to compare whether the values on both sides are equal, <=> Or, let the distinction go.
#set err=1;
#END if;
END WHILE;
IF (err=0) THEN
commit;
select 'OK';
ELSE
rollback;
select 'err';
END IF;
END;