14.2.4　使用SQL*Plus格式化查询结果

在SQL*Plus中查询数据时经常会碰到的问题就是由于查询的列过多无法在一行全部显示出来，结果看起来非常混乱。例如，查询dba_users中的全部数据。查询结果如图14.19所示。

图　14.19　查询dba_users中全部数据

从查询结果中可以看出由于查询出的列比较多，显示时根本看不出具体的查询结果，只是知道查询出了41条数据。为了能够在SQLPlus中显示出比较清晰的查询结果，SQLPlus提供了多种设置查询结果显示的方法。下面讲解几个比较常用的设置命令。

1.使用COLUMN命令

使用COLUMN命令既可设置显示列的别名也可以设置显示列的格式。下面分别讲解COLUMN命令的两种用法。

（1）使用COLUMN命令设置别名

使用COLUMN命令设置别名与在SQL语句中设置别名的方法类似。具体语法如下：

COLUMN oldname HEADING newname

【语法说明】

❑oldname：要查询表中的列名。

❑newname：新的列名。

【示例10】设置别名

下面就使用上面的命令，查询dba_users中的用户名（username）和用户ID（user_id），并把username的列名使用COLUMN命令换成“用户名”。查询结果如图14.20所示。

图　14.20　使用COLUMN命令设置查询结果的列名

在此，username列的列名即被替换成“用户名”，设置查询结果操作成功。

（2）使用COLUMN命令格式化数据显示的格式

在使用SQLPlus查询数据时经常会遇到需要格式化显示的数字格式，在SQLPlus中仍然可以使用COLUMN命令完成。具体语法如下：

COLUMN column_name FORMAT dataformat

【语法说明】

❑column_name：格式化查询结果的列名。

❑dataformat：格式化后显示的格式。设置数据格式一般规则如表14.1所示。

【示例11】格式化查询结果

下面使用COLUMN命令格式化查询结果，查询dba_users中的用户名（username）和用户ID（user_id），并把user_id的列名显示的格式写成{0，0}。格式化后的查询结果如图14.21所示。

图　14.21　使用COLUMN命令格式化查询结果

在此，通过COLUMN命令格式化后，已经把user_id的显示格式改写成0,0的格式了。

说明　使用COLUMN命令设置列的格式之后，这个列的格式是会一直保持的，除非重新设置该列的格式或者取消该列的格式。取消列的格式可以用COLUMN column_name clear命令来完成。例如，如果要取消对username的列设置，结果如图14.22所示。

图　14.22　取消查询结果的格式

2.使用SET命令设置格式

使用SET命令设置查询结果的格式也是经常会用到的，使用SET命令可以设置每一页显示的行数、设置每行显示的字符数、显示查询数据所用的时间、设置查询结果是否显示列标题、设置查询结果是否显示“已选择行数”等。

（1）使用SET命令设置查询结果的行数

在使用SQLPlus查询数据时，经常是把所有的数据都显示在整个页面上，这就造成了页面显示数据非常乱，无法识别的问题。在SQLPlus中提供对每页显示行数的设置。具体命令如下：

SET PAGESIZE n

这里，n代表每页显示的行数，默认在每页显示的行数是24。

除了设置每页显示的行数之外，还可以设置每页之间的空格数，这样就使每页之间都有间隔，更方便数据库管理员查看数据。具体的命令如下：

SET NEWPAGE n

这里，n代表每页之间间隔的空格数。

【示例12】设置查询结果显示的行数

下面就利用上面的两个命令完成查询结果的设置。从dba_users表中查询用户名（username），设置每页显示5行，每页之间间隔1个空格。具体操作如图14.23所示。

图　14.23　设置查询结果显示效果

在此，可以看出每页显示的记录都是2而不是5，这是因为使用set pagesize 5设置的并不是一页中所显示数据表中的记录行数，而是还要包括每页显示的标题以及页之间的间隔。

如果在查询数据之前要查看一下当前在SQL*Plus中每页显示行数以及页之间的空格，可以通过下面的命令来完成：

SHOW PAGESIZE

SHOW NEWPAGE

【示例13】查询显示页的信息

下面利用上面的两个查看命令，查看当前SQL*Plus中PAGESIZE与NEWPAGE的值。查询结果如图14.24所示。

图　14.24　显示页的信息

说明　如果将每页显示行数设置成0，那么就不会显示列标题；如果将每页之间的间隔设置成0，那么页与页会直接连上。

（2）设置每行显示的字符数

在设置了每页显示行数的基础上，也可以对每行显示的字符数进行设置。具体设置每行显示字符数的命令如下：

SET LINESIZE n

这里，n代表每行显示的字符数，默认显示的字符数是80。

显示当前SQL*Plus中的LINESIZE，使用的命令如下：

SHOW LINESIZE

【示例14】设置每行显示字符数

下面利用LINESIZE的设置完成对查询结果的每行显示字符数的设置。从dba_users表中查询用户名（username）、密码（password）、用户ID（user_id），并设置每行显示的字符数是100。查询结果如图14.25所示。

图　14.25　设置查询结果每行显示的字符数

（3）显示查询数据所用的时间

在SQLPlus中查询数据一般只能看到一共查询到多少条数据，在实际的应用中为了检验语句的性能，通常需要知道每条语句执行的时间。在SQLPlus中显示查询语句所用时间的命令如下：

SET TIMING ON/OFF

这里，设置成ON，则显示语句的执行时间；若设置成OFF，则取消显示语句的执行时间。

【示例15】显示语句执行时间

下面利用显示语句执行时间的设置完成对图14.25所示结果的设置，结果如图14.26所示。

图　14.26　显示语句的执行时间

从图14.26中可以看出当前的查询语句执行的时间是39毫秒。

（4）设置查询结果是否显示列标题

如果在保存查询结果时为了保证数据库的安全可以隐藏掉每列显示的标题。具体命令如下：

SET HEADING ON/OFF

这里，ON表示显示列标题；OFF表示不显示列标题。

【示例16】取消显示查询结果的标题

下面利用上面的命令完成取消显示查询结果列标题的操作。取消显示dba_user表中查询结果的列标题操作如图14.27所示。

图　14.27　取消显示列标题

此时，显示的查询结果中就取消了列标题，但是还是保持了原有的格式输出。

（5）设置查询结果是否显示“已选择行数”

在SQL*Plus中每一个查询结果后面都会默认有一个已选择行数的提示，如果要取消该提示可以使用下面的命令：

SET FEEDBACK ON/OFF

这里，ON代表显示“已选择行数”的提示；OFF代表不显示“已选择行数”的提示。

【示例17】设置不显示“已选择行数”

下面就以查询TEST表中的USERNAME为例，设置不显示“已选择行数”。操作结果如图14.28所示。

图　14.28　不显示“已选择行数”

设置完不显示“已选择行数”后，执行完查询后并没有显示“已选择行数”，如果想设置显示“已选择行数”，操作如图14.29所示。

图　14.29　显示“已选择行数”

3.使用SPOOL命令输出查询结果

在SQL*Plus查询出的结果经常需要保存到文件中。保存结果的命令如下：

SPOOL filename

SPOOL OFF

【语法说明】

❑filename：保存输出结果的文件名，文件的扩展名可以写成.sql。

❑SPOOL OFF：当执行完SPOOL OFF之后，才把查询结果真正写入到指定的文件中。

【示例18】把查询结果写入文件

下面利用上面的语句完成把查询结果写入到文件test.sql中。把从dba_users表中的查询结果写入到test.sql文件中。写入操作如图14.30所示。

图　14.30　创建文件test.sql存储查询结果

当完成查询后，执行SPOOL OFF即可完成把查询结果写入到test.sql文件中。操作如图14.31所示。

图　14.31　写入查询结果

此时，查看文件test.sql，即可看到写入文件中的查询结果，如图14.32所示。

图　14.32　查看文件中的查询结果

在此就可以看出在文件test.sql中不仅有查询结果还有查询的语句存在。由于这是数据库使用者必备的知识，所以希望读者自己多加练习。

在上面的练习中查询结果在SQL*Plus中显示的过程也是省略的，但是只是在执行语句的脚本时才会省略具体的执行过程。命令如下：

SET TERM ON/OFF

这里，ON是指在SQLPlus中显示查询结果；OFF是指在SQLPlus中不显示查询结果。

【示例19】隐藏查询结果，把结果写入到文件中

下面利用上面的命令隐藏查询结果直接把结果写入到文件中。操作步骤如下：

（1）创建一个脚本文件

在脚本文件中输入查询语句，查询dba_users中用户名（username）、密码（password）、用户ID（user_id）。

创建好的脚本文件保存成test.sql，脚本文件如图14.33所示。

图　14.33　test.sql脚本文件

（2）执行脚本文件

在SQLPlus中执行脚本文件可以使用@filename的方式。在执行脚本文件前使用set term off语句设置不在SQLPlus中显示查询结果，操作结果如图14.34所示。此时，没有在图14.34中显示出脚本的查询结果。

图　14.34　执行脚本文件

为了对比不显示执行结果语句的方式，在执行了图14.34所示的文件之后，使用set term on设置显示查询结果后，再次执行脚本文件，结果如图14.35所示。

图　14.35　显示执行结果

（3）查看文件TESTPOOL.sql

为了验证是否已经把test.sql中的查询结果写入到TESTPOOL.sql，打开TESTPOOL.sql，如图14.36所示。

图　14.36　查看TESTPOOL.sql

至此，向TESTPOOL.sql文件中写入脚本文件test.sql的执行结果操作成功。

4.使用TTITLE命令设置标题

在SQLPlus中经常会使用到的就是报表的制作，那么如何给报表设置标题呢？SQLPlus为报表设置标题的命令如下：

TTITLE title

这里，title是指设置的标题名。注意，标题名要用单引号括起来。

【示例20】设置标题

下面使用TTITLE命令为查询结果设置标题。继续使用示例19中创建的脚本文件test.sql，在执行脚本文件之前先设置标题为“查询用户信息”。具体操作如图14.37所示。

图　14.37　设置标题

图14.37中所查询的信息全部保存到TITLETEST文件中，TITLETEST文件的内容如图14.38所示。

图　14.38　TITLETEST文件

在图14.38中就可以看到“查询用户信息”的标题显示在文件中。

除了使用TTITLE添加标题之外，还可以使用BTITLE定义尾标题。这里就不举例说明了，请读者对上述文件自行添加尾标题。