mysql 存储过程 实例

为了两个表之间同步数据写了一个存储过程

里面有动态游标的使用, 以及其他的一些逻辑

留作以后参考.

 

 

 

drop procedure if exists pro_rpt_sync; --SELECT count(distinct BIND_MONTH) into @totalNum from RPT_SALE_MONTH_PROV_TMP ; create procedure pro_rpt_sync ( tablename varchar(200),timecolumn varchar(20),manutype int) BEGIN DECLARE done INT DEFAULT 0; DECLARE bindtime varchar(10); DECLARE finished int DEFAULT 0; DECLARE deleteNum int; DECLARE insertNum int; DECLARE sumTimeCount int DEFAULT 0; --创建临时游标 DECLARE cur1 CURSOR FOR SELECT distinct BIND_TIME from CURSOR_TMP; DROP TABLE if exists CURSOR_TMP; SET @tmpname = concat(tablename,'_TMP'); SET @sql_txt = concat('CREATE TABLE CURSOR_TMP SELECT distinct ',timecolumn,' as BIND_TIME from ',@tmpname); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name ; -- 合并厂商ID字段 IF manutype = 0 THEN SET @sql_txt = concat('UPDATE ', @tmpname ,' t1 SET t1.MERGE_MANU_ID = MANU_ID'); ELSE SET @sql_txt = concat('UPDATE ', @tmpname ,' t1 SET t1.BEGIN_MERGE_MANU_ID = BEGIN_MANU_ID, t1.END_MERGE_MANU_ID = END_MANU_ID'); END IF; PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name ; COMMIT; -- 合并厂商ID IF manutype = 0 THEN SET @sql_txt = concat('UPDATE ', @tmpname , ' t1, OEM_MANUFACTURER_MERGE t2 SET t1.MERGE_MANU_ID = t2.MERGE_MANU_ID where t1.MERGE_MANU_ID = t2.PRE_MANU_ID'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name ; ELSE SET @sql_txt = concat('UPDATE ', @tmpname , ' t1, OEM_MANUFACTURER_MERGE t2 SET t1.BEGIN_MERGE_MANU_ID = t2.MERGE_MANU_ID where t1.BEGIN_MERGE_MANU_ID = t2.PRE_MANU_ID'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name ; SET @sql_txt = concat('UPDATE ', @tmpname , ' t1, OEM_MANUFACTURER_MERGE t2 SET t1.END_MERGE_MANU_ID = t2.MERGE_MANU_ID where t1.END_MERGE_MANU_ID = t2.PRE_MANU_ID'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name ; END IF; COMMIT; --UPDATE RPT_SALE_MONTH_PROV_TMP t1, OEM_MANUFACTURER_MERGE t2 SET t1.MERGE_MANU_ID = t2.MERGE_MANU_ID where t1.MERGE_MANU_ID = t2.PRE_MANU_ID; --查询同步报表的开始时间和结束时间 SET @totalTimeNum = 0; SET @maxTime =''; SET @minTime =''; SET @sql_txt = concat('SELECT count(distinct BIND_TIME) into @totalTimeNum from CURSOR_TMP'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name ; SET @sql_txt = concat('SELECT max(BIND_TIME) into @maxTime from CURSOR_TMP'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name ; SET @sql_txt = concat('SELECT min(BIND_TIME) into @minTime from CURSOR_TMP'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name ; --查询同步表最大ID +1 作为本次更新ID SELECT max(SYNC_ID) into @syncid from OEM_RPT_SYNC_HISTORY; Set @syncid = @syncid + 1; SET @tablename = tablename; SET @sql_txt = concat('INSERT INTO OEM_RPT_SYNC_HISTORY (SYNC_ID,SYNC_TABLE,SYNC_STATUS,START_SYNC_DATE,RPT_START_TIME,RPT_END_TIME) VALUES(?,?,1,current_timestamp,?,? )'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name using @syncid,@tablename,@minTime,@maxTime; select @syncid,tablename,@minTime,@maxTime; -- 提交数据 commit; --更新数据前先停止索引 SET @sql_txt = concat('ALTER TABLE ', @tablename ,' DISABLE KEYS'); SET @deleteNum = 0; SET @insertNum = 0; OPEN cur1; REPEAT FETCH cur1 INTO bindtime; SET @bindtime = bindtime; -- 删除目标表月份数据 SET @sql_txt = concat(' delete from ',tablename,' where ',timecolumn,' = ?'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name USING @bindtime; SET @deleteNum = @deleteNum + row_count(); -- 插入目标表新的月份数据 SET @sql_txt = concat(' insert into ',tablename,' ( SELECT * FROM ',@tmpname,' t2 where ',timecolumn,' = ?)'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name using @bindtime; SET @insertNum = @insertNum + row_count(); -- 提交数据 commit; SET finished = finished+1; IF finished >= @totalTimeNum THEN SET done = 1; END IF; UNTIL done END REPEAT; CLOSE cur1; --更新数据完成后启用索引 SET @sql_txt = concat('ALTER TABLE ', @tablename ,' ENABLE KEYS'); --更新同步状态为结束 SET @sql_txt = concat('UPDATE OEM_RPT_SYNC_HISTORY SET SYNC_STATUS=2,END_SYNC_DATE=current_timestamp,DELETE_NUM=?,INSERT_NUM=? WHERE SYNC_ID = ?'); PREPARE stmt_name FROM @sql_txt; EXECUTE stmt_name using @deleteNum,@insertNum,@syncid; commit; -- select @maxTime,@minTime,@totalTimeNum,finished; END;