maridb存储过程 带事务 sql拼接

DELIMITER $$DROPPROCEDUREIFEXISTS‘updat{e}_{e}mpid‘$$
CREATE PROCEDURE update_empid(
in peid varchar(20),
in old_empid varchar(20),
in new_empid varchar(20),
in option_user varchar(20),
out updaterow int)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
DECLARE tmp_update_row int default 0;
DECLARE tmp_table_name varchar(20);
DECLARE tmp_peid_column varchar(20);
DECLARE tmp_empid_column varchar(20);

DECLARE s INT DEFAULT 0;

DECLARE t_error INTEGER DEFAULT 1;
BEGIN
DECLARE cursor_changeTable CURSOR
FOR
select table_name,peid_column,empid_column from change_empid_table;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET s=1;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=0;
START TRANSACTION;

OPEN cursor_changeTable;
FETCH cursor_changeTable INTO tmp_table_name,tmp_peid_column,tmp_empid_column;
WHILE s<>1 DO
SET @sqlTmp=CONCAT(‘update ‘,tmp_table_name,’ set ',tmp_empid_column,' = ‘’’,new_empid,’’’ where ',tmp_peid_column,' = ‘’’,peid,’’’ and ',tmp_empid_column,' = ‘’’,old_empid, ‘’’’);
PREPARE stmt FROM @sqlTmp;
EXECUTE stmt;
deallocate prepare stmt;
set tmp_update_row =tmp_update_row +1;
FETCH NEXT FROM cursor_changeTable INTO tmp_table_name,tmp_peid_column,tmp_empid_column;
END WHILE;
CLOSE cursor_changeTable;

IF t_error=0 THEN
ROLLBACK;
set tmp_update_row=-1;
ELSE
SET @sqlTmps=CONCAT(‘insert into change_empid_log(peid,old_empid,new_empid,update_by,update_date) values (’’’,
peid,’’’,’’’,old_empid,’’’,’’’,new_empid,’’’,’’’,option_user,’’’,STR_TO_DATE(’’’,NOW(),’’’,’’%Y-%m-%d %H:%i:%s’’)’,’)’);
PREPARE stmts FROM @sqlTmps;
EXECUTE stmts;
deallocate prepare stmts;
COMMIT;
END IF;
END;
set updaterow = tmp_update_row;
END$$
DELIMITER ;