V_NUM NUMBER;
CURSOR CARDINFOB IS
SELECT MB.ID, MB.CARD_ID_DEX, MB.EMPO, MB.CARD_STATUS, MB.OP_TYPE
FROM CMS_CARD_USER_SYN_BAK MB;
BEGIN
V_NUM := 0;
FOR C IN CARDINFOB LOOP
UPDATE CMS_CARD_USER_SYN MO
SET MO.OP_TYPE = C.OP_TYPE,
MO.OP_DATE = SYSDATE,
MO.CARD_STATUS = C.CARD_STATUS
WHERE MO.ID = C.ID
AND MO.CARD_ID_DEX = C.CARD_ID_DEX
AND MO.EMPO = C.EMPO;
DELETE FROM CMS_CARD_USER_SYN_BAK MD
WHERE MD.ID = C.ID
AND MD.CARD_ID_DEX = C.CARD_ID_DEX
AND MD.EMPO = C.EMPO;
V_NUM := V_NUM + 1;
IF V_NUM = 5000 THEN
COMMIT;
V_NUM := 0;
END IF;
END LOOP;
COMMIT;
END;
/
DECLARE
V_NUM NUMBER;
V_ID VARCHAR2(20);
CURSOR CARDINFO IS
SELECT MS.ID,
MS.CARD_ID_DEC,
MS.CARD_ID_DEX,
MS.UMID,
MS.EMPO,
MS.ISSUER,
MS.REMARKS,
MS.MATCH_ID,
MS.CARD_TYPE,
MS.CARD_STATUS,
MS.NAME_CN,
MS.NAME_EN,
MS.SEX,
MS.OP_DATE,
MS.OP_TYPE,
MS.DEPT,
MS.EMP_TYPE,
MS.GUARD_GROUP,
MS.DEPT_NAME,
MS.VALIDTY,
MS.VALID_BEGINTIME,
MS.VALID_ENDTIME,
MS.LCD,
MS.LCU
FROM ECDATA.CMS_CARD_USER_SYN_DEAL MD,
ECDATA.CMS_CARD_USER_SYN MS,
(SELECT MAX(T.LCD) LCD, T.CARD_ID_DEX FDEX, T.EMPO FEMPO
FROM CMS_CARD_USER_SYN T
WHERE 1 = 1
GROUP BY T.CARD_ID_DEX, T.EMPO) MF
WHERE MD.CARD_ID = MS.CARD_ID_DEX
AND MD.EMPO = MS.EMPO
AND MS.CARD_ID_DEX = MF.FDEX
AND MS.EMPO = MF.FEMPO
AND MS.LCD = MF.LCD;
BEGIN
V_NUM := 0;
FOR C IN CARDINFO LOOP
SELECT GETROWID('CMS_PARTNER_INFO') INTO V_ID FROM DUAL;
INSERT INTO CMS_CARD_USER_SYN_BAK
(ID,
CARD_ID_DEC,
CARD_ID_DEX,
UMID,
EMPO,
ISSUER,
REMARKS,
MATCH_ID,
CARD_TYPE,
CARD_STATUS,
NAME_CN,
NAME_EN,
SEX,
OP_DATE,
OP_TYPE,
DEPT,
EMP_TYPE,
GUARD_GROUP,
DEPT_NAME,
VALIDTY,
VALID_BEGINTIME,
VALID_ENDTIME,
LCD,
LCU)
VALUES
(C.ID,
C.CARD_ID_DEC,
C.CARD_ID_DEX,
C.UMID,
C.EMPO,
C.ISSUER,
C.REMARKS,
C.MATCH_ID,
C.CARD_TYPE,
C.CARD_STATUS,
C.NAME_CN,
C.NAME_EN,
C.SEX,
C.OP_DATE,
C.OP_TYPE,
C.DEPT,
C.EMP_TYPE,
C.GUARD_GROUP,
C.DEPT_NAME,
C.VALIDTY,
C.VALID_BEGINTIME,
C.VALID_ENDTIME,
C.LCD,
C.LCU);
INSERT INTO CMS_CARD_USER_SYN
(ID,
CARD_ID_DEC,
CARD_ID_DEX,
UMID,
EMPO,
ISSUER,
REMARKS,
MATCH_ID,
CARD_TYPE,
CARD_STATUS,
NAME_CN,
NAME_EN,
SEX,
OP_DATE,
OP_TYPE,
DEPT,
EMP_TYPE,
GUARD_GROUP,
DEPT_NAME,
VALIDTY,
VALID_BEGINTIME,
VALID_ENDTIME,
LCD,
LCU)
VALUES
(V_ID,
C.CARD_ID_DEC,
C.CARD_ID_DEX,
C.UMID,
C.EMPO,
C.ISSUER,
C.REMARKS,
C.MATCH_ID,
C.CARD_TYPE,
'0004',
C.NAME_CN,
C.NAME_EN,
C.SEX,
SYSDATE,
'0004',
C.DEPT,
C.EMP_TYPE,
C.GUARD_GROUP,
C.DEPT_NAME,
C.VALIDTY,
C.VALID_BEGINTIME,
C.VALID_ENDTIME,
SYSDATE,
C.LCU);
V_NUM := V_NUM + 1;
IF V_NUM = 5000 THEN
COMMIT;
V_NUM := 0;
END IF;
END LOOP;
COMMIT;
END;
/