--其中F_GET_PKGSTR('batchid', PI_PCKBED)是获取参数值的方法
--存储过程用法
CREATE OR REPLACE PROCEDURE P_MOVER_DATA (
IN PI_PCKBED VARCHAR(32672),IN PI_PCKEND VARCHAR(32672))
BEGIN
DECLARE SQL1 VARCHAR(200);
DECLARE SQL2 VARCHAR(200);
DECLARE SQL3 VARCHAR(200);
DECLARE SQL4 VARCHAR(200);
DECLARE SQL5 VARCHAR(200);
DECLARE SQL6 VARCHAR(200);
DECLARE SQL7 VARCHAR(200);
DECLARE SQL8 VARCHAR(200);
DECLARE SQL9 VARCHAR(200);
DECLARE SQL10 VARCHAR(200);
DECLARE SQL11 VARCHAR(200);
DECLARE SQL12 VARCHAR(200);
--备份原始流水表
SET SQL1='SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,TRANBR, ACCTBR,'
+'ITEMCD, CRCYCD, IOFLAG, CENTCD, PRSNCD, CUSTCD, PRDUCD,PRLNCD, ACCTNO,'
+'TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD,SOURDT, SOURSQ,'
+'SOURST, SRVCSQ, TOITEM, ASSIS0, ASSIS1, ASSIS2, ASSIS3,ASSIS4, ASSIS5,'
+'ASSIS6, ASSIS7, ASSIS8, ASSIS9, DEALST, PRCSCD, ITEMNA,PRCSNA, STRKST,'
+'STRKDT, STRKSQ, CRCYSD, TRANEQ, TAXBST, DEALMG, BATCHID FROM KL_GLI_VCHR'
+'WHERE TRANDT<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND TRANDT>='+F_GET_PKGSTR('batchid', PI_PCKBED);
SET SQL2='KL_GLI_VCHR_H';
CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL1||') OF CURSOR INSERT INTO '||SQL2);
--备份特殊处理后的流水表
SET SQL3='SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,TRANBR, ACCTBR,'
+'ITEMCD, CRCYCD, IOFLAG, CENTCD, PRSNCD, CUSTCD, PRDUCD,PRLNCD, ACCTNO,'
+'TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD,SOURDT, SOURSQ,'
+'SOURST, SRVCSQ, TOITEM, ASSIS0, ASSIS1, ASSIS2, ASSIS3,ASSIS4, ASSIS5,'
+'ASSIS6, ASSIS7, ASSIS8, ASSIS9, DEALST, PRCSCD, ITEMNA,PRCSNA, STRKST,'
+'STRKDT, STRKSQ, CRCYSD, TRANEQ, TAXBST, DEALMG, BATCHID FROM GLI_VCHR'
+'WHERE TRANDT<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND TRANDT>='+F_GET_PKGSTR('batchid', PI_PCKBED);
SET SQL4='GLI_VCHR_H';
CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL3||') OF CURSOR INSERT INTO '||SQL4);
--备份会计分录表
SET SQL5='SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,TRANBR, ACCTBR,'
+'ITEMCD, CRCYCD, CENTCD, PRSNCD, CUSTCD, PRDUCD, PRLNCD,ACCTNO, TRANTP,'
+'AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD, TOITEM,ASSIS0, ASSIS1,'
+'ASSIS2, ASSIS3, ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8,ASSIS9, TRANST,'
+'TRSDAM, CRCYSD FROM TXA_VCHR WHERE TRANDT<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND'
+'TRANDT>='+F_GET_PKGSTR('batchid', PI_PCKBED);
SET SQL6='TXA_VCHR_H';
CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL5||') OF CURSOR INSERT INTO '||SQL6);
--备份非分期还款记录表
SET SQL7='SELECT RD80DATE, RD80DPNOA, RD80DPNO, RD80CLITYP,RD80DUEBNO,'
+'RD80CUR, RD80DATEO, RD80OWNTYP, RD80STANO, RD80PAYCHA, RD80PBTYPE,RD80PBMODE,'
+'RD80ACIDP, RD80AMTP, RD80AMTPCA, RD80OPR, RD80STAN, RD80SEQ, BATCHID FROM '
+'RDPF80 WHERE RD80DATE<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND RD80DATE>='+F_GET_PKGSTR('batchid', PI_PCKBED);
SET SQL8='RDPF80_H';
CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL7||') OF CURSOR INSERT INTO '||SQL8);
--备份分期还款记录表
SET SQL7='SELECT RD90DATE, RD90DPNOA, RD90DPNO, RD90CLITYP, RD90DUEBNO,'
+'RD90CUR, RD90CACNT, RD90DATEO, RD90STANO, RD90PAYCHA, RD90ACIDP, RD90ACIDRP, RD90PRITYP,'
+'RD90PPTYP, RD90AMTPP, RD90AMTPCA, RD90ACIDA, RD90ACIDRA, RD90ACRTYP, RD90PATYP, RD90AMTPA,'
+'RD90AMTACA, RD90OPR, RD90STAN, RD90SEQ, BATCHID FROM RDPF90 WHERE RD90DATE<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND '
+'RD90DATE>='+F_GET_PKGSTR('batchid', PI_PCKBED);
SET SQL8='RDPF90_H';
CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL9||') OF CURSOR INSERT INTO '||SQL10);
--备份日志表
SET SQL7='SELECT BSNSDT, USERCD, TRANTI, LOGMSG FROM COM_LOGM WHERE BSNSDT<='+F_GET_PKGSTR('batchid', PI_PCKEND)+' AND '
+'BSNSDT>='+F_GET_PKGSTR('batchid', PI_PCKBED);
SET SQL8='COM_LOGM_H';
CALL SYSPROC.ADMIN_CMD('LOAD FROM ('||SQL11||') OF CURSOR INSERT INTO '||SQL12);
END;
--直接在客户端中执行
COMMIT
ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
--备份原始流水表
INSERT INTO KL_GLI_VCHR_H SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,
TRANBR, ACCTBR, ITEMCD, CRCYCD, IOFLAG, CENTCD, PRSNCD, CUSTCD, PRDUCD,
PRLNCD, ACCTNO, TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD,
SOURDT, SOURSQ, SOURST, SRVCSQ, TOITEM, ASSIS0, ASSIS1, ASSIS2, ASSIS3,
ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8, ASSIS9, DEALST, PRCSCD, ITEMNA,
PRCSNA, STRKST, STRKDT, STRKSQ, CRCYSD, TRANEQ, TAXBST, DEALMG, BATCHID
FROM KL_GLI_VCHR WHERE TRANDT<=F_GET_PKGSTR('batchid', PI_PCKEND) AND
TRANDT>=F_GET_PKGSTR('batchid', PI_PCKBED);
COMMIT;
COMMIT
ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
--备份特殊处理后的流水表
INSERT INTO GLI_VCHR_H SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,
TRANBR, ACCTBR, ITEMCD, CRCYCD, IOFLAG, CENTCD, PRSNCD, CUSTCD, PRDUCD,
PRLNCD, ACCTNO, TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD,
SOURDT, SOURSQ, SOURST, SRVCSQ, TOITEM, ASSIS0, ASSIS1, ASSIS2, ASSIS3,
ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8, ASSIS9, DEALST, PRCSCD, ITEMNA,
PRCSNA, STRKST, STRKDT, STRKSQ, CRCYSD, TRANEQ, TAXBST, DEALMG FROM
GLI_VCHR WHERE TRANDT<=F_GET_PKGSTR('batchid', PI_PCKEND) AND
TRANDT>=F_GET_PKGSTR('batchid', PI_PCKBED);
COMMIT;
COMMIT
ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
--备份会计分录表
INSERT INTO TXA_VCHR_H SELECT STACID, SYSTID, TRANDT, TRANSQ, VCHRSQ,
TRANBR, ACCTBR, ITEMCD, CRCYCD, CENTCD, PRSNCD, CUSTCD, PRDUCD, PRLNCD,
ACCTNO, TRANTP, AMNTCD, TRANAM, SMRYTX, EXCHCN, EXCHUS, USERCD, TOITEM,
ASSIS0, ASSIS1, ASSIS2, ASSIS3, ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8,
ASSIS9, TRANST, TRSDAM, CRCYSD FROM TXA_VCHR WHERE TRANDT<=F_GET_PKGSTR('batchid', PI_PCKEND)
AND TRANDT>=F_GET_PKGSTR('batchid', PI_PCKBED);
COMMIT;
COMMIT
ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
--备份非分期还款记录表
INSERT INTO RDPF80_H SELECT RD80DATE, RD80DPNOA, RD80DPNO, RD80CLITYP,
RD80DUEBNO, RD80CUR, RD80DATEO, RD80OWNTYP, RD80STANO, RD80PAYCHA, RD80PBTYPE,
RD80PBMODE, RD80ACIDP, RD80AMTP, RD80AMTPCA, RD80OPR, RD80STAN, RD80SEQ, BATCHID
FROM RDPF80 WHERE RD80DATE<=F_GET_PKGSTR('batchid', PI_PCKEND)
AND RD80DATE>=F_GET_PKGSTR('batchid', PI_PCKBED);
COMMIT;
COMMIT
ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
--备份分期还款记录表
INSERT INTO RDPF90_H SELECT RD90DATE, RD90DPNOA, RD90DPNO, RD90CLITYP, RD90DUEBNO,
RD90CUR, RD90CACNT, RD90DATEO, RD90STANO, RD90PAYCHA, RD90ACIDP, RD90ACIDRP, RD90PRITYP,
RD90PPTYP, RD90AMTPP, RD90AMTPCA, RD90ACIDA, RD90ACIDRA, RD90ACRTYP, RD90PATYP, RD90AMTPA,
RD90AMTACA, RD90OPR, RD90STAN, RD90SEQ, BATCHID FROM RDPF90 WHERE RD90DATE<=F_GET_PKGSTR('batchid', PI_PCKEND)
AND RD90DATE>=F_GET_PKGSTR('batchid', PI_PCKBED);
COMMIT;
COMMIT
ALTER TABLE KL_GLI_VCHR_H ACTIVATE NOT LOGGED INTIALLY
--备份日志表
INSERT INTO COM_LOGM_H SELECT BSNSDT, USERCD, TRANTI, LOGMSG FROM COM_LOGM
WHERE BSNSDT<=F_GET_PKGSTR('batchid', PI_PCKEND) AND BSNSDT>=F_GET_PKGSTR('batchid', PI_PCKBED);
COMMIT;