CREATE OR REPLACE PROCEDURE GUANGGAO.DELETE_DUPLIATE_DATA
IS
CURSOR cur_tempacct
IS
SELECT TA.gameid AS gameid, TA.account AS account
FROM TEMP_ACCOUNT_ACTIVE TA
GROUP BY TA.GAMEID, TA.account
HAVING COUNT (1) > 1;
BEGIN
FOR account_info IN cur_tempacct
LOOP
DELETE FROM TEMP_ACCOUNT_ACTIVE TAC
WHERE TAC.ACCOUNT = account_info.account
AND TAC.GAMEID = account_info.gameid
AND ROWNUM = 1;
END LOOP;
COMMIT;
IS
CURSOR cur_tempacct
IS
SELECT TA.gameid AS gameid, TA.account AS account
FROM TEMP_ACCOUNT_ACTIVE TA
GROUP BY TA.GAMEID, TA.account
HAVING COUNT (1) > 1;
BEGIN
FOR account_info IN cur_tempacct
LOOP
DELETE FROM TEMP_ACCOUNT_ACTIVE TAC
WHERE TAC.ACCOUNT = account_info.account
AND TAC.GAMEID = account_info.gameid
AND ROWNUM = 1;
END LOOP;
COMMIT;
END DELETE_DUPLIATE_DATA;
--注:1、gameid和account唯一确定一条记录
--2、一次只能删除一条重复记录,所以需要重复调用
--3、由于数据量大不考虑使用in语句