1、创建数据库连接,假设数据库连接名为uat,此示例用于从uat数据库中把指定表的数据 导入 到当前数据库中。
2、假设需要到入数据的表为 tnt_report_theme,执行以下脚本:
DECLARE
v_sql VARCHAR2(2000) := NULL;
BEGIN
------------------------------------------------------------------------------
-- 禁用约束
------------------------------------------------------------------------------
FOR tc IN
(SELECT table_name,
constraint_name
FROM user_constraints
WHERE lower(table_name) IN ('tnt_report_theme')
AND constraint_type = 'R'
)
LOOP
v_sql := 'alter table ' || tc.table_name || ' disable constraints ' || tc.constraint_Name;
EXECUTE immediate v_sql ;
END LOOP;
------------------------------------------------------------------------------
-- 导入数据
------------------------------------------------------------------------------
FOR tbl IN
(SELECT table_name
FROM user_tables@uat
WHERE lower(table_name) IN ('tnt_report_theme')
)
LOOP
----------------------------------------------------------------------------
-- 删除数据
----------------------------------------------------------------------------
v_sql := 'delete from ' || tbl.table_name;
EXECUTE immediate v_sql;
COMMIT;
----------------------------------------------------------------------------
-- 导入数据
----------------------------------------------------------------------------
v_sql := 'insert into ' || tbl.table_name || ' select * from ' || tbl.table_name || '@uat';
EXECUTE immediate v_sql ;
COMMIT;
END LOOP;
------------------------------------------------------------------------------
-- 重启约束
------------------------------------------------------------------------------
FOR rtc IN
(SELECT table_name,
constraint_name
FROM user_constraints
WHERE lower(table_name) IN ('tnt_report_theme')
AND constraint_type = 'R'
)
LOOP
v_sql := 'alter table ' || rtc.table_name || ' enable constraints ' || rtc.constraint_name;
EXECUTE immediate v_sql;
END LOOP;
END;