DECLARE
V_END_DATE VARCHAR2(30);–归档数据临界时间,使用归档当天的零点
PROCEDURE PRO_ARCHIVE_PK(L_TNAME VARCHAR2,L_SQL VARCHAR2,L_PK VARCHAR2)
IS
–归档有主键的表,L_TNAME为表名,L_SQL 为归档语句,L_PK为主键
L_RESULT NUMBER(3);
L_COLS VARCHAR2(3000);
L_COLS2 VARCHAR2(3000);
L_COLS3 VARCHAR2(3000);
L_STR VARCHAR2(3000);
L_ERROR_CODE NUMBER(10);
L_ERROR_MESSAGE VARCHAR2(200);
BEGIN
–检查目标表是否存在,表列是否一致
SELECT A.C_ROWS-B.C_ROWS INTO L_RESULT FROM (SELECT COUNT(*) C_ROWS FROM USER_TAB_COLS WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’) A,
(SELECT COUNT(*) C_ROWS FROM USER_TAB_COLS@MESDB WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’) B;
IF(L_RESULT<>0) THEN
INSERT INTO T_ARCHIVE_LOG(M_DATE,M_STATUS,M_ERROR) VALUES(SYSDATE,0,’归档库缺少对应的归档表 ‘||L_TNAME||’ 或者两边表列不一致’);
COMMIT;
ELSE
SELECT TO_CHAR(WM_CONCAT(COLUMN_NAME)) INTO L_COLS FROM USER_TAB_COLS WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’;
SELECT TO_CHAR(WM_CONCAT(‘A.’||COLUMN_NAME)) INTO L_COLS2 FROM USER_TAB_COLS WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’;
SELECT TO_CHAR(WM_CONCAT(L_TNAME||’.’||COLUMN_NAME)) INTO L_COLS3 FROM USER_TAB_COLS WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’;
–建立归档临时表
SELECT COUNT(*) INTO L_RESULT FROM USER_TABLES WHERE TABLE_NAME=SUBSTR(‘T_ARC_’||L_TNAME,1,30);
IF(L_RESULT<>0) THEN
L_STR:=’DROP TABLE ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||’ PURGE’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
END IF;
L_STR:=’CREATE TABLE ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||’ as select * from ‘||L_TNAME||’ WHERE ROWNUM<1′;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–建立非归档临时表
SELECT COUNT(*) INTO L_RESULT FROM USER_TABLES WHERE TABLE_NAME=SUBSTR(‘T_NOARC_’||L_TNAME,1,30);
IF(L_RESULT<>0) THEN
L_STR:=’DROP TABLE ‘||SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||’ PURGE’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
END IF;
L_STR:=’CREATE TABLE ‘||SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||’ as select * from ‘||L_TNAME||’ WHERE ROWNUM<1′;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–开始插入临时表
–归档临时表
L_STR:=’INSERT /*+ append */ INTO ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||'(‘||L_COLS||’) SELECT ‘||L_COLS3||’ FROM ‘||L_SQL;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–非归档临时表
L_STR:=’INSERT /*+ append */ INTO ‘||SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||'(‘||L_COLS||’) SELECT ‘||L_COLS2
||’ FROM ‘||L_TNAME||’ A,’||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||’ B WHERE A.’||L_PK
||’=B.’||L_PK||'(+) AND B.’||L_PK||’ IS NULL’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–清理原表
L_STR:=’TRUNCATE TABLE ‘||L_TNAME;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–回插非归档数据并清理临时表
L_STR:=’INSERT /*+ append */ INTO ‘||L_TNAME||'(‘||L_COLS||’) SELECT ‘||L_COLS||’ FROM ‘||SUBSTR(‘T_NOARC_’||L_TNAME,1,30);
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–归档表数据插入
L_STR:=’INSERT /*+ append */ INTO ‘||L_TNAME||’@MESDB(‘||L_COLS||’) SELECT ‘||L_COLS||’ FROM ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30);
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
L_STR:=’DROP TABLE ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||’ PURGE’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
L_STR:=’DROP TABLE ‘||SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||’ PURGE’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
L_ERROR_CODE := SQLCODE;
L_ERROR_MESSAGE := SQLERRM;
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS,M_ERROR)
VALUES(L_TNAME,SYSDATE,L_STR,0,TO_CHAR(L_ERROR_CODE)||’: ‘|| L_ERROR_MESSAGE);
COMMIT;
END;
PROCEDURE PRO_ARCHIVE_NONPK(L_TNAME VARCHAR2,L_SQL VARCHAR2)
IS
–归档没有主键的表,L_TNAME为表名,L_SQL 为归档语句
L_RESULT NUMBER(3);
L_COLS VARCHAR2(3000);
L_COLS2 VARCHAR2(3000);
L_COLS3 VARCHAR2(3000);
L_STR VARCHAR2(3000);
L_ERROR_CODE NUMBER(10);
L_ERROR_MESSAGE VARCHAR2(200);
BEGIN
–检查目标表是否存在,表列是否一致
SELECT A.C_ROWS-B.C_ROWS INTO L_RESULT FROM (SELECT COUNT(*) C_ROWS FROM USER_TAB_COLS WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’) A,
(SELECT COUNT(*) C_ROWS FROM USER_TAB_COLS@MESDB WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’) B;
IF(L_RESULT<>0) THEN
INSERT INTO T_ARCHIVE_LOG(M_DATE,M_STATUS,M_ERROR) VALUES(SYSDATE,0,’归档库缺少对应的归档表 ‘||L_TNAME||’ 或者两边表列不一致’);
COMMIT;
ELSE
SELECT TO_CHAR(WM_CONCAT(COLUMN_NAME)) INTO L_COLS FROM USER_TAB_COLS WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’;
SELECT TO_CHAR(WM_CONCAT(‘A.’||COLUMN_NAME)) INTO L_COLS2 FROM USER_TAB_COLS WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’;
SELECT TO_CHAR(WM_CONCAT(SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||’.’||COLUMN_NAME)) INTO L_COLS3 FROM USER_TAB_COLS WHERE TABLE_NAME=L_TNAME AND HIDDEN_COLUMN=’NO’;
–建立归档临时表
SELECT COUNT(*) INTO L_RESULT FROM USER_TABLES WHERE TABLE_NAME=SUBSTR(‘T_ARC_’||L_TNAME,1,30);
IF(L_RESULT<>0) THEN
L_STR:=’DROP TABLE ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||’ PURGE’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
END IF;
L_STR:=’CREATE TABLE ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||’ as select rownum pk_col,a.* from ‘||L_TNAME||’ a WHERE ROWNUM<1′;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–建立非归档临时表
SELECT COUNT(*) INTO L_RESULT FROM USER_TABLES WHERE TABLE_NAME=SUBSTR(‘T_NOARC_’||L_TNAME,1,30);
IF(L_RESULT<>0) THEN
L_STR:=’DROP TABLE ‘||SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||’ PURGE’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
END IF;
L_STR:=’CREATE TABLE ‘||SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||’ as select rownum pk_col,a.* from ‘||L_TNAME||’ a’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–开始插入临时表
–归档临时表
L_STR:=’INSERT /*+ append */ INTO ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||'(PK_COL,’||L_COLS||’) SELECT PK_COL,’||L_COLS3
||’ FROM ‘||REPLACE(L_SQL,L_TNAME,SUBSTR(‘T_NOARC_’||L_TNAME,1,30));
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–清理原表
L_STR:=’TRUNCATE TABLE ‘||L_TNAME;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–回插非归档数据并清理临时表
L_STR:=’INSERT /*+ append */ INTO ‘||L_TNAME||'(‘||L_COLS||’) SELECT ‘||L_COLS2||’ FROM ‘
||SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||’ A,’||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||’ B WHERE A.PK_COL=B.PK_COL(+) AND B.PK_COL IS NULL’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
–归档表数据插入
L_STR:=’INSERT /*+ append */ INTO ‘||L_TNAME||’@MESDB(‘||L_COLS||’) SELECT ‘||L_COLS||’ FROM ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30);
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
L_STR:=’DROP TABLE ‘||SUBSTR(‘T_ARC_’||L_TNAME,1,30)||’ PURGE’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
L_STR:=’DROP TABLE ‘||SUBSTR(‘T_NOARC_’||L_TNAME,1,30)||’ PURGE’;
EXECUTE IMMEDIATE L_STR;
–DBMS_OUTPUT.PUT_LINE(L_STR);
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS)
VALUES(L_TNAME,SYSDATE,L_STR,1);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
L_ERROR_CODE := SQLCODE;
L_ERROR_MESSAGE := SQLERRM;
INSERT INTO T_ARCHIVE_LOG(M_T_NAME,M_DATE,M_SQL,M_STATUS,M_ERROR)
VALUES(L_TNAME,SYSDATE,L_STR,0,TO_CHAR(L_ERROR_CODE)||’: ‘|| L_ERROR_MESSAGE);
COMMIT;
END;
BEGIN
V_END_DATE:=’2017-10-01 00:00:00′;
–这个地方非常重要,一定要扣掉90天
V_END_DATE:=TO_CHAR((TO_DATE(V_END_DATE,’YYYY-MM-DD HH24:MI:SS’)-90),’YYYY-MM-DD HH24:MI:SS’);
IF(LENGTH(TRIM(V_END_DATE))<1) THEN
RAISE_APPLICATION_ERROR(-20001,’请先指定归档临界时间!’,TRUE);
END IF;
–1 ok
PRO_ARCHIVE_PK(‘CR_WEIGHTING’,’CR_WEIGHTING
WHERE SN IN ((SELECT VALUE FROM CR_FFSERIALNUMBER WHERE UNITID IN (SELECT UNITID FROM CR_FFPACKAGE WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))) UNION (SELECT DISTINCT KEYUNITBARCODE FROM KEYUNITSCAN WHERE SUBSTR(KEYUNITBARCODE,1,2)=”8S” AND UNITID IN (SELECT UNITID FROM CR_FFPACKAGE WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))))’,’ID’);
DBMS_OUTPUT.PUT_LINE(‘INPUT归档完毕!’);
–2
PRO_ARCHIVE_PK(‘INPUT’,’INPUT WHERE SN IN ((SELECT VALUE FROM CR_FFSERIALNUMBER WHERE UNITID IN (SELECT UNITID FROM CR_FFPACKAGE WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))) UNION (SELECT DISTINCT KEYUNITBARCODE FROM KEYUNITSCAN WHERE SUBSTR(KEYUNITBARCODE,1,2)=”8S” AND UNITID IN (SELECT UNITID FROM CR_FFPACKAGE WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))))’,’ID’);
DBMS_OUTPUT.PUT_LINE(‘INPUT归档完毕!’);
–3
PRO_ARCHIVE_PK(‘OUTPUT’,’OUTPUT WHERE SN IN ((SELECT VALUE FROM CR_FFSERIALNUMBER WHERE UNITID IN (SELECT UNITID FROM CR_FFPACKAGE WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))) UNION (SELECT DISTINCT KEYUNITBARCODE FROM KEYUNITSCAN WHERE SUBSTR(KEYUNITBARCODE,1,2)=”8S” AND UNITID IN (SELECT UNITID FROM CR_FFPACKAGE WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))))’,’ID’);
DBMS_OUTPUT.PUT_LINE(‘INPUT归档完毕!’);
–4
PRO_ARCHIVE_NONPK(‘INPUTDELETE’,’INPUTDELETE WHERE SN IN ((SELECT VALUE FROM CR_FFSERIALNUMBER WHERE UNITID IN (SELECT UNITID FROM CR_FFPACKAGE WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))) UNION (SELECT DISTINCT KEYUNITBARCODE FROM KEYUNITSCAN WHERE SUBSTR(KEYUNITBARCODE,1,2)=”8S” AND UNITID IN (SELECT UNITID FROM CR_FFPACKAGE WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))))’);
DBMS_OUTPUT.PUT_LINE(‘INPUTDELETE归档完毕!’);
–5
PRO_ARCHIVE_NONPK(‘OUTPUTDELETE’,’OUTPUTDELETE
WHERE SN IN ((
SELECT VALUE FROM CR_FFSERIALNUMBER
WHERE UNITID IN (
SELECT UNITID FROM CR_FFPACKAGE
WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))) UNION (SELECT DISTINCT KEYUNITBARCODE FROM KEYUNITSCAN
WHERE SUBSTR(KEYUNITBARCODE,1,2)=”8S” AND UNITID IN (SELECT UNITID FROM CR_FFPACKAGE
WHERE CARTONNO IN(SELECT DISTINCT BOXNO FROM STOCKINDETAIL WHERE DT<TO_DATE(”’||V_END_DATE||”’,”YYYY-MM-DD HH24:MI:SS”)’||’))))’);
DBMS_OUTPUT.PUT_LINE(‘OUTPUTDELETE归档完毕!’);
END;
/