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

墨蓝 2022-07-12 10:55 534阅读 0赞

### 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

<table style="width: 835px; border-collapse: collapse;"> 
 <tbody> 
 <tr style="height: 12.75pt;"> 
 <td style="width: 125pt; height: 12.75pt; background-color: white; border: black 0.5pt solid;">工号</td> 
 <td style="border-right: black 0.5pt solid; border-top: black 0.5pt solid; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">姓名</td> 
 <td style="border-right: black 0.5pt solid; border-top: black 0.5pt solid; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;">薪水</td> 
 <td style="border-right: black 0.5pt solid; border-top: black 0.5pt solid; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;">部门号</td> 
 </tr> 
 <tr style="height: 12.75pt;"> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black 0.5pt solid; width: 125pt; border-bottom: black 0.5pt solid; height: 12.75pt; background-color: white;" align="right">7369</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">SMITH</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">800</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">20</td> 
 </tr> 
 <tr style="height: 12.75pt;"> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black 0.5pt solid; width: 125pt; border-bottom: black 0.5pt solid; height: 12.75pt; background-color: white;" align="right">7499</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">ALLEN</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">1600</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">30</td> 
 </tr> 
 <tr style="height: 12.75pt;"> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black 0.5pt solid; width: 125pt; border-bottom: black 0.5pt solid; height: 12.75pt; background-color: white;" align="right">7521</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">WARD</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">1250</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">30</td> 
 </tr> 
 <tr style="height: 12.75pt;"> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black 0.5pt solid; width: 125pt; border-bottom: black 0.5pt solid; height: 12.75pt; background-color: white;" align="right">7566</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">JONES</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">2975</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">20</td> 
 </tr> 
 <tr style="height: 12.75pt;"> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black 0.5pt solid; width: 125pt; border-bottom: black 0.5pt solid; height: 12.75pt; background-color: white;" align="right">7654</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">MARTIN</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">1250</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">30</td> 
 </tr> 
 <tr style="height: 12.75pt;"> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black 0.5pt solid; width: 125pt; border-bottom: black 0.5pt solid; height: 12.75pt; background-color: white;" align="right">7698</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">BLAKE</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">2850</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">30</td> 
 </tr> 
 <tr style="height: 12.75pt;"> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black 0.5pt solid; width: 125pt; border-bottom: black 0.5pt solid; height: 12.75pt; background-color: white;" align="right">7782</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">CLARK</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">2450</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">10</td> 
 </tr> 
 <tr style="height: 12.75pt;"> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black 0.5pt solid; width: 125pt; border-bottom: black 0.5pt solid; height: 12.75pt; background-color: white;" align="right">7788</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 216pt; border-bottom: black 0.5pt solid; background-color: white;">SCOTT</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 125pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">4000</td> 
 <td style="border-right: black 0.5pt solid; border-top: black; border-left: black; width: 160pt; border-bottom: black 0.5pt solid; background-color: white;" align="right">20</td> 
 </tr> 
 </tbody> 
</table>

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成功,,,如果有其它的需求,继续深入,我的需求这些方法都无法满足,期待下一篇.....