在数据库日渐庞大时进行归档的解决思路[2]

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;

/

[] []