spool命令

概述最近工作中，需对数据进行比对。在此之前，则需将数据导出。此处，利用SQL*Plus的spool命令

最近工作中，需对数据进行比对。在此之前，则需将数据导出。想到以前用过的spool命令，实验一番，分享如下：

需建sql执行脚本，内容如下：

set Feedback off   --关掉行数显示
set heading off     --关掉标题行
set termout off    --关掉终端显示
set echo off         --关掉回显
set pagesize 0     --去掉头顶的空行
set trims on        --去掉空字符
spool /home/oracle/test.txt
select * from dept;
spool off

执行该脚本，最后/home/oracle/test.txt显示的内容即为表dept的数据：

50 IT CHINA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 IT CHICAGO
40 OPERATIONS BOSTON

没有开头的sql> select * from dept;和结尾的sql> spool off

结合官档，总结一下sql*Plus关于格式输出的命令：

SET ECHO {ON | OFF}                       Controls whether the START command Lists each command in a script as the command is executed.

SET Feed[BACK] {6 | n | ON | OFF}    displays the number of records returned by a query when a query selects at least n records.

SET HEA[DING] {ON | OFF}                Controls printing of column headings in reports.

SET liN[ESIZE] {80 | n}                    Sets the total number of characters that sql*Plus displays on one line before beginning a new line.

SET LONG {80 | n}                            Sets maximum wIDth (in bytes) for displaying LONG,BLOB,Bfile,CLOB,NCLOB and XMLType values; and for                                                      copying LONG values.

SET PAGES[IZE] {14 | n}                   Sets the number of lines in each page. 

SET TERM[OUT] {ON | OFF}               Controls the display of output generated by commands executed from a script.

SET TI[ME] {ON | OFF}                      Controls the display of the current time.

SET TIMI[NG] {ON | OFF}                   Controls the display of timing statistics.

SET TRims[POol] {ON | OFF}             Determines whether sql*Plus puts trailing blanks at the end of each spooled line.

SET TRIM[OUT] {ON | OFF}                Determines whether sql*Plus puts trailing blanks at the end of each displayed line.

SET VER[IFY] {ON | OFF}                   Controls whether sql*Plus Lists the text of a sql statement or PL/sql command before and after sql*Plus

                                                      replaces substitution variables with values.

SET WRA[P] {ON | OFF}                     Controls whether sql*Plus truncates the display of a SELECTed row if it is too long for the current line wIDth

PS：关于SET ECHO语句，从定义上看有点费解，下面我们来看一下实验结果：

一、 SET ECHO OFF

      1> 编辑sql脚本t1.sql如下：

          set echo off

          select * from dept;

       2> 执行该脚本       

sql> @t1.sql    DEPTNO Dname      LOC---------- -------------- -------------    50 IT          CHINA    10 ACCOUNTING      NEW YORK    20 RESEARCH      DALLAS    30 IT          CHICAGO    40 OPERATIONS      BOSTON

二、 SET ECHO ON

      1> 编辑sql脚本t1.sql如下：

          set echo on

          select * from dept;

      2> 执行该脚本

sql.sqlsqlselect * from dept;    DEPTNO Dname      LOC40 OPERATIONS      BOSTON

 

      

  

 

           

 

总结

以上是内存溢出为你收集整理的spool命令全部内容，希望文章能够帮你解决spool命令所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。