利用PL/SQL从Oracle数据库导出和导入数据

下面是“利用PL/SQL从Oracle数据库导出和导入数据”的完整攻略。

1. PL/SQL的介绍

PL/SQL是面向对象的程序设计语言，用于与Oracle数据库进行交互。PL/SQL具有结构化编程的能力，可以编写存储过程和触发器等程序对象。

2. 导出数据的基本步骤

导出数据是从数据库中把数据取出来并保存到本地文件中。PL/SQL提供了dbms_datapump这个包来实现数据的导出和导入。

下面是导出数据的基本步骤：

（1）连接到Oracle数据库，并打开PL/SQL开发工具，例如SQL Developer。

（2）运行以下代码创建一个数据泵作业：

DECLARE
  dp_handle  NUMBER;
BEGIN
  dp_handle := DBMS_DATAPUMP.OPEN(operation => 'EXPORT', job_mode => 'FULL', job_name => 'EXPORT_JOB', version => 'COMPATIBLE');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT.LOG', 'LOG');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT.DMP', 'DMP');
  DBMS_DATAPUMP.METADATA_FILTER(dp_handle, 'SCHEMA_EXPR', 'IN (''SCHEMA_NAME'')');
  DBMS_DATAPUMP.DATA_FILTER(dp_handle, 'INCLUDE', 'TABLE', 'IN (''TABLE_NAME'')');
  DBMS_DATAPUMP.START_JOB(dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(dp_handle);
  DBMS_DATAPUMP.DETACH(dp_handle);
END;
/

其中，SCHEMA_NAME和TABLE_NAME需要替换为实际的模式名称和表名称。

执行该代码可以创建一个名称为EXPORT_JOB的导出作业，并将导出数据保存到EXPORT.DMP文件中。

3. 导入数据的基本步骤

导入数据是把之前导出的数据文件中的数据重新插入到数据库中。导入数据的步骤也可以使用dbms_datapump这个包来实现。

下面是导入数据的基本步骤：

（1）连接到Oracle数据库，并打开PL/SQL开发工具，例如SQL Developer。

（2）运行以下代码创建一个数据泵作业：

DECLARE
  dp_handle  NUMBER;
BEGIN
  dp_handle := DBMS_DATAPUMP.OPEN(operation => 'IMPORT', job_mode => 'FULL', job_name => 'IMPORT_JOB', version => 'COMPATIBLE');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'IMPORT.LOG', 'LOG');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT.DMP', 'DMP');
  DBMS_DATAPUMP.METADATA_FILTER(dp_handle, 'SCHEMA_EXPR', 'IN (''SCHEMA_NAME'')');
  DBMS_DATAPUMP.DATA_FILTER(dp_handle, 'INCLUDE', 'TABLE', 'IN (''TABLE_NAME'')');
  DBMS_DATAPUMP.START_JOB(dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(dp_handle);
  DBMS_DATAPUMP.DETACH(dp_handle);
END;
/

其中，SCHEMA_NAME和TABLE_NAME需要替换为实际的模式名称和表名称。需要注意的是，导入数据之前需要确保数据库中不存在相同的表。

执行该代码可以创建一个名称为IMPORT_JOB的导入作业，并从EXPORT.DMP文件中读取数据重新插入到数据库中。

4. 示例

假设有一个名为TEST的模式，其中包含一个名为EMP的表，我们需要导出该表的数据并保存到本地文件中，然后重新导入数据到另一个数据库中。下面给出两个示例说明。

示例1

导出数据：

DECLARE
  dp_handle  NUMBER;
BEGIN
  dp_handle := DBMS_DATAPUMP.OPEN(operation => 'EXPORT', job_mode => 'FULL', job_name => 'EXPORT_EMP', version => 'COMPATIBLE');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT_EMP.LOG', 'LOG');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT_EMP.DMP', 'DMP');
  DBMS_DATAPUMP.METADATA_FILTER(dp_handle, 'SCHEMA_EXPR', 'IN (''TEST'')');
  DBMS_DATAPUMP.DATA_FILTER(dp_handle, 'INCLUDE', 'TABLE', 'IN (''EMP'')');
  DBMS_DATAPUMP.START_JOB(dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(dp_handle);
  DBMS_DATAPUMP.DETACH(dp_handle);
END;
/

执行该代码将导出TEST模式下的EMP表，并保存到EXPORT_EMP.DMP文件中。

导入数据：

DECLARE
  dp_handle  NUMBER;
BEGIN
  dp_handle := DBMS_DATAPUMP.OPEN(operation => 'IMPORT', job_mode => 'FULL', job_name => 'IMPORT_EMP', version => 'COMPATIBLE');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'IMPORT_EMP.LOG', 'LOG');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT_EMP.DMP', 'DMP');
  DBMS_DATAPUMP.METADATA_FILTER(dp_handle, 'SCHEMA_EXPR', 'IN (''TEST'')');
  DBMS_DATAPUMP.DATA_FILTER(dp_handle, 'INCLUDE', 'TABLE', 'IN (''EMP'')');
  DBMS_DATAPUMP.START_JOB(dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(dp_handle);
  DBMS_DATAPUMP.DETACH(dp_handle);
END;
/

执行该代码将从EXPORT_EMP.DMP文件中读取数据，并重新插入到数据库中。

示例2

假设现在需要把从EMP表中删除工资小于3000的记录，同时需要备份删除前的数据。具体步骤如下：

（1）备份数据

DECLARE
  dp_handle  NUMBER;
BEGIN
  dp_handle := DBMS_DATAPUMP.OPEN(operation => 'EXPORT', job_mode => 'FULL', job_name => 'EXPORT_EMP', version => 'COMPATIBLE');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT_EMP_BEFORE_DELETE.LOG', 'LOG');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT_EMP_BEFORE_DELETE.DMP', 'DMP');
  DBMS_DATAPUMP.METADATA_FILTER(dp_handle, 'SCHEMA_EXPR', 'IN (''TEST'')');
  DBMS_DATAPUMP.DATA_FILTER(dp_handle, 'INCLUDE', 'TABLE', 'IN (''EMP'')', 'QUERY_EXPR', 'WHERE SALARY < 3000');
  DBMS_DATAPUMP.START_JOB(dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(dp_handle);
  DBMS_DATAPUMP.DETACH(dp_handle);
END;
/

执行该代码将导出工资小于3000的EMP表数据，并保存到EXPORT_EMP_BEFORE_DELETE.DMP文件中。

（2）删除数据

DELETE FROM TEST.EMP WHERE SALARY < 3000;
COMMIT;

执行该代码可以删除工资小于3000的EMP表记录，并进行数据库事务提交。

（3）重新导入数据

DECLARE
  dp_handle  NUMBER;
BEGIN
  dp_handle := DBMS_DATAPUMP.OPEN(operation => 'IMPORT', job_mode => 'FULL', job_name => 'IMPORT_EMP', version => 'COMPATIBLE');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'IMPORT_EMP.LOG', 'LOG');
  DBMS_DATAPUMP.ADD_FILE(dp_handle, 'EXPORT_EMP_BEFORE_DELETE.DMP', 'DMP');
  DBMS_DATAPUMP.METADATA_FILTER(dp_handle, 'SCHEMA_EXPR', 'IN (''TEST'')');
  DBMS_DATAPUMP.DATA_FILTER(dp_handle, 'INCLUDE', 'TABLE', 'IN (''EMP'')');
  DBMS_DATAPUMP.START_JOB(dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(dp_handle);
  DBMS_DATAPUMP.DETACH(dp_handle);
END;
/

执行该代码将从EXPORT_EMP_BEFORE_DELETE.DMP文件中读取数据，并重新插入到数据库中。