• 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> @t1.sql
    SQL> select * from dept;
    
        DEPTNO DNAME      LOC
    ---------- -------------- -------------
        50 IT          CHINA
        10 ACCOUNTING      NEW YORK
        20 RESEARCH      DALLAS
        30 IT          CHICAGO
        40 OPERATIONS      BOSTON
    复制代码

          

      

               

  • 相关阅读:
    Delphi 拖动
    Unknown picture file extension
    鼠标指针形状
    C_FD_PhysRDBMSKinds
    delphichromiumembedded
    delphi使用 DockForm DesignEditors F2613 Unit 'DockForm' not found
    TBitConverter
    sql 存储过程返回值 变量名
    XE7 数据库独立运行需要的文件
    C++Builder 内存泄露检测
  • 原文地址:https://www.cnblogs.com/xieweikai/p/6838077.html
一二三 - 开发者的网上家园