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命令来实现。