Oracle数据库利用spool导出数据例子

在工作中，有时需要将表中的数据查询出来，保存成txt格式或者csv格式，以便于做数据检索与分析。

在超过几万行或者几十万行数据，乃至上百万行数据时，我们使用PLSQL在查询所有记录时会报临时空间不足的错误。

这时我们需要使用spool命令将数据导出成txt或者csv格式。

为了保证数据格式的完整性，我们需要在使用spool前加入一些参数。

spool常用的设置

set colsep' '; //域输出分隔符
set echo off; //显示start启动的脚本中的每个sql命令，缺省为on
set feedback off; //回显本次sql命令处理的记录条数，缺省为on
set heading off; //输出域标题，缺省为on
set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。
set termout off; //显示脚本中的命令的执行结果，缺省为on
set trimout on; //去除标准输出每行的拖尾空格，缺省为off
set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off

通常情况下，我们使用SPOOL方法，将数据库中的表导出为文本文件的时候会采用两种方法，如下述：

方法一：采用以下格式脚本

 
set colsep '|' ------设置列分隔符
set trimspool on
set linesize 20000
set pagesize 0
set newpage 1
set heading off
set term off
spool 路径+文件名
select * from tablename;
spool off
 
方法二：采用以下脚本

 
set trimspool on
set linesize 20000
set pagesize 0
set newpage 1
set heading off
set term off
spool 路径+文件名
select col1 ||'|'|| col2 ||'|'|| col3 ||'|'|| col4 from tablename;
spool off
 
spool的其它参数

 
set colsep' ';　　　　 //-域输出分隔符
set newp none            //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的
set echo off;　　　　 //显示start启动的脚本中的每个sql命令，缺省为on
set echo on               //设置运行命令是是否显示语句
set feedback on;       //设置显示“已选择XX行”
set feedback off;　     //回显本次sql命令处理的记录条数，缺省为on即去掉最后的 "已经选择10000行"
set heading off;　　   //输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据
set pagesize 0;　　    //输出每页行数，缺省为24,为了避免分页，可设定为0。
set linesize 80;　　    //输出一行字符个数，缺省为80
set numwidth 12;　    //输出number类型域长度，缺省为10
set termout off;　　   //显示脚本中的命令的执行结果，缺省为on
set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为off
set trimspool on;　　//去除重定向（spool）输出每行的拖尾空格，缺省为off
set serveroutput on;  //设置允许显示输出类似dbms_output
set timing on;          //设置显示“已用时间：XXXX”
set autotrace on-;    //设置允许对执行的sql进行分析
set verify off                     //可以关闭和打开提示确认信息old 1和new 1的显示.
 
上面是用脚本对数据进行导出操作的方式，使用Navicat Premium数据库客户端工具，对于大数据的导入导出就非常的方便了，不需要再编写那么多脚本，也不用考虑到导出的数据格式问题