某Q友问题—为何sqlplus中new_value后续的..到底为何含义-CSDN博客

前言

spool产生的输出文件如果用到&变量，其形式为spool &var..log,这2个.到底含义是什么呢？本文我们测试下这个

测试结论

1,new_value即把select from 中的查询结果可以传递给new_value指定的参数

2,&filedate为变量，此变量用于spool中，后面必须跟2个..，最终扩展中为log,显示出来才是文件名称.log

3,&filedate后面2个. 表示1个. ,而3个.表示2个.,其实就类似于转义字符

而如果后跟1外.,直接就忽略了这个.

测试明细

通过new_value参数化filedate

SQL> col filedate new_value filedate

把系统日期存在到参数filedate

SQL> select to_char(sysdate,'yyyymmddhh24miss') filedate from dual;

FILEDATE

--------------

20150615082504

把上述参数filedate用于spool输出的文件名称中，注意后面是2个..

SQL> spool insert_after&filedate..log

SQL> select * from dual;

SQL> spool off;

产生的spool文件为insert_after加上系统日期.log

SQL> ! ls -ltr

-rw-r--r-- 1 oracle oinstall 286 06-15 08:25 insert_after20150615082504.log

再看下如果是1个.会如何

SQL> spool insert_after&filedate.log

SQL> select * from dual;

1个.则spool生成的文件名称为insert_after系统日期及log，且自动生成文件扩展名.lst

SQL> spool off;

SQL> ! ls -ltr

-rw-r--r-- 1 oracle oinstall 286 06-15 08:26 insert_after20150615082504log.lst

查阅官方手册,获取spool具体用法

SQL*Plus? User's Guide and Reference
Release 11.2

SPOOL

Syntax

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

Stores query results in a file, or optionally sends the file to a printer.

Terms

file_name[.ext]

Represents the name of the file to which you wish to spool. SPOOL followed by file_name begins spooling displayed output to the named file. If you do not specify an extension, SPOOL uses a default extension (LST or LIS on most systems). The extension is not appended to system files such as /dev/null and /dev/stderr.

CRE[ATE]

Creates a new file with the name specified.

REP[LACE]

Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file. This is the default behavior.

APP[END]

Adds the contents of the buffer to the end of the file you specify.

OFF

Stops spooling.

OUT

Stops spooling and sends the file to your computer's standard (default) printer. This option is not available on some operating systems.

Enter SPOOL with no clauses to list the current spooling status.

Usage

To spool output generated by commands in a script without displaying the output on the screen, use SET TERMOUT OFF. SET TERMOUT OFF does not affect output from commands that run interactively.

You must use quotes around file names containing white space.

To create a valid HTML file using SPOOL APPEND commands, you must use PROMPT or a similar command to create the HTML page header and footer. The SPOOL APPEND command does not parse HTML tags.

SET SQLPLUSCOMPAT[IBILITY] to 9.2 or earlier to disable the CREATE, APPEND and SAVE parameters. See SQL*Plus Compatibility Matrix to determine what functionality is controlled by the SET SQLPLUSCOMPAT[IBILITY] command.

主要看上述标色的手册部分，可知SPOOL默认的输出文件如不添加扩展名，默认为lst或者lis

SQL> spool off

SQL> !ls -lrt

-rw-r--r-- 1 oracle oinstall 291 06-15 08:32 onlytest.lst

SQL> host rm -rf *

SQL> !ls -rt

如果后面跟3个.，最终显示2个.,可见..表示1个.

SQL> spool insert_after&filedate...log

SQL> select 1 from dual;

----------

SQL> spool off

SQL> !ls -trt