Oracle存储过程

芸尚非

已于 2024-01-30 09:12:25 修改

阅读量156

点赞数 1

分类专栏：数据库文章标签： oracle 数据库 sql

于 2022-08-11 11:11:56 首次发布

本文链接：https://blog.csdn.net/qq_36223335/article/details/126281385

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

分批从一张表导入另一张备份表，使用游标

/*调用存储过程*/
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;