DELIMITER
D
R
O
P
P
R
O
C
E
D
U
R
E
I
F
E
X
I
S
T
S
‘
u
p
d
a
t
e
e
m
p
i
d
‘
DROP PROCEDURE IF EXISTS `update_empid`
DROPPROCEDUREIFEXISTS‘updateempid‘
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 ;