同一数据库中两个表中复制数据

最新推荐文章于 2024-08-20 06:52:37 发布

原创最新推荐文章于 2024-08-20 06:52:37 发布 · 897 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #generator #insert #exception #actor #payment

oracle 专栏收录该内容

19 篇文章

订阅专栏

本文提供了一个使用PL/SQL进行Oracle数据库数据迁移的示例，包括如何从一个表读取数据并插入到另一个表，同时处理自增主键。

DECLARE
Cursor mcb_c is select * from EMS_CUSTOM_BROKER;
mcb mcb_c%rowType;
mcb_id NUMBER(10);
begin
       begin
         /*应先在ID_GENERATOR表中新增ID KEY的记录*/
         SELECT ID_VALUE INTO mcb_id FROM ID_GENERATOR WHERE ID_KEY='CUSTOMS_BROKER_ID';
         EXCEPTION
         WHEN NO_DATA_FOUND THEN
             mcb_id := 1;
             INSERT INTO ID_GENERATOR(ID_KEY, ID_VALUE) VALUES('CUSTOMS_BROKER_ID', mcb_id);
         COMMIT;
       end;
       begin
         OPEN mcb_c;
         LOOP
            FETCH mcb_c INTO mcb;
            EXIT WHEN mcb_c%NOTFOUND;

            INSERT INTO MEMS_CUSTOMS_BROKER(ID, CODE, NAME,
                        IS_IN_BLACK_LIST, OWED_PAYMENT_BILL_COUNT,
                        CREATED_BY, CREATED_TIME,
                        LASTUPDATED_BY, LASTUPDATED_TIME, VERSION)
                VALUES (mcb_id, mcb.CUSTOM_CODE, mcb.CUSTOM_NAME,
                       CASE WHEN mcb.BLACK_STATUS = 'Y' THEN 1 ELSE 0 END, 0,
                       mcb.CREATE_BY_ACTOR, CAST(mcb.CREATE_DATETIME AS TIMESTAMP),
                       mcb.UPDATE_BY_ACTOR, CAST(mcb.UPDATE_DATETIME AS TIMESTAMP), 0);
     /*重设CUSTOMS_BROKER主键*/
            mcb_id := mcb_id + 1;
         END LOOP;
         CLOSE mcb_c;

         UPDATE ID_GENERATOR SET ID_VALUE = mcb_id WHERE ID_KEY='CUSTOMS_BROKER_ID_1';

         dbms_output.put_line('import success');
         COMMIT;
       end;
end;