第2章 SQL Plus命令行工具的使用
设置SQL Plus 的运行环境
常用的SQL Plus 命令
格式化查询结果
实例028 使用set newpage命令修改空行的数量
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\028
实例说明
本实例要求：首先显示当前SQL Plus 环境中一页有多少空行，然后使用set newpage 命令修改空行的数量，并通过检索数据记录来观察空行的改变。运行结果如图 2.1所示。
图2.1 使用set newpage命令修改指定数量的空行
技术要点
首先介绍些SQL*Plus工具。
Oracle的SQL*Plus是与oracle进行交互的客户端工具，在SQL*Plus中，可以运行SQL*Plus命令与SQL*Plus语句。
SQL*Plus是一个基于C/S两层结构的客户端操作工具，包括客户层（即命令行窗口）和服务器层（即数据库实例），这两层既可以在一台主机上，也可以在不同主机上。SQL*Plus是一个非常重要的操作Oracle数据库的实用工具，本书中大多数关于SQL和PL/SQL的示例都是在SQL*Plus环境下进行演示的。
除了 Oracle 自身提供的 SQL*Plus 工具以外，还有许多第三方的 Oracle 的开发工具，如TOAD 和PL/SQL Developer 等，它们均具有与SQL*Plus 同样的功能，甚至还具备了SQL*Plus不具备的许多新功能。
SQL*Plus工具主要用来进行数据查询和数据处理。利用SQL*Plus可将SQL和Oracle专有的PL/SQL结合起来进行数据查询和处理。SQL*Plus工具具备以下功能。
● 定义变量，编写SQL 语句。
● 插入、修改、删除、查询，以及执行命令和PL/SQL语句，比如执行show parameter命令。
● 格式化查询结构、运算处理、保存、打印机输出等。
● 显示任何一个表的字段定义，并实现与用户进行交互。
● 完成数据库的几乎所有管理工作，如维护表空间和数据表。
● 运行存储在数据库中的子程序或包。
● 以sysdba 身份登录数据库实例，可以实现启动/停止数据库实例。
说明：在第1章中已介绍过关于如何启动SQL*Plus工具和如何连接数据库实例，这里不再赘述，详见第1章的实例004（使用SQL*Plus工具）。
在Oracle 11g 数据库中，用户可以使用SET 命令来设置SQL*Plus 的运行环境，SET 命令的语法格式为：
SET system_variable value
● system_variable：变量名。
● value：变量值。
SET命令的常用变量名、可选值及其说明如表2.1所示。
表2.1 SET命令的常用变量名、可选值及其说明
实现本实例需要使用NEWPAGE变量，该变量用来设置一页中空行的数量，默认情况下，其值为 1，根据实际情况的需要，用户可以修改其值，该值是一个整数。在修改完该值以后，可以通过检索记录来查看修改后的效果。
说明：通过SET命令设置的环境变量是临时的，不是永久的。当用户退出SQL*Plus环境后，用户设置的环境参数会全部丢失。
实现过程
（1）启动SQL*Plus，以system身份连接数据库。
（2）使用set newpage 命令修改空行的数量为4。
（3）主要程序代码如下：
SQL> show newpage
SQL> set newpage 4
SQL> select user_id,username from dba_users;
举一反三
根据本实例，读者可以进行以下操作。
使用set time on 命令显示当前的系统时间。
实例029 使用set pagesize命令修改一页的行数
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\029
实例说明
在对SET命令的功能及其若干常用变量选项了解之后，本节针对在Oracle操作过程中经常用到的几个变量选项及其实例应用进行详细讲解。本实例要求使用set pagesize 命令修改一页的行数为18，然后再使用新的pagesize值显示数据行。本例运行结果如图2.2所示。
图2.2 使用set pagesize命令修改一页的行数
技术要点
本实例用PAGESIZE变量可以实现。
该变量用来设置从顶部标题至页结束之间的行数，其语法格式如下：
SET PAGESIZE value
value变量的默认值为14，根据实际情况的需要，用户可以修改value的值，该值是一个整数。
当 SQL*Plus 返回查询结果时，它首先会显示用户所选择数据的列标题，然后在相应列标题下显示数据行，上下两个列标题所在行之间的空间就是 SQL*Plus 的一页。一页中所显示的数据行的数量就是PAGESIZE变量的值。若要查看当前SQL*Plus环境中的一页有多少行，可以使用命令show pagesize 命令。
注意：不要把当前窗口区域内能够显示的行数看做是SQL*Plus环境中一页的行数，一页的行数由PAGESIZE变量值来决定。
说明：一页内的数据行包括两个列标题之间的数据行、上面的一个列标题、分割线和空行。
实现过程
（1）启动SQL*Plus，以system身份连接数据库。
（2）使用NUMFORMAT变量格式化雇员的工资。
（3）主要程序代码如下：
SQL> set pagesize 18
SQL> select user_id,username,account_status from dba_users;
举一反三
根据本实例，读者可以进行以下操作。
显示当前SQL*Plus环境中的一页有多少行。
实例030 使用set linesize命令修改一行显示的最多字符
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\030
实例说明
首先show line 命令显示当前SQL*Plus 环境中一行可以显示的最多字符数量，然后使用set linesize 命令修改一行中可以显示的最多字符数量为100。本实例运行结果如图2.3所示。
图2.3 使用set linesize命令修改一行显示的最多字符
技术要点
本实例用LINESIZE变量可以实现。
该变量用来设置在SQL*Plus环境中一行所显示的最多字符总数，其语法格式如下：
SET LINESIZE value
value的默认值为80，根据实际情况的需要，用户可以修改value的值，该值是一个整数。
如果数据行的宽度大于LINESIZE变量的值，当在SQL*Plus环境中按照LINESIZE指定的数量输出字符时，数据就会发生折行显示的情况。如果适当调整LINESIZE的值，使其值等于或稍大于数据行的宽度，则输出的数据就不会折行。所以在实际操作Oracle数据库的过程中，要根据具体情况来适当调整LINESIZE的值。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）首先使用show line 命令显示当前SQL*Plus 环境中一行可以显示的最多字符数量，然后使用set linesize 命令修改一行中可以显示的最多字符数量为100。
（3）主要程序代码如下：
SQL> show line
SQL> set linesize 100
举一反三
根据本实例，读者可以进行以下操作。
使用set linesize 命令修改一行中可以显示的最多字符数量为80。
实例031 使用SET PAUSE命令设置显示结果按页暂停
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\031
实例说明
当在SQL*Plus环境中显示多行数据时，并且一页无法容纳下这么多数据行，不利于用户查看。可以使用SETPAUSE命令设置显示结果按页暂停，以便用户操作。本例运行结果如图2.4所示。
图2.4 使用SET PAUSE命令设置显示结果按页暂停
技术要点
本实例用PAUSE变量可以实现。
该变量用来设置SQL*Plus输出结果是否滚动显示，其语法格式如下：
SET PAUSE value
value变量值有以下3种情况。
● OFF：这是默认值，返回结果一次性输出完毕，中间的每一页不会暂停。
● ON：表示输出结果的每一页都暂停，用户按Enter 键后继续显示。
● TEXT：在设置PAUSE 的值为NO 之后，若再设置TEXT 的值，则每次暂停都将显示该字符串。当PAUSE的值为OFF时，设置TEXT值没有任何意义。
如果PAUSE变量值为OFF时，则SQL*Plus窗口输出的数据行会快速滚动，非常不利于用户查看。这就需要数据行在滚动时最好能够按页暂停，以便于用户逐页地查看输出结果。当把PAUSE变量的值设置为ON时，就可以实现控制SQL*Plus在显示完一页后暂停滚动，直到按Enter键后才继续显示下一页。另外，在设置PAUSE变量值为NO之后，还可以通过PAUSE变量设置暂停后显示的字符串，以便于提示用户操作。
说明：当不再需要按页暂停时，可以使用“SET PAUSE OFF”命令取消显示结果的暂停功能，同时屏幕上不再有“提示字符串”输出。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用SET PAUSE 命令设置显示结果按页暂停，并在暂停后显示“按<enter>键继续”字符串。
（3）主要程序代码如下：
SQL> set pause on
SQL> set pause '按<enter>键继续'
SQL> select user_id,username,account_status from dba_users;
举一反三
根据本实例，读者可以进行以下操作。
使用SET PAUSE 命令取消显示结果的暂停功能。
实例032 使用指定格式显示雇员的工资
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\032
实例说明
在SQL Plus 环境中，使用“$999,999,999.00”格式显示scott.emp 表中的雇员工资。本例运行结果如图2.5所示。
图2.5 格式化工资
技术要点
本实例用NUMFORMAT变量可以实现。
NUMFORMAT变量用来设置显示数值的默认格式，该格式是数值格式，其语法格式如下：
SET NUMFORMAT format
format为数值的掩码，数值的常用掩码及其说明如表2.2所示。
表2.2 数值的常用掩码及其说明
当用户查询数据库中的数值时，SQL Plus 环境将使用默认的格式显示数值，即以 10 个字符的宽度和常规格式来显示数字。
在上面的代码中，SAL字段列出了雇员的工资，工资的格式为默认格式。但在显示和打印工资时，通常习惯性把金额显示成带有两位小数的数值，并且为了便于阅读大金额，还需要给金额加上逗号分隔符和货币种类符号。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用NUMFORMAT变量格式化雇员的工资。
（3）主要程序代码如下：
SQL> set numformat $999,999,999.00
SQL> select ename,job,sal from scott.emp;
举一反三
根据本实例，读者可以进行以下操作。
使用默认格式显示scott.emp表中雇员工资。
使用指定格式显示scott.emp表中雇员入职时间。
实例033 使用help index命令来查看SQL Plus命令清单
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\033
实例说明
SQL Plus 工具提供了许多操作Oracle 数据库的命令，并且每个命令都有很多选项，把所有命令的选项都记住，这对于用户来非常困难。为了解决这个难题，SQL Plus 提供了HELP 命令来帮助用户查询指定命令的选项。如果用户无法记清所要使用的 SQL Plus 命令，则可以使用help index 命令来查看SQL Plus 命令清单。本例运行结果如图2.6 所示。
图2.6 SQL Plus命令清单
技术要点
HELP命令的语法形式如下：
HELP|? [topic]
?表示一个命令的部分字符，这样就可以通过提供命令的部分字符以模糊查询的方式来查询命令格式；topic 参数表示将要查询的命令的完整名称。若省略“?”和“topic”参数，直接执行HELP命令，则会输出HELP命令本身的语法格式及其功能描述信息。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用help index 命令来查看SQL Plus 命令清单。
（3）主要程序代码如下：
SQL> help index
举一反三
根据本实例，读者可以进行以下操作。
使用HELP命令查看startup命令。
实例034 通过DESCRIBE命令查看dba_tablespaces数据字典表
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\034
实例说明
在 SQL*Plus的众多命令中，DESCRIBE命令可能是被使用最频繁的一个，它用来查询指定数据对象的组成结构。比如，通过DESCRIBE命令查询表和视图的结构，查询结果就可以列出其各个列的名称、是否为空及类型等属性。本实例通过DESCRIBE命令查看dba_tablespaces数据字典表（用来存储表空间信息的内部表）的结构。本例运行结果如图2.7所示。
图2.7 查看dba_tablespaces数据字典表结构
技术要点
DESCRIBE命令的语法形式如下：
desc[ribe] object_name;
describe可以缩写为desc，object_name表示将要查询的对象名称。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用DESCRIBE命令来查看dba_tablespaces数据字典表。
（3）主要程序代码如下：
SQL> desc dba_tablespaces;
举一反三
根据本实例，读者可以进行以下操作。
使用DESCRIBE命令查看emp表结构。
实例035 将销售员的记录保存到指定文件中
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\035
实例说明
本实例使用spool命令声称emp.txt文件，并将scott.emp表中销售员（SALESMAN）的记录保存到该文件中，运行结果如图2.8和图2.9所示。
图2.8 将销售员的记录保存到指定文件中
图2.9 emp.txt文件
技术要点
SPOOL命令可以把查询结果输出到指定文中，这样可以保存查询结果并方便打印。SPOOL命令的语法格式如下：
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] |APP[END]] | OFF | OUT]
参数 file_name 用于指定脱机文件的名称，默认的文件扩展名为 LST。在该参数后面可以跟一个关键字，该关键字有以下4种情况。
● CRE[ATE]：表示穿件一个新的脱机文件，这也是SPOOL 命令的默认状态。
● REP[LACE]：表示替代已经存在的脱机文件。
● APP[END]：表示把脱机内容附加到一个已经存在的脱机文件中。
● OFF | OUT：表示关闭spool 输出。
注意：只有使用spool off或spool out命令关闭输出，才会在输出文件中看到输出的内容，否则输出文件中无内容或无更新内容。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用SPOOL命令把销售员的记录保存在指定文件中。
（3）主要程序代码如下：
SQL> spool c:\emp.txt
SQL> select empno,ename,job,sal from scott.emp where job='SALESMAN';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7844 TURNER SALESMAN 1500
SQL> spool off
上面程序的执行结果是：从spool 命令开始（但不包括该命令行），一直到spool off 命令行（包括该命令行）之间的所有内容都被写入emp.txt文件中。
举一反三
根据本实例，读者可以进行以下操作。
清空SQL缓冲区中的内容。
将SQL缓冲区中的最近一条SQL语句或PL/SQL块保存到一个文件中。
实例036 使用save命令保存SQL语句到文件中
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\036
实例说明
在SQL语句执行之后，想要将此SQL语句保存到文件中，可以使用save命令实现。实例运行结果如图2.10所示。
图2.10 使用save命令保存SQL语句到文件中
技术要点
该命令实现将SQL缓冲区中的最近一条SQL语句或PL/SQL块保存到一个文件中，其语法格式如下：
SAVE file_name
参数 file_name 表示要保存的文件名，若果不为保存的文件指定路径，则该文件会保存在Oracle系统安装的主目录中（但不建议这样做）。如果不为保存的文件指定扩展名，则默认扩展名为SQL，即保存的文件为一个SQL脚本文件。
技巧：如果要清空SQL 缓冲区中的内容，可以使用clear buffer 命令。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用save命令保存SQL缓冲区中的SQL语句到“c:\dept.sql”文件中。
（3）代码及运行结果如下：
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------------------ -------------- -------------
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> save c:\dept.sql
已创建 file c:\dept.sql
举一反三
根据本实例，读者可以进行以下操作。
清空SQL缓冲区中的内容。
实例037 加载并执行SQL脚本文件
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\037
实例说明
首先在“c:\dept.sql”文件中写入 SQL 脚本，然后通过GET命令将C:\dept.sql文件的内容加载到SQL*Plus缓冲区，最后使用运行命令“/”执行SQL*Plus缓冲区的语句。实例运行结果如图2.11所示。
图2.11 加载并执行SQL脚本文件
技术要点
本实例用GET变量可以实现。
该命令实现把一个SQL脚本文件的内容放进SQL缓冲区，其语法格式如下：
GET [FILE] file_name[.ext] [LIST | NOLIST]
● file_name：要检索的文件名，如果省略了文件的扩展名，则默认文件的扩展名为SQL。
● LIST：指定文件的内容加载到缓冲区时显示文件的内容。
● NOLIST：指定文件的内容加载到缓冲区时不显示文件的内容。
执行GET命令时，如果file_name参数不包括被检索文件的路径，则SQL*Plus工具会在Oracle 系统安装的主目录下检索指定文件。在 SQL*Plus 找到指定文件后，会把文件中的内容加载到SQL*Plus缓冲区，并显示该文件的内容。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）通过GET命令将C:\dept.sql文件的内容加载到SQL*Plus缓冲区，使用运行命令“/”执行SQL*Plus缓冲区的语句。
（3）代码及运行结果如下：
SQL> get c:\dept.sql
1* select * from scott.dept
SQL>/
举一反三
根据本实例，读者可以进行以下操作。
自己写段SQL语句，保存到“c:\emp.sql”文件中，然后再加载到SQL*Plus缓冲区，最后执行。
实例038 使用format选项格式化员工工资格式
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\038
实例说明
本实例使用heading选项把scott.emp表中的empno、ename、sal等3个列名转换为中文形式。实例运行结果如图2.12所示。
图2.12 把列名转换为中文形式
技术要点
本实例是使用COLUMN命令。
该命令可以实现格式化查询结果、设置列宽度、重新设置列标题等功能。其语法格式如下：
COL[UMN] [column_name | alias | option]
● column_name：用于指定要设置的列的名称。
● alias：用于指定列的别名，通过它可以把英文列标题设置为汉字。
● option：用于指定某个列的显示格式，option 选项的值及其说明如表2.3 所示。
表2.3 option选项的值及其说明
如果在关键字column后面未指定任何参数，则column命令将显示SQL*Plus环境中所有列的当前定义属性；如果在column后面指定某个列名，则显示指定列的当前定义属性。
在本例中用到的option选项的值是format，该选项用于格式化指定的列，需要在FORMAT关键字的后面跟一个掩码格式。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用format选项格式化sal列的值。
（3）主要程序代码如下：
SQL> col sal format $999,999.00
SQL> select empno,ename,sal from scott.emp;
举一反三
根据本实例，读者可以进行以下操作。
设置scott.emp表中的sal列（工资列）的格式为“$999,999.00”，但同时使用off选项取消定义的列属性状态，输出结果显示sal列没有被格式化。
实例039 使用heading选项将英文列名转换为中文形式
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\039
实例说明
本实例使用heading选项把scott.emp表中的empno、ename、sal等3个列名转换为中文形式。实例运行结果如图2.13所示。
图2.13 把列名转换为中文形式
技术要点
本实例是使用HEADING选项。该选项用于定义列标题，比如，许多数据表或视图的列名都为英文形式，可以使用此选项将英文形式的列标题显示为中文形式。
实现过程
（1）启动 SQL*Plus，输入用户名 scott、口令 tiger连接数据库。
（2）使用heading选项将英文列名转换为中文形式。
（3）主要程序代码如下：
SQL> col empno heading 雇员编号
SQL> col ename heading 雇员姓名
SQL> col sal heading 雇员工资
SQL> select empno,ename,sal from scott.emp;
举一反三
根据本实例，读者可以进行以下操作。
使用null选项把列值为null的显示成“空值”字符串。
实例040 为打印记录设置头标题和底标题
这是一个可以提高基础技能的实例
实例位置：光盘\mingrisoft\02\040
实例说明
在SQL Plus 环境中，执行SQL 语句后的显示结果在默认情况下包括列标题、页分割线、查询结果和行数合计等内容，用这些默认的输出信息打印报表，并不十分美观。如果能为整个输出结果设置报表头（即头标题），为每页都设置页标题和页码，为整个输出结果设置报表尾（如打印时间或打印人员），那么使用这样的输出结果打印报表一定非常美观。为了实现这些功能，SQL Plus 工具提供了TTITLE 和BTITLE 命令，这两个命令分别用来设置打印时每页的顶部和底部标题。本实例实现了打印输出scott.salgrade数据表中的所有记录，并要求为每页设置头标题（报表名称）和底标题（打印时间和打印人）的功能。实例运行结果如图2.14所示。
图2.14 为打印记录设置头标题和底标题
技术要点
TTITLE命令的语法格式如下：
TTI[TLE] [printspec [text|variable] ...] | [OFF|ON]
● printspec：用来作为头标题的修饰性选项，printspec 选项的值及其说明如表2.4 所示。
表2.4 printspec选项的值及其说明
● text：用于设置输出结果的头标题（即报表头文字）。
● variable：用于在头标题中输出相应的变量值。
● OFF：表示禁止打印头标题。
● ON：表示允许打印头标题。
注意：BTITLE的语法格式与TTITLE的语法格式相同。如果在TTITLE或BTITLE命令后面有任何参数，则显示当前的TTITLE或BTITLE的定义。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用TTITLE命令实现为打印记录设置头标题和底标题。
（3）主要程序代码如下：
SQL> set pagesize 8
SQL> ttitle left ' 销售情况排行表'
SQL>btitle left '打印日期：2011年9月20日 打印人：东方'
SQL> select * from scott.salgrade;
在上面的例子中，头标题“销售情况排行表”是一个固定的字符串，另外，头标题也可以使用变量来输出。
举一反三
根据本实例，读者可以进行以下操作。
打印输出每页的页标题和页码。
实例041 打印输出scott.emp数据表中的所有记录
本实例可以方便操作、提高效率
实例位置：光盘\mingrisoft\02\041
实例说明
打印输出scott.emp数据表中的所有记录，并要求头标题和底标题都使用define命令定义的变量输出。实例运行结果如图2.15所示。
图2.15 输出头标题和底标题
技术要点
本实例中使用TTITLE和BTITLE命令。具体用法参见实例040。
实现过程
（1）启动 SQL*Plus，输入用户名 scott、口令 tiger 连接数据库。
（2）使用TTITLE和BTITLE命令实现打印输出scott.emp数据表中的所有记录。
（3）主要程序代码如下：
SQL> set pagesize 8
SQL>define varT=' 雇员信息表'
SQL> define varB='操作员：东方'
SQL> ttitle left varT
SQL> btitle left varB
SQL> select empno,ename,job from scott.emp;
上面代码中所设置的头标题和底标题的有效期直到本次会话结束后才终止。若要手动清除这些设置，可以分别使用 ttitle off 命令和 btitle off 命令取消头标题和底标题的设置信息。
举一反三
根据本实例，读者可以进行以下操作。
在报表底部的打印输出打印时间。
实例042 使用TEXT值代替NULL值列
本实例可以方便操作、提高效率
实例位置：光盘\mingrisoft\02\042
实例说明
有些时候，为了显示效果的美观，经常需要把空值列的显示做特殊处理。本实例要求使用TEXT值代替NULL值列。查询scott.emp表后的运行结果如图2.16所示。
图2.16 使用TEXT值代替NULL值列
技术要点
实现本实例需要使用COLUMN命令，该命令可以用来实现格式化查询结果、设置列宽度、重新设置列标题等功能。把COLUMN 命令的参数设置为null，即可实现设置null 为指定显示值的功能。
实现过程
（1）启动SQL*Plus，输入用户名scott、口令tiger连接数据库。
（2）使用COLUMN命令。
（3）主要程序代码如下：
SQL>col comm null 'TEXT'
SQL>select ename,comm from scott.emp where comm is null;
举一反三
根据本实例，读者可以进行以下操作。
使用format选项格式化emp中的hiredate列。