Mysql stored procedures include transactions and SQL data is imported for execution. it is required to use transactions in mysql stored procedures and to execute dynamic SQL statements.
The code is as follows:
BEGINDECLARE in_data TEXT;/** mark whether an error occurs */DECLARE errno int default '0';/** if an SQL exception occurs, SET t_error to 1 and then CONTINUE the subsequent operation */declare continue handler for sqlexception Rollin ROLLBACK; SET errno = 1; END; start transaction; -- PREPARE stmt FROM @ in_data; EXECUTE stmt; IF (errno = 1) then rollback; else commit; end if; SELECT errno; END
Call:
SET @ in_data = 'Insert into accounts ('userid', 'password') value (122222222,2), (22222,11) '; CALL SYN_Updata (@ in_data );
In fact, this stored procedure can be used wherever transactions are used, because the execution conditions are dynamic.