Oracle笔记之spool介绍及常用设置

拼搏现实的明天。 2022-03-01 09:12 875阅读 0赞

> 要写脚本，用到了Spool，之前没用过，于是学习了一下，在此做个总结

##### Oracle spool是什么 #####

Spool是Oracle快速导出数据的工具，它是sqlplus的一个控制指令，使用spool和sql语句组合可以动态生成一些脚本或数据。  
spool并不算SQL命令，只是一种指令。

##### spool的作用 #####

spool命令的作用就是用来保存文件和设定是否打印执行结果，以及以规范打印执行结果的形式，方便之后程序的导入。

##### spool的常用设置 #####

<table> 
 <thead> 
  <tr> 
   <th>命令</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>set closep ‘,’</td> 
   <td>设置域输出分隔符</td> 
  </tr> 
  <tr> 
   <td>set echo off</td> 
   <td>设置是否显示执行的SQL命令，默认为on</td> 
  </tr> 
  <tr> 
   <td>set heading off</td> 
   <td>设置是否显示数据的字段名，默认为on</td> 
  </tr> 
  <tr> 
   <td>set feedback off</td> 
   <td>设置是否显示处理记录的条数，默认为on</td> 
  </tr> 
  <tr> 
   <td>set linesize 1000</td> 
   <td>设置每行允许输出的最大字符,默认为80，这个应该根据需要设置,太小会造成该行显示不全，太大了会导致导出的文件变大</td> 
  </tr> 
  <tr> 
   <td>set pagesize 0</td> 
   <td>设置每页允许输出的最大行数，默认为24，避免分页可设定为0</td> 
  </tr> 
  <tr> 
   <td>set trimspool on</td> 
   <td>设置是否去除每行末尾的空格，默认为off</td> 
  </tr> 
  <tr> 
   <td>set termout on</td> 
   <td>设置是否显示脚本中的命令执行结果，默认为on</td> 
  </tr> 
  <tr> 
   <td>set newwidth 20</td> 
   <td>设置输出number类型域的长度，默认为10</td> 
  </tr> 
  <tr> 
   <td>set timing on</td> 
   <td>设置是否显示“已用时间：xxx”,默认为on</td> 
  </tr> 
 </tbody> 
</table>

使用spool导出文件的方式如下：

set colsep '|'
    set trimspool on 　　
    set linesize 120 　　
    set pagesize 2000 　　
    set newpage 1 　　
    set heading off 　
    set term off
    set num 18
    set feedback off 　　
    spool 路径+文件名 　　
    select * from 表名; 　　
    spool off

下面是一个平时用到的示例：

set colsep '|'
    set trimspool on
    set linesize 32767
    set pagesize 0
    set heading off
    set termout on
    set numwidth 20
    set feedback on
    set timing on
    set echo on
    --当发生错误时回滚
    WHENEVER SQLERROR EXIT ROLLBACK
    WHENEVER OSERROR  EXIT ROLLBACK
    spool 文件夹名/文件名，格式可以是：序号-表名-for-用途.文件后缀
    --查询当前时间
    select sysdate  from  dual;
    --备份需要变更的表数据，不要只变更部分数据却对全表进行备份
    create table 备份的表名，格式可以是：用户名.t当前时间_用途_序号 as select * from 原表名 where 查询条件;
    
    --查询当前时间
    select sysdate from dual;
    --查询结束，关闭文件
    spool off