熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL語言 >> 正文

在數據庫日漸龐大時進行歸檔的解決思路[2]

2013-11-13 12:33:58  來源: SQL語言 

   procedure:

  CREATE OR REPLACE PROCEDURE archive_emp
  (maxarchiverow number) IS /* 定義歸檔的記錄數*/
  v_crowid archive_rowscrowid%type; /* 存放基表的rowid*/
  intLoop number; /* 記數器*/
  v_primarykey archive_rowsPRIMARYKEY%type;
  CURSOR C_ARCH_EMP IS
  SELECT * FROM ARCHIVE_ROWS; /* 創建游標*/
  BEGIN
  DELETE FROM ARCHIVE_ROWS; /*先清空臨時表*/
  COMMIT;
  INSERT INTO ARCHIVE_ROWS
  SELECT ROWID FROM EMP_B WHERE ROWNUM < MAXARCHIVEROW;
  COMMIT; /*將基表中指定行數的rowid插入臨時表*/
  INTLOOP:=;
  OPEN C_ARCH_EMP;
  LOOP FETCH C_ARCH_EMP INTO V_CROWIDV_PRIMARYKEY;
  INTLOOP:=INTLOOP+;
  EXIT WHEN C_ARCH_EMP%NOTFOUND;
  INSERT INTO EMP_ARCHIVE SELECT * FROM EMP_B WHERE ROWID=V_CROWID;
  /*根據rowid將數值插入到歸檔表中*/
  COMMIT;
  DELETE FROM EMP_B WHERE ROWID=V_CROWID; /*將基表已歸檔數據刪除*/
  COMMIT;
  IF MOD(INTLOOP)= /*以行為界記錄歸檔情況*/
  THEN
  INSERT INTO ARCHIVE_LOG VALUES(EMP_BINTLOOPSYSDATE);
  COMMIT;
  END IF;
  END LOOP;
  END ARCHIVE_EMP;
  /

[]  []  


From:http://tw.wingwit.com/Article/program/SQL/201311/16144.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.