oracle sql*plus set &spool介绍(二)

Oracle spool 用法小结[半转半加]

关于SPOOL(SPOOL是SQLPLUS的命令，不是SQL语法里面的东西。)

对于SPOOL数据的SQL，最好要自己定义格式，以方便程序直接导入,SQL语句如：
select empno||','||ename||','||sal from emp;

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

导出文本数据的建议格式：SQL*PLUS环境设置

               SET NEWPAGE NONE 
                SET HEADING OFF 
                SET SPACE 0 
                SET PAGESIZE 0 
                SET TRIMOUT ON 
                SET TRIMSPOOL ON 
                SET LINESIZE 2500

 

注：LINESIZE要稍微设置大些，免得数据被截断，它应和相应的TRIMSPOOL结合使用防止导出的文本有太多的尾部空格。但是如果LINESIZE设置太大，会大大降低导出的速度，另外在WINDOWS下导出最好不要用PLSQL导出，速度比较慢，直接用COMMEND下的SQLPLUS命令最小化窗口执行。

对于字段内包含很多回车换行符的应该给与过滤，形成比较规矩的文本文件。通常情况下，我们使用SPOOL方法，将数据库中的表导出为文本文件的时候会采用两种方法，如下述：

方法一：(设置分隔符导出) 
        set colsep ','    --设置,为分隔符,符合CSV文件格式
　　set trimspool on --设置导出样式
　　set linesize 220 
　　set pagesize 2000          
　　set newpage NONE 
　　set heading off            
　　set term off 
        set num 18                  
        set feedback off            
　　spool D:/A.CSV    --你要生成的文件路径
　　select * from EMP;  --你要获取数据的表的SQL语句
　　spool off  --导出

 

方法二：(拼接SQL导出)
        set trimspool on 
　　set linesize 220 
　　set pagesize 2000 
　　set newpage none 
　　set heading off 
　　set term off   
　　spool D:/A.CSV 
　　select empno||','||ename||','||sal  from EMP; 
　　spool off

比较以上方法，即方法一采用设定分隔符然后由sqlplus自己使用设定的分隔符对字段进行分割，方法二将分隔符拼接在SELECT语句中，即手工控制输出格式。

在实践中，发现通过方法一导出来的数据具有很大的不确定性，这种方法导出来的数据再由sqlldr导入的时候出错的可能性在95%以上，尤其对大批量的数据表，如100万条记录的表更是如此，而且导出的数据文件大。

而方法二导出的数据文件格式很规整，数据文件的大小可能是方法一的1/4左右。经这种方法导出来的数据文件再由sqlldr导入时，出错的可能性很小，基本都可以导入成功。

因此，实践中我建议大家使用方法二手工去控制spool文件的格式，这样可以减小出错的可能性，避免走很多弯路(为什么会这样呢,没有说明..)

后面还有LINUX的shell脚本写的导出,(LINUX战时没有深入,待学习..)

 

结合之前的HTML导出的方式,看下面一段代码导出比较公整的EXCEL文件:

 

conn SCOTT/TIGER@FZLGFM

set heading off
Set pagesize 0
Set term off
Set feedback off
set linesize 99999
set trimspool off
set term off verify off feedback off pagesize 99999 
spool D:/b.xls
set markup html on  entmap ON spool on preformat off 
select '工号','姓名','薪水','部门号' from dual
union all
select to_char(sal), ename, to_char(sal), to_char(sal) from emp;
spool off

工号姓名薪水部门号7369SMITH800207499ALLEN1600307521WARD1250307566JONES2975207654MARTIN1250307698BLAKE2850307782CLARK2450107788SCOTT400020

 

PS:注意,UNION ALL字段类型要匹配

select '工号','姓名','薪水','部门号' from dual
union all
select to_char(sal), ename, to_char(sal), to_char(sal) from emp;

 

以上是通过客户端实现的导出,再来看看服务器的导出的存储过程:

 

第一步:创建目录和赋权限

--********************************************************************* 
--*** 
--***首先以dba或者是其他具有create directory的用户创建directory，然后授权 
--*** 
--********************************************************************* 
CREATE OR REPLACE DIRECTORY dir_export AS 'd:/oracle'; 
GRANT read,write ON directory dir_export TO scott;

 

第二步:建立导出存储过程:

--********************************************************************* 
--*** 
--***执行导出文本文件的存储过程：p_exp 
--***p_tname:要导出的表明；p_query：where条件，根据这个条件导出，如果为null,则全表导出 
--*** 
--*** 
--*********************************************************************

create or replace procedure p_exp(p_tname varchar2)
as
  v_line  varchar2(2000);                      --记录的数据
  v_sql    varchar2(2000);                      --动态sql变量
  v_fname  varchar2(40);                        --导出数据的文件名字
  cursor cursor_column                          --游标：通过数据字典查找出表有哪些字段
        is select column_name,data_type
        from user_tab_columns where table_name=upper(p_tname);   --获取表字段
  type type_cursor is ref cursor ;
  mycursor type_cursor;        
  outf utl_file.file_type;     --导出文件类型

begin
  v_fname:=p_tname||'.csv';    --导出的文件名和类型
  for col in cursor_column loop
  --判断字段类型,生成对应的数据格式
    if col.data_type='DATE' then
          v_sql:=v_sql||'to_char('||col.column_name||',''yyyy-mm-dd-hh24:mi:ss'')'||''',''';
    else
          v_sql:=v_sql||col.column_name||'||'',''||';
    end if;
  end loop;
  v_sql:=rtrim(v_sql,'||'',''||'); --把拼接字段最后的拼接字符去除
  v_sql:='select '||v_sql||' from '||p_tname;
  dbms_output.put_line(v_sql);
  outf:=utl_file.fopen('DIR_EXPORT',v_fname,'w'); --打开文件夹,创建文件
  open mycursor for v_sql;
  loop
    fetch mycursor into v_line;
    exit when mycursor%notfound;
    --dbms_output.put_line(v_line); --控制台打印每条记录
    utl_file.put_line(outf,v_line);  --将每条记录写入文件
  end loop;
  utl_file.fclose(outf);   --关闭文件读写流
  dbms_output.put_line('导出成功');
end;

 

 

第三布:执行该存储过程得到 EMP.TXT里的如下数据

7369, SMITH ,CLERK ,7902 ,1980-12-17-00:00:00, 800, 200 ,20
7499 ,ALLEN ,SALESMAN, 7698 ,1981-02-20-00:00:00 ,1600 ,300 ,30

..........

 

呵呵,导出CSV成功,,,如果有其它的需求,继续深入,我的需求这些方法都无法满足,期待下一篇.....