Oracle数据库数据、存储过程和函数的导出方法详解

Oracle数据库数据、存储过程和函数的导出方法详解

本文详细介绍了Oracle数据库中导出数据、存储过程和函数的多种方法，包括使用PL/SQL Developer、SQL*Plus等工具，以及通过脚本实现定时导出等。文章内容详尽，包含具体步骤和示例代码，适合对Oracle数据库有需求的技术人员阅读。

Oracle数据库查询结果导出到Excel的方法

1. 打开Oracle数据库，点击登录选项，点击文件选项，点击新建选项，最后点击SQL窗口。

2. 在SQL窗口中编写SQL语句，查询出想要导出的数值。

3. 在查询结果处，不要选中数据，将鼠标放到空白单元格处进行鼠标右键操作，找到“复制到Excel”选项。

4. 在“复制到Excel”中选择保存格式为xls，打开后即可得到Excel形式的数据。

5. 默认的保存路径显示在Excel名称上，根据路径即可打开保存的文件。

Oracle导出Excel的多种方法

方法一：使用PL/SQL Developer

执行 File -> New Report Window。在SQL标签中写入需要的SQL语句，点击执行或按快捷键F8，会显示查询结果。在右侧工具栏中，可以选择“另存为HTML”、“复制为HTML”、“导出结果”，其中“导出结果”按钮可以导出Excel文件、CSV文件、TSV文件、XML文件。

方法二：使用SQL*Plus

1. 创建 main.sql 脚本，用于设置环境并调用具体功能脚本：

   set linesize 200
   set term off verify off feedback off pagesize 999
   set markup html on entmap ON spool on preformat off
   spool d:/tables.xls
   @d:/get_tables.sql
   spool off
   exit
   

2. 创建 get_tables.sql 脚本，实现具体功能：

   select owner, table_name, tablespace_name, blocks, last_analyzed
   from all_tables order by 1,2;
   

3. 执行并获得输出：

   sqlplus "/ as sysdba" @d:/main.sql
   

方法三：使用PL/SQL Developer的简单方法

打开PL/SQL Developer工具，执行 File -> New SQL Window，输入SQL语句，点击工具栏“执行”按钮或按快捷键F8。会显示出结果集。点击结果集的左上方，可全部选中结果集，然后右键选择“复制”，直接粘贴到Excel文件中。

方法四：使用SQL*Plus的复杂方法

在SQL*Plus中，执行想要的SQL，把结果集copy到文本文件中（或者直接用spool命令直接输入到文本文件中），把不必要的字符、空格替换成逗号","，然后另存为CSV文件，最后在用Excel另存为EXL文件。

将Excel文件数据导入到Oracle表中的方法

方法一：使用PL/SQL Developer

1. 复制整个工作簿中的数据到某个表中。点击Excel工作区左上角，全部选中数据，Ctrl+C复制整个工作簿中的数据。在PL/SQL Developer中，编辑表格表的数据，点击数据展示区左上角，直接粘贴。

2. 复制某列的数据。选中Excel某一列的数据，复制，选中Oracle某个表的某一列，直接粘贴。

方法二：使用SQL Loader

1. 录入Excel测试表格，test.xls。

2. 另存为.csv格式。

3. 创建SQL*Loader控制文件test.ctl：

   Load data
   Infile 'c:/test.csv'
   insert Into table test Fields terminated by ','(column1, column2, column3, column4, column5)
   

4. 在数据库中建立对应的测试表test：

   create table test (
       column1 Varchar2(10),
       column2 Varchar2(10),
       column3 Varchar2(10),
       column4 Varchar2(10),
       column5 Varchar2(10)
   )
   

5. 执行导入命令：

   Sqlldr userid = system/manager control='C:/test.ctl'
   

方法三：创建Oracle外部表

将Excel文件另存为CSV文件a.csv，然后创建一个外部表t，数据指向a.csv。然后根据外部表创建一个普通的表：

create table a as select * from t

方法四：拼接SQL语句

在Excel中，把数据拼接成如下SQL语句：

insert into emp values('1','2','3');
insert into emp values('4','5','6');
insert into emp values('7','8','9');

copy出以上SQL，执行即可。

定时导出数据的方法

1. 创建数据库日志表导出为文本文件的脚本 tabout.ctl 或 tabout.sql：

   SET NEWPAGE NONE
   SET HEADING OFF
   SET TERM OFF
   SET SPACE 0
   SET PAGESIZE 0
   SET TRIMOUT ON
   SET TRIMSPOOL ON
   SET LINESIZE 2500
   set feedback off
   set echo off
   SET VERIFY OFF
   column v_date new_value filename;
   select to_char(sysdate,'yyyymmdd') || '.log' v_date from dual;
   spool ..\\HttpLog\\filename
   select id||','||name from orcluser.test;
   spool off;
   exit;
   

2. 创建调用该脚本的 logout.bat 文件：

   mkdir ..\\HttpLog\\
   C:\\oracle\\ora92\\bin\\sqlplus orcluser/orcl @C:Oracleout.ctl
   

3. 通过Windows定时任务实现自动定期执行。

Oracle数据导出的两种方式

使用exp imp导出导入

exp：

1. 将数据库orcl完全导出：

   exp system/manager@orcl file=d:\\orcl_bak.dmp full=y
   

2. 将数据库中system用户的表导出：

   exp system/manager@orcl file=d:\\system_bak.dmp owner=system
   

3. 将数据库中表table1，table2导出：

   exp system/manager@orcl file=d:\\table_bak.dmp tables=(table1,table2)
   

4. 将数据库中的表customer中的字段mobile以"139"开头的数据导出：

   exp system/manager@orcl file=d:\\mobile_bak.dmp tables=customer query="where mobile like '139%'"
   

imp：

1. 将备份文件bak.dmp导出数据库：

   imp system/manager@orcl file=d:\\bak.dmp
   

2. 将备份文件bak.dmp中的表table1导入：

   imp system/manager@orcl file=d:\\bak.dmp tables=(table1)
   

使用Oracle数据泵expdp impdp导出导入

1. 为输出路径建立一个数据库的directory对象：

   create or replace directory dumpdir as 'd:\\';
   

2. 给将要进行数据导出的用户授权访问：

   grant read,write on directory dumpdir to test_expdp;
   

3. 将数据导出：

   expdp test_expdp/test_expdp directory=dumpdir dumpfile=test_expdp_bak.dmp logfile=test_expdp_bak.log schemas=test_expdp
   

4. 给将要进行数据导入的用户授权访问：

   grant read,write on directory dumpdir to test_impdp;
   

5. 将数据导入：

   impdp test_impdp/impdp directory=dumpdir dumpfile=test_expdp_bak.dmp remap_schema=test_expdp:test_impdp
   

Oracle函数和存储过程的导出

使用Oracle的一般都装了PL/SQL，对象窗口，找到对应的函数或者存储过程，右键查看或编辑。

1. 单个导出：

   set echo off ;
   set heading off ;
   set feedback off ;
   spool d:\\tmp.txt
   select text from user_source ;
   spool off;
   

2. 使用PL/SQL菜单 Tools -> Export user objects...，对弹出的dialog中选择user和output file，选中要导出的内容，点击export导出。