第8章 SQL*Plus工具介绍
本章介绍SQL*Plus系列产品的使用。SQL*Plus是一个被系统管理员（DBA）和开发人员广泛使用的功能强大而且很直接的Oracle工具。SQL*Plus可以运行在Oracle运行的任何平台上，在客户端可以通过安装Oracle客户端软件时安装，在服务器端通过安装Oracle Server软件时安装。SQL*Plus可以执行输入的SQL语句和包含SQL语句的文件，及PL/SQL语句。通过SQL*Plus，可以与数据库进行“对话”。
iSQL*Plus是在SQL*Plus基础上开发的基于Web访问的具有三层结构的新工具，与传统的命令行模式的SQL*Plus相比，它提供了更为友善的界面，不需要安装Oracle客户端就可以用浏览器访问，直接操作数据库，大大方便了用户的使用。
本章的主要内容如下：
◎ 如何使用SQL*Plus与数据库交互
◎ 如何使用SQL*Plus的编辑功能
◎ 如何使用SQL*Plus格式化查询结果
◎ 如何使用iSQL*Plus
8.1 用SQL*Plus与数据库交互
为了使用SQL语句及其过程语言PL/SQL（Oracle对标准SQL的扩展）与数据库进行交互，Oracle提供了SQL*Plus和iSQL*Plus两个工具，它们都是与Oracle数据库一起安装的。通过它们，就可以使用SQL及PL/SQL语句创建并管理数据库的所有对象。
其中，SQL*Plus是一个基于传统的C/S两层结构的SQL开发工具，包括客户层和服务器层，这两层既可以在一台主机上，也可以在不同主机上。SQL*Plus也是要给大家重点介绍的一个工具，本书中绝大多数的SQL和PL/SQL都将在SQL*Plus环境下进行演示。
而iSQL*Plus是基于目前流行的三层模型的B/S结构，不需要单独安装，它通过浏览器访问数据库。
SQL及PL/SQL的使用环境，除了Oracle提供的以外，还有许多第三方厂家的Oracle的开发工具，如TOAD（Tools of Oracle Application Developer）和PL/SQL Developer等，它们均具有与SQL*Plus同样的功能，甚至还具备了SQL*Plus不具备的许多功能。
SQL工具与数据库服务器之间的结构和连接如图8-1所示。
图8-1 SQL工具与数据库服务器之间的结构和连接
8.1.1 SQL*Plus的主要功能
SQL*Plus工具主要用来进行数据查询和数据处理。利用SQL*Plus可将SQL和Oracle专有的PL/SQL结合起来进行数据查询和处理。SQL*Plus工具具备以下功能：
◎ 插入、修改、删除、查询，以及执行SQL、PL/SQL块。
◎ 查询结果的格式化、运算处理、保存、打印及输出Web格式。
◎ 显示任何一个表的字段定义，并与终端用户交互。
◎ 连接数据库，定义变量。
◎ 完成数据库管理。
◎ 运行存储在数据库中的子程序或包。
◎ 启动/停止数据库实例，要完成该功能，必须以sysdba身份登录数据库。
8.1.2 启动SQL*Plus连接数据库
如前所述，SQL*Plus是Oracle系统为用户提供的使用SQL和PL/SQL进行创建、管理和使用数据库对象，并与Oracle服务器进行交互的前端工具。可以把它想象成一个编辑器，SQL*Plus为用户输入、调试SQL和PL/SQL语句并在其中获得结果提供了一个环境。
下面分几种情况介绍在不同环境下启动SQL*Plus的两种方法。
1．Oracle数据库安装在Windows操作系统下，从命令窗口直接启动SQL*Plus
单击“开始”菜单中的“运行”命令，打开“运行”对话框。
输入sqlplus，如图8-2所示。（当然，也可以输入cmd，打开DOS命令行窗口，然后输入sqlplus命令，结果是一样的。）
图8-2 “运行”对话框
如果指定某个具体用户登录，则可以输入sqlplus “scott\tiger”，如图8-3所示。
如果是以sys dba的身份登录数据库，则需要明确指出，可以输入sqlplus “/as sysdba”，如图8-4所示。
图8-3 指定确定的用户登录数据库图
图8-4 该方式直接以dba的身份登录本机的数据库
如果是以无连接的方式进行输入，则可以输入sqlplus /nolog，如图8-5所示，进入sqlplus后再决定以哪种身份连接数据库（此时需要使用connect命令）。
图8-6为其中的一张结果图片，其他情况就不再一一列举了。
图8-5 以无连接方式进入sqlplus环境
图8-6 连接到Oracle数据库中
输入SQL命令，在命令的结尾处输入“;”号，然后按回车键执行，如图8-7所示。如果SQL语句的结尾没有“;”号，那么，sqlplus会认为SQL语句还没有结束，而自动往下续行。
如果要退出sqlplus，直接输入exit即可。
如果不想退出sqlplus，但是想运行Windows操作系统的命令，则可以在SQL>提示符下直接输入host，然后输入DOS命令操作，此时输入exit，即可退回到sqlplus环境。如图8-8所示。
图8-7 执行SQL语句
图8-8 运行操作系统命令
2．从菜单命令中启动窗口程序形式的SQL*Plus
可执行如下操作：
依次选择“开始 → 程序 → Oracle-OraDb11g_home1 → 应用程序开发>SQL Plus”菜单命令，弹出输入用户名和口令的窗口，如图8-9所示。其余操作与前面的讲述相同。
图8-9 从开始菜单执行
在Linux操作系统下，启动SQL*Plus与Windows操作系统类似，以Oracle用户登录系统，直接在命令行状态下执行sqlplus即可，此处不再赘述。
8.2 使用SQL*Plus的编辑功能
作为一个调试和运行SQL语句和PL/SQL的软件工具，SQL*Plus的功能远不止在其中输入命令行，然后执行得到结果这么简单。使用SQL*Plus可以十分方便地编辑和管理编程的过程。本节主要介绍SQL*Plus的管理功能，主要包括：
◎ 编辑命令；
◎ 保存命令；
◎ 加入注释；
◎ 运行命令；
◎ 编写交互命令；
◎ 使用绑定变量；
◎ 跟踪语句。
8.2.1 SQL语句、PL/SQL块与SQL*Plus命令的区别
在正式介绍SQL*Plus之前，我们需要明确SQL语句、PL/SQL块和SQL*Plus命令这几个概念之间的区别。
（1）SQL语句是以数据库为操作对象的语言，主要包括数据定义语言DDL、数据操纵语言DML和数据控制语言DCL及数据存储语言DSL。当输入SQL语句后，SQL*Plus将其保存在内部缓冲区中。当SQL命令输入完毕时，有3种方法可以结束SQL命令：在命令行的末尾输入分号“;”并按回车键；在单独一行上用斜杠（/）；或用空行表示。
（2）PL/SQL块同样是以数据库中的数据为操作对象。但由于SQL不具备过程控制功能，所以，为了能够与其他语言一样具备面向过程的处理功能，在SQL中加入了诸如循环、选择等面向过程的处理功能，由此形成了PL/SQL。所有PL/SQL语句的解释均由PL/SQL引擎来完成,使用PL/SQL块可编写过程、触发器和包等数据库永久对象。
（3）SQL*Plus命令主要用来格式化查询结果、设置选择、编辑及存储SQL命令、以设置查询结果的显示格式，并且可以设置环境选项。
8.2.2 编辑命令
下面介绍使用SQL*Plus的编辑命令时应注意的几点。
（1）当运行SQL*Plus时，Oracle会在缓冲区中保留最后执行的命令，因而方便了命令的修改和执行。要访问缓冲区，只要输入斜杠“/”并按“Enter”键即可，该操作将使最后输入的SQL查询语句再次被运行，如图8-10所示。
图8-10 输入“/”访问缓冲区
（2）在很多情况下，在SQL*Plus环境下输入SQL命令时，往往难以避免出错。此时，使用SQL*Plus的行编辑功能比重新输入整行更方便快捷得多。尽管SQL*Plus的行编辑功能相对简单，但十分有用，可以很快地修改SQL缓冲区中的SQL语句并再次执行。表8-1显示了SQL*Plus主要的行编辑命令。
表8-1 SQL*Plus行编辑命令
因为SQL*Plus具备的是行编辑功能，所以使用编辑器的关键是要明白什么是“当前行”，即允许修改的行。参看图8-11中的代码。
图8-11 代码1
此时，如果输入List命令，则会列出在缓冲区中SQL语句的所有行，但要注意的是，前面带有 *的行是“当前行”，也就是SQL*Plus目前在编辑的行，如图8-12所示。
“*”表示当前行。如果要将第1行变为当前行，则可以使用命令LIST（缩写为L）后面直接跟行号1，简写为L1，即可以将第1行变为当前行，如图8-13所示。
图8-12 代码2
图8-13 代码3
这表示，现在当前行为第1行。
（3）上面介绍的命令行编辑器只允许编辑SQL查询语句本身。许多情况下，为了使简单的SQL查询成为实用的报表，需要许多格式和设置，这项工作最好由全屏编辑器来完成。在SQL*Plus中有一个命令，允许定义直接在SQL*Plus中使用的编辑器，其命令格式为“define_editor=editor_name”，其中的“editor_name”是用户选择的编辑器的名称。在UNIX中，该编辑器名可以是“vi”；在VMS中，该名称可以是“edt”；在Windows下，编辑器是“记事本”。
用户可以设置自选的任何编辑器。为了使用以上述格式定义的编辑器，输入命令“edit”或者用缩写“ed”，Oracle将使用用户在“define_editor”命令中定义的编辑器。例如，在Windows xp操作系统下，启动SQL*Plus后执行“ed”命令，将打开“记事本”程序，缓冲区中的SQL语句自动出现在“记事本”中，如图8-14所示。
图8-14 打开“记事本”编辑SQL语句
在“记事本”程序中，用户可以像编辑普通文本那样编辑SQL语句，然后保存修改，关闭“记事本”。这时再在SQL*Plus中输入斜杠“/”，就可以执行更新后的SQL语句了。
8.2.3 保存命令
通过SQL*Plus，可以将命令存储在命令文件中，当创建了一个命令文件后，可以重新提取、编辑和运行它。使用命令文件保存命令，可以使该命令能够重复使用，特别是对于复杂的SQL命令和PL/SQL块。
在SQL*Plus中，可以将一个或者多个SQL命令、PL/SQL块和SQL*Plus命令存储在命令文件中，可以使用3种方式在SQL*Plus创建命令文件。
◎ 通过使用SAVE命令，可以直接保存缓冲区中的SQL语句到指定的文件；
◎ 通过使用INPUT输入命令，可以连同SQL*Plus命令与SQL语句一起保存在缓冲区，然后使用save命令保存在指定的文件中；
◎ 可以直接使用EDIT命令创建文件。
（1）输入SAVE命令，保存缓冲区中的SQL命令或者PL/SQL块。
格式为：
SAVE file_name
默认保存在当前路径下，也可以是绝对路径，如SAVE D:\test，则会保存在D盘中，文件全名为test.sql 。
SQL*Plus为文件名添加.SQL扩展名，表示它是一个SQL查询文件。下面的示例显示了如何保存当前的SQL语句（首先显示一下缓冲区中的SQL语句），如图8-15所示。
图8-15 保存当前的SQL语句
（2）SQL*Plus命令（注意不是SQL语句和PL/SQL块）不会自动保存到缓冲区中。可以使用INPUT命令，将SQL*Plus命令输入到缓冲区中，然后使用SAVE命令保存包含SQL*Plus命令在内的查询语句到指定的文件。
下面的示例显示如何结合使用IINPUT和SAVE保存命令。
使用INPUT编写和保存查询，清除缓冲区；然后，使用IINPUT命令输入SQL*Plus命令和SQL语句，用SAVE保存。（注意，此处以空行回车），如图8-16所示。
图8-16 使用IINPUT和SAVE保存命令
注意
在上面的例子中，SAVE命令后面的REPLACE关键字将覆盖同名的文件。如果不使用这个关键字而又恰恰存在该文件名的文件，将不能写入。
使用INPUT命令将SQL*Plus命令加到缓冲区中，因此，不能在缓冲区中直接执行该命令，否则会报错。
如果要执行刚刚保存的SQL*Plus命令和SQL命令，需要使用SQL>@filename，如图8-17所示。
图8-17 使用IINPUT和SAVE保存命令
8.2.4 加入注释
在代码中加入注释能够使用户的编程更具有可读性。这里我们再系统地讨论一下加入注释的方法及应该注意的一些问题。
可以使用3种方式在命令文件中输入注释。
◎ 使用SQL*Plus REMARK命令输入单行注释；
◎ 使用SQL注释定界符/*... */输入多行注释的单行注释；
◎ 使用ANSI/ISO注释--，用于输入单行注释。
1．使用REMARK命令
使用REMARK命令在命令文件的一行上加注释。
REMARK Commission Report; REMARK to be run monthly.; COLUMN LAST_NAME HEADING 'LAST_NAME'; COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999; COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90; REMARK Includes only salesmen; SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN'
2．使用/*…*/
输入SQL注释分隔符/*…*/，例如：
/* Commission Report to be run monthly. */ COLUMN LAST_NAME HEADING 'LAST_NAME'; COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999; COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90; REMARK Includes only salesmen; SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW /* Include only salesmen.*/ WHERE JOB_ID='SA_MAN'
3．使用--
可以使用ANSI/ISO样式注释“--”，例如：
-- Commissions report to be run monthly DECLARE --block for reporting monthly sales
对于SQL*Plus命令，如果本身在一行，可以只包含“--”，例如，下面的注释是合法的：
-- set maximum width for LONG to 777 SET LONG 777
如果输入下面的SQL*Plus命令，SQL*Plus将解释它为注释，而不会执行这条命令。
-- SET LONG 777
SQL*Plus不会编译和执行作为注释的语句，也没有SQL或者PL/SQL命令编译器。它扫描每个新的语句的前面几个关键字，确定命令类型。下面的一些规则，可以帮助读者更好地使用SQL*Plus。
不要将注释放在语句前面几个关键字中，例如：
SQL> CREATE OR REPLACE
2 /* HELLO */
3 PROCEDURE HELLO AS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('HELLO');
警告: 创建的过程带有编译错误。
不要将注释语句放在语句终止符后面，例如，如果输入：
SQL> SELECT * FROM SCOTT.EMP;--TESTING 2
系统认为SQL语句还没有终止。应将终止符放到注释语句的后面，例如：
SQL> SELECT * FROM SCOTT.DEPT --GET DEPARTMENT INFORMATION;
DEPTNO DNAME LOC
------------- ------------------------ -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
8.2.5 运行命令
本节我们系统讨论运行命令的方法。在前面的介绍中，我们都是采用在SQL命令行的后面加分号（;）来运行命令的。实际上，运行SQL命令和PL/SQL块有3种方式。
◎ 命令行方式；
◎ SQL缓冲区方式；
◎ 命令文件方式。
1．命令行方式
命令行方式就是前面介绍过的在命令后面加分号（;）作为终止符来运行SQL命令的方式。
2．SQL缓冲区方式
为了以缓冲区方式执行SQL命令或PL/SQL块，SQL*Plus提供了RUN命令和/（斜杠）命令。RUN命令的语法是：
R[UN]
RUN命令列出并执行当前存储在缓冲区中的SQL命令或PL/SQL块。
RUN命令显示缓冲区中的命令并返回查询的结果。另外，RUN命令使得缓冲区中的最后一行成为当前行。
/命令类似于RUN命令，它执行存储在缓冲区中的SQL命令或PL/SQL块，但不显示缓冲区的内容。此外，/命令不会使缓冲区中的最后一行成为当前行。
3．命令文件方式
要以命令文件方式运行SQL命令、SQL*Plus命令或PL/SQL块，有两种命令：
START @（读作“at”）
START命令的语法如下：
START file_name[.sql] [arg1 arg2]
参数file_name[.ext]代表用户想运行的命令文件，如果省略扩展名，那么，SQL*Plus将使用默认的命令文件扩展名（通常为.sql）。
SQL*Plus在当前目录下查找具有在START命令中指定的文件名和扩展名的文件。如果没有找到符合条件的文件，SQL*Plus将在SQLPATH环境变量定义的目录中查找该文件。在参数文件中也可以包括文件的全路径名，例如：C:\MYSQL\TEST.SQL。
参数部分（[arg1 arg2]）代表用户希望传递给命令文件中的参数的值，命令文件中的参数必须使用如下格式声明：&1、&2（或&&1、&&2）。如果输入一个或多个参数，SQL*Plus使用这些值替换命令文件中的参数。第一个参数替代每个&1，第二个参数替代每个&2，依此类推。
@命令的功能与START命令非常类似，唯一的区别就是@命令既可以在SQL*Plus会话内部运行，又可以在启动SQL*Plus时的命令行级别运行，而START命令只能在SQL*Plus会话内部运行。
此外，使用EXECUTE命令能够直接在SQL*Plus提示符状态下执行单条PL/SQL语句，而不需要从缓冲区或命令文件中执行。EXECUTE的主要用途是运行涉及函数或存储过程的PL/SQL语句。
8.2.6 编写交互命令
使用SQL*Plus可以编写交互命令，使得最终用户能够：
◎ 定义用户变量；
◎ 在命令中替代值；
◎ 使用START命令提供值；
◎ 提示值。
1．定义用户变量
用户可以定义用户变量，称为User variables，可以在命令文件中重复使用。注意：用户还可以在标题中定义用户变量。下列的示例显示如何定义用户变量。
定义用户变量L_NAME，赋值为“SMITH”，输入下面的命令：
DEFINE MYFRIEND=SMITH
如果需要列出所有的变量定义，可以在命令提示符处输入DEFINE。
SQL> DEFINE DEFINE _SQLPLUS_RELEASE = "900010001" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production" (CHAR) DEFINE _O_RELEASE = "900010101" (CHAR) DEFINE MYFRIEND = "SMITH" (CHAR)
注意
任何用户都必须使用DEFINE显示定义。如果需要删除一个用户变量，可以使用SQL*Plus命令UNDEFINE加上变量名。
2．在命令中替代值
如果希望编写一个查询，列出具有某种工作的雇员信息，那么用户很容易想到使用WHERE子句，设置关于工作列名的条件，例如：
WHERE JOB=’SALES’
可是，如果需要列出不同工作的雇员信息，且工作部门由最终的用户选择而不是SQL语句的输入者，那么，就需要替代变量编写交互SQL命令。
替代变量是在用户变量名前加入一个或者两个&变量。当SQL*Plus遇到替代变量时，SQL*Plus执行命令，就如同包含替代变量的值。例如，如果变量SORTCOL包含值JOB_ID，变量MYABLE包含值EMP_DETAILS_VIEW，SQL*Plus执行命令：
SELECT &SORTCOL, SALARY FROM &MYTABLE WHERE SALARY>12000;
等价于执行下面的SQL语句：
SELECT JOB_ID, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
但是，要明确如何使用替代变量，及何时使用替代变量。除了在语句的第一个关键词之外，用户可以在SQL和SQL*Plus命令的任何位置使用替代变量（例如，不能用替代变量替代查询关键词SELECT）。
下面创建一个包含替代变量的交互SQL命令。
SQL> CLEAR BUFFER buffer已清除 SQL> INPUT 1 SELECT ENAME,JOB,SAL 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO=D.DEPTNO 4 AND DNAME=&DNAME 5 SQL> SAVE TEST 已创建文件 TEST.sql
上面的例子用替代变量DNAME来代替具体的部门名称，由最终执行该命令的用户来指定而不是由SQL命令的编写者来指定。从这个角度来说，该命令对于命令的开发者和使用者是交互的。
下面，我们运行此命令：
SQL> @TEST 输入 dname的值: 'SALES' 原值 4: AND DNAME=&DNAME 新值 4: AND DNAME='SALES' ENAME JOB SAL -------------- ------------ ------------- ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 TURNER SALESMAN 1500 JAMES CLERK 950 已选择6行。
如果希望在替代变量后添加字符，可以使用“.”将变量和字符隔开。此外，还可以在变量上加引号，这样对于字符串变量，执行命令时，用户就无须使用引号了。例如，我们对上面保存得到的命令TEST.sql进行一些修改，如下所示。
SQL> GET TEST 1 SELECT ENAME,JOB,SAL 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO=D.DEPTNO 4* AND DNAME=&DNAME SQL> C/&DNAME/'&DNAME.S' 4* AND DNAME='&DNAME.S' SQL> SAVE TEST REPLACE 已写入文件 TEST.sql SQL> @TEST 输入 dname的值: SALE 原值 4: AND DNAME='&DNAME.S' 新值 4: AND DNAME='SALES' ENAME JOB SAL -------------- ------------ ------------- ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 TURNER SALESMAN 1500 JAMES CLERK 950 已选择6行。
在什么情况下使用两个&表示替代变量呢？请看下面这个例子。
SQL> SELECT ENAME,&COL FROM SCOTT.EMP
2 ORDER BY &COL;
输入 col的值: SAL
原值 1: SELECT ENAME,&COL FROM SCOTT.EMP
新值 1: SELECT ENAME,SAL FROM SCOTT.EMP
输入 col的值: SAL
原值 2: ORDER BY &COL
新值 2: ORDER BY SAL
ENAME SAL
----------------- -------------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
ENAME SAL
----------------- -------------
SCOTT 3000
FORD 3000
KING 5000
已选择14行。
在上面的例子中，两次用到了变量COL，而实际上，编写者的本意是按照执行命令的用户指定的列进行查询，并根据该列进行排序查询结果。因此，这里可以使用两个&符号来标识替代变量，这样在运行时就只需输入一次变量的值，如下所示。
SQL> SELECT ENAME,&&COL2 FROM SCOTT.EMP
2 ORDER BY &&COL2;
输入 col2的值: SAL
原值 1: SELECT ENAME,&&COL2 FROM SCOTT.EMP
新值 1: SELECT ENAME,SAL FROM SCOTT.EMP
原值 2: ORDER BY &&COL2
新值 2: ORDER BY SAL
ENAME SAL
----------------- -------------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
ENAME SAL
----------------- -------------
SCOTT 3000
FORD 3000
KING 5000
已选择14行。
在上面的例子中，不再使用变量COL是由于已经执行过交互命令，变量COL已经被赋值；而使用两个&标识变量，如果系统发现该变量已经赋值，则不再要求用户输入，而直接使用原来的赋值。例如，执行如下命令，就会发现系统不再需要用户输入变量COL的值了。
SQL> SELECT ENAME,&&COL FROM SCOTT.EMP
2 ORDER BY &&COL;
原值 1: SELECT ENAME,&&COL FROM SCOTT.EMP
新值 1: SELECT ENAME,SAL FROM SCOTT.EMP
原值 2: ORDER BY &&COL
新值 2: ORDER BY SAL
ENAME SAL
----------------- -------------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
ENAME SAL
----------------- -------------
SCOTT 3000
FORD 3000
KING 5000
已选择14行。
用户只需使用DEFINE命令就会发现：一经赋值，这个变量的值就保留在SQL*Plus中，如果再在命令中用到这个变量，系统直接将该值赋给变量。
SQL> DEFINE DEFINE _SQLPLUS_RELEASE = "900010001" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production" (CHAR) DEFINE _O_RELEASE = "900010101" (CHAR) DEFINE MYFRIEND = "SMITH" (CHAR) DEFINE DNAME = "SALE" (CHAR) DEFINE COL = "SAL" (CHAR) DEFINE COL2 = "SAL" (CHAR)
3．使用START命令提供值
在编写SQL*Plus命令时，也可以使用START命令将命令文件的参数值传给替代变量。为此，仅需将符号（&）置于命令文件数字前，替换替代变量。当每次运行此命令文件时，STRAT使用第一个值替换每个&1，使用第二个值替换&2。
例如，将下面的命令包含在命令文件MYFILE中。
SELECT * FROM EMP_DETAILS_VIEW WHERE JOB_ID='&1' AND SALARY='&2';
在下面的STRAT命令中，SQL*Plus将使用CLEARK替换&1，使用3100替换&2：
START MYFILE PU_CLERK 3100
4．与用户通信
在SQL*Plus中，可以使用3个命令——PROMPT、ACCEPT和PAUSE与最终用户进行通信。这些命令可以用来发送消息到屏幕，接受最终用户的输入。用户可以使用PROMPT和ACCEPT自定义值的提示，SQL*Plus自动生成替代变量。
PROMPT在屏幕上显示定义的消息，使用此消息引导用户进行操作。ACCEPT提示用户输入值，将输入的值存储在定义的变量中。
例如，下面的代码将提供报告标题，并将其存储在变量MYTITLE中。
SQL> CLEAR BUFFER buffer已清除 SQL> INPUT 1 PROMPT Please input a title 2 ACCEPT MYTITLE PROMPT 'Title:' 3 TTITLE LEFT MYTITLE SKIP2 4 SELECT ENAME,JOB 5 FROM SCOTT.EMP E,SCOTT.DEPT D 6 WHERE E.DEPTNO=D.DEPTNO 7 AND DNAME='SALES' 8 SQL> SAVE TEST1 已创建文件 TEST1.sql
在上面命令文件的第三行中，TTITLE命令是用来为报告设置标题的。运行该命令文件，如下所示。
SQL> @TEST1 Please input a title Title:Employee in Sales Employee in Sales ENAME JOB --------------- ---------------- ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN BLAKE MANAGER TURNER SALESMAN JAMES CLERK 已选择6行。
在继续操作以前，关闭TTITLE命令。
TTITLE OFF
对于上面介绍的替代变量，如果在执行命令时突然要求用户给一个变量赋值，用户往往会不知所措。此时，利用PROMPT命令创建提示信息，就可以避免这种尴尬。并且，使用ACCEPT命令还可以制定接受赋值的数据类型。例如，希望用户输入合适的部门ID（数字型），可是用户偏偏要输入部门名称（字符串），这样导致的错误往往不能引起用户的注意。而且，用户往往会以为是命令编写时的错误而不是自己输入变量值的时候发生了错误。
下面的例子给出了一个范本。
SQL> CLEAR BUFFER buffer已清除 SQL> INPUT 1 PROMPT Enter a valid department ID 2 PROMPT For example 10, 20, 30 or 40 3 ACCEPT DEPTNO NUMBER PROMPT 'Department ID: ' 4 SELECT * FROM SCOTT.DEPT 5 WHERE DEPTNO=&DEPTNO 6 SQL> SAVE TEST2 已创建文件 TEST2.sql
执行命令文件：
SQL> TTITLE OFF
SQL> @TEST2
Enter a valid department ID
For example 10, 20, 30 or 40
Department ID: 20
原值 2: WHERE DEPTNO=&DEPTNO
新值 2: WHERE DEPTNO= 20
DEPTNO DNAME LOC
------------------ ----------------- -------------
20 RESEARCH DALLAS
如果用户不输入数字，则会显示错误，并要求用户再次输入变量的值。
SQL> @TEST2 Enter a valid department ID For example 10, 20, 30 or 40 Department ID: RESEARCH SP4-0425: "RESEARCH"是无效的数字 Department ID:
如果希望在用户的屏幕上显示消息，然后当用户读取消息后再让用户输入，那么，可以使用SQL*Plus的PAUSE命令。例如，将上面的命令文件TEST2更改为如下所示。
PROMPT Enter a valid department ID PROMPT For example 10, 20, 30 or 40 PAUSE Press ENTER to continue ACCEPT DEPTNO PROMPT 'Department ID: ' SELECT * FROM SCOTT.DEPT WHERE DEPTNO=&DEPTNO
运行命令文件，结果如下：
SQL> @TEST2 Enter a valid department ID For example 10, 20, 30 or 40 Press ENTER to continue
首先要求用户按ENTER键表示已经阅读完提示内容，用户按ENTER键后命令才继续运行。
Department ID: 20
原值 2: WHERE DEPTNO=&DEPTNO
新值 2: WHERE DEPTNO=20
DEPTNO DNAME LOC
------------------ ----------------- -------------
20 RESEARCH DALLAS
如果希望在显示报告之前首先清除屏幕，则可以使用CLEAR命令加上SCREEN。将命令文件TEST2更改为如下所示。
PROMPT Enter a valid department ID PROMPT For example 10, 20, 30 or 40 PAUSE Press ENTER to continue ACCEPT DEPTNO PROMPT 'Department ID: ' CLEAR SCREEN SELECT * FROM SCOTT.DEPT WHERE DEPTNO=&DEPTNO
8.2.7 使用绑定变量
假设希望显示SQL*Plus中的PL/SQL子程序使用的变量，或者在多个子程序中使用相同的变量。如果在PL/SQL子程序中声明变量，则不能在SQL*Plus中显示，可以在PL/SQL中使用绑定变量访问来自SQL*Plus的变量。
绑定变量是在SQL*Plus中创建的变量，然后在PL/SQL和SQL中引用，就像在PL/SQL子程序中声明的变量一样。我们可以使用绑定变量存储返回的代码，调试PL/SQL子程序。
可以使用VARIABLE命令在SQL*Plus中创建绑定变量，例如：
VARIABLE ret_val NUMBER
该命令创建了一个绑定变量，称为ret_val，数据类型是NUMBER类型。
在PL/SQL中通过键入冒号（：）引用绑定变量，例如：
:ret_val := 1;
当需要在SQL*Plus中改变绑定变量的值的时候，必须进入PL/SQL块中，例如：
SQL> VARIABLE ret_val NUMBER SQL> BEGIN 2 :ret_val:=4; 3 END; 4 / PL/SQL过程已成功完成。
该命令将值赋予绑定变量ret_val。
如果需要在SQL*Plus中显示绑定变量，则可以使用SQL*Plus命令PRINT，例如：
SQL> PRINT RET_VAL
RET_VAL
----------------
4
SQL*Plus提供了REFCURSOR绑定变量，使得SQL*Plus能够提取和格式化PL/SQL块中包含的SELECT语句返回的结果。REFCURSOR绑定变量能够用于引用存储过程中的PL/SQL块的游标变量，使得用户能够将SELECT语句存储在数据库中，被SQL*Plus引用。
下面的示例显示如何创建、引用和显示REFCURSOR绑定变量，首先，声明REFCURSOR数据类型的本地绑定变量。
VARIABLE employee_info REFCURSOR
然后，进入在OPEN…FOR SELECT语句的绑定变量，该语句打开一个游标，执行查询，在本例中，我们将SQL*Plus employee_info变量绑定给游标变量。
BEGIN OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID=’SA_MAN’ ; END; / PL/SQL 过程已成功完成。
SELECT语句的结果现在显示在SQL*Plus中。
PRINT employee_info EMPLOYEE_ID SALARY --------------------------- ------------------------ 145 14000 146 13500 147 12000 148 11000 149 10500
PRINT命令同样关闭游标，如果需要重新打印结果，就需要重新执行PL/SQL块。
下面演示如何在过程中使用REFCURSOR绑定变量。REFCURSOR绑定变量作为参数传给过程，参数包含REFCURSOR类型。首先，定义类型。
CREATE OR REPLACE PACKAGE cv_types AS TYPE EmpInfoTyp is REF CURSOR RETURN emp%ROWTYPE; END cv_types; / 包已创建。
然后，创建存储过程，包含OPEN…FOR SELECT语句。
CREATE OR REPLACE PROCEDURE EmpInfo_rpt (emp_cv IN OUT cv_types.EmpInfoTyp) AS BEGIN OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW - WHERE JOB_ID=’SA_MAN’ ; END; / 过程已创建。
执行带有SQL*Plus绑定变量的过程。
VARIABLE odcv REFCURSOR EXECUTE EmpInfo_rpt(:odcv) PL/SQL 过程已成功完成。
打印绑定变量：
PRINT odcv EMPLOYEE_ID SALARY --------------------------- ------------------------ 145 14000 146 13500 147 12000 148 11000 149 10500
这个过程可以使用相同或者不同的REFCURSOR绑定变量执行多次。
VARIABLE pcv REFCURSOR EXECUTE EmpInfo_rpt(:pcv)
其结果为：
PL/SQL 过程已成功完成。
输入下面的命令：
PRINT pcv
得到的结果如下：
EMPLOYEE_ID SALARY --------------------------- ------------------------ 145 14000 146 13500 147 12000 148 11000 149 10500
同样，可以在存储的函数中使用REFCURSOR变量，首先创建一个包含OPEN…FOR SELECT语句的存储函数。
CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN - cv_types.EmpInfo IS resultset cv_types.EmpInfoTyp; BEGIN OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW - WHERE JOB_ID=’SA_MAN’ ; RETURN(resultset); END; / 函数已创建。
执行函数：
VARIABLE rc REFCURSOR EXECUTE :rc := EmpInfo_fn
返回结果如下：
PL/SQL 过程已成功完成。
打印绑定变量：
PRINT rc
得到的结果如下：
EMPLOYEE_ID SALARY --------------------------- ------------------------ 145 14000 146 13500 147 12000 148 11000 149 10500
函数可以使用相同的，也可以是不同的绑定变量执行多次。
EXECUTE :rc := EmpInfo_fn
显示结果如下：
PL/SQL 过程已成功完成。
打印绑定变量：
PRINT rc
输出结果如下：
EMPLOYEE_ID SALARY --------------------------- ------------------------ 145 14000 146 13500 147 12000 148 11000 149 10500
8.2.8 跟踪语句
用户通过SQL优化器和语句执行统计自动获得执行路径的报告，该报告在成功执行SQL DML以后生成，对于监视和调整这些语句的性能是非常重要的。
1．控制报告
我们可以设置AUTOTRACE系统变量控制报告。
◎ SET AUTOTRACE OFF：不会生成AUTOTRACE报告，这是默认情况；
◎ SET AUTOTRACE ON EXPLAIN：AUTOTRACE报告只显示优化器执行路径的报告；
◎ SET AUTOTRACE ON STATISTICS：AUTOTRACE显示SQL语句执行的统计；
◎ SET AUTOTRACE ON：AUTOTRACE报告报告优化器执行路径和SQL语句执行统计；
◎ SET AUTOTRACE TRACEONLY：与SET AUTOTRACE ON 类似，只是不显示查询输出。
要使用这些特性，就必须在方案中创建PLAN_TABLE表，然后，将PLUSTRACE角色赋予你。为此，需要DBA权限进行授权。
在SQL*Plus会话中执行下面的命令创建PLAN_TABLE。
CONNECT HR/HR @$ORACLE_HOME\RDBMS\ADMIN\UTLXPLAN.SQL
例如：C:\ORACLE\RDBMS\ADMIN\UTLXPLAN.SQL
显示结果如下：
表已创建。
可以在SQL*Plus会话中使用下面的命令创建PLUSTRACE角色，将该角色授予DBA。
CONNECT PLUSTRACE/PLUSTRACE AS SYSDBA @$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL
显示结果如下：
SQL> drop role plustrace; 角色已丢弃。 SQL> create role plustrace; 角色已创建。 SQL> SQL> grant select on v_$sesstat to plustrace; 授权成功。 SQL> grant select on v_$statname to plustrace; 授权成功。 SQL> grant select on v_$session to plustrace; 授权成功。 SQL> grant plustrace to dba with admin option; 授权成功。
创建角色以后，进行授权，执行下面的命令，将PLUSTRACE角色授权给HR用户。
CONNECT/AS SYSDBA GRANT PLUSTRACE TO HR;
显示结果如下：
授权成功。
2．执行计划
执行计划显示了SQL优化器执行查询的路径。执行计划的每行都包含一个序列号，SQL*Plus显示了父操作的序列号。
执行计划包含4列，如表8-2所示。
表8-2 执行计划
列的格式可以使用COLUMN命令进行修改，例如，为了停止PARENT_ID_PLUS_EXP列的显示，可以输入下面的命令：
COLUMN PARENT_ID_PLUS_EXP NOPRINT
可以使用EXPLAIN PLAN命令生成执行计划输出。
当语句执行时，请求服务器资源，服务器就会生成统计信息，在统计中的客户就是SQL*Plus。Oracle Net指的是SQL*Plus与服务器之间的进程通信，用户不能改变统计报告的格式。下面演示如何跟踪性能统计和查询执行路径的语句。首先假定SQL缓冲区中包含下面的语句。
SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE FROM HR.EMPLOYEES E, HR.JOBS J WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000
当语句执行时，可以自动执行：
SET AUTOTRACE ON
/
返回结果如下：
LAST_NAME SALARY JOB_TITLE
------------------------- ------------------ -----------------------------------
King 24000 President
Kochhar 17000 AdministrationVice President
De Haan 17000 Administration Vice President
Russell 14000 Sales Manager
Partners 13500 Sales Manager
Hartstein 13000 Marketing Manager
已选择6行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=59 Bytes=2832)
1 0 HASH JOIN (Cost=3 Card=59 Bytes=2832)
2 1 TABLE ACCESS (FULL) OF 'JOBS' (Cost=1 Card=19 Bytes=513)
3 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=59 Bytes=1239)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
也可以跟踪语句，不用显示查询结果，输入下面的代码：
SET AUTOTRACE TRACEONLY
/
显示结果如下：
已选择6行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=59 Bytes=2832)
1 0 HASH JOIN (Cost=3 Card=59 Bytes=2832)
2 1 TABLE ACCESS (FULL) OF 'JOBS' (Cost=1 Card=19 Bytes=513)
3 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=59 Bytes=1239)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
该选项对调试大型查询时非常有用。
下面演示如何使用数据库连接跟踪语句，输入下面的语句：
SET AUTOTRACE TRACEONLY EXPLAIN SELECT * FROM HR.EMPLOYEES@SID;
例如：SELECT * FROM HR.EMPLOYEES@luyao
显示结果如下：
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=107 Bytes=7276) 1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=107 Bytes=7276)
有时，我们需要跟踪并行和分布式查询，当跟踪并行和分布式查询的时候，执行计划显示了优化器估计的成本。例如，我们使用并行查询选项跟踪语句，输入下面的代码：
CREATE TABLE D2_T1 (UNIQUE1 NUMBER) PARALLEL -(DEGREE 6);
显示结果如下：
表已创建。
再输入下面的代码：
CREATE TABLE D2_T2 (UNIQUE1 NUMBER) PARALLEL - (degree 6);
显示结果如下：
表已创建。
输入下面的代码：
CREATE UNIQUE INDEX D2_I_UNIQUE1 ON D2_T1(UNIQUE1);
显示结果如下：
索引已创建。
为了创建执行计划，输入下面的代码：
SET LONG 500 LONGCHUNKSIZE 500 SET AUTOTRACE ON EXPLAIN SELECT /*+ INDEX(B,D2_I_UNIQUE1) USE_NL(B) ORDERED - */ COUNT (A.UNIQUE1) FROM D2_T2 A, D2_T1 B WHERE A.UNIQUE1 = B.UNIQUE1;
显示结果如下：
---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26) 1 0 SORT (AGGREGATE) 2 1 SORT* (AGGREGATE) :Q2000 3 2 NESTED LOOPS* (Cost=1 Card=41 Bytes=1066) :Q2000 4 3 TABLE ACCESS* (FULL) OF ’D2_T2’ (Cost=1 Card=41 Byte :Q2000 s=533) 5 3 INDEX* (UNIQUE SCAN) OF ’D2_I_UNIQUE1’ (UNIQUE) :Q2000 2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(A1.C0 )) FROM (SELECT /*+ ORDERED NO_EXPAND USE_NL (A3) INDEX(A3 "D2_I_UNIQUE1") */ A2.C0 C0,A3 .ROWID C1,A3."UNIQUE1" C2 FROM (SELECT /*+ N O_EXPAND ROWID(A4) */ A4."UNIQUE1" C0 FROM " D2_T2" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A4) A2,"D2_T1" A3 WHERE A2.C0=A3."UNIQUE1") A1 3 PARALLEL_COMBINED_WITH_PARENT 4 PARALLEL_COMBINED_WITH_PARENT 5 PARALLEL_COMBINED_WITH_PARENT
8.3 使用SQL*Plus格式化查询结果
本节主要介绍如何格式化查询结果，生成良好的报告。主要包括以下几点：
◎ 格式化列；
◎ 使用空间和概述行阐明报告；
◎ 定义页与报告的标题和维；
◎ 存储和打印结果；
◎ 创建Web报告。
8.3.1 格式化列
通过SQL*Plus的COLUMN命令，可以改变列的标头，重新格式化查询中的列的数据。
1．修改列标头
当显示列标题时，可以使用默认的标头，也可以使用COLUMN命令修改列标头。下面，我们将介绍如何使用COLUMN命令进行修改。
当显示查询结果时，SQL*Plus使用列或者表达式名称作为列的标题。如果需要改变默认标题，可以使用COLUMN命令，具体格式如下：
COLUMN column_name HEADING column_heading
为了生成一个来自EMP_DETAILS_VIEW的报告，带有新的标题：LAST_NAME，SALARY，和COMMISSION_PCT，输入下面的命令：
COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' COLUMN COMMISSION_PCT HEADING COMMISSION SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM HR.EMP_DETAILS_VIEW WHERE JOB_ID=’SA_MAN’
显示结果如下：
LAST NAME MONTHLY SALARY COMMISSION ------------------------- -------------------------- ----------------------- Russell 14000 .4 Partners 13500 .3 Errazuriz 12000 .3 Cambrault 11000 .3 Zlotkey 10500 .2
也可以拆分列标题，例如，输入下面的命令：
COLUMN SALARY HEADING 'MONTHLY|SALARY' COLUMN LAST_NAME HEADING 'LAST|NAME' /
得到的结果如下：
LAST MONTHLY NAME SALARY COMMISSION ------------------------- -------------------------- ----------------------- Russell 14000 .4 Partners 13500 .3 Errazuriz 12000 .3 Cambrault 11000 .3 Zlotkey 10500 .2
同样，我们可以设置下画线字符，为此，输入下面的命令：
SET UNDERLINE = /
得到的结果如下：
LAST MONTHLY NAME SALARY COMMISSION =========== ================== ============ Russell 14000 .4 Partners 13500 .3 Errazuriz 12000 .3 Cambrault 11000 .3 Zlotkey 10500 .2
2．格式化NUMBER列
当显示NUMBER列时，可以接受SQL*Plus的默认显示格式，也可以使用COLUMN命令进行修改，下面介绍如何使用COLUMN命令修改默认设置。
NUMBER列的宽度等于标题的宽度或者是FORMAT的宽度加上一个空格。如果没有使用FORMAT，则该列的宽度至少是SET NUMWIDTH的值。
一般情况下，SQL*Plus尽可能显示数字，直到SET命令设置的NUMWIDTH变量的值。如果数字多于SET NUMWIDTH的值，则SQL*Plus将截断该值，使得它到达所允许的最大数字数。
用户可以在COLUMN命令中使用格式化模型，选择任何NUMBER列的不同格式，格式化模型是用来显示数字列的描述方式，使用9s表示数字。
下面介绍如何修改默认显示。COLUMN命令标识了希望格式化的列和使用的模型，具体如下：
COLUMN column_name FORMAT model
使用格式化模型添加逗号、美元符号等，可以将值限定为给定的十进制数。下面的示例显示带有美元符的SALARY，命令如下：
COLUMN SALARY FORMAT $99,990 /
得到的结果如下：
LAST MONTHLY NAME SALARY COMMISSION ------------------------- -------------------------- ----------------------- Russell $14,000 .4 Partners $13,500 .3 Errazuriz $12,000 .3 Cambrault $11,000 .3 Zlotkey $10,500 .2
3．格式化数据类型
当显示数据类型时，可以接受SQL*Plus的默认显示格式，也可以使用COLUMN命令进行修改，主要有以下数据类型：
◎ CHAR
◎ NCHAR
◎ VARCHAR2 (VARCHAR)
◎ NVARCHAR2 (NCHAR VARYING)
◎ DATE
◎ LONG
◎ CLOB
◎ NCLOB
当需要改变数据类型的显示宽度或DATE时，可以使用COLUMN命令，带上格式化模型，具体格式如下：
COLUMN column_name FORMAT model
如果定义列的宽度小于列的标题，则SQL*Plus截断标题；如果为LONG,CLOB和NCLOB定义宽度，那么，SQL*Plus使用LONGCHUNKSIZE或者定义的宽度。
例如，设置LAST_NAME列的宽度为4个字符，返回查询结果，输入下面的命令：
COLUMN LAST_NAME FORMAT A4 /
得到的结果如下：
LAST MONTHLY NAME SALARY COMMISSION ------------------------- -------------------------- ----------------------- Russ $14,000 .4 ell Part $13,500 .3 ners Erra $12,000 .3 zuriz Camb $11,000 .3 rault Zlot $10,500 .2 key
4．复制列显示属性
当希望给定多个列相同的显示属性时，可以在COLUMN命令中使用LIKE子句减少输入命令的长度。LIKE子句告诉SQL*Plus拷贝前面定义的列显示属性给新的列。例如，输入下面的命令：
COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS
返回的查询结果如下：
LAST MONTHLY NAME SALARY COMMISSION ------------------------- -------------------------- ----------------------- Russell $14,000 $0 Partners $13,500 $0 Errazuriz $12,000 $0 Cambrault $11,000 $0 Zlotkey $10,500 $0
5．列出和重新设置列显示属性
为了列出给定列的当前显示属性，可以使用COLUMN命令，格式如下：
COLUMN column_name
为了显示所有列的显示属性，可以输入下面的命令：
COLUMN
重新设置列的显示属性为默认情况，使用下面的命令格式：
COLUMN column_name CLEAR
6．在外层列值后面打印一行字符
默认情况下，当显示的值不适合列的宽度时，SQL*Plus将列的值包装为附加行。如果希望插入一个记录分隔符，则可以使用SET命令的RECSEP和RECSEPCHAR变量。RECSEP决定何时打印字符行，将RECSEP设置为EACH，即表示在每行后面进行打印；设置为WRAPPED，就表示在包装行后面打印，设置为OFF表示压缩打印。RECSEP的默认值为WRAPPED。
首先，在缓冲区中输入下面的查询（以空格行终止，仅保存在缓冲区而不执行）。
SELECT LAST_NAME, JOB_TITLE, CITY FROM HR.EMP_DETAILS_VIEW WHERE SALARY>12000;
RECSEPCHAR设置在每行中打印的字符，可以将RECSEPCHAR设置为任何字符。例如：
SET RECSEP WRAPPED SET RECSEPCHAR "-"
显示JOB_TITLE列的宽度：
COLUMN JOB_TITLE FORMAT A20 WORD_WRAPPED
运行查询，得到的查询结果如下：
/
LAST
NAME JOB_TITLE CITY
------------------------- -------------------- ------------------------------
King President Seattle
Kochhar Administration Vice Seattle
President
--------------------------------------------------------------------------------------
De Haan Administration Vice Seattle
President
--------------------------------------------------------------------------------------
Hartstein Marketing Manager Toronto
Russell Sales Manager Oxford
Partners Sales Manager Oxford
已选择6行。
7．使用空格和概述行阐明报告
当在SQL SELECT命令中使用ORDER BY子句时，将在输出结果中对行数据进行排序。使用SQL*Plus的BREAK和COMPUTE命令可以创建记录的子集合，添加空格和概述行。
例如，对于下面的查询，没有BREAK和COMPUTE命令。
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM HR.EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID;
得到的结果如下：
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
20 Hartstein 13000
80 Russell 14000
80 Partners 13500
90 King 24000
90 Kochhar 17000
90 De Haan 17000
已选择6行。
用户可以使用BREAK命令，制定某一列作为断行，对于重复的数据只显示一次，如下所示：
BREAK ON DEPARTMENT_ID;
执行上面的查询，得到的结果如下：
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
20 Hartstein 13000
80 Russell 14000
Partners 13500
90 King 24000
Kochhar 17000
De Haan 17000
已选择6行。
断开列时，可以插入空白行，也可以开始一个新页，插入n个空白行，使用BREAK命令，格式如下：
BREAK ON break_column SKIP n
使用下面的命令跳过页：
BREAK ON break_column SKIP PAGE
例如，下面的示例表示在一个断开列的值改变时，插入空白行。
BREAK ON DEPARTMENT_ID SKIP 1
执行查询，得到的结果如下：
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
---------------------------- -------------------------- ---------------------
20 Hartstein 13000
80 Russell 14000
Partners 13500
90 King 24000
Kochhar 17000
De Haan 17000
已选择6行。
还可以使用多空格技巧。假设在ORDER BY子句中有多行，希望在每列值修改时插入空白行，为此，首先清除缓冲区。
CLEAR BUFFER
然后，在缓冲区中输入下面的命令（以空格行的方式终止）：
SELECT DEPARTMENT_ID, JOB_ID, LAST_NAME, SALARY FROM HR.EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID,JOB_ID
当DEPARTTMENT_ID值修改同时JOB_ID值被修改时，跳过一行，输入下面的命令：
BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1
为了显示SKIP PAGE的影响，创建带有页码的TTITLE：
TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO
运行新的查询，结果如下：
Page: 1
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
20 Hartstein 13000
Page: 2
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
80 Russell 14000
Partners 13500
Page: 3
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
90 King 24000
Kochhar 17000
De Haan 17000
已选择6行。
如果已经使用BREAK命令将报告的行分为子集，那么，可以对每个子集执行很多计算。为此，可以使用SQL*Plus的COMPUTE函数，语法如下：
BREAK ON break_column COMPUTE function LABEL label_name OF column column column ... ON break_column
用户可以包含多个断列和操作，使用COMPUTE时，包含多个断列和操作，可以使用下面的命令格式：
BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1 COMPUTE function LABEL label_name OF column column column ... ON break_column_2
COMPUTE命令如果没有对应的BREAK命令，将不会产生任何影响，下面列出了计算函数及其功能，如表8-3所示。
表8-3 计算函数
这些函数可以应用于在OF后、ON前的列，计算的值将在不同行显示。
例如，下面的代码按照部门计算SALARY的总和，首先列出当前的BREAK定义。
BREAK
显示结果如下：
break on DEPARTMENT_ID page nodup on JOB_ID skip 1 nodup
输入下面的COMPUTE命令，运行当前查询：
COMPUTE SUM OF SALARY ON DEPARTMENT_ID /
得到的结果如下：
Page: 1
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
20 Hartstein 13000
***************** ---------------------
sum 13000
Page: 2
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
80 Russell 14000
Partners 13500
***************** ---------------------
sum 27500
Page: 3
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
90 King 24000
Kochhar 17000
De Haan 17000
***************** ---------------------
sum 58000
已选择6行。
为了计算部门10和部门20的薪水总和，不要打印计算标签，输入下面的命令：
COLUMN DUMMY NOPRINT; COMPUTE SUM OF SALARY ON DUMMY; BREAK ON DUMMY SKIP 1; SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY FROM HR.EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID;
得到的结果如下：
Page: 1
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
20 Hartstein 13000
---------------------
13000
Page: 2
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
80 Russell 14000
80 Partners 13500
---------------------
27500
Page: 3
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
90 King 24000
90 Kochhar 17000
90 De Haan 17000
---------------------
58000
已选择6行。
在报告后面计算薪水：
COLUMN DUMMY NOPRINT; COMPUTE SUM OF SALARY ON DUMMY; BREAK ON DUMMY; SELECT NULL DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY FROM HR.EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID;
得到的结果如下：
Page: 1
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
20 Hartstein 13000
80 Russell 14000
80 Partners 13500
90 King 24000
90 Kochhar 17000
90 De Haan 17000
---------------------
98500
已选择6行。
如果需要在报告后面计算的总结行，可以使用下面的命令格式。
BREAK ON REPORT COMPUTE function LABEL label_name OF column column column ... ON REPORT
例如，计算并打印所有销售的薪水总和，并修改计算标签。首先，输入下面的BREAK和COMPUTE命令。
BREAK ON REPORT COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT
然后，输入并运行新的查询。
SELECT LAST_NAME, SALARY FROM HR.EMP_DETAILS_VIEW WHERE JOB_ID=’SA_MAN’;
得到的结果如下：
Page: 1
LAST MONTHLY
NAME SALARY
------------------------------------- ------------------
Russell 14000
Partners 13500
Errazuriz 12000
Cambrault 11000
Zlotkey 10500
------------------
TOTAL 61000
要打印全部查询的总和，可以使用下面的命令格式。
BREAK ON break_column ON REPORT COMPUTE function LABEL label_name OF column ON break_column COMPUTE function LABEL label_name OF column ON REPORT
同时，用户可以计算多个总结值和行，可以输入下面的命令。
COMPUTE SUM OF SALARY COMMISSION_PCT ON REPORT
改变所需查询的第一行，包含COMMISSION_PCT。
SQL> L1 1* SELECT LAST_NAME, SALARY SQL> APPEND , COMMISSION_PCT 1* SELECT LAST_NAME, SALARY, COMMISSION_PCT
最后，运行修改的查询，查看结果。
Page: 1
LAST MONTHLY
NAME SALARY COMMISSION
------------------------------------- ------------------ -------------------
Russell 14000 .4
Partners 13500 .3
Errazuriz 12000 .3
Cambrault 11000 .3
Zlotkey 10500 .2
------------------ -------------------
sum 61000 1.5
用户还可以在相同的断列上打印多个总结行，可以将每个总结行的函数包含在COMPUTE命令中。
COMPUTE function LABEL label_name function LABEL label_name function LABEL label_name ... OF column ON break_column
如果在OF与ON之间包含了多个列，COMPUTE将计算并打印每个列的值。
例如，计算销售部门薪水的平均值和总和，首先输入BREAK和COMPUTE命令。
BREAK ON DEPARTMENT_ID COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID
然后，输入并运行下面的命令。
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM HR.EMP_DETAILS_VIEW WHERE DEPARTMENT_ID = 30 ORDER BY DEPARTMENT_ID, SALARY;
得到的结果如下：
Page: 1
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
30 Colmenares 2500
Himuro 2600
Tobias 2800
Baida 2900
Khoo 3100
Raphaely 11000
***************** ---------------------
avg 4150
sum 24900
已选择6行。
8.3.2 定义页与报告的标题和维
“页”是指全屏幕的信息，用户可以在每页添加上下标题、设置每页的行数，及确定每行的宽度。
“报告”是指一个查询的完整结果，可以在每个报告上加上标题和脚注，就像页的上下标题一样。
1．设置上下标题、题头和脚注
在上面的介绍中，我们已经简单介绍过使用TTITLE命令设置报告的上标题的方法。同样，也可以设置每页的下标题。TTITLE命令定义上标题，BTITTLE命令定义下标题。还可以为每个报告设置题头，REPHEADER命令定义报告题头，REPFOOTER命令定义报告脚注。
TTITLE，BTITLE，REPHEADER和REPFOOTER命令包含了子句，用于定义其格式、位置。用户可以包含多个子句。
TTITLE position_clause(s) char_value position_clause(s) char_value ... BTITLE position_clause(s) char_value position_clause(s) char_value ... REPHEADER position_clause(s) char_value position_clause(s) char_value ... REPFOOTER position_clause(s) char_value position_clause(s) char_value ...
若要在报告的每页上面添加标题，可以输入下面的命令。
TTITLE CENTER - "ACME SALES DEPARTMENT PERSONNEL REPORT" BTITLE CENTER "COMPANY CONFIDENTIAL"
运行查询，得到的结果如下：
ACME SALES DEPARTMENT PERSONNEL REPORT LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
30 Colmenares 2500
Himuro 2600
Tobias 2800
Baida 2900
Khoo 3100
Raphaely 11000
***************** ---------------------
avg 4150
COMPANY CONFIDENTIAL
ACME SALES DEPARTMENT PERSONNEL REPORT
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------------------- -------------------------- ---------------------
sum 24900
COMPANY CONFIDENTIAL
已选择6行。
若要将题头置于一个报告的上面，可以输入下面的命令。
REPHEADER PAGE CENTER 'PERFECT WIDGETS'
执行当前查询：
/
输出结果如下：
ACME SALES DEPARTMENT PERSONNEL REPORT PERFECT WIDGETS COMPANY CONFIDENTIAL ACME SALES DEPARTMENT PERSONNEL REPORT LAST MONTHLY DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- 30 Colmenares 2500 Himuro 2600 Tobias 2800 Baida 2900 Khoo 3100 Raphaely 11000 ***************** --------------------- avg 4150 COMPANY CONFIDENTIAL ACME SALES DEPARTMENT PERSONNEL REPORT LAST MONTHLY DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- sum 24900 COMPANY CONFIDENTIAL 已选择6行。
同样，可以定位标题元素，输入下面的命令。
TTITLE CENTER 'A C M E W I D G E T' SKIP 1 - CENTER ================== SKIP 1 LEFT 'PERSONNEL REPORT' - RIGHT 'SALES DEPARTMENT' SKIP 2 SET LINESIZE 60 /
得到的结果如下：
A C M E W I D G E T ================== PERSONNEL REPORT SALES DEPARTMENT PERFECT WIDGETS COMPANY CONFIDENTIAL A C M E W I D G E T ================== PERSONNEL REPORT SALES DEPARTMENT LAST MONTHLY DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- 30 Colmenares 2500 Himuro 2600 Tobias 2800 Baida 2900 Khoo 3100 COMPANY CONFIDENTIAL A C M E W I D G E T ================== PERSONNEL REPORT SALES DEPARTMENT DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- 30 Raphaely 11000 ***************** --------------------- avg 4150 sum 24900 COMPANY CONFIDENTIAL 已选择6行。
在TTITLE或BTITTLE命令中使用COL子句，可以缩进标题元素。输入下面的命令：
TTITLE LEFT 'ACME WIDGET' SKIP 1 - COL 6 'SALES DEPARTMENT PERSONNEL REPORT' SKIP 2
执行当前查询，返回结果如下：
ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT PERFECT WIDGETS COMPANY CONFIDENTIAL ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT LAST MONTHLY DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- 30 Colmenares 2500 Himuro 2600 Tobias 2800 Baida 2900 Khoo 3100 COMPANY CONFIDENTIAL ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- 30 Raphaely 11000 ***************** --------------------- avg 4150 sum 24900 COMPANY CONFIDENTIAL 已选择6行。
当需要输入一个大于500个的字符擦黑标题时，可以使用SQL*Plus的DEFINE命令：
DEFINE LINE1 = 'This is the first line...' DEFINE LINE2 = 'This is the second line...' DEFINE LINE3 = 'This is the third line...'
然后，在TTILTLE或BTITTLE命令中引用变量。
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 – CENTER LINE3
2．显示页序号和系统维护值
如果需要显示当前页的序号和标题中系统维护值，那么，可以使用下面的命令：
TTITLE LEFT system-maintained_value_name
例如，要在每页上显示页序号，可以输入下面的命令：
TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' SQL.PNO SKIP 2
执行当前查询，得到的结果如下：
ACME WIDGET PAGE: 1 PERFECT WIDGETS ACME WIDGET PAGE: 2 LAST MONTHLY DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- 30 Colmenares 2500 Himuro 2600 Tobias 2800 Baida 2900 Khoo 3100 30 Raphaely 11000 ***************** --------------------- COMPANY CONFIDENTIAL ACME WIDGET PAGE: 3 LAST MONTHLY DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- avg 4150 sum 24900 COMPANY CONFIDENTIAL 已选择6行。
还可以格式化标题中系统维护值，命令格式如下：
TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' FORMAT 999 - SQL.PNO SKIP 2
3．列出、压缩和恢复页标题的定义
若要列出标题定义，可以输入下面的命令：
TTITLE BTITLE
若要压缩标题定义，可以输入下面的命令：
TTITLE OFF BTITLE OFF
如果需要恢复当前定义，可以输入下面的命令：
TTITLE ON BTITLE ON
4．显示标题的列值
如果希望创建详细报告，用来在页的上面显示改变的主列的值。也可以在上标题引用一个列值，通过将值存储在变量中，在TTITLE命令中引用变量，使用下面的命令格式定义变量。
COLUMN column_name NEW_VALUE variable_name
下面的示例显示如何创建详细报告。详细报告用于显示两个不同经理的雇员号，每个雇员号都在不同的页上。首先，创建变量MGRVAR，保存当前经理的雇员号。
COLUMN MANAGER_ID NEW_VALUE MGRVAR NOPRINT
由于只需要在标题中显示经理的雇员号，因而不希望作为细节部分打印。
然后，将标签和值包含在页标题中，输入相应的BREAK命令，压缩标题。
TTITLE LEFT 'Manager: ' MGRVAR SKIP 2 BREAK ON MANAGER_ID SKIP PAGE BTITLE OFF
最后，输入并运行下面的查询。
SELECT MANAGER_ID, DEPARTMENT_ID, LAST_NAME, SALARY FROM HR.EMP_DETAILS_VIEW WHERE MANAGER_ID IN (101, 201) ORDER BY MANAGER_ID, DEPARTMENT_ID;
得到的结果如下：
Manager: 101 PERFECT WIDGETS Manager: 101 LAST MONTHLY DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- 10 Whalen 4400 40 Mavris 6500 70 Baer 10000 100 Greenberg 12000 110 Higgins 12000 Manager: 201 LAST MONTHLY DEPARTMENT_ID NAME SALARY ------------------------- -------------------------- --------------------- 20 Fay 6000 已选择6行。
如果希望在页的下面打印列的值，可以使用COLUMN命令，格式如下：
COLUMN column_name OLD_VALUE variable_name
5．在标题中显示当前日期
当然，可以通过在标题中简单输入一个值，在报告中加入日期。若要创建变量，可以在SQL*Plus的LOGIN文件中添加命令：
SET TERMOUT OFF BREAK ON TODAY COLUMN TODAY NEW_VALUE _DATE SELECT TO_CHAR(SYSDATE, ’fmMonth DD, YYYY’) TODAY FROM DUAL; CLEAR BREAKS SET TERMOUT ON
启动SQL*Plus时，这些命令将SYSDATE值放入变量中。若要显示当前日期，可以在标题中引用_DATE。
6．设置页维
一般情况下，报告的每页都包含一定数量的空行。当一个报告太长、SQL*Plus不能在单页中显示时，将分为几页显示报告，每个都带有自己的标题和列标题。SQL*Plus在每页上显示的数据的数量依赖于当前页的维。
SQL*Plus使用的默认页维如下。
在上标题前面的行的数量：1；
每个页的行数，从上标题到下标题：24；
每行的字符数：80。
若要设置每页的开始和上标题之间的行数，可以使用SET命令的NEWPAGE变量。
SET NEWPAGE number_of_lines
若要设置每页的行数，可以在SET命令中使用PAGESIZE变量。
SET PAGESIZE number_of_lines
例如，将页设置为66行，清除屏幕，设置行大小为70，可以输入下面的命令：
SET PAGESIZE 66 SET NEWPAGE 0 SET LINESIZE 70
重新设置PAGESIZE、NEWPAGE和LINESIZE为默认值。
SET PAGESIZE 24 SET NEWPAGE 1 SET LINESIZE 80
使用SHOW命令列出这些变量的当前值。
SHOW PAGESIZE SHOW NEWPAGE SHOW LINESIZE
8.3.3 存储和打印结果
如果用户希望使用字处理软件编辑查询结果，那么，可以使用SPOOL命令将查询结果发送到文件中，并同时在屏幕上显示。
SPOOL file_name
当在不同软件产品之间移动数据时，很有必要使用“flat”文件。使用SQL*Plus创建一个“flat”文件，必须首先输入SET命令。
SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET MARKUP HTML OFF SPOOL OFF
将结果发送给文件，使用下面的格式。
SPOOL file_name
将结果发送给打印机，输入下面的命令。
SPOOL OUT
例如，若要生成一个最终的报告，然后打印结果，应进行如下操作。
首先，使用操作系统文本编辑器的EDIT命令创建命令文件。
EDIT EMPRPT
然后，在文件中输入下面的命令。
SPOOL TEMP CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN DEPARTMENT_ID HEADING DEPARTMENT COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999 BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT COMPUTE SUM OF SALARY ON DEPARTMENT_ID COMPUTE SUM OF SALARY ON REPORT SET PAGESIZE 24 SET NEWPAGE 0 SET LINESIZE 70 TTITLE CENTER 'A C M E W I D G E T' SKIP 2 - LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' - FORMAT 999 SQL.PNO SKIP 2 BTITLE CENTER 'COMPANY CONFIDENTIAL' SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM HR.EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID; SPOOL OFF
如果不希望在屏幕上看到输出，可以添加SET TERMOUT OFF到文件开始处，将SET TERMOUT ON置于文件后端。
8.4 本章小结
本章重点介绍了SQL*Plus工具的使用，特别适用于对数据库的简单管理和数据的简单操作。