在生产中常会遇到需要将数量比较大的表值导入到本地文本文件中 方法有很多种比较常用的就是spool命令: 要输出符合要求格式的数据文件只需在select时用字符连接来规范格式比如有如下表
SQL>; select idusernamepassword from myuser;//测试表
John
Jack
Rose
Joe
Tom
Jordan
要输出符合John这样的数据格式就用select id||||username||||password|| from myuser这样的语句
SQL>; select id||||username||||password|| from myuser;
John
Jack
写个下面这样的脚本就行可以输出符合要求格式的数据至文件中不会含有其它不需要东西只有数据部分
－－脚本文件名为expmyusrsql存数据的文件名为e:\exptxt
set echo on 是否显示执行的命令内容 set feedback off 是否显示 * rows selected set heading off 是否显示字段的名称set verify off 是否显示替代变量被替代前后的语句filset trimspool off 去字段空格
set pagesize 页面大小set linesize //linesize设定尽量根据需要来设定大了生成的文件也大
define fil= e:\exptxt
prompt *** Spooling to &fil
spool &fil
select id||||username||||||password|| from myuser;
spool off;
－－执行过程
SQL>; @e:\expmyusrsql
*** Spooling to e:\exptxt
John
Jack
Rose
Joe
Tom
Jordan
检查可知结果符合要求
·Oracle SPOOL的两种方法之对比
通常情况下我们使用SPOOL方法将数据库中的表导出为文本文件的时候会采用两种方法如下述
方法一采用以下格式脚本
set colsep 设置列分隔符
set trimspool on
set linesize
set pagesize
set newpage
set heading off
set term 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语句中即手工控制输出格式
在实践中我发现通过方法一导出来的数据具有很大的不确定性这种方法导出来的数据再由sql ldr导入的时候出错的可能性在%以上尤其对大批量的数据表如万条记录的表更是如此而且导出的数据文件狂大
而方法二导出的数据文件格式很规整数据文件的大小可能是方法一的/左右经这种方法导出来的数据文件再由sqlldr导入时出错的可能性很小基本都可以导入成功
因此实践中我建议大家使用方法二手工去控制spool文件的格式这样可以减小出错的可能性避免走很多弯路
