分批从一张表导入另一张备份表,使用游标
call TABLE1_TRANSFER_TO_BAK('2022-07-01 00:00:00', '2022-07-02 00:00:00');
create or replace procedure TABLE1_TRANSFER_TO_BAK(
START_TIME IN varchar2,
END_TIME IN varchar2
) AS
begin
DECLARE
cursor cur is select * from TABLE1 t1 where not exists (select 1 from TABLE1_BAK t2 where
t1.id=t2.id) and t1.startTime >= to_date(START_TIME, 'YYYY-MM-DD HH24:MI:SS') and t1.startTime <= to_date(END_TIME, 'YYYY-MM-DD HH24:MI:SS');
TYPE rec IS TABLE OF cur%ROWTYPE;
recs rec;
begin
open cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
INSERT INTO TABLE1_BAK ("ID","CREATED_TIME","MODIFIED_TIME","AUTHFLAG","CLIENTFILENAME","CLIENTIP","COMPRESSFLAG","CONTLEN","EBCDICFLAG","ENDTIME","FILEEXISTS","FILEINDEX","FILEMSGFLAG","FILENAME","FILERENAMECTRL","FILERETMSG","FILESIZE","FILEVERSION","LASTPIECE","NODELIST","NODENAME","OFFSET","PIECENUM","SCRTFLAG","SERVERIP","SERVERNAME","STARTTIME","SUSS","SYSNAME","TARFILENAME","TARSYSNAME","TRANCODE","UNAME","NANO","ERRCODE","ORIFILENAME","FLOWNO","BYCLIENT") VALUES recs(i);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
delete from TABLE1 t1 where t1.startTime >= to_date(START_TIME, 'YYYY-MM-DD HH24:MI:SS') and t1.startTime <= to_date(END_TIME, 'YYYY-MM-DD HH24:MI:SS');
END;
END;