第3章使用SQL﹡Plus工具
SQL*Plus是Oracle系统的支持工具之
一，它用于运行SQL语句和PL/SQL块，并且也用于跟踪调试SQL语句和PL/SQL块。
通过它，用户可以连接位于相同服务器上的数据库，也可以连接位于网络中不同服务器上的数据库。
本章将讲述SQL*Plus工具的使用以及常用的一些SQL*Plus操作命令。
掌握SQL*Plus的主要功能熟练使用SQL*Plus常用指令掌握变量的声明和使用掌握格式化查询结果命令的使用掌握报表的设计和数据统计 3.1SQL﹡Plus概述 SQL*Plus工具是随Oracle数据库服务器或客户端的安装而自动进行安装的管理与开发工具，Oracle数据库中所有的管理操作都可以通过SQL*Plus工具完成，同时开发人员利用SQL*Plus可以测试、运行SQL语句和PL/SQL程序。
本节将简单介绍SQL*Plus的主要功能以及连接/断开数据库的方式。
3.1.1SQL﹡Plus的主要功能 SQL*Plus工具主要用于数据库的查询和数据处理。
利用SQL*Plus可以将SQLServer和Oracle专有的PL/SQL结合起来进行数据查询和处理。
SQL*Plus是一个最常用的工具，具有很强的功能，主要有：
（1）数据库的维护，如启动、关闭等，这一般在服务器上操作。
（2）执行SQL语句和PL/SQL块。
（3）执行SQL脚本。
（4）数据的导出、报表。
（5）应用程序开发、测试SQL和PL/SQL。
（6）生成新的SQL脚本。
（7）供应用程序调用，如安装程序中进行脚本的安装。
第
3 使用SQL﹡Plus工具 章
（8）用户管理及权限维护等。
在SQL*Plus中可以执行SQL语句、PL/SQL程序和SQL*Plus命令。
（1）SQL语句。
SQL语句是以数据库对象为操作对象的语言，主要包括DDL、DML、和DCL。
（2）PL/SQL程序。
PL/SQL语句同样是以数据库对象为操作对象，但所有的PL/SQL语句的解释均由PL/SQL引擎来完成。
使用PL/SQL语句可以编写存储过程、触发器和包等数据库永久对象。
（3）SQL*Plus命令。
SQL*Plus命令主要用来格式化查询结果，设置选择，编辑以及存储SQL命令，设置查询结果的显示格式，并且可以设置环境选项，还可以编辑交互语句，可以与数据库进行“对话”。
3.1.2SQL﹡Plus连接与断开数据库 通过SQL*Plus工具可以很方便地连接与断开数据库，下面介绍两种使用SQL*Plus连接数据库的方式。
1．启动SQL﹡Plus，命令行方式 要从命令行启动SQL*Plus，可以使用sqlplus命令。
sqlplus命令的语法格式 47 如下： sqlplus[username]|[password][@connect_identifier]|[AS{SYSDBA|SYSOPER|SYSASM|[NOLOG] 语法说明如下：（1）username：指定数据库的用户名。
（2）password：指定该数据库用户的密码。
（3）@connect_identifier：指定要连接的数据库。
（4）AS：指定所使用的管理权限，权限的可选值有SYSDBA、SYSOPER和SYSASM。
（5）SYSOPER：具有SYSOPER权限的管理员可以启动和关闭数据库，执行联机和脱机备份，归档当前重做日志文件，连接数据库。
（6）SYSDBA：SYSDBA权限包括SYSOPER的所有权限，另外还能够创建数据库，并且授权SYSDBA或SYSOPER权限给其他数据库用户。
（7）SYSASM：SYSASM权限是OracleDatabase11g的新增特性，是ASM实例所特有的，用来管理数据库存储。
（8）NOLOG：不记入日志文件。
下面以system用户为例，连接数据库，在DOS命令窗口中输入sqlplussystem/oracle，按Enter键后提示连接到数据库，如图3-1所示。
2．启动SQL﹡Plus，连接到默认数据库 启动SQL﹡Plus连接到默认数据库方法的具体步骤如下： Oracle11g基础教程与实验指导 图3-1通过命令行连接数据库
（1）执行【开始】｜【程序】｜Oracle-oraDb11g_home1｜【应用程序开发】｜SQLPlus命令，出现如图3-2所示的登录窗口。
48 图3-2SQL*Plus登录窗口
（2）在登录窗口中输入用户名（如system）之后按Enter键，登录窗口紧接着会提示“输入口令”信息，输入口令之后按Enter键，如果用户名和口令均正确，则显示如图3-3所示登录成功的窗口。
图3-3SQL*zlus登录成功 另外，也可以用户名和口令一起输入，格式为：用户名/口令，如system/oracle，只 第
3 使用SQL﹡Plus工具 章 是这种方式有一个缺点，就是会显示口令信息。
3．使用SQL﹡Plus命令连接与断开数据库 在SQL*Plus中连接数据库时，可以使用CONNECT（可简写为CONN）命令指定不同的登录用户。
连接数据库后，SQL*Plus维持数据库会话。
CONNECT命令的语法格式如下： CONN[ECT][{user_name[/password][@connect_identifier]}[AS{SYSOPER|SYSDBA|SYSTEM}]] 如果需要断开与数据库的连接，可以使用DISCONNECT（可简写为DISCONN）命令，该命令可以结束当前会话，但是保持SQL*Plus运行。
要退出SQL*Plus，关闭SQL*Plus窗口，可以执行EXIT或者QUIT命令。
3.2使用SQL﹡Plus命令 通常所说的DML、DDL、DCL语句都是SQL*Plus语句，它们执行完后，都可以保 存在一个称为SQLBUFFER的内存区域中，并且只能保存一条最近执行的SQL语句。
除了SQL*Plus语句，在SQL*Plus中执行的其他语句称为SQL*Plus命令。
SQL*Plus命 49 令执行完后，不保存在SQLBUFFER的内存区域中，它们一般用来对输出的结果进行格 式化显示，以便于制作报表。
3.2.1使用DESCRIBE命令查看表结构 表结构包括一个数据库表的名称、有哪些字段以及哪些字段是主键等信息。
表结构可以通过使用DESCRIBE命令在数据库中查询，该命令的语法格式如下： DESCRIBE{[schema.]object[@connect_identifer]} 其中，schema表示指定对象所属的用户名；object表示对象的名称，如表名或视图名；@connect_identifer表示数据库连接字符串。
技巧DESCRIBE也可以简写为DESC。
下面使用DESCRIBE命令查看scott用户下的emp表的结构，结果如下： SQL>DESCemp 名称 是否为空?
-------- ---------- EMPNO NOTNULL ENAME JOB 类型 --------NUMBER
(4)VARCHAR2(10)VARCHAR2
(9) Oracle11g基础教程与实验指导 MGRHIREDATESALCOMMDEPTNO NUMBER
(4)DATENUMBER(7,2)NUMBER(7,2)NUMBER
(2) 使用DESCRIBE命令查看表结构时，如果指定的表不存在，则提示信息：objecttablename（表名）doesnotexist。
如果指定的表存在，则显示该表的结构。
如上面的结果所示，显示表结构时，按照“名称”、“是否为空？”、“类型”这三列进行显示。
其中，“名称”表示列的名称；“是否为空？”表示对应列的值是否可以为空，如果可以为空，则不显示任何内容，如果不能为空，则为NOTNULL；“类型”表示该列的数据类型，并且显示其精度。
3.2.2执行SQL脚本 在工作需要的时候，常常会写很多命令，而这些命令如果一条一条的输入再执行， 则会降低工作效率，浪费劳力，这时，执行SQL脚本文件可以解决这一难题。
下面介绍 执行SQL脚本方法的使用。
50 使用@执行SQL脚本的语法格式如下： SQL>@full_path\file_name 其中，full_path表示脚本文件的路径；file_name表示脚本文件的文件名。
例： SQL>@D:\test.sql 在SQL*Plus中输入以上命令之后，按Enter键，开始执行SQL脚本文件。
@命令将test.sql文件的内容读入SQL*Plus缓冲区，然后执行缓冲区中的内容。
下面通过一个示例介绍@命令的使用方法，该命名执行后的结果如下： SQL>@F:\test.sql名称 ------------------------EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNODEPTNODNAMELOC 是否为空?
----------NOTNULL NOTNULL 类型 ---------------NUMBER
(4)VARCHAR2(10)VARCHAR2
(9)NUMBER
(4)DATENUMBER(7,2)NUMBER(7,2)NUMBER
(2)NUMBER
(2)VARCHAR2(14)VARCHAR2(13) 其中，test.sql文件的路径为F:\test.sql，test.sql文件的内容为： 第
3 使用SQL﹡Plus工具 章 descemp;descdept; @F:\test.sql命令分别执行上面两条命令，两条命令的结果均会显示在窗口中。
提示执行SQL脚本还可以使用start命令，start命令等同于@命令，如startF:\test.sql等同于 @F:\test.sql。
3.2.3使用SAVE命令保存缓冲区内容到文件 当执行完一条SQL语句后，该语句就会被存入缓冲区，而把以前存在缓冲区的语句覆盖，也就是说，缓冲区只能存放刚刚执行完的SQL语句。
使用SAVE命令可以将缓冲区中的内容保存到文件中，SAVE命令的语法格式如下： SAV[E][FILE]file_name[CRE[ATE]|REP[LACE]|APP[END]] 参数说明如表3-1所示。
51 表3-1SAVE命令参数表 参数 说明 file_nameCREATEREPLACE APPEND 表示将SQL*Plus缓冲区的内容保存到文件名file_name的文件中表示创建一个file_name文件，该选项为默认值表示如果file_name文件已经存在，则覆盖file_name文件的内容；如果该文件不存在，则创建该文件如果file_name文件已经存在，则将缓冲区中的内容追加到file_name文件的末尾；如果该文件不存在，则创建该文件 下面通过一个示例介绍SAVE命令的使用方法。
执行下面的SQL语句： SELECTenameFROMemp; 此时，缓冲区的内容就是上面的SQL语句，使用SAVE命令将SQL*Plus缓冲区中的SQL语句保存到一个名为test2.sql的文件中，代码如下： saveF:\test2.sql; 注意在SAVE命令中，如果没有为文件指定路径，则会默认保存到Oracle安装路径的 product\11.2.0\dbhome_1\BIN目录下。
Oracle11g基础教程与实验指导 3.2.4使用GET命令读取脚本文件到缓冲区 前面介绍了使用SAVE命令将缓冲区的内容保存到一个文件中，而使用GET命令可以将文件中的内容读取到缓冲区。
GET命令的语法格式如下： GET[FILE]file_name[LIST|NOLIST]; 语法说明如下：（1）file_name：一个指定文件，将该文件的内容读入SQL*Plus缓冲区中。
（2）LIST：列出缓冲区中的语句。
（3）NOLIST：不列出缓冲区中的语句。
下面通过一个示例介绍GET命令的具体使用方法。
将3.2.3节中保存的test2.sql文件的内容读入到缓冲区中，并获取执行结果，代码如下： SQL>GETF:\test2.sql 521*SELECTenameFROMempWHEREename='SMITH'SQL>RUN1*SELECTenameFROMempWHEREename='SMITH'ENAME---------SMITH 注意使用GET命令时，如果file_name指定的文件在Oracle的安装目录 product\11.2.0\dbhome_1\BIN下，则只需要指出文件名；如果不在这个目录下，则必须指定完整的路径名。
3.2.5使用EDIT命令编辑缓冲区内容或文件 使用EDIT命令，可以编辑缓冲区的内容。
这样，如果语句执行出错，用户可以很方便地进行修改，特别是长的、复杂的SQL语句。
EDIT命令可以将SQL*Plus缓冲区中的内容复制到一个文件名为afied.buf的文件中，可以使用文本编辑器打开这个文件，该命令的语法格式如下： ED[IT][file_name] 其中，file_name默认为afied.buf，也可以指定一个其他的文件。
下面通过一个示例介绍EDIT命令的具体使用方法。
在SQL*Plus中，使用EDIT命令将缓冲区中的内容复制到afied.buf文件中。
第
3 使用SQL﹡Plus工具 章 SQL>SELECTENAMEFROMempWHEREename='SMITH';ENAME---------SMITHSQL>EDIT已写入fileafiedt.buf 在SQL*Plus中输入EDIT命令之后，将打开一个记事本文件afiedt.buf，在该文件中显示缓冲区中的内容，如图3-4所示。
图3-4使用EDIT命令编辑缓冲区内容 对afiedt.buf文件中的内容进行编辑，将文件内容修改为： SELECTenameFROMempWHEREename=’JONES’ 53 当退出编辑器时，该文件的内容将被复制到SQL*Plus缓冲区中。
这时可以使用斜杠（/）运行刚才修改过的查询语句，代码如下： 1*SELECTenameFROMempWHEREename='JONES'SQL>/ENAME---------JONES 3.2.6使用SPOOL命令复制输出结果到文件 使用SPOOL命令可以将SQL*Plus中的输出结果复制到一个指定的文件中，直到使用SPOOLOFF命令为止。
SPOOL命令的语法格式如下： SPO[OL][file_name[CRE[ATE]|REP[LACE]|APP[END]]|OFF|OUT] 语法说明如下：（1）file_name：指定一个操作系统文件。
（2）CREATE：创建一个指定的文件名为file_name的文件。
（3）REPLACE：如果指定的文件已经存在，则替换该文件。
（4）APPEND：将内容追加到一个已经存在的文件末尾。
（5）OFF：停止将SQL*Plus中的输出结果复制到file_name文件中，并关闭该文件。
Oracle11g基础教程与实验指导
（6）OUT：启动该功能，将SQL*Plus中的输出结果复制到指定的文件名为file_name的文件中。
例如，使用SPOOL命令将SQL*Plus中的输出结果保存到F:\spool.txt文件中，代码如下： SQL>SPOOLF:\spool.txt 然后执行SQL语句： SQL>SELECT*FROMemp; 执行SPOOLOFF命令，在该命令之后所操作的任何语句，将不再保存其执行结果。
SQL>SPOOLOFF; 打开F:\spool.txt文件，该文件内容是上述SQL语句的执行结果。
3.3变量 在Oracle数据库中，可以使用变量来编写通用的SQL语句。
由于这些变量通常用 54 来替代值，因此称为替代变量。
替换变量有以下两种类型：
1．临时变量 临时变量只在使用它的SQL语句中有效，值不能保留。
2．已定义变量 已定义变量会一直保留到被显示地删除、重定义或退出SQL*Plus为止。
3.3.1临时变量 在SQL语句中，可以使用字符&定义临时变量，后面跟要定义的变量名。
例如，&v_number就定义了一个名为v_number的变量。
在SQL*Plus中输入以下SQL语句： SQL>SELECTdeptno,dnameFROMdeptWHEREdeptno=&v_deptno; 在上面的SQL语句中定义了一个名为v_deptno的变量，按回车键后将会显示以下信息： 输入v_deptno的值: SQL*Plus提示用户为v_deptno变量输入一个值，然后在SELECT语句的WHERE子句中使用这个变量值，代码如下： 原值新值 1:SELECTdeptno,dnameFROMdeptWHEREdeptno=&v_deptno1:SELECTdeptno,dnameFROMdeptWHEREdeptno=10 第
3 使用SQL﹡Plus工具 章 DEPTNO DNAME ------------------------ 10 ACCOUNTING 由上面的示例可以看出，执行替换的行号为
1，变量v_deptno被替换为数字10。
1．控制输出行 原值和新值的输出可以使用SETVERIFY命令控制。
如果输入SETVERIFYOFF命令，就会禁止显示原值和新值，下面使用斜杠（/）再次运行上面的SQL语句，SQL*Plus就会提示为变量v_deptno输入一个新值，代码如下： SQL>SETVERIFYOFF SQL>/ 输入v_deptno的值:10 DEPTNO DNAME ------------------------ 10 ACCOUNTING 如上面代码所示，使用SETVERIFYOFF命令之后，执行SQL语句时原值和新值 行没有输出，直接输出了SQL语句的执行结果。
如果想重新显示这些行，可以使用SET VERIFYON命令，这里不再详细介绍该命令的使用方法。
55
2．修改变量定义字符 在SQL语句中，通常使用字符&定义临时变量，也可以指定使用其他字符，这时就需要使用SETDEFINE命令，下面的示例显示了如何使用SETDEFINE命令将变量定义字符设置为字符#。
SQL>SETDEFINE'#' SQL>SELECTdeptno,dnameFROM 输入v_deptno的值:10 DEPTNO DNAME ------------------------ 10 ACCOUNTING dept WHERE deptno=#v_deptno; 3.3.2定义变量 已定义变量是指具有明确定义的变量，该变量的值会一直保留到被显式地删除、重定义或退出SQL*Plus为止。
可以使用DEFINE命令定义变量，在使用完变量后，可以使用UNDEFINE命令将其删除。
1．使用DEFINE命令定义并查看变量 DEFINE命令既可以用来定义一个新变量，也可以用来查看已经定义的变量。
该命令的语法格式有以下3种： Oracle11g基础教程与实验指导
（1）DEF[INE]：显示所有已定义变量。
（2）DEF[INE]variable：显示指定变量的名称、值和数据类型。
（3）DEF[INE]variable=value：创建一个CHAR类型的变量，并为该变量赋初始值。
例如，定义一个名为v_ename的变量，并为其赋初始值为“SMITH”，代码如下： SQL>DEFv_ename='SMITH' 使用DEFINE命令加上变量名就可以查看该变量的定义，代码如下： SQL>DEFv_enameDEFINEv_ename ="SMITH"(CHAR) 提示单独使用DEFINE命令，可以查看当前会话的所有变量。
已定义变量可以用来指定一个元素，下面的查询使用到了之前定义的变量v_ename，并在WHERE子句中引用该变量的值。
SQL>SELECTename 562FROMemp3WHEREename='&v_ename';原值3:WHEREename='v_ename'新值3:WHEREename='SMITH'ENAME---------SMITH 由上面结果可以看出，这一次并没有提示用户输入v_ename的值，而是直接使用了该变量已经设置好的值。
2．使用ACCEPT命令定义并设置变量 ACCEPT命令用于等待用户为变量输入一个值。
该命令既可以为现有的变量设置一个新值，也可以定义一个新变量，并使用一个值对该变量进行初始化。
ACCEPT命令还可以为变量指定数据类型。
ACCEPT命令的语法格式如下： ACCEPTvariable_name[type][FOR[MAT]format][DEF[AULT]default][PROMPTtext|NOPR[EMPT]][HIDE] 语法说明如下：（1）variable_name：指定为变量分配的名字。
（2）type：指定变量的数据类型，可以使用的类型有CHAR、NUMBER和DATE。
默认情况下，变量的类型为CHAR。
（3）FORMAT：指定变量的格式，包括A15（15个字符）、9999（一个4位数字）和DD-MON-YYYY（日期）。
第
3 使用SQL﹡Plus工具 章
（4）DEFAULT：为变量指定一个默认值。
（5）PROMPT：在用户输入数据之前显示的文本消息。
（6）HIDE：隐藏用户为变量输入的值。
下面通过一个示例介绍ACCEPT命令的使用方法。
在该示例中，使用ACCEPT命令定义一个名为v_deptno的变量，该变量为两位数字，并在查询语句中引用该变量，代码如下： SQL>ACCEPTv_deptnoNUMBERFORMAT99PROMPT'pleaseinsertthedeptno:' pleaseinsertthedeptno:10 SQL>SELECT* 2FROMdept 3WHEREdeptno=&v_deptno; 原值3:WHEREdeptno=&v_deptno 新值3:WHEREdeptno 10 DEPTNO DNAME LOC ------------------------------------- 10 ACCOUNTING NEWYORK 在上面代码中并没有将输入的变量值“10”隐藏，在实际的开发应用中，为了安全 起见，通常会隐藏用户输入的值，可以通过在ACCEPT命令的末尾加上“HEDE”来 实现。
57
3．使用UNDEFINE命令删除变量 UNDEFINE命令用于删除变量。
例如，使用UNDEFINE命令删除变量v_deptno，代码如下： UNDEFINEv_deptno 3.4练习3-1：使用多个变量动态获取部门信息 在实际应用中变量的使用是很普遍的，例如，当需要查询不同的部门信息时，只需要输入一个新的部门值即可。
在SQL语句中定义了3个变量，分别需要输入一个列名（变量名为v_deptno）、一个表名（变量名为v_table）和一个列值（变量名为v_value）： SQL>SELECT&v_deptno,dname 2FROM&v_table 3WHERE&v_deptno=&v_value; 输入v_deptno的值:deptno 输入v_table的值:dept 输入v_deptno的值:deptno 输入v_value的值:10 DEPTNO DNAME ------------------------ 10 ACCOUNTING Oracle11g基础教程与实验指导 由上面的示例可以看出，需要输入两次相同的变量（v_deptno）的值，为了避免重复输入相同的变量，可以使用&&定义变量，上面的示例可以修改为： SQL>SELECT&&v_deptno,dname 2FROM&v_table 3WHERE&&v_deptno=&v_value; 输入v_deptno的值:deptno 输入v_table的值:dept 输入v_value的值:10 DEPTNODNAME ------------------------ 10 ACCOUNTING 以上介绍了临时变量的使用方法，使用变量为编写其他可以运行的脚本提供了很多灵活性，可以为用户提供一个脚本，用户只需要输入变量值即可。
3.5格式化查询结果 SQL*Plus提供了大量的命令用于格式化查询结果，使用这些命令可以格式化列，设 58置和一底页标显题示，多在少报行表数中据显，示设当置前一日行期显和示页多号少，个也字可符以和为创报建表简添单加报新表的，统并计为数报据表等添。
加常标用题的 格式化查询结果命令有COLUMN、COMPUTE、BREAK、BTITLE、TTITLE等。
3.5.1格式化列 在SQL*Plus中，经常使用COLUMN命令对所输出的列进行格式化，即按照一定的格式进行显示。
COLUMN命令的语法格式如下： COLUMN{column|alias}[options] 语法说明如下：（1）column：指定列名。
（2）alias：指定要格式化的列的别名。
（3）options：指定用于格式化列或别名的一个或多个选项。
在COLUMN命令中，可以使用很多选项，表3-2列出了其中的部分选项。
选项FOR[MAT]formatHEA[DING]headingJUS[TIFY][{LEFT|CENTER|RIGHT}] 表3-2COLUMN命令 说明将列或列名的显示格式设置为由format字符串指定的格式将列或列名的标题中的文本设置为由heading字符串指定的格式将列输出设置为左对齐、居中或右对齐 选项WRA[PPED] WOR[D_WRAPPED]CLE[AR] 第
3 使用SQL﹡Plus工具 章 续表 说明在输出结果中将一个字符串的末尾换行显示，该选项可能导致单个单词跨越多行与WRAPPED选项类似，不同之处在于单个单词不会跨越多行清除列的任何格式化（将格式设置回默认值） 表3-2中的format字符串可以使用很多格式化参数，可以指定的参数取决于该列中保存的数据。
如果列中包含字符，可以使用Ax对字符进行格式化，其中x指定了字符宽度，如A13就是将宽度设置为13个字符；如果列中包含数字，可以使用数字格式，如$99.99就是在数字前加美元符号；如果列中包含日期，可以使用日期格式，如MM-DD-YYYY设置的格式就是一个两位的月份（MM）、一个两位的日（DD）、一个4位的年份（YYYY）。
下面通过一个示例介绍COLUMN命令的使用方法。
该示例查询emp表中的eanme、empno和sal列，并分别对它们进行格式化，代码如下： SQL>COLUMNempnoHEADING"员工编号"SQL>COLUMNenameHEADING"雇员名"FORMATA13 SQL>COLUMNempnoFORMAT9999SQL>COLUMNsalHEADING"工资"FORMAT$9999.99 59 SQL>SELECTempno,ename,sal 2FROMemp; 员工编号 雇员名 工资 -------- ---------------- ------ 7369 SMITH $800.00 7499 ALLEN $1600.00 7521 WARD $1250.00 7566 JONES $2975.00 ... 7900 JAMES $950.00 7902 FORD $3000.00 7934已选择14行。
MILLER $1300.00 由上面的示例可以看出，使用COLUMN命令不仅可以对列的值进行格式化，还可以修改列名，使用别名来显示，从而使查询结果更加简明、直观。
在该示例中，输出结果中标题显示了两次，为了让结果看起来更美观，只让标题显示一次，可以通过设置页面显示行数据来实现，3.5.2节将介绍如何设置页面显示行数据。
3.5.2设置每页显示的数据行 每页中显示的数据可以使用SETPAGESIZE命令来设置。
这个命令的具体功能是设置SQL*Plus输出结果中一页应该显示的行数，超过这个行数之后，SQL*Plus就会再次 Oracle11g基础教程与实验指导 显示标题。
SETPAGESIZE命令的语法格式如下： SETPAGESIZEn 其中，参数n表示每一页显示的行数，最大只可以为50000，默认值为14。
下面使用SETPAGESIZE命令将页面大小设置为20行，再次执行3.5.1节中的示例： SQL>SETPAGESIZE20 SQL>/员工编号 雇员名 --------------------- 7369 SMITH 7499 ALLEN 7521 WARD 7876 ADAMS ... 7902 FORD 7934已选择14行。
MILLER 工资--------$800.00$1600.00$1250.00$1100.00 $3000.00$1300.00 上面示例中，设置了该页显示20行数据，故只在顶部显示了一次标题，这样的输出 60结构看起来更加简明。
提示SQL*Plus中的页并不是仅仅由输出数据行构成，而是由SQL*Plus显示到屏幕上的所有 输出结果构成，包括标题和空行等。
3.5.3设置每行显示的字符数 一行中显示的字符数可以使用SETLINESIZE命令来设置，默认值为80。
如果设置的值比较小，那么表中每行数据有可能在屏幕上需要分多行显示；如果设置的值比较大，则表中每行数据就可以在屏幕的一行中进行显示。
LINESIZE命令的语法格式如下： SETLINESIZEn 其中，n表示屏幕上一行数据中显示的字符数，有效范围是1～32767。
下面以设置一行显示20个字符为例，查询emp表中雇员编号和雇员姓名。
示例代码如下： SQL>SETLINESIZE20SQL>SELECTempno,ename 2FROMemp;员工编号-------雇员名------------- 第
3 使用SQL﹡Plus工具 章 7369SMITH 7499ALLEN 7521WARD... 7566JONES 7654MARTIN 上面示例中，由于一行显示的字符数为20，因此所有超出的部分换行显示。
提示如果不再使用列的格式化，可以使用COLUMN命令加上CLEAR选项来清除，CLEAR columns为清除所有列的格式化。
3.6创建简单报表 61 所谓报表就是用表格、图表等格式来动态显示数据。
计算机上报表的主要特点是数 据动态化和格式多样化，并且实现报表数据和报表格式的完全分离，用户可以只修改数 据或者只修改格式。
3.6.1报表的标题设计 报表的标题是利用SQL*Plus的两个命令来设计的，即TTITLE和BTITLE。
其中，TTITLE命令用来设计报表的头部标题，而BTITLE命令用来设计报表的尾部标题。
TTITLE命令设计的头部标题显示在报表每页的顶部。
设计头部标题时，要指定显示的信息和显示的位置，还可以使标题分布在多行之中。
用TTITLE命令设计头部标题的操作是比较复杂的，这条命令的语法格式如下： TTI[TLE][printspec[text|variable]...]|[OFF|ON] 语法说明如下：（1）printspec：指定出现在报表中每一个页面顶端的页眉，其可选值有CENTER、RIGHT、BOLD、FORMATtext、COLn、S[KIP][n]和TABn。
（2）OFF：取消设置。
（3）ON：启用设置。
下面是使用TTITLE命令设置页面的示例。
LEFT、 SQL>TTITLERIGHT'日期：'_DATECENTER'生成报表'SQL.USER'user'SQL>RIGHT'页：'FORMAT999SQL.PNOSKIP2 Oracle11g基础教程与实验指导 其中，_DATE表示当前日期；SQL.USER表示显示当前用户；SQL.PNO表示显示当前页（FORMAT用来格式化数字）；CENTER和RIGHT表示文本的对齐方式；SKIP2表示跳过两行。
BTITLE命令的用法与TTITLE命令是一样的，区别在于BTITLE命令用来设计尾部标题，显示的位置在报表每页的底部。
BTITLE命令的语法格式如下： BTI[TLE][printspec[text|variable]...]|[OFF|ON] 下面的示例演示了BTITLE命令的使用。
BTITLECENTER"谢谢使用该表"RIGHT"页："FORMAT999SQL.PNO 查询scott用户下的emp表中的数据并以报表的信息显示出来。
在设计报表时，需要使用TTITLE和BTITLE命令设置页眉和页脚信息，其具体的操作步骤如下：
（1）在F:\SQL文件夹下创建report.sql文件，该脚本中包含了TTITLE和BTITLE命令，脚本内容如下: TTITLERIGHT'日期：'_DATECENTER'生成报表'SQL.USER BTITLECENTER'谢谢使用该表'RIGHT'页：'FORMAT999SQL.PNO SETPAGESIZE20 62 SETLINESIZE120COLUMNenameHEADING"员工姓名"FORMATA20 COLUMNsalHEADING"工资"FORMAT$9999.99 COLUMNhiredateHEADING"入职时间" COLUMNempnoHEADING"员工编号" SELECTempno,ename,hiredatesal FROMemp; TTITLEOFF BTITLEOFF
（2）运行F:\SQL\report.sql文件，生成报表，代码如下： @F:\SQL\report.sql 员工编号 日期：16-8月-12 员工姓名 入职时间 生成报表SCOTT工资 ----------------------------------------------------- 7369 SMITH 17-12月-80 $800.00 7499 ALLEN 20-2月-81 $1600.00 ...79027934 FORDMILLER 03-12月-8123-1月-82 $3000.00$1300.00谢谢使用该表 已选择14行。
页：
1 在上面的示例中，首先使用TTITLE和BTITLE设置了报表的页眉和页脚，然后使用COLUMN格式化列信息，最后使用TTITLEOFF和BTITLEOFF关闭了设置的页眉 第
3 使用SQL﹡Plus工具 章 和页脚，从而其他报表中不适用该页眉、页脚的设置。
3.6.2统计数据 BREAK和COMPUTE命令可以结合使用，用来为列添加小计。
BREAK子句可以使SQL*Plus根据列值的范围分隔输出结果，COMPUTE子句可以使SQL*Plus计算一列的值。
BREAK命令的语法格式如下： BRE[AK][ONcolumn_name]SKIPn 语法说明如下：（1）column_name：对哪一列执行操作。
（2）SKIPn：在指定列的值变化之前插入n个空行。
COMPUTE命令的语法格式如下： COMP[UTE]functionLABELlabelOFcolumn_nameONbreak_column_name 语法说明如下：
（1）Function：执行的操作，如SUM（求和）、MAXIMUN（最大值）、MINIMUN （最小值）、AVG（平均值）、COUNT（非空值的列数）、NUMBER（行数）、VARIANCE 63 （方差）以及STD（均方差）等。
（2）LABEL：指定显示结果时的文本信息。
下面使用BREAK命令和COMPUTE命令计算不同部门的工资总数。
首先在F:\SQL 文件夹下新建文件sum.sql，该脚本的内容如下： BREAKONdeptnoCOMPUTESUMOFsalONdeptnoCOLUMNdeptnoHEADING"部门号"FORMAT99COLUMNenameHEADING"员工姓名"FORMATA20COLUMNsalHEADING"工资"SELECTdeptno,ename,salFROMemp ORDERBYdeptno; 然后使用@F:\SQL\sum.sql命令执行sum.sql脚本，执行结果如下： SQL>@F:\SQL\sum.sql 部门号------ 10 ******sum ... 员工姓名----------------------- CLARKKINGMILLER 20JONES 工资 -----$2450.00$5000.00$1300.00--------$8750.00$2975.00 Oracle11g基础教程与实验指导 30****** sum已选择14行。
MARTIN $1250.00--------- $9400.00 当deptno有了新值后，SQL*Plus会对输出结果重新进行分隔，并对deptno相同行的sal列进行求和，deptno列相同的行只会显示一次deptno的值。
3.7练习3-2：使用报表统计各部门的最高工资 在3.6节，介绍了如何使用TTITLE和BTITLE命令创建报表以及如何使用BREAK和COMPUTE命令统计数据，下面把这两种命令结合起来使用创建一个报表，并统计各部门的最高工资。
（1）在F:\SQL文件夹下创建生成报表的脚本文件max_sal.sql。
（2）在max_sal.sql脚本文件中，首先使用TTITLE和BTITLE命令设置报表的页眉和页脚信息，并使用SETPAGESIZE命令设置每页显示50行数据，使用SETLINESEIZ命令设置每行显示100个字符，代码如下： TTITLERIGHT'日期：'_DATECENTER'使用报表统计各部门的最高工资' 64 BTITLECENTER''RIGHT''FORMAT999SKIP2SQL.PNOSETPAGESIZE50 SETLINESIZE100
（3）使用COLUMN命令格式化列的信息，在格式化列之前，需要使用CLEAR命令清除列的格式，代码如下： CLEARCOLUMNSCOLUMNempnoHEADING'员工编号'FORMAT9999COLUMNenameHEADING'员工姓名'FORMATA10COLUMNdeptnoHEADING'部门编号'FORMAT9999COLUMNsalHEADIN'工资'FORMAT$999,999.99
（4）使用BREAK命令根据deptno列值的不同分隔输出，并使用COMPUTE命令根据deptno列统计sal的最大值，代码如下： BREAKONdeptnoCOMPUTEMAXIMUMOFsalONdeptnoSELECTdeptno,empno,ename,salFROMempORDERBYdeptno;
（5）在SQL*Plus中使用scott用户登录，当连接成功后，使用START命令运行max_sal.sql文件，运行结果如下： SQL>STARTF:\SQL\max_sal.sql 使用报表统计各部门的最高工资 部门编号 员工编号 员工姓名 日期：17-8月-12工资 --------------------------------------------------- 10 7782 CLARK $2,450.00 第
3 使用SQL﹡Plus工具 章 最高工资20 最高工资30 ...最高工资 已选择14行。
78397934 75667902 75217844 KINGMILLER JONESFORD WARDTURNER 谢谢使用该表 $5,000.00$1,300.00$5,000.00$2,975.00$3,000.00$3,000.00$1,250.00$1,500.00 $2,850.00页：
1 在上述的输出结果中，输出了页眉和页脚信息，每个数据列的内容都按照指定的格式进行显示，使用BREAK命令根据deptno列值的不同进行分隔输出，并使用COMPUTE命令，从而统计了每个部门的最高工资。
3.8扩展练习
1．格式化输出结果 65 使用SETPAGESIZE命令将页面大小设置为30行，并使用SETLINESIZE命令来设 置每行显示的字符数为20，然后使用这种设置格式化查询当前用户下的所有表并显示 出来。
2．使用报表统计各部门的平均工资 使用报表统计各部门的平均工资，要求在报表中显示当前时间，结果如下： 使用报表统计各部门的平均工资 部门编号 员工编号员工姓名 日期：30-8月-12工资 ------------------------------------- 10 7782CLARK $2,450.00 7839KING $5,000.00 ******** 7934MILLER $1,300.00------------ avg $2,916.67 20 7566JONES $2,975.00 ******** 7902FORD $3,000.00------------ avg $2,175.00 30 7521WARD $1,250.00 7844TURNER $1,500.00 7499ALLEN $1,600.00 7900JAMES $950.00 谢谢使用该表 页：
1 已选择14行。
一，它用于运行SQL语句和PL/SQL块，并且也用于跟踪调试SQL语句和PL/SQL块。
通过它，用户可以连接位于相同服务器上的数据库，也可以连接位于网络中不同服务器上的数据库。
本章将讲述SQL*Plus工具的使用以及常用的一些SQL*Plus操作命令。
掌握SQL*Plus的主要功能熟练使用SQL*Plus常用指令掌握变量的声明和使用掌握格式化查询结果命令的使用掌握报表的设计和数据统计 3.1SQL﹡Plus概述 SQL*Plus工具是随Oracle数据库服务器或客户端的安装而自动进行安装的管理与开发工具，Oracle数据库中所有的管理操作都可以通过SQL*Plus工具完成，同时开发人员利用SQL*Plus可以测试、运行SQL语句和PL/SQL程序。
本节将简单介绍SQL*Plus的主要功能以及连接/断开数据库的方式。
3.1.1SQL﹡Plus的主要功能 SQL*Plus工具主要用于数据库的查询和数据处理。
利用SQL*Plus可以将SQLServer和Oracle专有的PL/SQL结合起来进行数据查询和处理。
SQL*Plus是一个最常用的工具，具有很强的功能，主要有：
（1）数据库的维护，如启动、关闭等，这一般在服务器上操作。
（2）执行SQL语句和PL/SQL块。
（3）执行SQL脚本。
（4）数据的导出、报表。
（5）应用程序开发、测试SQL和PL/SQL。
（6）生成新的SQL脚本。
（7）供应用程序调用，如安装程序中进行脚本的安装。
第
3 使用SQL﹡Plus工具 章
（8）用户管理及权限维护等。
在SQL*Plus中可以执行SQL语句、PL/SQL程序和SQL*Plus命令。
（1）SQL语句。
SQL语句是以数据库对象为操作对象的语言，主要包括DDL、DML、和DCL。
（2）PL/SQL程序。
PL/SQL语句同样是以数据库对象为操作对象，但所有的PL/SQL语句的解释均由PL/SQL引擎来完成。
使用PL/SQL语句可以编写存储过程、触发器和包等数据库永久对象。
（3）SQL*Plus命令。
SQL*Plus命令主要用来格式化查询结果，设置选择，编辑以及存储SQL命令，设置查询结果的显示格式，并且可以设置环境选项，还可以编辑交互语句，可以与数据库进行“对话”。
3.1.2SQL﹡Plus连接与断开数据库 通过SQL*Plus工具可以很方便地连接与断开数据库，下面介绍两种使用SQL*Plus连接数据库的方式。
1．启动SQL﹡Plus，命令行方式 要从命令行启动SQL*Plus，可以使用sqlplus命令。
sqlplus命令的语法格式 47 如下： sqlplus[username]|[password][@connect_identifier]|[AS{SYSDBA|SYSOPER|SYSASM|[NOLOG] 语法说明如下：（1）username：指定数据库的用户名。
（2）password：指定该数据库用户的密码。
（3）@connect_identifier：指定要连接的数据库。
（4）AS：指定所使用的管理权限，权限的可选值有SYSDBA、SYSOPER和SYSASM。
（5）SYSOPER：具有SYSOPER权限的管理员可以启动和关闭数据库，执行联机和脱机备份，归档当前重做日志文件，连接数据库。
（6）SYSDBA：SYSDBA权限包括SYSOPER的所有权限，另外还能够创建数据库，并且授权SYSDBA或SYSOPER权限给其他数据库用户。
（7）SYSASM：SYSASM权限是OracleDatabase11g的新增特性，是ASM实例所特有的，用来管理数据库存储。
（8）NOLOG：不记入日志文件。
下面以system用户为例，连接数据库，在DOS命令窗口中输入sqlplussystem/oracle，按Enter键后提示连接到数据库，如图3-1所示。
2．启动SQL﹡Plus，连接到默认数据库 启动SQL﹡Plus连接到默认数据库方法的具体步骤如下： Oracle11g基础教程与实验指导 图3-1通过命令行连接数据库
（1）执行【开始】｜【程序】｜Oracle-oraDb11g_home1｜【应用程序开发】｜SQLPlus命令，出现如图3-2所示的登录窗口。
48 图3-2SQL*Plus登录窗口
（2）在登录窗口中输入用户名（如system）之后按Enter键，登录窗口紧接着会提示“输入口令”信息，输入口令之后按Enter键，如果用户名和口令均正确，则显示如图3-3所示登录成功的窗口。
图3-3SQL*zlus登录成功 另外，也可以用户名和口令一起输入，格式为：用户名/口令，如system/oracle，只 第
3 使用SQL﹡Plus工具 章 是这种方式有一个缺点，就是会显示口令信息。
3．使用SQL﹡Plus命令连接与断开数据库 在SQL*Plus中连接数据库时，可以使用CONNECT（可简写为CONN）命令指定不同的登录用户。
连接数据库后，SQL*Plus维持数据库会话。
CONNECT命令的语法格式如下： CONN[ECT][{user_name[/password][@connect_identifier]}[AS{SYSOPER|SYSDBA|SYSTEM}]] 如果需要断开与数据库的连接，可以使用DISCONNECT（可简写为DISCONN）命令，该命令可以结束当前会话，但是保持SQL*Plus运行。
要退出SQL*Plus，关闭SQL*Plus窗口，可以执行EXIT或者QUIT命令。
3.2使用SQL﹡Plus命令 通常所说的DML、DDL、DCL语句都是SQL*Plus语句，它们执行完后，都可以保 存在一个称为SQLBUFFER的内存区域中，并且只能保存一条最近执行的SQL语句。
除了SQL*Plus语句，在SQL*Plus中执行的其他语句称为SQL*Plus命令。
SQL*Plus命 49 令执行完后，不保存在SQLBUFFER的内存区域中，它们一般用来对输出的结果进行格 式化显示，以便于制作报表。
3.2.1使用DESCRIBE命令查看表结构 表结构包括一个数据库表的名称、有哪些字段以及哪些字段是主键等信息。
表结构可以通过使用DESCRIBE命令在数据库中查询，该命令的语法格式如下： DESCRIBE{[schema.]object[@connect_identifer]} 其中，schema表示指定对象所属的用户名；object表示对象的名称，如表名或视图名；@connect_identifer表示数据库连接字符串。
技巧DESCRIBE也可以简写为DESC。
下面使用DESCRIBE命令查看scott用户下的emp表的结构，结果如下： SQL>DESCemp 名称 是否为空?
-------- ---------- EMPNO NOTNULL ENAME JOB 类型 --------NUMBER
(4)VARCHAR2(10)VARCHAR2
(9) Oracle11g基础教程与实验指导 MGRHIREDATESALCOMMDEPTNO NUMBER
(4)DATENUMBER(7,2)NUMBER(7,2)NUMBER
(2) 使用DESCRIBE命令查看表结构时，如果指定的表不存在，则提示信息：objecttablename（表名）doesnotexist。
如果指定的表存在，则显示该表的结构。
如上面的结果所示，显示表结构时，按照“名称”、“是否为空？”、“类型”这三列进行显示。
其中，“名称”表示列的名称；“是否为空？”表示对应列的值是否可以为空，如果可以为空，则不显示任何内容，如果不能为空，则为NOTNULL；“类型”表示该列的数据类型，并且显示其精度。
3.2.2执行SQL脚本 在工作需要的时候，常常会写很多命令，而这些命令如果一条一条的输入再执行， 则会降低工作效率，浪费劳力，这时，执行SQL脚本文件可以解决这一难题。
下面介绍 执行SQL脚本方法的使用。
50 使用@执行SQL脚本的语法格式如下： SQL>@full_path\file_name 其中，full_path表示脚本文件的路径；file_name表示脚本文件的文件名。
例： SQL>@D:\test.sql 在SQL*Plus中输入以上命令之后，按Enter键，开始执行SQL脚本文件。
@命令将test.sql文件的内容读入SQL*Plus缓冲区，然后执行缓冲区中的内容。
下面通过一个示例介绍@命令的使用方法，该命名执行后的结果如下： SQL>@F:\test.sql名称 ------------------------EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNODEPTNODNAMELOC 是否为空?
----------NOTNULL NOTNULL 类型 ---------------NUMBER
(4)VARCHAR2(10)VARCHAR2
(9)NUMBER
(4)DATENUMBER(7,2)NUMBER(7,2)NUMBER
(2)NUMBER
(2)VARCHAR2(14)VARCHAR2(13) 其中，test.sql文件的路径为F:\test.sql，test.sql文件的内容为： 第
3 使用SQL﹡Plus工具 章 descemp;descdept; @F:\test.sql命令分别执行上面两条命令，两条命令的结果均会显示在窗口中。
提示执行SQL脚本还可以使用start命令，start命令等同于@命令，如startF:\test.sql等同于 @F:\test.sql。
3.2.3使用SAVE命令保存缓冲区内容到文件 当执行完一条SQL语句后，该语句就会被存入缓冲区，而把以前存在缓冲区的语句覆盖，也就是说，缓冲区只能存放刚刚执行完的SQL语句。
使用SAVE命令可以将缓冲区中的内容保存到文件中，SAVE命令的语法格式如下： SAV[E][FILE]file_name[CRE[ATE]|REP[LACE]|APP[END]] 参数说明如表3-1所示。
51 表3-1SAVE命令参数表 参数 说明 file_nameCREATEREPLACE APPEND 表示将SQL*Plus缓冲区的内容保存到文件名file_name的文件中表示创建一个file_name文件，该选项为默认值表示如果file_name文件已经存在，则覆盖file_name文件的内容；如果该文件不存在，则创建该文件如果file_name文件已经存在，则将缓冲区中的内容追加到file_name文件的末尾；如果该文件不存在，则创建该文件 下面通过一个示例介绍SAVE命令的使用方法。
执行下面的SQL语句： SELECTenameFROMemp; 此时，缓冲区的内容就是上面的SQL语句，使用SAVE命令将SQL*Plus缓冲区中的SQL语句保存到一个名为test2.sql的文件中，代码如下： saveF:\test2.sql; 注意在SAVE命令中，如果没有为文件指定路径，则会默认保存到Oracle安装路径的 product\11.2.0\dbhome_1\BIN目录下。
Oracle11g基础教程与实验指导 3.2.4使用GET命令读取脚本文件到缓冲区 前面介绍了使用SAVE命令将缓冲区的内容保存到一个文件中，而使用GET命令可以将文件中的内容读取到缓冲区。
GET命令的语法格式如下： GET[FILE]file_name[LIST|NOLIST]; 语法说明如下：（1）file_name：一个指定文件，将该文件的内容读入SQL*Plus缓冲区中。
（2）LIST：列出缓冲区中的语句。
（3）NOLIST：不列出缓冲区中的语句。
下面通过一个示例介绍GET命令的具体使用方法。
将3.2.3节中保存的test2.sql文件的内容读入到缓冲区中，并获取执行结果，代码如下： SQL>GETF:\test2.sql 521*SELECTenameFROMempWHEREename='SMITH'SQL>RUN1*SELECTenameFROMempWHEREename='SMITH'ENAME---------SMITH 注意使用GET命令时，如果file_name指定的文件在Oracle的安装目录 product\11.2.0\dbhome_1\BIN下，则只需要指出文件名；如果不在这个目录下，则必须指定完整的路径名。
3.2.5使用EDIT命令编辑缓冲区内容或文件 使用EDIT命令，可以编辑缓冲区的内容。
这样，如果语句执行出错，用户可以很方便地进行修改，特别是长的、复杂的SQL语句。
EDIT命令可以将SQL*Plus缓冲区中的内容复制到一个文件名为afied.buf的文件中，可以使用文本编辑器打开这个文件，该命令的语法格式如下： ED[IT][file_name] 其中，file_name默认为afied.buf，也可以指定一个其他的文件。
下面通过一个示例介绍EDIT命令的具体使用方法。
在SQL*Plus中，使用EDIT命令将缓冲区中的内容复制到afied.buf文件中。
第
3 使用SQL﹡Plus工具 章 SQL>SELECTENAMEFROMempWHEREename='SMITH';ENAME---------SMITHSQL>EDIT已写入fileafiedt.buf 在SQL*Plus中输入EDIT命令之后，将打开一个记事本文件afiedt.buf，在该文件中显示缓冲区中的内容，如图3-4所示。
图3-4使用EDIT命令编辑缓冲区内容 对afiedt.buf文件中的内容进行编辑，将文件内容修改为： SELECTenameFROMempWHEREename=’JONES’ 53 当退出编辑器时，该文件的内容将被复制到SQL*Plus缓冲区中。
这时可以使用斜杠（/）运行刚才修改过的查询语句，代码如下： 1*SELECTenameFROMempWHEREename='JONES'SQL>/ENAME---------JONES 3.2.6使用SPOOL命令复制输出结果到文件 使用SPOOL命令可以将SQL*Plus中的输出结果复制到一个指定的文件中，直到使用SPOOLOFF命令为止。
SPOOL命令的语法格式如下： SPO[OL][file_name[CRE[ATE]|REP[LACE]|APP[END]]|OFF|OUT] 语法说明如下：（1）file_name：指定一个操作系统文件。
（2）CREATE：创建一个指定的文件名为file_name的文件。
（3）REPLACE：如果指定的文件已经存在，则替换该文件。
（4）APPEND：将内容追加到一个已经存在的文件末尾。
（5）OFF：停止将SQL*Plus中的输出结果复制到file_name文件中，并关闭该文件。
Oracle11g基础教程与实验指导
（6）OUT：启动该功能，将SQL*Plus中的输出结果复制到指定的文件名为file_name的文件中。
例如，使用SPOOL命令将SQL*Plus中的输出结果保存到F:\spool.txt文件中，代码如下： SQL>SPOOLF:\spool.txt 然后执行SQL语句： SQL>SELECT*FROMemp; 执行SPOOLOFF命令，在该命令之后所操作的任何语句，将不再保存其执行结果。
SQL>SPOOLOFF; 打开F:\spool.txt文件，该文件内容是上述SQL语句的执行结果。
3.3变量 在Oracle数据库中，可以使用变量来编写通用的SQL语句。
由于这些变量通常用 54 来替代值，因此称为替代变量。
替换变量有以下两种类型：
1．临时变量 临时变量只在使用它的SQL语句中有效，值不能保留。
2．已定义变量 已定义变量会一直保留到被显示地删除、重定义或退出SQL*Plus为止。
3.3.1临时变量 在SQL语句中，可以使用字符&定义临时变量，后面跟要定义的变量名。
例如，&v_number就定义了一个名为v_number的变量。
在SQL*Plus中输入以下SQL语句： SQL>SELECTdeptno,dnameFROMdeptWHEREdeptno=&v_deptno; 在上面的SQL语句中定义了一个名为v_deptno的变量，按回车键后将会显示以下信息： 输入v_deptno的值: SQL*Plus提示用户为v_deptno变量输入一个值，然后在SELECT语句的WHERE子句中使用这个变量值，代码如下： 原值新值 1:SELECTdeptno,dnameFROMdeptWHEREdeptno=&v_deptno1:SELECTdeptno,dnameFROMdeptWHEREdeptno=10 第
3 使用SQL﹡Plus工具 章 DEPTNO DNAME ------------------------ 10 ACCOUNTING 由上面的示例可以看出，执行替换的行号为
1，变量v_deptno被替换为数字10。
1．控制输出行 原值和新值的输出可以使用SETVERIFY命令控制。
如果输入SETVERIFYOFF命令，就会禁止显示原值和新值，下面使用斜杠（/）再次运行上面的SQL语句，SQL*Plus就会提示为变量v_deptno输入一个新值，代码如下： SQL>SETVERIFYOFF SQL>/ 输入v_deptno的值:10 DEPTNO DNAME ------------------------ 10 ACCOUNTING 如上面代码所示，使用SETVERIFYOFF命令之后，执行SQL语句时原值和新值 行没有输出，直接输出了SQL语句的执行结果。
如果想重新显示这些行，可以使用SET VERIFYON命令，这里不再详细介绍该命令的使用方法。
55
2．修改变量定义字符 在SQL语句中，通常使用字符&定义临时变量，也可以指定使用其他字符，这时就需要使用SETDEFINE命令，下面的示例显示了如何使用SETDEFINE命令将变量定义字符设置为字符#。
SQL>SETDEFINE'#' SQL>SELECTdeptno,dnameFROM 输入v_deptno的值:10 DEPTNO DNAME ------------------------ 10 ACCOUNTING dept WHERE deptno=#v_deptno; 3.3.2定义变量 已定义变量是指具有明确定义的变量，该变量的值会一直保留到被显式地删除、重定义或退出SQL*Plus为止。
可以使用DEFINE命令定义变量，在使用完变量后，可以使用UNDEFINE命令将其删除。
1．使用DEFINE命令定义并查看变量 DEFINE命令既可以用来定义一个新变量，也可以用来查看已经定义的变量。
该命令的语法格式有以下3种： Oracle11g基础教程与实验指导
（1）DEF[INE]：显示所有已定义变量。
（2）DEF[INE]variable：显示指定变量的名称、值和数据类型。
（3）DEF[INE]variable=value：创建一个CHAR类型的变量，并为该变量赋初始值。
例如，定义一个名为v_ename的变量，并为其赋初始值为“SMITH”，代码如下： SQL>DEFv_ename='SMITH' 使用DEFINE命令加上变量名就可以查看该变量的定义，代码如下： SQL>DEFv_enameDEFINEv_ename ="SMITH"(CHAR) 提示单独使用DEFINE命令，可以查看当前会话的所有变量。
已定义变量可以用来指定一个元素，下面的查询使用到了之前定义的变量v_ename，并在WHERE子句中引用该变量的值。
SQL>SELECTename 562FROMemp3WHEREename='&v_ename';原值3:WHEREename='v_ename'新值3:WHEREename='SMITH'ENAME---------SMITH 由上面结果可以看出，这一次并没有提示用户输入v_ename的值，而是直接使用了该变量已经设置好的值。
2．使用ACCEPT命令定义并设置变量 ACCEPT命令用于等待用户为变量输入一个值。
该命令既可以为现有的变量设置一个新值，也可以定义一个新变量，并使用一个值对该变量进行初始化。
ACCEPT命令还可以为变量指定数据类型。
ACCEPT命令的语法格式如下： ACCEPTvariable_name[type][FOR[MAT]format][DEF[AULT]default][PROMPTtext|NOPR[EMPT]][HIDE] 语法说明如下：（1）variable_name：指定为变量分配的名字。
（2）type：指定变量的数据类型，可以使用的类型有CHAR、NUMBER和DATE。
默认情况下，变量的类型为CHAR。
（3）FORMAT：指定变量的格式，包括A15（15个字符）、9999（一个4位数字）和DD-MON-YYYY（日期）。
第
3 使用SQL﹡Plus工具 章
（4）DEFAULT：为变量指定一个默认值。
（5）PROMPT：在用户输入数据之前显示的文本消息。
（6）HIDE：隐藏用户为变量输入的值。
下面通过一个示例介绍ACCEPT命令的使用方法。
在该示例中，使用ACCEPT命令定义一个名为v_deptno的变量，该变量为两位数字，并在查询语句中引用该变量，代码如下： SQL>ACCEPTv_deptnoNUMBERFORMAT99PROMPT'pleaseinsertthedeptno:' pleaseinsertthedeptno:10 SQL>SELECT* 2FROMdept 3WHEREdeptno=&v_deptno; 原值3:WHEREdeptno=&v_deptno 新值3:WHEREdeptno 10 DEPTNO DNAME LOC ------------------------------------- 10 ACCOUNTING NEWYORK 在上面代码中并没有将输入的变量值“10”隐藏，在实际的开发应用中，为了安全 起见，通常会隐藏用户输入的值，可以通过在ACCEPT命令的末尾加上“HEDE”来 实现。
57
3．使用UNDEFINE命令删除变量 UNDEFINE命令用于删除变量。
例如，使用UNDEFINE命令删除变量v_deptno，代码如下： UNDEFINEv_deptno 3.4练习3-1：使用多个变量动态获取部门信息 在实际应用中变量的使用是很普遍的，例如，当需要查询不同的部门信息时，只需要输入一个新的部门值即可。
在SQL语句中定义了3个变量，分别需要输入一个列名（变量名为v_deptno）、一个表名（变量名为v_table）和一个列值（变量名为v_value）： SQL>SELECT&v_deptno,dname 2FROM&v_table 3WHERE&v_deptno=&v_value; 输入v_deptno的值:deptno 输入v_table的值:dept 输入v_deptno的值:deptno 输入v_value的值:10 DEPTNO DNAME ------------------------ 10 ACCOUNTING Oracle11g基础教程与实验指导 由上面的示例可以看出，需要输入两次相同的变量（v_deptno）的值，为了避免重复输入相同的变量，可以使用&&定义变量，上面的示例可以修改为： SQL>SELECT&&v_deptno,dname 2FROM&v_table 3WHERE&&v_deptno=&v_value; 输入v_deptno的值:deptno 输入v_table的值:dept 输入v_value的值:10 DEPTNODNAME ------------------------ 10 ACCOUNTING 以上介绍了临时变量的使用方法，使用变量为编写其他可以运行的脚本提供了很多灵活性，可以为用户提供一个脚本，用户只需要输入变量值即可。
3.5格式化查询结果 SQL*Plus提供了大量的命令用于格式化查询结果，使用这些命令可以格式化列，设 58置和一底页标显题示，多在少报行表数中据显，示设当置前一日行期显和示页多号少，个也字可符以和为创报建表简添单加报新表的，统并计为数报据表等添。
加常标用题的 格式化查询结果命令有COLUMN、COMPUTE、BREAK、BTITLE、TTITLE等。
3.5.1格式化列 在SQL*Plus中，经常使用COLUMN命令对所输出的列进行格式化，即按照一定的格式进行显示。
COLUMN命令的语法格式如下： COLUMN{column|alias}[options] 语法说明如下：（1）column：指定列名。
（2）alias：指定要格式化的列的别名。
（3）options：指定用于格式化列或别名的一个或多个选项。
在COLUMN命令中，可以使用很多选项，表3-2列出了其中的部分选项。
选项FOR[MAT]formatHEA[DING]headingJUS[TIFY][{LEFT|CENTER|RIGHT}] 表3-2COLUMN命令 说明将列或列名的显示格式设置为由format字符串指定的格式将列或列名的标题中的文本设置为由heading字符串指定的格式将列输出设置为左对齐、居中或右对齐 选项WRA[PPED] WOR[D_WRAPPED]CLE[AR] 第
3 使用SQL﹡Plus工具 章 续表 说明在输出结果中将一个字符串的末尾换行显示，该选项可能导致单个单词跨越多行与WRAPPED选项类似，不同之处在于单个单词不会跨越多行清除列的任何格式化（将格式设置回默认值） 表3-2中的format字符串可以使用很多格式化参数，可以指定的参数取决于该列中保存的数据。
如果列中包含字符，可以使用Ax对字符进行格式化，其中x指定了字符宽度，如A13就是将宽度设置为13个字符；如果列中包含数字，可以使用数字格式，如$99.99就是在数字前加美元符号；如果列中包含日期，可以使用日期格式，如MM-DD-YYYY设置的格式就是一个两位的月份（MM）、一个两位的日（DD）、一个4位的年份（YYYY）。
下面通过一个示例介绍COLUMN命令的使用方法。
该示例查询emp表中的eanme、empno和sal列，并分别对它们进行格式化，代码如下： SQL>COLUMNempnoHEADING"员工编号"SQL>COLUMNenameHEADING"雇员名"FORMATA13 SQL>COLUMNempnoFORMAT9999SQL>COLUMNsalHEADING"工资"FORMAT$9999.99 59 SQL>SELECTempno,ename,sal 2FROMemp; 员工编号 雇员名 工资 -------- ---------------- ------ 7369 SMITH $800.00 7499 ALLEN $1600.00 7521 WARD $1250.00 7566 JONES $2975.00 ... 7900 JAMES $950.00 7902 FORD $3000.00 7934已选择14行。
MILLER $1300.00 由上面的示例可以看出，使用COLUMN命令不仅可以对列的值进行格式化，还可以修改列名，使用别名来显示，从而使查询结果更加简明、直观。
在该示例中，输出结果中标题显示了两次，为了让结果看起来更美观，只让标题显示一次，可以通过设置页面显示行数据来实现，3.5.2节将介绍如何设置页面显示行数据。
3.5.2设置每页显示的数据行 每页中显示的数据可以使用SETPAGESIZE命令来设置。
这个命令的具体功能是设置SQL*Plus输出结果中一页应该显示的行数，超过这个行数之后，SQL*Plus就会再次 Oracle11g基础教程与实验指导 显示标题。
SETPAGESIZE命令的语法格式如下： SETPAGESIZEn 其中，参数n表示每一页显示的行数，最大只可以为50000，默认值为14。
下面使用SETPAGESIZE命令将页面大小设置为20行，再次执行3.5.1节中的示例： SQL>SETPAGESIZE20 SQL>/员工编号 雇员名 --------------------- 7369 SMITH 7499 ALLEN 7521 WARD 7876 ADAMS ... 7902 FORD 7934已选择14行。
MILLER 工资--------$800.00$1600.00$1250.00$1100.00 $3000.00$1300.00 上面示例中，设置了该页显示20行数据，故只在顶部显示了一次标题，这样的输出 60结构看起来更加简明。
提示SQL*Plus中的页并不是仅仅由输出数据行构成，而是由SQL*Plus显示到屏幕上的所有 输出结果构成，包括标题和空行等。
3.5.3设置每行显示的字符数 一行中显示的字符数可以使用SETLINESIZE命令来设置，默认值为80。
如果设置的值比较小，那么表中每行数据有可能在屏幕上需要分多行显示；如果设置的值比较大，则表中每行数据就可以在屏幕的一行中进行显示。
LINESIZE命令的语法格式如下： SETLINESIZEn 其中，n表示屏幕上一行数据中显示的字符数，有效范围是1～32767。
下面以设置一行显示20个字符为例，查询emp表中雇员编号和雇员姓名。
示例代码如下： SQL>SETLINESIZE20SQL>SELECTempno,ename 2FROMemp;员工编号-------雇员名------------- 第
3 使用SQL﹡Plus工具 章 7369SMITH 7499ALLEN 7521WARD... 7566JONES 7654MARTIN 上面示例中，由于一行显示的字符数为20，因此所有超出的部分换行显示。
提示如果不再使用列的格式化，可以使用COLUMN命令加上CLEAR选项来清除，CLEAR columns为清除所有列的格式化。
3.6创建简单报表 61 所谓报表就是用表格、图表等格式来动态显示数据。
计算机上报表的主要特点是数 据动态化和格式多样化，并且实现报表数据和报表格式的完全分离，用户可以只修改数 据或者只修改格式。
3.6.1报表的标题设计 报表的标题是利用SQL*Plus的两个命令来设计的，即TTITLE和BTITLE。
其中，TTITLE命令用来设计报表的头部标题，而BTITLE命令用来设计报表的尾部标题。
TTITLE命令设计的头部标题显示在报表每页的顶部。
设计头部标题时，要指定显示的信息和显示的位置，还可以使标题分布在多行之中。
用TTITLE命令设计头部标题的操作是比较复杂的，这条命令的语法格式如下： TTI[TLE][printspec[text|variable]...]|[OFF|ON] 语法说明如下：（1）printspec：指定出现在报表中每一个页面顶端的页眉，其可选值有CENTER、RIGHT、BOLD、FORMATtext、COLn、S[KIP][n]和TABn。
（2）OFF：取消设置。
（3）ON：启用设置。
下面是使用TTITLE命令设置页面的示例。
LEFT、 SQL>TTITLERIGHT'日期：'_DATECENTER'生成报表'SQL.USER'user'SQL>RIGHT'页：'FORMAT999SQL.PNOSKIP2 Oracle11g基础教程与实验指导 其中，_DATE表示当前日期；SQL.USER表示显示当前用户；SQL.PNO表示显示当前页（FORMAT用来格式化数字）；CENTER和RIGHT表示文本的对齐方式；SKIP2表示跳过两行。
BTITLE命令的用法与TTITLE命令是一样的，区别在于BTITLE命令用来设计尾部标题，显示的位置在报表每页的底部。
BTITLE命令的语法格式如下： BTI[TLE][printspec[text|variable]...]|[OFF|ON] 下面的示例演示了BTITLE命令的使用。
BTITLECENTER"谢谢使用该表"RIGHT"页："FORMAT999SQL.PNO 查询scott用户下的emp表中的数据并以报表的信息显示出来。
在设计报表时，需要使用TTITLE和BTITLE命令设置页眉和页脚信息，其具体的操作步骤如下：
（1）在F:\SQL文件夹下创建report.sql文件，该脚本中包含了TTITLE和BTITLE命令，脚本内容如下: TTITLERIGHT'日期：'_DATECENTER'生成报表'SQL.USER BTITLECENTER'谢谢使用该表'RIGHT'页：'FORMAT999SQL.PNO SETPAGESIZE20 62 SETLINESIZE120COLUMNenameHEADING"员工姓名"FORMATA20 COLUMNsalHEADING"工资"FORMAT$9999.99 COLUMNhiredateHEADING"入职时间" COLUMNempnoHEADING"员工编号" SELECTempno,ename,hiredatesal FROMemp; TTITLEOFF BTITLEOFF
（2）运行F:\SQL\report.sql文件，生成报表，代码如下： @F:\SQL\report.sql 员工编号 日期：16-8月-12 员工姓名 入职时间 生成报表SCOTT工资 ----------------------------------------------------- 7369 SMITH 17-12月-80 $800.00 7499 ALLEN 20-2月-81 $1600.00 ...79027934 FORDMILLER 03-12月-8123-1月-82 $3000.00$1300.00谢谢使用该表 已选择14行。
页：
1 在上面的示例中，首先使用TTITLE和BTITLE设置了报表的页眉和页脚，然后使用COLUMN格式化列信息，最后使用TTITLEOFF和BTITLEOFF关闭了设置的页眉 第
3 使用SQL﹡Plus工具 章 和页脚，从而其他报表中不适用该页眉、页脚的设置。
3.6.2统计数据 BREAK和COMPUTE命令可以结合使用，用来为列添加小计。
BREAK子句可以使SQL*Plus根据列值的范围分隔输出结果，COMPUTE子句可以使SQL*Plus计算一列的值。
BREAK命令的语法格式如下： BRE[AK][ONcolumn_name]SKIPn 语法说明如下：（1）column_name：对哪一列执行操作。
（2）SKIPn：在指定列的值变化之前插入n个空行。
COMPUTE命令的语法格式如下： COMP[UTE]functionLABELlabelOFcolumn_nameONbreak_column_name 语法说明如下：
（1）Function：执行的操作，如SUM（求和）、MAXIMUN（最大值）、MINIMUN （最小值）、AVG（平均值）、COUNT（非空值的列数）、NUMBER（行数）、VARIANCE 63 （方差）以及STD（均方差）等。
（2）LABEL：指定显示结果时的文本信息。
下面使用BREAK命令和COMPUTE命令计算不同部门的工资总数。
首先在F:\SQL 文件夹下新建文件sum.sql，该脚本的内容如下： BREAKONdeptnoCOMPUTESUMOFsalONdeptnoCOLUMNdeptnoHEADING"部门号"FORMAT99COLUMNenameHEADING"员工姓名"FORMATA20COLUMNsalHEADING"工资"SELECTdeptno,ename,salFROMemp ORDERBYdeptno; 然后使用@F:\SQL\sum.sql命令执行sum.sql脚本，执行结果如下： SQL>@F:\SQL\sum.sql 部门号------ 10 ******sum ... 员工姓名----------------------- CLARKKINGMILLER 20JONES 工资 -----$2450.00$5000.00$1300.00--------$8750.00$2975.00 Oracle11g基础教程与实验指导 30****** sum已选择14行。
MARTIN $1250.00--------- $9400.00 当deptno有了新值后，SQL*Plus会对输出结果重新进行分隔，并对deptno相同行的sal列进行求和，deptno列相同的行只会显示一次deptno的值。
3.7练习3-2：使用报表统计各部门的最高工资 在3.6节，介绍了如何使用TTITLE和BTITLE命令创建报表以及如何使用BREAK和COMPUTE命令统计数据，下面把这两种命令结合起来使用创建一个报表，并统计各部门的最高工资。
（1）在F:\SQL文件夹下创建生成报表的脚本文件max_sal.sql。
（2）在max_sal.sql脚本文件中，首先使用TTITLE和BTITLE命令设置报表的页眉和页脚信息，并使用SETPAGESIZE命令设置每页显示50行数据，使用SETLINESEIZ命令设置每行显示100个字符，代码如下： TTITLERIGHT'日期：'_DATECENTER'使用报表统计各部门的最高工资' 64 BTITLECENTER''RIGHT''FORMAT999SKIP2SQL.PNOSETPAGESIZE50 SETLINESIZE100
（3）使用COLUMN命令格式化列的信息，在格式化列之前，需要使用CLEAR命令清除列的格式，代码如下： CLEARCOLUMNSCOLUMNempnoHEADING'员工编号'FORMAT9999COLUMNenameHEADING'员工姓名'FORMATA10COLUMNdeptnoHEADING'部门编号'FORMAT9999COLUMNsalHEADIN'工资'FORMAT$999,999.99
（4）使用BREAK命令根据deptno列值的不同分隔输出，并使用COMPUTE命令根据deptno列统计sal的最大值，代码如下： BREAKONdeptnoCOMPUTEMAXIMUMOFsalONdeptnoSELECTdeptno,empno,ename,salFROMempORDERBYdeptno;
（5）在SQL*Plus中使用scott用户登录，当连接成功后，使用START命令运行max_sal.sql文件，运行结果如下： SQL>STARTF:\SQL\max_sal.sql 使用报表统计各部门的最高工资 部门编号 员工编号 员工姓名 日期：17-8月-12工资 --------------------------------------------------- 10 7782 CLARK $2,450.00 第
3 使用SQL﹡Plus工具 章 最高工资20 最高工资30 ...最高工资 已选择14行。
78397934 75667902 75217844 KINGMILLER JONESFORD WARDTURNER 谢谢使用该表 $5,000.00$1,300.00$5,000.00$2,975.00$3,000.00$3,000.00$1,250.00$1,500.00 $2,850.00页：
1 在上述的输出结果中，输出了页眉和页脚信息，每个数据列的内容都按照指定的格式进行显示，使用BREAK命令根据deptno列值的不同进行分隔输出，并使用COMPUTE命令，从而统计了每个部门的最高工资。
3.8扩展练习
1．格式化输出结果 65 使用SETPAGESIZE命令将页面大小设置为30行，并使用SETLINESIZE命令来设 置每行显示的字符数为20，然后使用这种设置格式化查询当前用户下的所有表并显示 出来。
2．使用报表统计各部门的平均工资 使用报表统计各部门的平均工资，要求在报表中显示当前时间，结果如下： 使用报表统计各部门的平均工资 部门编号 员工编号员工姓名 日期：30-8月-12工资 ------------------------------------- 10 7782CLARK $2,450.00 7839KING $5,000.00 ******** 7934MILLER $1,300.00------------ avg $2,916.67 20 7566JONES $2,975.00 ******** 7902FORD $3,000.00------------ avg $2,175.00 30 7521WARD $1,250.00 7844TURNER $1,500.00 7499ALLEN $1,600.00 7900JAMES $950.00 谢谢使用该表 页：
1 已选择14行。
声明：
该资讯来自于互联网网友发布，如有侵犯您的权益请联系我们。
相关文章
