Oracle数据导出spool用法详解

spool可导出select查询的结果集

在实际的工作中，很多时候我们需要将Oracle中查询的数据导出来，生成excel格式的表以便于处理。虽然plsql可以导数据，但是只能手动不太方便，今天讲一下利用spool命令自动导表的脚本。

以emp表为例

先看以下表结构：

2:如何将select * from emp 表生成的数据集合作为一个表导出来呢？
1）我们就可以利用spool命令，代码如下：

 set colsep ',' 
 set heading on 
 set linesize 20000 
 set echo off 
 set feedback off 
 set pagesize 0 
 set termout off 
 set trimout on 
 set trimspool on 
  spool C:/export/test.csv
  select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
  spool off 

2）.将脚本保存为test.sql。
3）.在SQL plus中执行,结果如下

4）数据就出来了，但是没有表头，spool命令导出的表头也很有问题，这时可以利用伪表制作表头。

select '工号','姓名','职位','上级工号','入职日期',
'工资','奖金','部门编号' from dual;

结果如下：

5）有时数据导出来，行和列会出现格式不齐，我们可以采用将所有字段拼接在一起的方法来应对这个问题。

 set colsep ',' 
 set heading on 
 set linesize 20000 
 set echo off 
 set feedback off 
 set pagesize 0 
 set termout off 
 set trimout on 
 set trimspool on 
  spool C:/export/test3.csv
  select '工号','姓名','职位','上级工号','入职日期',
'工资','奖金','部门编号' from dual;
select empno||','||ename||','||job||','||mgr||','||hiredate
||','||sal||','||comm||','||deptno from emp;
  spool off 

6）展示结果如下：

spool命令参数

set colsep ‘,’;　　　//域输出分隔符
set echo off;　　　　//显示start启动的脚本中的每个sql命令，缺省为on
set feedback off;　　//回显本次sql命令处理的记录条数，缺省为on，设置显示“已选择XX行”
set heading off;　　 //输出域标题，字段的名称，缺省为on
SET LINESIZE 2500; //每行允许的最大字符数，设置大些，免得数据被截断，但不宜过大，太大会大大降低导出的速度(注意必须与trimspool结合使用防止导出的文本有太多的尾部空格)
set newpage 1; //设置页与页之间的分隔{1|n|NONE};当值为0时在每页开头有一个小的黑方框;当值为n时在页和页之间隔着n个空行;当为none时，会在页和页之间没有任何间隔；
set newp none; //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的
set num 18; //设置数字的长度，如果不够大，则用科学记数法显示
set numwidth 12;　 //输出number类型域长度，缺省为10
SET NULL text; //显示时,用text值代替NULL值
set pagesize 2000;　 //输出每页行数，页面大小，缺省为24,为了避免分页，可设定为0
set serveroutput on; //设置允许显示输出类似dbms_output;–编写存储过程时,大多会将必要的信息输出；
SET SPACE 0;
set term off; //不在屏幕上输出执行结果
set termout off;　　 //显示脚本中的命令的执行结果，缺省为on
set timing on; //显示每个sql语句花费的执行时间，设置显示“已用时间：XXXX”
set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为off
set trimspool on;　　//去除重定向（spool）输出每行的拖尾空格，缺省为off
set verify off //是否显示替代变量被替代前后的语句

spool的参数设置参考以下文章
链接: [link](https://blog.csdn.net/zwj1030711290/article/details/47170557）.