Oracle+11g+笔记(3)-SQL/Plus
3、SQL/Plus
3.1 启动退出SQL/Plus
> sqlplus 账号/密码@数据库
# 示例
> sqlplus scott/tiger@orcl
> sqlplus /nolog -- 无日志登录：避免别人从日志中查询到登录信息
> conn soctt/soctt@orcl
# 示例
> sqlplus /nolog
> conn scott/tiger@orcl
> sqlplus '/ AS SYSDBA'
# 退出
exit或者是quit
3.2 SQL/Plus编辑器的编辑命令
使用SQL*Plus可以方便地编辑和管理编程的过程，其中包括编辑命令、保存命令、加入注释、运行命令、编写交
互命令、使用绑定变量、跟踪语句等。
3.2.1 编辑命令
在使用SQL*Plus的编辑命令时可以使用斜杠(/)加回车命令使最后输入的SQL语句再次被运行。
SQL*Plus有自己内嵌的命令行编辑器，它允许在SQL*Plus中编辑已经保存在缓冲区中的语句。SQL*Plus的行
编辑命令如表所示。
# 示例
SQL> L
1* select * from dept
SQL>
在SQL*Plus中有一个命令，允许定义直接在SQL*Plus中使用的编辑器，其格式为：
define_editor=editor_name
其中，editor_name是用户选择的编辑器的名称。在UNIX 系统中，该编辑器可以是vi，在虚拟内存系统(Virtual
Memory System,VMS)中可以是edt，在 Windows操作系统中可以是notepad。
# 示例
SQL> define_editor=notepad
为了使用以上用户自定义的编辑器，输入命令edit或者缩写ed，Oracle 将使用用户在define_editor命令中定
义的编辑器。例如，在Windows XP 操作系统下，启动SQL*Plus后执行ed命令，将打开记事本程序，缓冲区中
的SQL 语句会自动出现在编辑器中。
ed
3.2.2 保存命令
在SQL*Plus中，可以将一个或多个SQL 命令、PL/SQL块和SQL*Plus命令存储在命令文件中，其方式包括SAVE
命令、INPUT命令、EDIT命令3种。
1、SAVE命令
# 格式
SAVE file_name
使用SAVE命令可以直接将缓冲区中的SQL 语句保存到在当前路径或指定路径下指定的文件中，扩展名是.SQL，
说明是一个SQL查询文件。
SQL> SAVE C:\Users\zhangshixing\Desktop\temp\select
已创建 file C:\Users\zhangshixing\Desktop\temp\select.sql
2、INPUT命令
可以将INPUT和SAVE 命令结合使用，使用 INPUT命令将SQL*Plus命令输入到缓冲区中，然后可以使用SAVE命
令保存到文件中。
SQL> input select deptno from dept;
SQL> input select dname from dept;
SQL> input select loc from dept;
SQL> L *
3* select loc from dept
SQL> SAVE C:\Users\zhangshixing\Desktop\temp\select
已创建 file C:\Users\zhangshixing\Desktop\temp\select.sql
SQL>
select.sql文件的内容：
select deptno from dept
select dname from dept
select loc from dept
/
3、EDIT 命令
可以直接使用EDIT命令创建文件。
EDIT C:\Users\zhangshixing\Desktop\temp\select1
3.2.3 加入注释
在代码中加入注释能够提高可读性，在SQL*Plus中加入注释的方式包括REMARK命令、/*...*/、--3种。
1、使用 REMARK命令
使用REMARK命令在一个命令文件的一行上加注释。例如：
creatreport.sql文件内容：
remark 今天是个好日子;
select * from dept;
SQL> @C:\Users\zhangshixing\Desktop\temp\creatreport.sql
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
2、使用/*...*/
使用 SQL 注释分隔符/*...*/可以对一个命令文件的一行或多行加注释。例如：
/*only female*/
WHERE_SEX='FEMALE'
3、使用–
使用 ANSI/ISO 样式注释--对单行进行注释。例如：
--清除屏幕
CLEAR SCREEN
-- scr代表screen
clear scr
3.2.4 运行命令
运行SQL 命令和 PL/SQL 块有三种方式，分别为命令行方式、SQL缓冲区方式、命令文件方式。
1、命令行方式
在命令后面加分号(;)作为终止符来运行SQL命令的方式。
2、SQL缓冲区方式
SQL*Plus提供了 RUN 命令和斜杠(/)命令来以缓冲区方式执行SQL命令。
# RUN命的格式为
R[un]
SQL> R
1* select LOC from dept
LOC
-------------
NEW YORK
DALLAS
CHICAGO
BOSTON
SQL>
RUN命令列出并执行当前存储在缓冲区中的SQL 命令或PL/SQL块，它可以显示缓冲区的命令并返回查询的结果,并
使缓冲区中的最后一行成为当前行。
斜杠(/)命令类似于RUN 命令，它执行存储在缓冲区中的SQL 命令或 PL/SQL块，但不显示缓冲区的内容，也不会
使缓冲区的最后一行成为当前行。
SQL> /
LOC
-------------
NEW YORK
DALLAS
CHICAGO
BOSTON
SQL>
3、命令文件方式
以命令文件方式运行一个SQL命令或SQL*Plus命令或PL/SQL块，有两种方式：START 命令和@命令。
# START命令的格式为
START file_name[.sql][argl arg2]
SQL*Plus在当前路径下查找具有在START命令中指定的文件名和扩展名的文件。如果没有找到，将在SQLPATH
环境变量定义的目录中查找。参数部分([arg1 arg2])代表用户希望传递给命令文件中的参数值，必须是如下格
式：&1、&2(或者&&1、&&2)。如果输入一个或多个参数，SQL*Plus使用这些值替换命令文件中的参数。
SQL> START C:\Users\zhangshixing\Desktop\temp\creatreport.sql
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
@命令与START命令的功能相似，唯一的区别是@命令既可在SQL*Plus会话内部运行，又可在启动SQL*Plus时
的命令行级别运行，而START命令只能在SQL*Plus会话内部运行。
SQL> @C:\Users\zhangshixing\Desktop\temp\creatreport.sql
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
提示：此外，使用 EXECUTE 命令能够直接在SQL*Plus提示符下执行单条PL/SQL语句，而不需要从缓冲区或命
令文件中执行。
3.2.5 编写交互命令
1、定义用户变量
# 使用DEFINE定义
DEFINE NEWSTU = ZHANGSAN
SQL> DEFINE NEWSTU = ZHANGSAN
SQL> DEFINE
DEFINE _DATE = "28-9月 -22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1102000100" (CHAR)
DEFINE _RC = "0" (CHAR)
DEFINE NEWSTU = "ZHANGSAN" (CHAR)
SQL>
2、在命令中替代值
替代变量是在用户变量名前加入一个或两个&符号的变量。当SQL*Plus遇到一个替代变量时，SQL*Plus执行命
令，好像它包含替代变量的值一样。
SELECT &SORTCOL,SALARY FROM &MYTABLE WHERE SALARY>15000;
等价于：
SELECT TEA_ID,SALARY FROM TEA_VIEW WHERE SALARY>15000;
3、使用START命令提供值
在编写SQL*Plus命令时，也可以使用START命令将命令文件的参数值传给替代变量，这时需要将&符号置于命令
文件数字的前面，替换替代变量。当每次运行该命令文件时，START使用第一个值替换&1，使用第二个值替换
&2，依此类推。
MYFILE.sql文件的内容：
select * from dept where DEPTNO = '&1' and DNAME = '&2';
SQL> START C:\Users\zhangshixing\Desktop\temp\MYFILE 10 ACCOUNTING;
原值 1: select * from dept where DEPTNO = '&1' and DNAME = '&2'
新值 1: select * from dept where DEPTNO = '10' and DNAME = 'ACCOUNTING'
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL>
4、与用户通信
在SQL*Plus中，可以使用PROMPT、ACCEPT、PAUSE 命令与最终用户进行通信，发送消息到屏幕，并接受最终
用户的输入。其具体功能介绍如下：
PROMPT：用于在屏幕上显示定义的消息，提示用户操作。
ACCEPT：用于提示用户输入值，并将输入的值存储在定义的变量中，可以控制输入的数据类型。
如果希望用户读取屏幕上的提示信息后，按键再继续让用户输入，可以使用SQL*Plus提供的PAUSE命令。
SQL> CLEAR BUFFER
buffer 已清除
SQL> PROMPT Please input a valid class
Please input a valid class
SQL> PAUSE Press Enter to continue
Press Enter to continue
SQL> ACCEPT CLASSNO NUMBER PROMPT 'Class no:'
Class no:1
SQL>
3.2.6 使用绑定变量
使用VARIABLE命令在SQL*Plus中创建绑定变量。
VARIABLE ret_val NUMBER
在PL/SQL 中通过输入冒号(:)引用绑定变量，如：
:ret_val :=1;
当需要在SQL*Plus中改变绑定变量的值时，须进入PL/SQL，如：
SQL> variable ret_val NUMBER;
SQL> begin
2 :ret_val :=8;
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> print ret_val;
RET_VAL
----------
8
SQL>
SQL*Plus 也提供了REFCURSOR 来绑定变量，使 SQL*Plus 能够提取和格式化PL/SQL块中包含的 SELECT 语句返
回的结果。
【下面举例说明如何创建、引用和显示REFCURSOR 绑定变量】
SQL> VARIABLE tea_info REFCURSOR
SQL> begin
2 OPEN:tea_info FOR select deptno,dname from dept;
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> print tea_info;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SQL>
【下面举例在存储过程中使用REFCURSOR 绑定变量】
# step1定义类型
CREATE OR REPLACE PACKAGE cv_type
AS
TYPE TeaInfoType is REF CURSOR
RETURN dept%ROWTYPE;
END cv_type;
/
# step2创建存储过程
CREATE OR REPLACE PROCEDURE TeaInfo_rpt
(tea_cv IN OUT cv_type.TeaInfoType)
AS
BEGIN
OPEN tea_cv FOR select deptno,dname,loc from dept;
END TeaInfo_rpt;
/
# step3执行带有SQL*Plus绑定变量的过程。
VARIABLE odcv REFCURSOR
EXECUTE TeaInfo_rpt(:odcv)
PRINT odcv
SQL> VARIABLE odcv REFCURSOR
SQL> EXECUTE TeaInfo_rpt(:odcv)
PL/SQL 过程已成功完成。
SQL> PRINT odcv
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
该过程可以使用相同或不同的 REFCURSOR 绑定变量执行多次。
SQL> VARIABLE pcv REFCURSOR
SQL> EXECUTE TeaInfo_rpt(:pcv)
PL/SQL 过程已成功完成。
SQL> print pcv
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
# step4在存储的函数中使用绑定变量
CREATE OR REPLACE FUNCTION TeaInfo_fn
RETURN cv_type.TeaInfoType
IS
resultset cv_type.TeaInfoType;
BEGIN
OPEN resultset FOR select deptno,dname,loc from dept;
RETURN(resultset);
END;
/
SQL> VARIABLE rc REFCURSOR
SQL> EXECUTE :rc := TeaInfo_fn
PL/SQL 过程已成功完成。
SQL> print rc
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
3.2.7 跟踪语句
可以通过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授权。
其具体操作过程如下：
# Step1在SQL*Plus会话中执行以下命令创建PLAN TABLE
SQL> CONNECT HR/hrroot
已连接。
SQL> @D:\app\zhangshixing\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql;
表已创建。
# 查看表Plan_Table的结构，具体该表的字段解释，请打开官方网站查阅
# https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5127.htm#REFRN29510
SQL> desc PLAN_TABLE;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)
OTHER_XML CLOB
SQL>
# Step2在SQL*Plus会话中使用下面命令创建PLUSTRACE角色,将该角色授予DBA
SQL> CONNECT PLUSTRACE/PLUSTRACE AS SYSDBA
已连接。
SQL> @D:\app\zhangshixing\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
SQL> drop role plustrace;
drop role plustrace
*
第 1 行出现错误:
ORA-01919: 角色 '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_$mystat to plustrace;
授权成功。
SQL> grant plustrace to dba with admin option;
授权成功。
SQL>
SQL> set echo off
SQL>
# Step3执行下面命令将PLUSTRACE角色授权给HR用户。
SQL> CONNECT / AS SYSDBA
已连接。
SQL> GRANT PLUSTRACE TO HR;
授权成功。
SQL>
在SQL*PLUS的窗口运行以下命令：
-
set time on： (说明：打开时间显示) -
set autotrace on： (说明：打开自动分析统计，并显示SQL语句的运行结果) -
set autotrace traceonly：(说明：打开自动分析统计，不显示SQL语句的运行结果) -
set autotrace on explan：(说明：显示SQL语句的运行结果和执行计划，不打开自动分析统计)
接下来你就运行测试SQL语句，看到其分析统计结果了。一般来讲，我们的SQL语句应该避免全表扫描。
关闭以上功能，在SQL/PLUS的窗口运行以下命令：
-
set time off： (说明：关闭时间显示) -
set autotrace off： (说明：关闭自动分析统计)
2、执行报告
执行计划显示了SQL优化器执行查询的路径，执行计划的每行都包含一个序列号，SQL*Plus显示了该操作的序列
号。PLAN_TABLE表中列的格式可用COLUMN命令修改，可以用EXPLAIN PLAN命令生成执行计划输出。
当语句执行时，请求服务器资源，服务器就会生成统计信息，在统计中的客户就是SQL*Plus。Oracle Net 指的
是SQL*Plus与服务器之间的进程通信。用户不能改变统计报告的格式。
3、操作示例
通过explain plan for指令分析SQL语句的执行计划。
SQL> explain plan for
2 select count(*) from employees;
已解释。
SQL>
查看表Plan_Table中的SQL语句执行计划信息。
SQL> set linesize 9999;
SQL> select id,operation,options,object_name,position from plan_table;
ID OPERATION OPTIONS
OBJECT_NAME POSITION
---------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT
1
1 SORT AGGREGATE
1
2 INDEX FULL SCAN
EMP_EMAIL_UK 1
SQL>
从输出结果，我们可以看到SQL语句的执行过程，结果的最后一行，ID说明步骤标识，OPERATION为INDEX说明
该步骤的行为是索引操作，OPTIONS为FAST FULL SCAN说明使用索引快速全扫描，OBJECT_NAME说明行为的对
象为EMP_EMAIL_UK。
4、AUTOTRACE命令
使用AUTOTRACE指令可以跟踪SQL语句并分析其执行步骤，统计信息如物理读数据量，磁盘和内存排序数据量。
但是要执行该指令需要设置几个参数。
-
SQL_TRACE：该参数说明是否启动对SQL语句的追踪。默认该参数为FALSE，要启用AUTOTRACE功能需要将参数
SQL_TRACE设置为TRUE，该参数可以动态改变。注意，在不需要追踪SQL语句时，最好将该参数设置为FALSE，因为它会造成跟踪所有执行的SQL语句，这样会产生大量的TRC文件，对磁盘空间有一定的冲击。 -
USER_DUMP_DEST：该参数说明SQL语句追踪文件的记录位置，在笔者的计算机上其默认目录为：D:\app\zhangshixing\product\11.2.0\dbhome_1\RDBMS\trace -
TIMED_STATISTICS：该参数可以使用ALTER SYSTEM或ALTER SESSION动态设置。默认参数值为TRUE。
所以， 我们只需要设置参数SQL_TRACE来启动对SQL语句执行的追踪。
SQL> alter system set sql_trace = true;
系统已更改。
SQL>
使用AUTOTRACE追踪SQL语句执行计划。
SQL> set autotrace traceonly;
SQL> select * from hr.employees;
已选择107行。
执行计划
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
9548 bytes sent via SQL*Net to client
493 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
SQL>
AUTOTRACE结果分两部分，一部分是SQL语句的执行计划，一部分是统计信息。从执行计划可以清楚看出SQL语
句的执行步骤，访问的对象以及消耗的CPU。
下面我们详细介绍统计信息中每一行的含义：
-
recursive calls：递归调用的次数。 -
db block gets：读数据块的数量。 -
consistent gets：总的逻辑I/O。 -
physical reads：物理I/O。 -
redo size：重做数量。 -
bytes sent via SQL*Net to client：SQL*Net通信。 -
bytes received via SQL*Net from client。 -
SQL*Net roundtrips to/from client。 -
sorts (memory)：内存排序统计。 -
sorts (disk)：磁盘排序统计。 -
rows processed：被检索的行数。
分析完毕后，记得要关闭AUTOTRACE功能。
SQL> alter system set sql_trace = false;
系统已更改。
SQL> SET AUTOTRACE OFF;
3.3 设置SQL*Plus环境
SQL*Plus有一组系统变量。可以用来设置或自定义SQL*Plus 的操作环境。如设置每行最多显示多少个字符、每
页最多显示多少行、是否自动提交、是否允许服务器输出、某个输出列的标题和格式、输出页的标题和脚注等。这
是SQL*Plus最灵活的地方，只要不关闭当前的SQL*Plus程序，无论是切换到哪个用户，这个环境的设置都是相
同的。
在Oracle 数据库中，用于维护SQL*Plus系统变量的命令包括SHOW 和SET。
3.3.1 SHOW 命令
SHOW命令可以用来显示当前SQL*Plus环境中的系统变量，还可以显示错误信息、初始化参数、当前用户等信
息。该命令的格式是：
SHO[w] option
其中，option包含的选项有：
system_variable,ALL,BTI[TLE],ERR[ORS][{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
TRIGGER|VIEW|TYPE|TYPE BODY}[schema.]name],PARAMETERS[parameter_name],
REL[EASE],REPF[OOTER],REPH[EADER],SGA,SPOO[L],SQLCODE,TT[ITLE],USER。
SHOW命令的基本功能如表所示。
3.3.2 SET命令
SET命令用于设置系统变量的值，以便于更改SQL*Plus的环境设置。该命令的格式为：
SET system_variablel valuel[system_variable2 value2]...
其中，system_variable 是系统变量名称，value 是给该变量所赋予的值。通过SET命令设置的系统变量很
多，可以在SQL*Plus中使用HELP SET命令来查看SET命令的功能，还可以设置所有系统变量。
1、ARRAYSIZE
用于设置从数据库中一次提取的行数，默认值为15。
SQL> SET arraysize 20;
SQL> SHOW arraysize;
arraysize 20
2、AUTOCOMMIT
用于在执行DML 语句时设置是否自动提交，默认值为OFF。当设置为ON并设置为n时，表示成功执行n条SQL语句
或PL/SQL块后自动提交。
SQL> SHOW autocommit;
autocommit OFF
SQL> SET autocommit 5;
SQL> SHOW autocommit;
AUTOCOMMIT ON 用于所有 5 DML 语句
SQL>
3、COLSEP
用于设置在选定列之间的分隔符，默认为空格。
4、ECHO
在用START命令执行一个脚本文件时，ECHO命令用于控制是否显示脚本文件中正在执行的SQL 语句。默认值为
OFF。
5、FEEDBACK
当一个查询选择出至少n行记录时，就会在结果集的项显示返回的行数，默认值是6。
6、HEADING
用于说明是否显示查询结果的列标题，默认值为ON，表示显示列标题。
7、HEADSEP
Heading separator，指定后面的标点符号用于将页标题或列标题分行显示。默认值为|。
8、LINESIZE
设置每行显示的字符个数，即宽度，默认值为80。
9、NEWPAGE
设置分隔页与页之间的空白行数。
10、PAGESIZE
设置每页显示的行数(其中包括了TITLE、BTITLE、COLUMN标题以及显示的空行)，默认值是14。
11、PAUSE
设置每页输出时是否暂停，如果设置了PAUSE text，则会在左下角显示text。
12、SERVEROUTPUT
用于控制是否显示PL/SQL 块或存储过程的输出，即允许函数DBMS_OUTPUT.PUT_LINE()的输出显示在屏幕上。默
认值为OFF，即当调用该函数时不会在SQL*Plus屏幕上显示输出结果。
13、SQLPROMPT
用于设置SQL*Plus的命令提示符，默认值为SQL>。
14、TIME
用于设置是否在SQL*Plus命令提示符前显示系统的当前时间，默认值为OFF。如果设置成ON，则可以从提示符
前的时间大致跟踪SQL语句、PL/SQL块的执行时间、花费时间。还可以利用闪回技术，查询那个时刻数据库中某
个表的数据，甚至将该表闪回到那个时刻的数据。
15、TIMING
用于设置是否显示执行SQL语句、PL/SQL块的花费时间，默认值为OFF。
16、TRIMSPOOL
用于设置是否将SPOOL输出中每行后面多余的空格去掉。默认值是OFF。
17、UNDERLINE
用于设置下划线字符的符，,默认值为_。
18、VERIFY
在交互使用替换变量时，用于设置是否列出一个SQL语句在获得替换变量的值前后的文本内容。默认值是ON，即
显示前后文本的内容，若设置为OFF，则表示不显示。
3.4 SQL*Plus环境介绍
3.4.1 存储SQL*Plus环境
可以使用STORE SET命令将当前的设置保存到一个脚本文件中，以便以后用START命令来运行该脚本文件，重现
当前的环境变量。STORE SET命令的语法格式为：
STORE{SET}filename[.ext][CRE[ATE]|REP[LACE]|APP[END]]
脚本文件的默认扩展名是.sql，如果filename中不包含路径，则脚本文件保存在与sqlplus.exe相同的路径
下。
3.4.2 假脱机输出
SQL*Plus提供了一系列有关脚本文件和假脱机文件的操作命令，设置其系统变量、格式化输出、建立交互式操作
方式的命令，并且往往每种命令还有若干可选项。
假脱机输出的命令如下：
spool 文件名
上述命令表示将这之后的各种操作及执行结果假脱机，即存盘到磁盘文件上，默认文件扩展名为.lst。
停止假脱机的命令如下：
spool off
3.4.3 联机帮助
在使用SQL*Plus 过程中，为了获取帮助，只需要在SQL*Plus的命令提示符下，输入HELP和命令名称，再回车
即可。
假如在SQL>提示符下，输入HELP select或?select，再按回车键，将会显示出有关select 命令的帮助信息。
3.5 使用SQL*Plus 格式化查询结果
使用SQL*Plus格式化查询结果，可以生成一个格式良好的报告。
3.5.1 格式化列
通过SQL*Plus的COLUMN命令，可以改变列的标头，重新格式化查询中列的数据。
1、修改列标头
当显示列标题时，可以使用默认的标头，也可以使用COLUMN命令修改列标头。当我们要显示查询结果时，
SQL*Plus 使用列或者表达式名称作为列的标题。如果需要改变默认标题，可以使用COLUMN命令，格式为：
COLUMN column_name HEADING column_heading
COLUMN FIRST_NAME HEADING "FIRSTNAME";
COLUMN LAST_NAME HEADING "LASTNAME";
SELECT FIRST_NAME ,LAST_NAME FROM hr.employees;
SQL> SELECT FIRST_NAME ,LAST_NAME FROM hr.employees;
FIRSTNAME LASTNAME
-------------------- -------------------------
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
Laura Bissot
# 也可以拆分列标题
COLUMN FIRST_NAME HEADING "FIRST|NAME";
COLUMN LAST_NAME HEADING "LAST|NAME";
SELECT FIRST_NAME ,LAST_NAME FROM hr.employees;
SQL> SELECT FIRST_NAME ,LAST_NAME FROM hr.employees;
FIRST LAST
NAME NAME
-------------------- -------------------------
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
2、格式化NUMBER列
NUMBER列的宽度等于标题的宽度或者是FORMAT的宽度加上一个空格，如果没有显示使用FORMAT，这列的宽度至
少是SET NUMWIDTH的值。一般情况下，SQL*Plus尽可能显示数字，直到SET命令设置的NUMWIDTH变量的值。
如果数字多于NUMWIDTH的值，则SQL*Plus将截断该值，使它达到所允许的最大数字数。格式化列的格式为：
COLUMN column_name FORMAT model
# 使用逗号或美元符号,可以将值限定为给定的十进制数字。
COLUMN SALARY FORMAT $9,990;
SELECT SALARY FROM hr.employees;
SQL> SELECT SALARY FROM hr.employees;
SALARY
-------
$2,600
$2,600
$4,400
#######
$6,000
$6,500
#######
#######
$8,300
#######
#######
3、格式化数据类型
当显示数据类型时，可以使用SQL*Plus的COLUMN命令进行修改，其中包括CHAR、NCHAR、
VARCHAR2(VARCHAR)、NVARCHAR2(NCHAR VARYING)、DATE、LONG、CLOB、NCLOB。
如果定义列的宽度小于列的标题，SQL*Plus截断标题，如果为LONG、CLOB和NCLOB定义宽度，SQL*Plus使用
LONGCHUNKSIZE或者定义的宽度。
# 设置FIRST_NAME列的宽度为1个字符
COLUMN FIRST_NAME FORMAT A1;
SELECT FIRST_NAME FROM hr.employees;
SQL> SELECT FIRST_NAME FROM hr.employees;
F
N
-
E
l
l
e
n
S
u
n
d
# 设置FIRST_NAME列的宽度为1个字符
COLUMN FIRST_NAME FORMAT A2;
SELECT FIRST_NAME FROM hr.employees;
SQL> SELECT FIRST_NAME FROM hr.employees;
FI
NA
--
El
le
n
Su
nd
ar
4、复制列显示属性
当希望多列有相同的显示属性时，可以在COLUMN命令中使用LIKE子句。
COLUMN <目标字段名> LIKE <来源字段名> HEADING <标题名>
COLUMN FIRST_NAME FORMAT A3;
COLUMN LAST_NAME LIKE FIRST_NAME HEADING LASTNAME;
SELECT FIRST_NAME ,LAST_NAME FROM hr.employees;
SQL> SELECT FIRST_NAME ,LAST_NAME FROM hr.employees;
FIR
NAM LAS
--- ---
Ell Abe
en l
Sun And
dar e
Moz Atk
he ins
on
5、列出和重新设置列显示属性
使用COLUMN命令可以列出给定的当前显示属性，其格式为：
COLUMN column_name
SQL> COLUMN FIRST_NAME
COLUMN FIRST_NAME ON
HEADING 'FIRST|NAME' headsep '|'
FORMAT A3
SQL>
提示：若只使用COLUMN不带参数的命令，则可以显示所有列的属性。
SQL> COLUMN
COLUMN LASTNAME ON
HEADING 'LASTNAME'
FORMAT A3
COLUMN WATER ON
HEADING 'BONUS'
FORMAT $9,990
COLUMN SALARY ON
FORMAT $9,990
COLUMN LAST_NAME ON
HEADING 'LASTNAME'
FORMAT A3
COLUMN FIRST_NAME ON
HEADING 'FIRST|NAME' headsep '|'
FORMAT A3
重新设置列的显示属性为默认情况，可使用下面的格式：
COLUMN column_name CLEAR
SQL> COLUMN LAST_NAME CLEAR
SQL>
6、在外层列值后显示一行字条
当显示的值不适合列的宽度时，SQL*Plus将列的值设为附加行。如果希望插入一个记录分隔符，可使用SET命令
的RECSEP和RECSEPCHAR关键字。其中，RECSEP决定何时打印字符行，如果将RECSEP设置为EACH，则在每行
后面进行打印，如果将RECSEP设置为WRAPPED(这也是RECSEP的默认值)则在包装行后面打印，如果设置为OFF则
压缩打印。
7、使用空格和概述行阐明报告
当在SQL SELECT命令中使用ORDER BY子句时，在输出时将对数据排序，使用SQL*Plus的BREAK和COMPUTE
命令可以创建记录的子集合，添加空格和概述行。
# 使用 BREAK和COMPUTE命令查询示例
SELECT DEPARTMENT_ID,LAST_NAME,SALARY FROM hr.employees WHERE SALARY>2000 ORDER BY DEPARTMENT_ID;
SQL> SELECT DEPARTMENT_ID,LAST_NAME,SALARY FROM hr.employees WHERE SALARY>2000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- -------
10 Whalen $4,400
20 Hartstein #######
20 Fay $6,000
30 Raphaely #######
30 Colmenares $2,500
30 Khoo $3,100
30 Baida $2,900
30 Tobias $2,800
30 Himuro $2,600
40 Mavris $6,500
50 OConnell $2,600
# 使用BREAK命令
BREAK ON DEPARTMENT_ID;
SELECT DEPARTMENT_ID,LAST_NAME,SALARY FROM hr.employees WHERE SALARY>2000 ORDER BY DEPARTMENT_ID;
SQL> SELECT DEPARTMENT_ID,LAST_NAME,SALARY FROM hr.employees WHERE SALARY>2000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- -------
10 Whalen $4,400
20 Hartstein #######
Fay $6,000
30 Raphaely #######
Colmenares $2,500
Khoo $3,100
Baida $2,900
Tobias $2,800
Himuro $2,600
40 Mavris $6,500
50 OConnell $2,600
如果执行下面命令，可以在列的值改变时，插入1个空白行。
BREAK ON DEPARTMENT_ID SKIP 1
SELECT DEPARTMENT_ID,LAST_NAME,SALARY FROM hr.employees WHERE SALARY>2000 ORDER BY DEPARTMENT_ID;
SQL> SELECT DEPARTMENT_ID,LAST_NAME,SALARY FROM hr.employees WHERE SALARY>2000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- -------
10 Whalen $4,400
20 Hartstein #######
Fay $6,000
30 Raphaely #######
Colmenares $2,500
Khoo $3,100
Baida $2,900
Tobias $2,800
Himuro $2,600
如果使用了BREAK命令将输出的行分为子集，则可以使用SQL*Plus的COMPUTE函数对每个子集进行一些计算。计
算函数如表所示。
列出当前BREAK的定义的命令：
BREAK
SQL> BREAK
break on DEPARTMENT_ID skip 1 nodup
# 计算输出薪资总和
BREAK ON REPORT;
COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT;
SELECT LAST_NAME,SALARY FROM hr.employees WHERE JOB_ID='FI_ACCOUNT';
SQL> SELECT LAST_NAME,SALARY FROM hr.employees WHERE JOB_ID='FI_ACCOUNT';
LAST_NAME SALARY
------------------------- -------
Faviet $9,000
Chen $8,200
Sciarra $7,700
Urman $7,800
Popp $6,900
-------
TOTAL #######
# 计算输出平均薪资
BREAK ON REPORT;
COMPUTE AVG LABEL AVG OF SALARY ON REPORT;
SELECT LAST_NAME,SALARY FROM hr.employees WHERE JOB_ID='FI_ACCOUNT';
SQL> SELECT LAST_NAME,SALARY FROM hr.employees WHERE JOB_ID='FI_ACCOUNT';
LAST_NAME SALARY
------------------------- -------
Faviet $9,000
Chen $8,200
Sciarra $7,700
Urman $7,800
Popp $6,900
-------
AVG $7,920
3.5.2 定义页与报告的标题和维数
当数据库中的数据比较多时，为了使显示的结果美观合理，我们可以在每页上添加上标题和下标题，也可以设置每
页显示的行数和每行的宽度，还可以在每个报告上加标题和脚注。
1、设置上下标题、题头和脚注
从前面知道使用TITLE命令可以设置上标题，还可以使用BTITLE命令设置每页的下标题和题头，使用
REPHEADER命令定义报告题头，使用REPFOOTER命令定义报告脚注。
2、显示页号和系统维护值
如果需要显示当前页的序号和标题中系统维护值时，可使用下面的命令：
TITLE LEFT system_maintained_value_name
3、列出标题、压缩标题和恢复页标题的定义
为了列出标题定义，可以使用下面的命令：
TITLE
BTITLE
为了压缩标题定义，可使用下面命令：
TITLE OFF
BTITLE OFF
如果需要恢复当前定义，可输入下面命令：
TITLE ON
BTITLE ON
4、显示标题的列值
在上标题引用一个列值，将值存储在变量中，在TITLE命令中再引用该变量。定义变量的格式：
COLUMN column_name NEW 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
为了显示日期，可以在标题中引用_DATE。
6、设置页维数
SQL*Plus在每页上显示的数据的数量依赖于当前页的维。
SQL*Plus使用默认页时，上标题前的行数是1，每页的行数是24，每行的字数是80。可使用SET命令的
NEWPAGE子句来设置每页开始和上标题间的行数：
SET NEW PAGE number_of_lines
使用PAGESIZE设置每页的行数：
SET PAGESIZE number_of_lines
将每页设置为60行，清除屏幕，设置行大小为75的命令为：
SET PAGESIZE 60
SET NEWPAGE O
SET LINESIZE 75
可以使用SHOW命令列出这些变量的当前值：
SHOW PAGESIZE
SHOW NEWPAGE
SHOW LINESIZE
3.5.3 存储和打印结果
在输出结果时，可以使用SPOOL命令将查询结果存储到文件中，并同时在屏幕上显示，格式：
SPOOL file_name
当在不同软件产品间移动数据时，有必要用flat文件。使用SQL*Plus创建一个flat文件，要先使用SET设置：
SET NEWPAGE O
SET SPACE O
SET LINESIZE O
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
若想把结果直接在打印机上打印出来，则可以使用命令SPOOL OUT来实现；若在输入到文件或打印机时，不想在
屏幕上看到输出，则可以用SET TERMOUT OFF命令来实现。
