plsql复制表数据

最新推荐文章于 2024-06-27 16:58:50 发布

kevinwu629

最新推荐文章于 2024-06-27 16:58:50 发布

阅读量6.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： DB oracle 文章标签： generator actor insert list payment c

本文链接：https://blog.youkuaiyun.com/kevinwu629/article/details/6218293

oracle 同时被 2 个专栏收录

19 篇文章

订阅专栏

7 篇文章

订阅专栏

这段PL/SQL代码展示了如何使用游标从EMS_CUSTOMS_BROKER和EMS_CUSTOMS_DETAIL表复制数据到MEMS_CUSTOMS_BROKER和MEMS_CUSTOMS_BROKER_ALIAS表。首先，它从ID_GENERATOR表获取新的ID，然后通过游标遍历源表，插入新记录，并更新ID_GENERATOR表以保持主键连续性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用游标

DECLARE
TYPE cur_type IS REF CURSOR;

mcb_c cur_type;
mcb_id NUMBER(10);
v_created_time DATE;
v_created_by VARCHAR2(30);
v_lastupdated_time DATE;
v_lastupdated_by VARCHAR2(30);
v_code VARCHAR2(10);
v_name VARCHAR2(60);
v_is_in_black_list VARCHAR2(2);

mcba_c cur_type;
mcba_id NUMBER(10);
v_a_created_time DATE;
v_a_created_by VARCHAR2(30);
v_a_code VARCHAR2(10);
v_a_name VARCHAR2(60);
v_broker_id NUMBER(10);

begin
       /*复制数据到CUSTOMS_BROKER*/
       /*应先在ID_GENERATOR表中新增ID KEY的记录*/
       SELECT ID_VALUE INTO mcb_id FROM ID_GENERATOR WHERE ID_KEY='CUSTOMS_BROKER_ID';

       OPEN mcb_c FOR SELECT CREATE_DATETIME, CREATE_BY_ACTOR, UPDATE_DATETIME, UPDATE_BY_ACTOR, CUSTOM_CODE, CUSTOMS_BROKER, BLACK_STATUS
       FROM EMS_CUSTOMS_BROKER WHERE CUSTOM_FLAG = 'C';
       LOOP
              FETCH mcb_c INTO v_created_time, v_created_by, v_lastupdated_time, v_lastupdated_by, v_code, v_name, v_is_in_black_list;
              EXIT WHEN mcb_c%NOTFOUND;

              /*重设CUSTOMS_BROKER主键*/
              mcb_id := mcb_id + 1;
              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, v_code, v_name, CASE WHEN v_is_in_black_list = 'Y' THEN 1 ELSE 0 END, 0,
       v_created_by, CAST(v_created_time AS TIMESTAMP), v_lastupdated_by, CAST(v_lastupdated_time AS TIMESTAMP), 0);

       END LOOP;
       CLOSE mcb_c;
       mcb_id := mcb_id + 5;

       UPDATE ID_GENERATOR SET ID_VALUE = mcb_id WHERE ID_KEY='CUSTOMS_BROKER_ID';
       COMMIT;

       /*复制数据到CUSTOMS_BROKER_ALIAS*/
       /*应先在ID_GENERATOR表中新增ID KEY的记录*/
       SELECT ID_VALUE INTO mcba_id FROM ID_GENERATOR WHERE ID_KEY='CUSTOMS_BROKER_ALIAS_ID';

       OPEN mcba_c FOR SELECT CREATE_DATETIME, CREATE_BY_ACTOR, CUSTOMS_BROKER, CUSTOM_CODE
       FROM EMS_CUSTOMS_DETAIL;
       LOOP
              FETCH mcba_c INTO v_a_created_time, v_a_created_by, v_a_name, v_a_code;
              EXIT WHEN mcba_c%NOTFOUND;

select b.id into v_broker_id from MEMS_CUSTOMS_BROKER b where b.CODE = v_a_code;

              /*重设CUSTOMS_BROKER_ALIAS主键*/
              mcba_id := mcba_id + 1;
              INSERT INTO MEMS_CUSTOMS_BROKER_ALIAS(ID, BROKER_ALIAS, CUSTOMS_BROKER_ID,
       CREATED_BY, CREATED_TIME, VERSION)
              VALUES (mcba_id, v_a_name, v_broker_id,
       v_a_created_by, CAST(v_a_created_time AS TIMESTAMP), 0);

       END LOOP;
       CLOSE mcba_c;
       mcba_id := mcba_id + 5;

       UPDATE ID_GENERATOR SET ID_VALUE = mcba_id WHERE ID_KEY='CUSTOMS_BROKER_ALIAS_ID';
       COMMIT;
end;