CREATEDEFINER=`***项目必要,已隐藏**`@`%`PROCEDURE`compare_twotb_data`(IN`TABLE1`VARCHAR(50),IN`TABLE2`VARCHAR(50),IN`timestr`VARCHAR(50))LANGUAGESQLNOTDETERMINISTICCONTAINSSQLSQL SECURITY DEFINERCOMMENT'比较两张表的数据是否一致'BEGINDECLARE cnt INT;DECLARE colname VARCHAR(100)DEFAULTNULL;DECLARE done INTDEFAULT0;-- 声明游标DECLARE cur CURSORFORselect COLUMN_NAME from information_schema.COLUMNSwhere TABLE_NAME = TABLE1
AND COLUMN_NAME NOTIN('ID','COMCODE','ENDDATA','DATAFLAG');DECLARECONTINUEHANDLERFORNOT FOUND SET done =1;-- 打开游标OPEN cur ;-- 使用repeat循环语法REPEAT-- 批读取数据到指定变量上FETCH cur INTO colname;set@stmt= CONCAT('SELECT COUNT(1) FROM ',TABLE1,' a
INNER JOIN ',TABLE2,' b ON a.COMCODE = b.COMCODE
‘/*
此处省略(原为两字段的匹配项)
*/’
WHERE a.ENDDATE = ',timestr,'
AND a.',colname ,' != b.',colname,' into @cnt;');PREPARE stmt1 FROM@stmt;EXECUTE stmt1;deallocateprepare stmt1;SET cnt =@cnt;if cnt >0thenINSERTINTO compare_table_date_log(TABLE1,TABLE2,COLNAME,diffcnt)VALUES(TABLE1,TABLE2,colname,cnt);ENDif;-- 循环结束条件
UNTIL done
ENDREPEAT;-- 关闭游标CLOSE cur ;END