- 检查话单重复入库
怀疑话单重复入库,可以用icd用户登录PL/SQL Developer,在SQL提示符下执行下面的语句检查进行检查(group by的字段需要能够确保定位到唯一的话单记录复):
SQL> select callid, callidnum from tbilllog7 group by callid, callidnum having count(1) > 1;
- 清理重复入库的话单
用icd用户登录UIDB,在SQL提示符下执行以下SQL脚本:
DECLARE
V_CALLID VARCHAR2(26);
V_CALLIDNUM NUMBER(10);
V_COUNT NUMBER(10) := 0;
CURSOR C_DAYLOG_BILL IS
SELECT CALLID, CALLIDNUM FROM TBILLLOG7 GROUP BY CALLID, CALLIDNUM HAVING COUNT(1) > 1;
BEGIN
OPEN C_DAYLOG_BILL;
LOOP
FETCH C_DAYLOG_BILL
INTO V_CALLID, V_CALLIDNUM;
EXIT WHEN C_DAYLOG_BILL%NOTFOUND;
V_COUNT := V_COUNT + 1;
DELETE FROM TBILLLOG7
WHERE CALLID = V_CALLID
AND CALLIDNUM = V_CALLIDNUM
AND ROWNUM = 1;
IF V_COUNT = 100 THEN
COMMIT;
V_COUNT := 0;
END IF;
END LOOP;
COMMIT;
RETURN;
END;
/
大工告成!