plsql复制表数据
来源:互联网 发布:java setscale 编辑:程序博客网 时间:2024/05/21 09:17
用游标
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;
- plsql复制表数据
- plSQL复制数据的方法
- plSQL复制数据的方法
- plsql仅仅复制表结构
- plsql developer如何导入导出表结构和数据以及如何复制表结构和数据?
- 复制plsql—develop表中的数据出现中文乱码的解决
- PLSQL操作Oracle复制表(DBLink)
- plsql 复制 乱码
- excel复制到plsql
- 表复制和数据复制
- plsql导出表结构表数据
- plsql 导出表数据和表结构
- plsql 导出表和表数据
- plsql导入excel数据oracle表中
- plsql导入excel数据oracle表中
- plsql 导入导出表、数据、序列、视图
- 使用plsql导出oracle表的数据
- plsql 导入导出表、数据、序列、视图
- 分页几种方式
- JAVA应用XFire框架来实现WebServie的大文件传输功能之一(下载)
- 英语听力资料
- android aidl
- Koogra 操作 Excel2007 VB.NET 版
- plsql复制表数据
- DBLINK的简单运用说明
- 32位/64位机上常用数据类型字节数(C语言)
- 从用户体验的角度来分析一些在线图片、幻灯片处理产品
- 如何同时启动多个Tomcat服务器(转)--fedora下验证也可以使用
- C# Tip -- 巧用??运算符
- Flex 屏幕截图实现代码
- 将16进制转换为十进制
- 儿童绘本