下面是“利用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文件中读取数据,并重新插入到数据库中。
5. 结论
本文详细讲解了如何利用PL/SQL从Oracle数据库导出和导入数据,并给出了两个实际示例。通过这些示例,我们了解了PL/SQL的基本使用方法,并且掌握了如何使用PL/SQL来进行数据备份和恢复的操作。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:利用PL/SQL从Oracle数据库导出和导入数据 - Python技术站