关于SPOOL(SPOOL是SQLPLUS的命令不是SQL语法里面的东西)
对于SPOOL数据的SQL最好要自己定义格式以方便程序直接导入SQL语句如
select taskindex|||||commonindex|||||tasktype|||||to_number(to_char(sysdateYYYYMMDD)) from ssrv_sendsms_task;
spool常用的设置
set colsep ; //域输出分隔符
set echo off;//显示start启动的脚本中的每个sql命令缺省为on
set feedback off;//回显本次sql命令处理的记录条数缺省为on
set heading off; //输出域标题缺省为on
set pagesize ;//输出每页行数缺省为为了避免分页可设定为
set termout off; //显示脚本中的命令的执行结果缺省为on
set trimout on;//去除标准输出每行的拖尾空格缺省为off
set trimspool on;//去除重定向（spool）输出每行的拖尾空格缺省为off
导出文本数据的建议格式
SQL*PLUS环境设置SET NEWPAGE NONE
SET HEADING OFF
SET SPACE
SET PAGESIZE
SET TRIMOUT ON
SET TRIMSPOOL ON
SET LINESIZE
注LINESIZE要稍微设置大些免得数据被截断它应和相应的TRIMSPOOL结合使用防止导出的文本有太多的尾部空格但是如果LINESIZE设置太大会大大降低导出的速度另外在WINDOWS下导出最好不要用PLSQL导出速度比较慢直接用COMMEND下的SQLPLUS命令最小化窗口执行
对于字段内包含很多回车换行符的应该给与过滤形成比较规矩的文本文件通常情况下我们使用SPOOL方法将数据库中的表导出为文本文件的时候会采用两种方法如下述
方法一采用以下格式脚本
set colsep | 设置|为列分隔符
set trimspool on
set linesize
set pagesize
set newpage
set heading off
set term off
set num
set feedback off
spool 路径+文件名
select * from tablename;
spool off
方法二采用以下脚本
set trimspool on
set linesize
set pagesize
set newpage
set heading off
set term off
spool 路径+文件名
select col||||col||||col||||col|| from tablename;
spool off
比较以上方法即方法一采用设定分隔符然后由sqlplus自己使用设定的分隔符对字段进行分割方法二将分隔符拼接在SELECT语句中即手工控制输出格式
在实践中发现通过方法一导出来的数据具有很大的不确定性这种方法导出来的数据再由sqlldr导入的时候出错的可能性在%以上尤其对大批量的数据表如万条记录的表更是如此而且导出的数据文件狂大
而方法二导出的数据文件格式很规整数据文件的大小可能是方法一的/左右经这种方法导出来的数据文件再由sqlldr导入时出错的可能性很小基本都可以导入成功
因此实践中我建议大家使用方法二手工去控制spool文件的格式这样可以减小出错的可能性避免走很多弯路
自测例将ssrv_sendsms_task表中的数据导出到文本(数据库Oracle i 操作系统 SUSE LINUX Enterprise Server )
spool_testsh脚本如下
#!/bin/sh
DB_USER=zxdbm_ismp #DB USER
DB_PWD=zxin_smap #DB PASSWORD
DB_SERV=zx___ #DB SERVICE NAME
send_day=`sqlplus s $DB_USER/$DB_PWD@$DB_SERV<<! #s 参数屏蔽打印到屏幕上的其他信息只显示sql执行信息
set trimspool on
set linesize
set pagesize
set newpage
set heading off
set term off
spool sp_testtxt
select taskindex|||||commonindex|||||tasktype|||||to_number(to_char(sysdateYYYYMMDD)) from ssrv_sendsms_task;
spool off
/
!`
执行/spool_testsh后生成sp_testtxt内容如下
|||
|||
|||
|||
|||
|||
|||
set time off echo off head off
set trimspool on
set termout off
set pagesize
set colsep
set trims on
set trimout on;
set feedback off
spool d:\testcsv
select rownum||||empno||||ename from emp;
spool off
