第一章
SQL*PLUS
本章介绍关系数据库的SQL语言及其在ORACLE中的应用技巧。
1．1简介．
SQL*PLUS是ORACLE的交互查询工具，它允许用户使用SQL命令交互式地访问数据库，也允许用户使用SQL*PLUS命令与系统发生联系。1．1．1结构查询语言SQL．．SQL语言是一种使用方便灵活的语言，因为它是一种非过程化程度相当高的语言，使用这种语言编程，用户只需在程序中指出要干什么，至于如何干，用户不必在程序中指出，而由系统来决定完成。SQL语言可以通过两种方式使用，一种是命令方式，另一种是程序方式。采用命令方式时，用户通过交互来执行该命令。SQL语言还可以嵌入C，COBOL和FORTRAN等高级语言中，组成一个完整的程序。用户可以根据不同需要选择不同的使用方式，以满足不同的要求。SQL语言还提供了事务控制，以保证数据共享以及并发使用时不产生干扰，也便于对数据库的恢复。此外，SQL语言还提供了授权控制，以保证数据的安全与保密，防止非法用户对数据库的使用与破坏。正因为SQL语言的这些特点，使它受到人们的广泛重视与欢迎。近几年来，SQL语言已成为标准的数据库语言，现在已有一百多个数据库管理产品支持SQL语言。1．1．2启动SQL*PLUS．．启动SQL*PLUS的步骤如下：（1）将ORACLE软件安装到机器上。（2）启动机器进行操作系统登录，成功后用户将在屏幕上看到操作系统的提示符，说明进入操作系统已经成功。（3）启动ORACLE数据库系统，将ORACLERDBMS装入内存中，命令执行后，就可以运行工具软件了。（4）在操作系统提示符下执行SQL*PLUS：$SQLPLUS[RETURN]SQL*PLUS将显示它的版本号、日期和版权信息，并提示你输入用户名。因为ORACLE保护对它所有数据的访问，所以与它连接通常需要一个用户标识（user-name）和口令（password）。Enteruser-name：输入用户名后按[RETURN]，SQL*PLUS提示你输入口令：Enterpassword：如果口令输入正确，SQL*PLUS将宣布你已经与ORACLE连接上了，然后显示：SQL>说明现在你已经进入SQL*PLUS中，并等待你的指示。如果输入的用户名或口令不正确，在向ORACLE三次注册失败后，屏幕上将出现终止服务信息，并退出SQL*PLUS。在进入SQL*PLUS时，也可以在命令行上直接输入用户名和口令，中间以“/”分隔，比如，你的用户名是SCOTT，口令是TIGER，那么你输入下列命令：$SQLPLUSSCOTT/TIGER
就可以进入SQL*PLUS中。当你想停止工作并离开SQL*PLUS，则可以输入EXIT或QUIT命令。SQL>EXIT1．1．3预备知识．．1、基表ORACLE是一种关系型数据库系统，它以关系的方式组织数据，这里所说的关系，我们称之为基表。一个数据库可以由若干个表组成，用户对数据库的操作也就是对表的操作。ORACLE用基表的形式存储信息，如表1.1所示。
表1.1EMP表
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNOSMITHCLERK7369790217-Dec-8080020ALLENSLESMAN769820-Feb-811600749930030WARDSALESMAN769822-Feb-817521125050030JONESMANAGER78392-Apr-8175662975207654MARTINSALESMAN769828-Sep-811250140030BLAKEMANAGER78391-May-817698285030每个基表都有一个表名，以便识别。每个基表都包含一个或一个以上的列，每列包含一类信息，并都有一个列名，以描述保存在那一列中的信息的种类。表名和列名构成了基表的框架，也就是基表的分类信息。具体信息一行一行存放，表的每一行表示一组独立的数据，它由具有不同列值的各列组成。2、基表之间的关系在数据库中，数据与数据之间是存在着联系的，我们可以将某个基表中的信息与其他基表中的信息建立关系，例如，在前面的EMP表中，每个雇员均有部门编号（DEPTNO），它对应于DEPT表中的部门编号（DEPTNO），见表1.2。
表1.2DEPT表DEPTNO40302010DNAMEOPERATIONSSALSERSESARCHACCOUNTINGLOCBOSTONCHICAGODALLASNEWYORK
这样可便于信息的独立组织，因为我们可以在EMP表中存放雇员信息，而无需在基中存放DEPT表中有关部门的信息。当然，我们还可以在多个基表之间建立联系。3、数据字典数据字典是ORACLE关系数据库管理系统（RDBMS）最重要的成分之一，是数据库系统的宝贵资源。数据库管理员（DBA）使用它来监视ORALCERDBMS的使用，并协助用户完成他们的工作。数据字典是由ORALCE自动建立并更新的一组基表和视图，在这里记录了用户的、基表和视图的定义、用户权限以及数据存储等方面的信息。数据字典中包括下列信息的描述：（1）ORACLE所有用户的标识及用户的存取权限。（2）每个用户所拥有的数据库实体（表、空间定义、视图、索引、聚簇
和同义词）的定义。（3）为数据库实体分配的空间情况。我们可以通过查询语句查看数据字典中的有关内容。例如：用户需要查看当前用户创建的所有基表、视图和同义词清单，可以使用下列命令：SQL>SELECT*FROMCAT；CAT表中存放的就是有关用户创建的基表、视图和同义词情况的描述。数据字典由几个基础表及派生出来的视图组成。基础表是由ORACLERDBMS自动更新，用户基本上不访问基础表，ORACLERDBMS不断地更新数据字典的基础表，而视图总是引用基础表中的最新数据。一般来说，用户不能直接更新ORACLE的数据字典，因为用户操作不当、删除或修改了某些数据字典的内容，就会造成数据库的破坏。1．1．4SQL*PLUS命令．．SQL*PLUS可接收两种类型的命令：SQL命令和SQL*PLUS命令。SQL命令主要用来对数据库进行操作，而SQL*PLUS命令主要用来设置查询结果的显示格式，设置一些环境选项。1、SQL命令存储、改变、检索和维护信息。SQL命令主要用于ORACLE数据库中创建、当输入SQL命令时，SQL*PLUS将其保存在内部缓冲区中，这个内部缓冲区称为SQL缓冲区。SQL缓冲区只保存一条SQL命令，当输入新的SQL命令时，原来保存的SQL命令就被删除。用户可以利用SQL缓冲区在不重新输入命令的情况下，编辑、运行上一次执行的SQL命令。SQL命令可以分一行或多行输入，如果在一行输入，则是
SQL>SELECTEMPNO,ENAME,JOB,SAL,FROMEMPWHERESAL<2500；
在SQL命令输入过程中，如果按RETURN键，则表示命令分多行输入，在下一行的开始处将自动出现一个行号。如：
SQL>SELECTEMPNO,ENAME,JOB,SAL2FROMEMP3WHERESAL<2500；
SQL命令完成，可以用分号（；、斜线（/）或空行来结束命令的输入。输）入分号并按RETURN键，则结束SQL命令的输入，并运行SQL缓冲区中的命令。在单独的一行上键入斜线并按RETURN键，SQL*PLUS将运行SQL命令。如果输入一个空行，即在命令的最后一行后按RETURN键，SQL*PLUS显示一个行号，此时再按RETURN键，SQL*PLUS结束SQL命令的输入，回到SQL*PLUS的提示符状态。2、SQL*PLUS命令SQL*PLUS命令用于设置查询结果的输出格式，形成复杂的报表，编辑SQL命令，设置系统变量并可提供帮助信息。SQL*PLUS的很多命令可简写成一个或几个字母，SQL*PLUS命令的输入与SQL命令不同，例如，我们想改变EMP表的SAL列的显示格式，可以键入：
SQL>COLUMNSALFORMAT$99,999HEADINGSALARY
COLUMN命令为SAL列的数据显示设置了“$”和“，，并给出了新的列”标题，通过运行前面的SQL命令可以清楚地看到SQL*PLUS命令的结果。在输入SQL*PLUS命令过程中，RETURN键表示输入完成，按而不必输入分号（；。）
如果命令输入占满一行，光标会自动移到下一行，可以继续输入，直到按RETURN键结束输入。1．1．5获取帮助．．1、随机帮助在实际使用SQL*PLUS时，有时需要能有一种快捷的方法，获得有关命令的语法、含义的说明。SQL*PLUS提供了HELP命令，为使用者提供随机帮助信息。如果要获取所有SQL和SQL*PLUS命令的信息，只需输入
SQL>HELP
如果希望获取某一个命令的信息，可以在HELP之后输入该命令的名字，例如：
SQL>HELPACCEPT
2、DESCRIBE命令当我们需要对基表进行操作的时候，往往需要获取关于基表的结构信息，可以使用DESCRIBE命令查看基表的结构：SQL>DESCRIBEDEP命令执行结果如下：
NAMEDEPTNODNAMELOCNULL?NOTNULLTYPENUMBER(2)CHAR(14)CHAR(13)
基表的结构信息包括：NAME列名NULL标识该列是否允许为空值，如缺省则表示允许为空值说明该列的数据类型，TYPE可以是字符型：CHAR（w），VARCHAR2（w）；数字型：NUMBER（w，d）日期型：DATE原始数据型：RAW以及LONG型等等。其中w表示列的宽度，d表示小数点之后的位数还可以使用DESCRIBE命令获取有关函数、过程以及包括描述，例如：要求显示有关函数AFUNC的信息：SQL>DESCRIBEafunc结果是functionafunc(F1CHAR，F2NUMBER)returnNUMBER；
命APPENDtextCHANGE/old/newCHANGE/textCLEARBUFFERDELINPUTINPUTtextLISTLISTnLIST*LISTLASTLISTmn
令
缩AtextC/old/newC/textCLBUFF无IItextLLn或nL*LASTLmn
写
意
义
text行尾增加在当前行中将old改为new从当前行中删除text删除SQL缓冲区的所有行删除当前行增加一行或多行增加一个由text组成的行显示SQL缓冲区的所有行显示一行显示当前行显示最后一行显示多行（从m到n）
1．1．6编辑SQL*PLUS命令的使用．．1、命令行编辑命令我们可以使用SQL*PLUS的一些行编辑命令编辑SQL缓冲区中的SQL命令，表1.3列出了编辑功能的SQL8PLUS命令。表1.3（1）显示SQL缓冲区的命令使用LIST命令可以显示当前SQL缓冲区的内容。如果LIST命令后面不带如果LIST后面带一个参数（如LIST任何参数，将显示SQL缓冲区中的所有行；n），则显示第n行的内容；如果LIST后面带有两个参数（LISTmn），那将显示多
行，即从m行到n行。例如：SQL>LIST1SELECTEMPNO，ENAME，JOB，SAL2*FROMEMPWHERESQL<2500；
行号2后面的星号表示第2行为当前行。除LIST命令外，其他编辑命令都只对SQL缓冲区的当前行操作。在SELECT命令尾部输入的分号（；）没有显示，因为SQL*PLUS并不把它保存在SQL缓冲区中，所以在缓冲区未尾增加新行时；不必从最后一行去掉分号，使编辑更为方便。（2）修改SQL命令使用CHANGE命令可以从当前行中删除一部分内容，或者用新的内容替换原先的内容。在使用CHANGE命令之前必须先用带一个参数的LIST命令选择当前行。例如，下列命令将DEPTNO错输出DPTNO：
SQL>SELECTDPTNO，ENAME，SAL2FROMEMP3WHEREDEPTNO=10；
系统给出出错信息：
SELECTDPTNO，ENAME，SAL*ERRORatLine1：ORA-0904：invalidcolumnname
使用CHANGE命令进行修改：
SQL>CHANGE/DPTNO/DEPTNO
改正后屏幕上显示：
1*SELECTDEPTNO，ENAME，SAL
这时可用RUN命令重新运行修改后的SQL命令。（3）增加新行使用INPUT命令可以在SQL缓冲区增加一个或多个新行。如果只输入INPUT，则在当前行之后插入多个新行；如果输入INPUTtext，则在当前行之后插入由text组成的新行。
例如，输入INPUT命令：SQL>INPUT4
这时在INPUT命令之后出现一个行号，这个行号表明所要插入的行，此时可输入一个或多个新行。输入一行内容后按RETURN键，SQL*PLUS给出一个新行号，如屏幕所示：
45ORDERBYSAL
再按RETURN键，表示插入结束，可用RUN命令重新运行来检验最终的查询结果。（4）在一行后追加内容使用APPEND命令可以在SQL缓冲区中的当前行未尾追加内容。首先用LIST命令将需要追加内容的行置为当前行，然后使用APPENDtext命令在当前行未尾追加内容。例如，要在第四行未尾追加DESC选项，先将第四行置为当前行：
SQL>LIST44*ORDERBYSAL
然后输入下列命令追加DESC：
SQL>APPENDDESC4*ORDERBYSALDESC
最后用RUN命令重新运行。（5）删除SQL缓冲区中的行使用DEL命令可以删除SQL缓冲区中的当前行，当前行以下各行的行号顺序减1，如果把第一行设为当前行，连续多次输入DEL后，可以删除SQL缓冲区中的所有行。删除SQL缓冲区中所有行也可以使用CLEARBUFFER命令，它的作用是将SQL缓冲区清空。2、EDIT命令EDIT命令用于调用一个操作系统提供的文本编辑器。编辑SQL缓冲区里的内容或操作系统下的文本文件，并将要编辑的文件名传送给该编辑器。如果文件扩展名（例如.ext）被省略，则系统自动追加.SQL的扩展名，如果文件名和扩展名均省略，则编辑器被调用，并将SQL*PLUS产生的文件名传给它，这个文件中包含着当前缓冲区的内容。如果当前缓冲区为空，且EDIT被调用时没有给出文件名，则EDIT命令失败。
SQL>EDIT
也可以是
SQL>EDIT文件名
可以使用EDFINE命令设置系统变量_EDITOR来改变文本编辑器的类型，一般在LOGIN.SQL文件中完成它的设置，SQL*PLUS被调用时将读出这些信当息。例如，在UNIX平台上，定义Vi作为SQL*PLUS调用的外部编辑器：
SQL>DEFINE_EDITOR=Vi
3、SAVE命令
如果要把SQL命令存储到磁盘上，或者想使用自己的编辑程序，用户可以通过SAVE命令将内容写到一个文件上。
SQL>SAVEDEPTINFO
这时SQL*PLUS响应：
CreatefileDEPTINFO
文件名后缀缺省为.SQL。这时用户需要保存的内容已经存入了DEPTINFO.SQL文件中，可以对文件进行编辑、运行。如果想将修改过的内容保存到原有的命令文件中，则在SAVE命令后增加REPLACE选项：
SQL>SAVEDEPTINFOREPLACE
如果想将SQL缓冲区的内容追加到已经存在的命令文件未尾，则在SAVE命令后增加APPEND选项：
SQL>SAVEfile—nameAPPEND
4、GET命令GET命令把磁盘上的命令文件内容调入当前缓冲区，并显示在屏幕上，格式是
SQL>GETfile—name
文件后缀缺省为.SQL，文件被调入缓冲区后，可以对它进行编辑、运行。5、运行命令文件START命令将指定文件调入缓冲区并执行文件内容：
SQL>STARTfile—name
文件后缀缺省为.SQL。也可以使用“@”命令运行文件：
SQL>@file—name
1．2数据操纵语言．
数据操纵语言（DML：DataManipulationLanguage）给用户或应用程序提供访问数据库系统的接口。1．2．1数据库的基本查询．．SQL语言中最主要、最核心的部分是它的查询功能。所谓查询就是从数据库中提取满足用户需要的数据，查询是由SELECT命令实现的。SQL语言中，在许多操作都涉及到SELECT命令。例如，半SELECT命令查询到的数据插入到另外一张基表中；使用SELECT命令用满足条件的数据创建一个视图等等。因此SELECT命令也是SQL语言中最灵活、最复杂的命令。我们将从简单的SELECT命令开始，逐步深入讨论。通常，一个SELECT命令可以分解成三个部分：查找什么数据，从哪里查找，查找条件是什么。因此，SELECT命令可以分成以下几个子句：SELECT子句和FROM子句（这两个子句是每条SELECT命令必须有的）WHERE子句，（是可选的），另外带可以选择GROUPBY子句、HAVING子句和ORDERBY子句等。它的基本语法是SELECT<查询内容>FORM<表名>WHERE<条件>GROUPBY<分组内容>HAVING<组内条件>ORDERBY<排序内容>
1、SELECT子句SELECT子句的<查询内容>指出要查找的数据，FROM子句的<表名>指出查找过程中涉及到的基表，这两个子句必不可少。
例1、查找整个基表的内容。SQL>SELECT*FROMEMP；
EMPNO73697499752175667654769877827788783978447876790079027934
ENAMESMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER
JOBCLERKSALESMANSALESMANMANAGERSALESMANMANAGERMANAGERANALYSTPRESIDENTSALESMANCLERK7788CLERK7698ANALYSTCLERK
MGR79027698769878397698783978397667698
HIREDATE17-Dec-9420-Feb-9522-Feb-9502-Apr-9528-Sep-9501-May-9509-Jun-9509-Nov-9517-Nov-9508-Sep-9523-Sep-9503-Dec-95
SAL800160012502975125028502450300050001500110095030001300
COMM3005001400
DEPTNO203030203030102010
0
3020302010
75667782
03-Dec-9423-Jan-94
14rowsselected.
“*”号意味着查找表中所有列。若SELECT子句中选择一个列名，则将得到这一列的所有数据。
例2、查看雇员名字。SQL>SELECTENAMEFROMEMP；
ENAMESMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER14rowsselected.
若查找多列数据，则用逗号将各个列名分隔开。
例3、查看雇员编号、名字和工种。SQL>SELECTEMPNO，ENAME，JOBFROMEMP；
EMPNO
ENAME
JOB
73697499752175667654769877827788783978447876790079027934
SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER
CLERKSALESMANSALESMANMANAGERSALESMANMANAGERMANAGERANALYSTPRESIDENTSALESMANCLERK7788CLERK7698ANALYSTCLERK
14rowsselected.
系统在显示查询结果时，各列的显示顺序由它们在SELECT子句中出现的顺序决定。如例3中希望显示顺序是JOB，ENAME，EMPNO，则可以输入
SQL>SELECTJOB，ENAME，EMPNOFROMEMP；
JOBCLERKSALESMANSALESMAN
ENAMESMITHALLENWARD
EMPNO736974997521
………………
14rowsselected.
在显示查询结果时，各列的标题就是基表中的列名，如果希望显示的列标题不同于列名，可在SELECT子句中定义列名的别名，方法是在列名后加一个空格，然后写上它的别名。
例4、SQL>SELECTENAMEEMPLOYEEFROMEMP；
EMPLOYEESMITHALLENWARD
…………14rowsselected.
如果SELECT子句中有表达式，则这个表达式就是显示信息时的标题，当然，也可以为表达式定义一个别名，便于明确表达式的意义。
例5、计算奖金占工资的比率。SQL>SELECTENAME，SAL，COMM，COMM/SAL“C/SRATIO”FROMEMP；
ENAMEMARTINWARDALLEN
SAL125012501600
COMM1400500300
C/SRATIO
1.120.40.1875
……
……
……
14rowsselected.
当别名中有诸如空格或斜线（/）这样的字符时，必须用双引号括起来。注意：别名只能用在SELECT子句中，其他子句不能使用，并且别名仅在使用它的SELECT命令中有效，而不影响其他查询语句的结果。2、WHERE子句前面讨论的都是无条件查询，即选中基表中的所有行，我们还可以通过SELECT命令中的WHERE子句来选择指定的行。
例6、查找在10号部门工作的雇员。SQL>SELECTENAMEFROMEMPWHEREDEPTNO=10；ENAMECLARKKINGIMILLER3rowsselected.
其中“DEPTNO=10”就是查询条件。系统在执行这种条件查询时，逐行地对表中的数据进行比较，检查它们是否满足条件，如果满足条件，则取出该行的有关信息，如不满足，则不取该行的信息。使用WHERE子句时，应注意列值的表示方法，若该列为字符型，需使用单引号（）将字符串括起来，如：WHERE‘’，而且应注意单引号内的字符串要区分大小写形式。若该列ENAME=‘SMITH’为数字型，则不必使用引号，如WHEREDEPTNO=20。此外列值还可以取其他列（或其他基表的列）的值，如：WHEREEMP.DEPTNO=DEPT.DEPTNO，这里列值取另一个基表中的列，这实际上是一种连接形式。关于联接将在1.6.1小节中具体论述。在HWERE子句的条件中可以使用下列比较运算符：
运算符=！=或<>>>=<<=IN（列表）BETWEEN值1AND值2LIKE%或_ISNULLISNOTNULL含义相等不等于大于大于等于小于小于等于等于列表中的任意值大于等于值1并且小于等于值2（即在值1和值2之间）模式匹配。“%”匹配0个或任意多个字符。“_”匹配一个字符空值非空值
（1）IN在查找中，经常会遇到要求表的列是某几个值中的一个。例7、要求查找有哪些职员和分析员。这里就要在EMP表中检查JOB列是否为‘CLERK’或‘ANALYST’，即JOB列的值为‘CLERK’和‘ANALYST’中的一个即可。对于这样的查询，一般可以用或（OR）操作来完成：
SQL>SELECTENAME，JOBFROMEMP
WHEREJOB=‘CLERK’ORJOB=‘ANALYST’；
如果JOB列还可以取多个值，则还需要多个OR操作，如果使用IN，就可以用一个简单的WHERE子句表示这种要求：
SQL>SELECTENAME，JOBFROMEMPWHEREJOBIN（‘CLERK’‘ANALYST’；，）
ENAMESMITHSCOTTADAMSJAMESIFORDMILLER6rowsselected.
JOBCLERKANALYSTCLERKCLERKANALYSTCLERK
谓词IN的作用就是检查列值是否等于它后面括弧内的一组值中的某一个，如果等于其中某一个值，则被查询出来。我们还可以使用NOTIN来表示与IN完全相反的含义，即选择那些不在列表中的行。
例8、查找不是职员和分析员的雇员。SQL>SELECTENAME，JOBFROMEMPWHEREJOBNOTIN（‘CLERK’‘ANALYST’；，）
（2）BETWEEN…AND在查找中，如果要求某列的数值在某个区间范围内，则可以使用BETWEEN…AND。
例9、查找哪些雇员的工资在$2000到$3000之间，可以写成SQL>SELECTENAME，JOB，SALFROMEMPWHERESALBETWEEN2000AND3000；
ENAMEJONESBLAKECLARKSCOTTFORD5rowsselected.
JOBMANAGERMANAGERMANAGERANALYSTANALYST
SAL29752850245030003000
与此相反，NOTBETWEEN……AND则选择列值不在该范围内的信息。
例10、SQL>SELECTENAME，JOB，SALFROMEMPWHERESALNOTBETWEEN2000AND3000；
（3）LIKE（模式匹配）在查找中，有时需要对字符串进行比较。在比较中，有的要求两个字符串完全相同，有的要求部分字符相同，而其余的字符可以任意。LIKE就可以用来搜索所有的数据，来查找与你描述的模式相匹配的行。LIKE提供两种字符串匹配方式：一种用下划线（_）表示，称为定位标志；另一种用百分号（%）表示，称为通配符。在检测一个字符串时，如果有一个字符可以任意，则在该字符串位置上用下划线表示。
例11、选择名字以W开头，后面仅有三个字母的雇员信息。
显然，在查找中，要求名字的第一个字母是W，后面仅有三个字母，可以
是任意字母，因此后面三位可以用三个下划线来表示：
SQL>SELECTENAME，DEPTNOFROMEMPWHEREENAMELIKE‘W___’；
ENAMEDEPTNOWARD301rowselected.
若在查找时只需包含某个字符串，其他字符任意的情况下，可用通配符%来表示。
例12、SQL>SELECTENAME，DEPTNOFROMEMPWHEREENAMELIKE‘S%’；
ENAMEDEPTNOSMITH20SCOTT202rowsselected.
通配符%还可以与下划线一起使用。
例13、查找名字以字母M开头，以R结尾，并且第三个字母为L的雇员名字、工种和所在部门号。
那么，在查找中，名字的第一个字母是M，第三个字母是L，第二个字母可以是任意的，该位置可用一个下划线表示。最后一个字母是R，而第三个字母和最后一个字母之间可以是任意长度的字符串，所以在字母L和R之间使用通配符%，其命令语句如下：
SQL>SELECTENAME，JOBFROMEMPWHEREENAMELIKE‘M_L%R’；
ENAMEDEPTNOMILLERCLERK1rowselected.
总之，我们可以使用通配符%匹配任意长度的字符串，使用‘_‘匹配一个任意字符。此外我们还可以使用NOTLIKE选择列值不匹配的行。
例14、查找哪些雇员的工种名不以SALES开头。SQL>SELECTENAME，JOBFROMEMPWHEREJOBNOTINLIKE‘SALES%’；
（4）ISNULL和ISNOTNULL我们使用的数据库一般是反映实际的具体情况，但有时某些数据在实际情况中还不存在，例如：新来的雇员还未分配到部门工作，那么他的DEPTNO就不存在，他的DEPTNO值既不等于0也不等于其他值，而是一个未知的值，我们称它的DEPTNO值为空值。空值实际是指一种未知的、不存在的或不可应用的数据，通常用NULL表示。NULL仅仅是一个符号，它不等同于零，所以它不能象零那样进行算术运算。NULL不能与等号之类的运算符连用，而应该使用关键字IS。
例15、选择没有奖金的雇员信息（即奖金为空值的雇员信息）SQL>SELECTENAME，JOBFROMEMPWHERECOMMISNULL；
ENAMESMITHJONESBLAKECLARKSCOTT
JOBCLERKMANAGERMANAGERMANAGERANALYST
KINGPRESIDENTADAMSCLERKJAMESCLERKFORDANALYSTMILLERCLERK10rowsselected.
若查找列值为非空的信息，则使用ISNOTNULL。
例16、选择有奖金的雇员。SQL>SELECTENAME，JOBFROMEMPWHERECOMMISNOTNULL；
（5）逻辑运算符NOT，AND，OR在WHERE子句中，也可以通过逻辑运算符连接多个条件，构成一个更复杂的条件进行查询。在WHERE子句中可以使用三种逻辑运算符：
运算符NOTANDOR名称含义逻辑非选择不满足条件的行逻辑与选择列值同时满足多个条件的行逻辑或选择列值满足任一条件的行
在WHERE子句中，关系比较符的优先级高于逻辑运算符。在逻辑运算符中，逻辑非（NOT）的优先级最高，逻辑与（AND）次之，逻辑或（OR）优先级最低。
例17、查找20号部门里不是职员的雇员信息。SQL>SELECTENAME，JOBFROMEMPWHEREDEPTNO=20ANDJOB！=‘LERK’；
ENAMEJOBJONESMANAGERSCOTTANALYSTFORDANALYST3recordsselected.例18、查找在20号部门工作或不是职员的雇员信息。SQL>SELECTENAME，JOB，DEPTNOFROMEMPWHEREDEPTNO=20ORJOB！=‘CLERK’；
ENAMEJOBEPNTOSMITHCLERKALLENSALESMANWARDSALESMANJONESMANAGERMARTINSALESMANBLAKEMANAGERCLARKMANAGERSCOTTANALYSTKINGPRESIDENTTURNERSALESMANADAMSCLERKFORDANALYST13rowsselected.
203030203030102010302020
综合上所述，在WHERE子句中，可以使用关系运算符、逻辑运算符以及特殊的运算符LIKE等构成条件，当条件满足时，则取有关的数据。这些运算符的优先顺序如下：
①=，！=，<>，>，>=，<，<=[NOT]BETWEEN…AND，[NOT]IN，[NOT]LIKE，IS[NOT]NULL②NOT③AND④OR括弧（）可以改变上述运算符执行的顺序。3、ORDERBY子句通常使用SELECT命令查找数据时，查询结果按各行在表中的顺序显示，当需要按照某种特定的顺序显示时，可以通过ORDERBY子句来改变查询结果的显示顺序。ORDERBY子句的格式是：SELECT……FROM……[WHERE……]ORDERBY<列名>[ASC/DESC][，<列名>[ASC/DESC]]……；在ORDERBY子句中，<列名>指出查询结果数据按该列排序，选项[ASC/DESC]表示按升序还是降序排列，选择ASC为升序显示，选择DESC为降序显示，该项缺省为升序形式。
例19、按字母顺序显示雇员的名字。SQL>SELECTENAMEFROMEMPORDERBYENAME；
ENAMEADAMSALLENBLAKECLARKFORDJAMESJONESKINGMARTINMILLERSCOTTSMITHTURNERWARD14rowsselected.
如果按降序排列，必须加上关键字“DESC”。
例20、按部门号降序显示。SQL>SELECT*FROMEMPORDERBYDEPTNODESC；
DEPTNODNAME40OPERATIONS30SALSE20RESEARCH10ACCOUNTING4rowsselected.
LOCBOSTONCHICAGODALLASNEWYORK
如果按多列进行排序时，应分别指出它们相应的列名及有关的递增或递减
方式。选择按多列排序，首先由第一个列名确定顺序，若第一排序列值相同，再按第二个列名排序，第二排序列值相同，再按第三列排序，依此类推。
例21、查找工资高于$2000的雇员信息，按部门号和雇员名字排序。SQL>SELECTENAME，SAL，DEPTNOFROMEMPWHERESAL>2000ORDERBYDEPTNO，ENAME；
ENAMECLARKSCOTTKINGFORKJONESBLAKE6rowsselected.
SAL1245030005000300029752850
DEPTNO
10202030
注意：如果排序列含有空值（如上述的DEPTNO），则含有空值的行首先显示，而不管选择升序还是降序形式。GROUPBY子句和HAVING子句将放到1.5.2节“聚组函数”具体论述。4、表达式在SQL命令中还可以使用加（+）、减（—）、乘（*）、除（/）运算符构成的表达式，其中乘、除运算的优先级高于加、减运算。算术表达式的处理顺序从左到右，括弧可以改变处理的顺序。例如，（SAL+COMM）=12*SAL+COMM。12*！这些运算符可以用于SQL命令的SELECT子句、WHERE子句、ORDERBY子句以及后面还要讲述的GROUPBY子句及HAVING子句中，但不能在FROM子句中使用运算符。
例22、选择奖金高于其工资的5%的雇员。SQL>SELECTENAME，SAL，COMM，COMM/SALFROMEMPWHERECOMM>0.05*SALORDERBYCOMM/SALDESC；
ENAMESALMARTIN1250WARD1250ALLEN16003rowsselected.
COMM1400500300
COMM/SAL1.120.40.1875
1．2．2数据更新：插入、修改和删除．．数据更新：插入、在数据库应用中，经常要对存放的数据进行更新操作，以满足不断变化的需求，因此对数据进行插入、修改和删除就成为必不可少的一项工作。1、数据的插入当需要为一个基表增加新的数据时，就要向基表中插入数据，这是由INSERT命令来实现的。INSERT命令有两种用法：一种是将指定的具体数值插入表中，通过关键字VALUES来实现；另一种是将其他表中满足条件的数据插入到一个表中，这是通过SELECT子句实现的。在插入数据时，应首先确认基表已经创建，然后确定基表的结构，基表的，可以通过DESCRIBE命令来查各列顺序、类型以及是否为非空（NOTNULL）看，以保证插入数据的类型与基表列的类型匹配。若插入字符型和日期型数据，
要用单引号括起来。（1）用VALUES插入。向基表中插入指定数据的INSERT命令格式是INSERTINTO<表名>[(列名表)]VALUES（<值表>）；其中：<表名>指出要插入数据的基表名；<列名表>指出在新插入行中的哪些列要插入数据，这是可选项，如果选择，则表示相应的列要插入数据，如果不选择，则默认表中所有的列均要插入数据；<值表>指出要插入列的具体值。
例1、为DEPT表插入一行新数据。SQL>INSERTINTODEPTVALUES（10，‘ACCOUNTING’‘NEWYORK’；，）DEPTTable
DEPTNO10
DNAMELOCACCOUNTINGNEWYORK
注意：当不选择<列名表>时，<值表>中的值应与基表的各列一一对应。如果在INSERT命令中给出列名，则要求<值表>中的值与<列名表>中的列名一一对应。
例2、SQL>INSERTINTODEPT（DNAME，DEPTNO）VALUES（‘ACCOUNTING’，10）；DEPTTable
DEPTNO10
DNAMELOCACCOUNTING
插入时，表名中的列如果未在INSERT的<列名表>中出现，那么这些列则，将该列置为为空值，如上例中的LOC列为空值。也可以为某列插入“NULL”空。插入空值的列，必须保证该列在建立时没有被定义成NOTUNLL属性，否则系统拒绝插入。（2）用SELECT命令插入。INSERT命令可以使用SELECT语句从其他基表中选择数据，插入基表中。其命令格式是INSERTINTO<表名>[(列名表)]SELECT语句；
例3、SQL>INSERTINTOEMP（EMPNO，ENAME，DEPTNO）SELECTID，NAME，DEPARTMENTFROMOLD_EMPWHEREDEPARTMENTIN（10，20，30，40）；
同使用VALUES类似，在使用SELECT命令时，应保证INSERT<列名表>中的各列与SELECT子句中选中的各列一一对应，类型匹配。（3）INSERT命令中使用参数。INSERT命令中还可以使用参数来接收用户输入的数据，每个参数以&;为前缀，后接参数名（可以取列名）。
例4、SQL>INSERTINTODEPTVALUES（&;DEPTNO，&;DNAME，&;LOC）；
在执行该命令时，SQL*PLUS将提示用户输入每个参数。重复执行该命令可以接收不同的数据，快速地向表中插入多条记录。若参数对应的是日期型或字符型数据时，可以在参数上加引号，这样用户输入数据时就不用再输入引号了。
例5、
SQL>INSERTINTODEPTVALUES（&;DEPTNO，‘&;DNAME’‘&;LOC’；，）
（4）插入日期型数据。在插入日期型数据时，日期必须带单引号且以缺省的ORACLE的日期格式出现，即‘DD-MON-YY’格式。
例6、加入一名雇员。SQL>INSERTINTOEMP（EMPNO，ENAME，HIREDATE）VALUES（7963，‘STONE’‘07-APR-95’；，）
如果想输入当前的系统时间，可以使用SYSDATE。如果要插入一个非缺省格式的日期，可以使用TO-DATE函数进行转换。关于TO-DATE函数将在1.5函数一节中详细论述。2、数据的修改在ORACLE中，对数据的修改是使用UPDATE命令来实现的。UPDATE命令的格式如下：UPDATE<列表名>SET<列名1>=<表达式1>，<列名2>=<表达式2>……[WHERE<条件>]；UPDATE命令对由<表名>指定的基表进行修改。修改时，对表中满足条件的行将用<表达式>的值替换相应列的值。
例7、将Martin提升为经理。SQL>UPDATEEMPSETJOB=‘MANAGER’WHEREENAME=‘MARTIN’；
如果例7中不使用WHERE子句，则EMP表中的每一行都将被置为SET子句中的数值。UPDATE命令不但可以修改一行的某一列数据，还可以修改若干行的几列数据。
例8、将所有销售人员的工资增加20%，并转入部门40。SQL>UPDATEEMPSETDEPTNO=40，SAL=SAL*（1+0.02）WHEREJOB=‘SALESMAN’；
（1）在UPDATE中使用SELECT命令。在UPDATE命令中可以嵌入SELECT语句设置修改值。
例9、SQL>UPDATEEMPSET（SAL，COMM）=（SELECTSALARY，COMMFROMOLD_EMPWHEREDEPARTMENT=10）WHEREDEPTNO=10；
需要修改的列必须出现在括号内，并以逗号隔开。被设置的值通过括号内的SELECT语句获得，注意：应保证SET<列名>与SELECT子句中选择的各列一一对应，并且SELECT语句最多返回一行结果，若未返回任何行，则被修改的各行的有关列被置成NULL。（2）用NULL进行修改。修改数据时，也可以将基表中的某列设置为NULL，这里使用空值是用等号而不是“IS”。
例10、SQL>UPDATEEMPSETCOMM=NULL
WHEREJOB=‘SALESMAN’；
3、数据的删除对表中数据进行删除是使用DELETE命令实现的，DELETE命令格式如下：DELETEFROM<表名>[WHERE<条件>]；DELETE命令是从由<表名>指定的表中删除满足<条件>的行。WHERE<条件>是可选项，若只删除指定的几行，则必须使用WHERE子句，如果WHERE子句缺省，则删除表中的全部数据。
例11、Martin离开公司，应从公司中除名。SQL>DELETEFROMEMPWHEREENAME=‘MARTIN’；
注意：不能删除一行的部分信息，只能把该列改为空值。总之，我们可以使用INSERT，UPDATE和DELETE命令对数据库进行更新，但要注意：这些命令常带有WHERE子句，它仅影响你所选中的各行数据。这三个命令扩展ORACLE的功能，使它不仅能够查询，还允许直接对数据进行操作。1．2．3事务控制命令．．
1．3数据定义语言．
1．3．1ORALCE数据库实体1．3．2数据类型1．3．3创建基表1．3．4视图的创建和使用1．3．5索引1．3．6同义词1．3．7基表、视图的拷贝和删除
1．4SQL*PLUS的环境命令．
1．4．1环境设置命令．．SQL*PLUS的环境特征参数一般由系统自动设置，用户可以根据需要将环境参数设置成自己所需要的值，SET命令和SHOW命令主要就是完成这项功能的。SET命令将SQL*PLUS的环境特征参数设置为开关状态或者某个确定的值。使用SHOW命令将显示这些参数的值。1、SET命令使用SET命令可以改变SQL*PLUS环境特征参数的值。其命令格式是：SET<选项><值或开关状态>其中选项是指环境参数的名称，<值或开关状态>指该参数被设置成ON还是OFF，或是某个具体的值。
例1、设置自动提交状态。SQL>SETAUTOCOMMITON
下面给出几个常用的环境参数设置（其中有下划线者为系统的缺省值）：（1）SETAUTOCOMMIT{OFF|ON|IMMEDIATE}OFF——关闭自动提交功能。
ON或IMM——打开自动提交功能。（2）SETECHO{OFF|ON}ON——SQL*PLUS执行命令文件时，将命令本身显示在屏幕上。OFF——执行命令文件时，命令本身不显示在屏幕上。（3）SETFEEDBACK{OFF|ON}ON——查询结束时，给出查询结果的记录数信息。OFF——查询结束时，没有查询结果的记录数信息。（4）SETHEADING{OFF|ON}ON——各列的标题（包括文字和下划线）在结果报表上显示。OFF——各列的标题不在报表上显示。（5）SETLINESIZE{80|n}该项设置SQL*PLUS的行宽，即一行所能显示的最大字符数。当用SQL*PLUS命令制图报表标题为居中或右对齐时，系统在计算标题的合适位置时也要用到这个参数。该参灵敏的缺省值为80个字符，最大值为999。（6）SETPAGESIZE{14|n}该参数设置每页输出的行数，包括TTITLE（头标题）、BTITLE（底标题）、COLUMN（列标题）和空行。该项缺省值是每页14行。（7）SETPAUSE{OFF|ON|text}ON——在显示输出每一页后，等待用户按RETURN键继续显示。OFF——表示每页显示之间不停顿。Text——在显示每一页后停顿，等待用户按RETUNR键，并在屏幕下方显示text的提示信息。（8）SETBUFFER{buffer}设置{buffer}为当前的命令缓冲区。通常情况下，SQL缓冲区为当前命令缓冲区。
例2、建立名为A的缓冲区：SQL>SETBUFFERA
由于SQL缓冲区只能存放一条SQL命令，所以可以用SETBUFFER命令设置其他命令缓冲区，来存放多条SQL命令和SQL*PLUS命令。用户可以使用SQL*PLUS的行编辑——LIST，DEL，APPEND，CHANGE等命令对该缓冲区中的所有行操作，也可以用SAVE命令将该缓冲区中的内容保存到一个文件中，并可以用GET命令将文件中的内容取回到缓冲区。但该命令缓冲区不能直接执行SQL或SQL*PLUS命令，而是通过将其中的内容保存到文件中，再使用START命令来完成。不论是来自于命令行还是命令文件，只要执行一个SQL命令，命令缓冲区就被自动置回为SQL缓冲区。但其他缓冲区中的内容依然存在，可以再次使用SETBUFFER命令进入所需要的缓冲区。SET命令更详细的说明可以参见本套书的第二册《ORACLE7技术手册》的SQL*PLUS命令一章。SET命令对环境特征参数的改变只在本次会话期间内有效，即一旦退出SQL*PLUS，再进入时，你所设置的参数值全部恢复成系统的缺省值。如果需要经常设置某些参数，可以在该用户的当前目录下建立名为LOGIN.SQL的文件，并将参数设置命令写入其中。此外还可以将SQL命令、PL/SQL过程块或SQL*PLUS命令定入该文件中。每次用户启动SQL*PLUS，系统都会自动寻找用户当前路径下的LOGIN.SQL文件，并执行文件中的内容。若对已有的LOGIN.SQL文件进行了修改，原有的内容将会保存在LOGIN.OLD文件中。用户可以通过LOGIN.SQL文件来设置自己的SQL*PLUS环境。
2、SHOW命令SHOW命令可以显示SQL*PLUS的一个或全部特征参数的值。其命令格式是：SHOW{ALL|选项}
例3、SQL>SHOWALL
该命令显示SQL*PLUS全部环境参数的值。
例4、SQL>SHOWAUTOCOMMIT
该命令显示自动提交状态的设置情况。
例5、SQL>SHOWUSER
该命令将显示当前的用户标识，即当前的用户名。
例6、SQL>SHOWTTITLE
该命令显示当前报表使用的头标题的内容。关于SHOW命令的详细说明请参见第二部分的第一章。1．4．2用SQL*PLUS生成报表．．SQL*PLUS通常被认为是一种交互式的报表生成器，它使用SQL命令从ORACLE数据库中获取信息，使用SQL*PLUS进行设置，就能产生精炼的、有良好格式的报表，使你很容易地对题目、列标题、部分及总和进行控制，对数字和文字重新编排格式。
例1、需要生成下面一张简单报表：
MonMar12SAMPLEREPORTforHITECHCORPDEPARTMENT10sum20NAMECLARKKING
page1
sum30sum
SALARY$2,450.00$5,000.00$7,450.00FORD$3,000.00$3,000.00SCOTTJONES$2,975.00$8,975.00BLAKE$2,850.00$2,850.00STRICTLYCONFIDENTIAL
可以使用下面的命令来完成：SQL>COLUMNDEPTNOHEADINGDEPARTMENTSQL>COLUMNENAMEHEADINGNAMESQL>COLUMNSALHEADINGSALARYSQL>COLUMNSALFROMAT$99,999.00SQL>TTITLE‘SAMPLEREPORTfor|HITECHCORP’SQL>BTITLE‘STRICTLYCONFIDENTIAL’SQL>BREAKONDEPTNOSQL>COMPUTESUMOFSALONDEPTNOSQL>SELECTDEPTNO，ENAME，SAL
FROMEMPWHERESAL>2000ORDERBYDEPTNO；应注意一点，SQL*PLUS格式命令的效果只有通过运行SQL命令才能够看到。与制作报表有关的SQL*PLUS命令见表1.4。表1.4
命令TTITLEBTITLECOLUMNBREAKCOMPUTESETLINESIZESETPAGESIZESETNEWPAGESETHEADSEP定义为报表的每一页设置头标题为报表的每一页设置底标题设置列的标题和格式将报表中的数据分组显示并设置组间间隔计算分组数据的汇总值设置报表每行允许的最大字符数设置每页的最大行数设置页与页之间的空行数设置标题分隔符
1、设置标题：TTITLE和BTITLE从例1中能够看出，我们可以为报表的每一页设置头标题和底标题（也就是表头和表尾），它们是通过TTITLE和BTITLE命令来实现的。它们的命令格式如下：TTITLE[位置说明<表头>][OFF|ON]BTITLE[位置说明<表尾>][OFF|ON]关于标题的位置说明可以使用下列子句：
子句COLnSKIPnLEFTCENTERRIGHT举例COL72SKIP2LEFTCENTERRIGHT说明让标题信息从当前行左边的第n个位置开始显示打印n个空行，如果n未指明，打印一个空行；如果n为0，则不打印空行；如果n大于1，则为两行文字间加入n-1个空行标题信息靠左放置标题信息居中放置标题信息靠右放置
例2、SQL>TTITLECENTER‘ACMEWIDGETSALESDEPARTMENTPERSONNELREPORT’SQL>BTITLECENTER‘COMPANYCONFIDENTIAL’SQL>SELECTDEPTNO，ENAME，SALFROMEMPWHEREDEPTNO=30；
ACMEWIDGETSALESDEPARTMENTPERSONNELREPORTDEPTNOENAMESAL················································································30ALLEN1600WARD1250MARTIN1250BLAKE2850TURNER1500JAMES950COMPANYCONFIDENTIAL6rowsselected.
如果想将上表的标题变换成更清楚的形式，可以增加一些子句，并使用SETLINESIZE进行设置；
SQL>TTITLECENTER’ACMEWIDGET’SKIP1>CENTER===================SKIP1LEFT‘PERSONNELREPORT’>RIGHT‘SALESDEPARTMENT’SKIP2SQL>SETLINESIZE60SQL>/
报表显示如下：
ACMEWIDGET==================================PERSONNELREPORTSALESDEPARTMENTDEPTNOENAMESAL················································································30ALLENWARDMARTINBLAKETURNERJAMESCOMPANYCONFIDENTIAL6rowsselected.16001250125028501500950
关键字LEFT，RIGHT和CENTER确定了其后紧跟的内容在页上显示位置；SKIP表示在打印完该行后将打印多少空行；行未尾的破折号“—”表明标题命令未完，紧接下一行内容，在单引号内的正文将被如实打印。这个例子中最后一行数据与表尾之间没有定义空行，若需要在它们之间加入空行，可以使用SKIPn子句，如例3所示。
例3、SQL>BTITLESKIP1CENTER‘COMPANYCONFIDENTIAL’
另外还可以使用COL子句对表头和表尾的位置进行缩排。例如：COL1表COL15表示标题显示在第15列上，示标题显示在第一列的位置，即靠左侧放置。左侧留出14个空格。当需要将一行标题分成两行显示时，可以使用标题分隔符——竖线（|），SQL*PLUS可以根据标题中出现的分隔符“|”将标题的内容分多“|”行显示。在后面讲述的COLUMN命令的HEADING子句中也可以使用分隔符。
例4、要将标题显示成下列形式：MonMar12page1SAMPLEREPORTforHITECHCORP
则要使用下述命令：
SQL>TTITLE‘SAMPLEREPORTfor|HITECHCORP’
若某些键盘上没有“|”，可以使用SETHEADSEP命令，将标题分隔符设置成另一个字符，例如：
SQL>SETHEADSEP！
那么标题分隔符就补设置为一个感叹号（!）。应当注意，若选择一个可能会出现在标题或列标题中的字符为分隔符，将会导致意料不到的分割。如果标题文本超过500个字符，可以使用SQL*PLUS的DEFINE命令，将各行的文本内容保存在不同的变量中。例如：
SQL>DEPINLINE1=’Thisisthefirstline……’SQL>DEPINLINE2=’Thisisthesecondline……’SQL>DEPINLINE3=’Thisisthethirdline……’
在TTITLE和BTITLE中可以使用上面定义的变量.。SQL>TTITLECENTERLINE1SKIP1CENTERLINE2SKIP1CENTER
LINE3另外，还可以控制页号的显示位置和格式。SQL.PNO是一个用来存储当面号的变量，通过该变量可以对面号进行控制。
例5、SQL>TTITLELEFT’ACMEWIDGET’PAGE:’FORMAT999SQL.PNOSKIP2SQL>/
ACMEWIDGETS
PAGE：1
DEPTNOENAMEAL………………………………………………30ALLEN1600WARD1250MARTIN1250BLAKE2850TURNER1500JAMES950COMPANYCONFIDENTIAL6rowsselected.
如果不定义页号显示格式，SQL.PNO的宽度为9位。若希望显示标题定义的情况，可以使用不带子句的命令：TTITLEBTITLE若取消定义的标题在报表上显示，但不清除已定义的标题，可以使用命令TTITLEOFFBTITLEOFF若想恢复定义的标题在报表上显示，使用命令TTITLEONBTITLEON若想清除已经定义的标题，使用命令TTITLECLEARBTITLECLEAR2．设置报表尺寸每页报表中都包含表头、列标题、查询的结果和有尾信息。报表尺寸的设置对于这些内容的正确显示都是十分必要的。系统缺省的报表尺寸如下：每页报表表头空一行；每页输出内容为14行（包括表头和表尾之间的所有内容）；每行所能显示的字符数为80。可以通过SET命令改变上述设置。（1）SETNEWPAGE命令该命令设置每一页的表头与每一页开始位置之间的空行数，实际上就是页与页之间的行数，命令如下：SETNEWPAGE行数如果设置行数为0，系统将在每页的日期前产生一个顶部格式字符（通常是16进制的13）。大部分打印机立即响应这个字符并将打印头移至下一页的开始，即报表打印的起始位置。如果将NEWPAGE设置变大，SQL*PLUS输出的信息行就会减小。而每页的总行数不变。（2）SETPAGESIZE命令该命令设置每页的输出行数，包括表头、表尾、列标题和查询出的信息。对
于一般的打印纸，该值通常设置为66。命令如下：SETPAGESIZE行数SETPAGESIZE命令一般与SETNEWPAGE相关使用。（3）SETLINESIZE命令该命令控制出现在一行上的最大字符数。命令如下：SETLINESIZE字符数如果一行查询结果的总宽度超过了LINESZIE设置的行宽，SQL*PLUS将把多出的列折行输出。LINESIZE的大小还会影响表头、日期和页码的放置位置，因为表头的居中显示和居右显示要根据LINESIZE的值确定。
例6、重新设置新的报表尺寸：SQL>SETPAGESIZE66SQL>SETNEWPAGE0SQL>SETLINESIZE32
若要恢复系统缺少设置，则执行下列命令：
SQL>SETPAGESIZE14SQL>SETNEWPAGE1SQL>SETLINESIZE80
3．设置列COLUMN使用SQL*PLUS的COLUMN命令可以改变列标题及各列数据的显示格式。（1）设置列标题SQL*PLUS使用列名和列表达式名作为列标题的缺省形式，如果需要改变列标题，可以使用COLUMN的HEADING子句：COLUMN列名HEADING列标题
例7、将查询EMP表的结果生成报表，并为各列定义明确的标题：SQL>COLUMNDEPTNOHEADINGDepartmentSQL>COLUMNENAMEHEADINGEmployeeSQL>COLUMNSALHEADINGSalarySQL>COLUMNCOMMHEADINGCommissionSQL>SELECTDEPTNO，ENAME,SAL，COMMFROMEMPWHEREJOB=’SALESMAN’；
查询结果显示如下：
DepartmentEmployeeSalaryCommission……………………………………………………………………30ALLEN1600300WARD1250500MARTIN12501400TURNER150004rowsselected.
设置的这些列标题一直有效，直到他们被重新设置或用户退出SQL*PLUS.还可以使用SETUNDERLINE命令为列标题设置不同的形式的下划线，例如：
SQL>SETUNDERLINE=SQL>/
DepartmentEmployeeSalaryCommission……………………………………………………………………30ALLEN160030030WARD125050030MARTIN1250140030TURNER150004rowsselected.
要恢复原来的下划线，使用下列命令：
SQL>SETUNDERLINE‘－’
（2）设置列的格式所有的数据类型都有缺省的显示格式，如果需要数据指定的格式显示，可以使用COLUMN命令，命令格式是COLUMN列名FORMAT格式数值型数据使用9作为位数描述符，并可以加入逗号、$、尖括号、<、>、/或前导0等字符。
例8、显示工资值SAL，加入$和，，并使用0表示个位。SQL>COLUMNSALFORMAT$99,990SQL>/
DepartmentEmployeeSalaryCommission……………………………………………………………………30ALLEN$1,60030030WARD$1,25050030MARTIN$1,250140030TURNER$1,50004rowsselected.
字符型数据的缺省显示宽度为该字符数据定义的宽度，如果字符列的标题宽度超过字符数据宽度，则显示的宽度以列标题为准。其缺省设置宽度为每行80个LONG型显示的宽度由SETLONG命令设置，字符。DATE型一般缺省显示格式为DD－MON－YY，宽度为9个字符。如果DATE型数据没有使用TO－CHAR函数进行转换，则该数据缺省的格式由参数CHAR，VARCHAR2）VARCHAR〕LONG以及DATE，文件中的初始化参数确定。型数据使用COLUMN语句，以字母A作为格式描述符，来改变数据的显式格式，而且这些数据以左对齐方式显示，如果定义的数据显示格式宽度比列标题短，列标题将会被截断。
例9给ENAME列定义标题为EMPLOYEENAME，显示宽度为4个字符。SQL>COLUMNENAMEHEADING’Employee|Name’SQL>COLUMNENAMEFORMATA4SQL>/Empl
DepartmentNameSalaryCommission……………………………………………………………………30ALLE$1,600300N30WARD$1,25050030MART$1,2501400IN30TURN$1,5000ER4rowsselected.
由于ENAME以4个字符宽度显示，其列标题“Employee”被截断成“Empl”,而其数据宽度大于4个字符的，则要折行显示。如果需要将超出宽度的内容截断，可以使用SETWRAPOFF命令来设置，其缺省状态为ON，即不截断状态。（3）参照列的显示属性当用户想为更多的列定义相同的显示属性时，可以使用COLUMN的LIKE子句来简化命令语句。SQL*PLUS的LIKE子句可以为新的一列拷贝已经定义的
列显示属性。
例10、为COMM列定义与SAL列相同的显示格式，并为COMM列定义标题“Bonus”:SQL>COLUMNCOMMLIKESALHEADINGBonusSQL>/
EmplDepartmentNameSalaryBonus……………………………………………………………………30ALLE$1,600$300N30WARD$1,250$50030MART$1,250$1,400IN30TURN$1,5000ER4rowsselected.
（4）显示和重置列的显示属性若想显示某一列的显示属性，可以使用命令COLUMN列名若想显示所有列的显示属性，则使用命令COLUMN如果想将某列的显示属性重置成缺省形式，可以使用COLUMN命令的CLEAR子句：COLUMN列名CLEAR若希望将所有列的显示属性重新置成缺省的形式，则使用下列命令：SQL>CLEARCOLUMNScolumnscleared.另外，还可以使用COLUMN的OFF，ON子句对列的显示属性进行抑制或恢复。使用OFF子句COLUMN列名OFF表示SQL*PLUS将以缺省格式作为列的显示属性，但并不取消列属性，使用ON子句，则重新恢复列的显示属性：COLUMN列名ON4、BREAK和COMPUTE命令当在SELECT命令中使用ORDERBY子句时，会将数据按某一列的值排序，该列值相同的各行数据将会排列在一起输出，为了使输出的内容更为有用和清晰，可以使用BREAK和COMPUTE命令，将数据分成小的记录子集，计算各个子集的汇总值，并用空格将各个子集分隔开。例如，下面的查询中可以使用BREAK和COMPUTE命令：
SQL>SELECTDEPTNO，ENAME，SALFROMEMPWHERESAL<2500ORDERBYDEPTNO；
DEPTNOENAMESAL……………………………………………………10CLARK245010MILLER130020SMITH800
20ADAMS30ALLEN30JAMES30TURNER30WARD30MARTIN9rowsselected.
11001600950150012501250
为使查询结果更为有效，可以使用BREAK命令，DEPTNO作为BREAK以命令列。BREAK命令能够抑制DEPTNO列相同值的显示，不同的DEPTNO值仅显示一次，并在不同部门之间添加空行或各部门内容从新页开始显示。还可以将BREAK和COMPUTE命令联合使用，计算出各个部门雇员工资历的总和（或平均值、最大、最小值等），以及所有部门的汇总值。（4）BREAK命令使用BREAK命令的格式如下：BREAKONbreak列名注意：在BREAK命令中使用的列或列表达式应与ORDERBY子句中指定BREAK命令也不起作用。的列或列表达式一致，否则数据显示时将会杂乱无章，
例11、如果希望抑制部门号的重复显示，可做以下操作：SQL>BREAKONDEPTNOSQL>SELECTDEPTNO，ENAME，SALFROMEMPWHERESAL<2500ORDERBYDEPTNO；
DEPTNOENAMESAL……………………………………………………10CLARK2450MILLER130020SMITH800ADAMS110030ALLEN1600JAMES950TURNER1500WARD1250MARTIN12509rowsselected.
若希望在BREAK列值发生变化时，插入几个空行或换成新页，可以使用下列命令：BREAKONbreak列名SKIPn——插n个空行BREAKONbreak列名SKIPPAGE——换成新页
例12、还使用上面的例子，在不同部门之间插入一个空行。SQL>BREAKONDEPTNOSKIP1SQL>/
DEPTNOENAMESAL……………………………………………………10CLARK2450MILLER130020SMITH800
ADAMSALLENJAMESTURNERWARDMARTIN9rowsselected.30
11001600950150012501250
如果希望在每行信息之间插入空行或换成新页，则使用下列命令：BREAKONROWSKIPnBREAKONROWSKIPPAGE注意：SKIPPAGE间隔的行数是你所定义一页的行数。若ORDERBY子句中使用了多列，则也要在多列上使用BREAK命令，并设置不同的间隔大小。
例13、希望不同DEPTNO值之间空一页，不同JOB之间空一行。SQL>BREAKONDEPTNOSKIPPAGEONJOBSKIP1SQL>SELECDEPTNO,JOB,ENAME,SALFROMEMPWHERESAL<2500ORDERBYDEPTNO,JOB;
DEPTNOJOBENAMESAL……………………………………………………10CLERKMILLER1300MANAGERCLARK2450DEPTNOJOBENAMESAL……………………………………………………20CLERKSMITH800ADAMS1100DEPTNOJOBENAMESAL……………………………………………………30CLERKJAMES950SALESMANALLEN1600TURNER1500WARD1250MARTIN12509rowsselected.
若想显示BREAK命令的当前设置，可以输入下列命令：BREAK如果希望清除BREAK命令的设置，输入命令CLEARBREAKS（2）COMPUTE命令如果使用了BREAK命令，将报表中的信息分成若干组后，就可以使用COMPUTE命令分别计算各组的汇总值，将BREAK和COMPUTE命令联合使用如下：BREAKONbreak列名COMPUTEfunctionOF列名1列名2……ONbreak列名若在多个列上使用BREAK命令，则COMPUTE命令的ON关键字后应该
是用来分组的列名，即BREAK命令中使用的break列名。命令如下：BREAKONbreak列名1SKIPPAGEONbreak列名2SKIP1COMPUTEfunctionOF列名1列名2……ONbreak列名如果没有相应的BREAK命令，COMPUTE命令将得不到结果。COPUTE命令可以对数字型数据进行汇总。COMPUTE命令中使用的函数如下所示：
函数SUMMINMAXAVGSTDVARCOUNTNUMBER说明计算总和计算最小值计算最大值计算平均值计算标准偏差计算协方差计算非空值的总个数计算行数
使用上述COMUTE的函数，可以计算COMPUTE命令的OF和ON之间的所有列或列表达式的汇总值，计算结果分别显示在各个分组之后，计算的内容显示在第一列上。
例14、计算各部门雇员工资的汇总值。SQL>BREAKONDEPTNOSKIPPAGEONJOBSKIP1SQL>COMPUTESUMOFSALONDEPTNOSQL>SELECTDEPTNO，JOB，ENAME，SALFROMEMPWHERESAL<2500ORDERBYDEPTNO，JOB；
DEPTNOJOBENAMESAL……………………………………………………………………10CLERKMILLER1300MANAGERCLARK2450************……………………sum3750DEPTNOJOBENAMESAL……………………………………………………20CLERKSMITH800ADAMS1100************………………sum1900DEPTNOJOBENAMESAL……………………………………………………30CLERKJAMES950SALESMANALLEN1600TURNER1500WARD1250MARTIN1250************………………sum65509rowsselected.
应该注意的是，上例中如果定义了SAL列的显示格式，这个格式也将影响汇总值的显示。因此，定义SAL列格式时，应保证能满足汇总数据的宽度，以免影响汇总值的正确显示。
使用BREAK和COMPUTE命令，还可以计算报表中所有数据的汇总值：BREAKONREPORTCOMPUTEfumctionOF列名1列名2……ONREPORT
例15、列出所有销售员的工资及其汇总值。SQL>BREAKONREPORTSQL>COMPUTESUMOFSALONREPORTSQL>SELECTENAME，SALFROMEMPWHEREJOB=’SALESMAN’；
ENAMESAL——————————ALLEN1600WARD1250MARTIN1250TURNER1500——————————sum56004rowsselected.
另外还可以计算全部数据的汇总值及各个分组的汇总值：BREAKONbreak列名ONREPORTCOMPUTEfumctionOF列名ONbreak列名COMPUTEfumctionOF列名ONREPORT还可以计算多个相同类型列的汇总值。
例16、计算所有销售员的工资、资金汇总值。SQL>COMPUTESUMOFSALCOMONREPORTSQL>.SELECTENAME，SAL，COMMFROMEMPWHEREJOB=’SALESMAN’；
ENAMESALCOMM………………………………………………ALLEN1600300WARD1250500MARTIN12501400TURNER15000………………………………………………sum560022004rowsselected.
还可以计算某列的多个汇总值：COMPUTEfumctionfumction……OF列名ONbreak列名
例17、列出30号部门的雇员及他们工资平均值和总和。SQL>BREAKONDEPTNOSQL>COMPUTEAVGSUMOFSALONDEPTNOSQL>.SELECTDEPTNO，ENAME，SALFROMEMPWHEREDEPTNO=30ORDERBYDEPTNO，SAL；
DEPTNOENAMESAL…………………………………………30JAMES950WARD1250MARTIN1250TURNER1500ALLEN1600
BLAKE2850…………………………………………avg1566.67sum94006rowsselected.
如果需要查看已经定义的COMPUTE命令，可以输入SQL>COMPUTE我们还可以使用带COMPUTES子句的CLEAR清除COMPUTE的设置：SQL>CLEARCOMPUTESComputescleared（5）BREAK和COMPUTE小结从上面的论述可以看出，COMPUTE命令与BREAK命令密切相关。COMPUTE命令一般是计算由BREAKON具体指定的那一部分内容的汇总值。COMPUTE命令必须有相应的BREAK命令，且关键字ON后面的内容应该一致。反之，BREAK命令却可以单独使用，不必有COMPUTE命令对应，但使用这两个命令必须遵循以下原则：·每一个BREAKON必须要有一个相关的ORDERBY子句。·每一个COMPUTE必须有一个相关的BREAKON。正确地掌握这两条原则就可以顺利地使用这两条命令。另外，COLUMN、BREAK、COMPUTE命令只作用于在SELECT语句中出现的列，对其他内容不起作用。
例18、SQL>COLUMNJOBFORMATA10SQL>BREAKONJOBSKIPPAGESQL>SELECTDEPTNO，ENAME，SALFROMEMPWHERESAL<2500ORDERBYDEPTNO；
DEPTNOENAMESAL…………………………………………10MILLER130010CLARK245020SMITH80020ADAMS110030JAMES95030ALLEN160030TURNER150030WARD125030MARTIN12509rowsselected.
可以看出，作用于JOB列的COLUMN和BREAK命令均不起作用，其原因就是JOB列并没有在SELECT命令中使用。一旦定义了COMPUTE和BREAK命令，它们将一直有效，直到它们被重新定义或清除。用户退出SQL*PLUS，本次设置的这两条命令也将失败。5、特殊报表输出
我们在工作中经常还会遇到这样一种较为特殊的报表，报表标题根据获取的数据不同而变化，每页数据与本页的标题相关，也就是形成一个含有主条目和细目的的分类报表。主条目即所说的标题，细目即该标题指明范围里的各条记录。这里可以使用基表的某列作为标题，根据该列对数据进行分组，首先要使用COLUMN命令将作为标题的列值放到变量中；COLUM列名NEW-VALUE变量名该列必须是SELECT命令的ORDERBY子句使用的那一列。
例19我们希望制作一份报表，列出经理及其下属的雇员情况。各页报表要以经理号标题；SQL>COLUMNMGRNEW-VALUEMGRVARNOPRINT
首先将MGR列的内容放到变量MGRVAR中。NOPRINT关键字告诉SQL*PLUS，当打印SQL语句的执行结果不打印该列，即在查询结果中不显示MGR列。然后就可以使用变量MGRVAR设置报表的表头和分组间隔，完成报表打印。
SQL>TTITLELEFT’Manager：’MGRVARSKIP2SQL>BREAKONMGRSKIPPAGESQL>BTITLEOFFSQL>SELECTMGR，ENAME，SAL，DEPTNOFROMEMPWHEREMGRIN(7698,7839)ORDERBYMGR；
Manager：7698ENAMESALDEPTNO…………………………………………ALLEN160030WARD125030TURNER150030MARTIN125030JAMES95030Manager：7839ENAMESALDEPTNO…………………………………………JONES297520BLAKE285030CLARK2450108rowsselected.
若想将列值作为表尾标题，可以使用命令COLUMN列名OLD-VALUE变量名关键字OLD-VALUE的用法与NEW-VALUE相似，这里不再重述。6、查询结果的存储和打印使用SQL*PLUS的SPOOL命令可以将查询结果存储到文件中或直接送打印机输出。将内容送到文件中的命令是SPOOL文件名SQL*PLUS将该命令之后屏幕上出现的所有信息存贮到指定的文件中，其中包括命令执行中的提示信息和错误信息。除非文件名中有“.扩展名”，否则文件名的后缀由系统设置，后缀名随系统而定，一般后缀多为“.LST”“.LIS”。如果将查询结果直接送到打印机输出，可以用SQL>SPOOLOUT
SPOOL将一直存储或打印信息，直到执行下列命令：SQL>SPOOLOFF
1.5
SQL*PLUS中使用的函数
SQL函数是用于处理一个数据项并返回结果的运算。函数可以接受零个或多个参数，带不同参数的函数完成不同的运算，并产生一个或多个结果。函数的一般格式是函数名（参数）如果参数是多个，则为函数名（参数1，参数2，……参数N）其中：参数是用户提供的常量或者变量。如果调用函数的参数数据类型不同于函数所要求的数据类型，则ORACLE在执行这个函数前，将这个参数的数据类型隐式地转换为函数所要求的数据类型。如果调用的函数带有空值参数，则函数自动返回一个空值。通常根据函数值的数据类型可将函数划分为三类：·数值函数·字符串函数·日期函数如果根据函数返回的查询结果是一行或多行，可将函数分成两类：·单行函数·聚组函数单行函数和聚组函数的根本区别在于行数上的不同。单行函数返回查询的表或视图中的一行，它对表或视图进行水平方向（横向）计算，所产生的结果对于表的每行均有一个值。聚组函数返回一组查询的行，它对表或视图进行垂直方向（纵向）计算，所产生的结果是一个单值。单行函数1.5.1单行函数单行函数可以出现在SELECT命令的SELECT子句、WHERE子句、ORDERBY子句、STARTWITH子句和CONNECTBY子句中。单行函数具有以下特点：·基于单行返回结果。·要求零个或多个参数。·参数允许是其他单行函数产生的结果。·对查询返回的各行进行分别计算。·能够用于相同数据类型的变量、列名或表达式出现的地方。下面根据函数值的类型不同分别讨论。1、字符串函数、字符串函数接收字符型输入数据，返回字符型或数值型结果。表1.5列出了SQL*PLUS提供的字符串函数。表1.5函数功能注释
ASCII(char)
CHAR(n)
计算char的第一个字符的ASCII码值或EBCDIC码值计算ASCII码值或EBCDIC值是n的字符将char串中的每个单词的首字母变成大写，其余字母变为小写求char1中从m位置起char2第n次出现的位置
函数返回值取决于计算机系统采用的字符集参数n依赖于计算机系统采用的字符集，n的取值在0-127或0-254之间单词之间用数字、空格、逗号、顿号、冒号、分号、句号、!、@、#、$、等符号分隔m,n缺省值为1。n>0时，当表示从char1的首部起始n<0（从左向右）正向搜索；时，表示从char1的尾部起始（从右向左）反向搜索
INITCAP(char)
INSTR(char1,char2[,m[,n]])
计算字符串char的长度将char中所有的字母改写成小写LPAD(char1,n[,char2])人左侧用char2补齐char2省略时，用空格填充，char1至长度nn
UPPER(char)
对应的字符所代替将char中所有的字母改变成大写
例1使用字符型函数将10号部门雇员的名字以不同的大小写成形式显示。
SQL>SELECTEMPNO，UPPER(ENAME)，INITCAP(ENAME)FROMEMPWHEREDEPTNO=10；
EMPNOUPPER(ENAME)INITCAP(ENAME)…………………………………………………………7782CLARKClark7839KINGKing7934MILLERMiller3rowsselected.
2、数值函数、数值函数接受数值型输入数据，返回数值型的结果，表1.6列出了SQL*PLUS提供的数值函数。表1.6函数功能注释ABS（n）计算n的绝对值n许允是数值型常数、数值型列名、包含一个有效数值的文字串或包含一个有效数值的字符型列名CEIL(n)计算大于或等于n的最小整数学中的向上取整运算数FLOOR(n)计算大于或等于n的最大整数学中的向下取整运算数MOD(m,n)计算m除以n的余数n=0MOD（m,1）=0说明m是一个时，返回m整数POWER(m,n)计算m的n次方n要求为整数，否则出错ROUND(m,[,n])n>0时，m四舍五入到小n=0或n被省略表示对m进行将数点右取整。边n位；n<0j时，将m四舍五入到小数点左边n位SIGN(n)如果n>0，函数返回1；n=0,判断n的正负。数返回0；n<0,函数计算n的平方根函数返回-1，SQRT(n)计算n的平方根n<0时，函数返回NULLTRUNC(m,[,n])n>0时，将m小数点右边nn=0或n被省略表示对m进位后各行取整。位截断；n<0时，将m小数点左边n位后各位截断，并添加n个0
例2计算10号部门雇员的日工资（一个月为25个工作日）。
SQL>SELECTENAME,ROUND(SAL/25,0),ROUND(SAL/25,-1)FROMEMPWHEREDEPTNO=10;
ENAMEROUND（SAL25，0）ROUND（SAL/25，-1）……………………………………………………………………CLARK98100
KINGMILLR3rowsselected.
20052
20050
3、日期函数、除了MONTHS-BETWEEN之外，日期函数返回日期型结果。表1.7列出了SQL*PLUS提供的日期函数，其中d表示日期值。表1.7函数功能注释ADD-MONTHS（d,n）计算d加上n个月的日n求为整数。N>0时，返回d之期后n个月的日期。n<0时，返回d之前n个月的日期LAST-DAY(d)计算d所在月份最后用来确定给定月份中的天数一天的日期MONTHS-BETWEEN计算d1和d2之间相隔返回结果>0时，表示d1晚于d2；(d1,d2)的月数返回结果<0时，表示d1早于d2NEXT-DAY(d,s)计算晚于d的一个s的s要求是‘Sunday’,’Monday’,Tuesday’,Wednesday’,’Thursday’日期‘Friday’或‘Saturday’SYSDATE求系统当前日期和时间
例3显示本星期五要付20号部门哪些人工资。
SQL>SELECTENAME,SAL,NEXT-DAY(SYSDATE,’FRIDAY’)AS-OFFROMEMPWHEREDEPTNO=20;
ENAMESALAS-OF……………………………………………SMITH180021-Nov-95JONES297521-Nov-95SCOTT300021-Nov-95ADAMS110021-Nov-95FORD300021-Nov-955rowsselected.
4、转换函数、转换函数是将一种数据类型的值转换成另一种数据类型的值。（1）TO=CHAR(date,’format’)TO-CHAR函数将一个日期值按‘format’指定的格式转换为字符串。如果不使用TO_CHAR函数，日期值在ORACLE中的缺省格式是‘DD-MON-YY’。（2）TO_DATE（char,’format’）TO_DATE函数将不同格式的字符串转换成ORACLE日期型格式，日期数据在系统内部精确到秒。在上述两个转换函数中均要给出格式参数’fromat’。关于格式定义有以下几点说明：·定义的格式要用单引号括起来。·每个格式均由一些基本的成份组成。·格式中包括的大写、小写和缩写形式均有特定的意义。
格式说明如表1.8所示。表1.8分类日期定义格式月份定义格式年份格式格式DdDyDayDdspthMmMonMonthYyyyyy说明用数字表示几号星期几的缩写星期几全拼形式序数词表示几号用数字表示月份月份的英文缩写形式月份的英文全拼形式两个数字表示年份四个数字表示年份例子12friFridayTwelfth03marmarch951995
（指12号）（星期五缩写）（星期五全拼）（12号）（数字3月）（三月份缩写）（三月份全拼）（即1995年）（即1995年）
可以根据上面给出的格式来定义不同形式的日期，注意字母大小字的意义不同，如：day,DAY，Day转换的结果将是不同的大小写内容。举例说明如下：
格式说明‘Mondd,yyyy’‘MONdd,yyyy’‘DayMONTHdd’‘Dy,ddspth’‘Monddhh:miam’例4查询10号部门雇员的信息。
SQL>SELECTENAME，TO_CHAR（HIREDATE,‘DyMondd,yyyy’）HIREDFROMEMPWHEREDEPTNO=10;ENAMEHIRED
内容Mar12,1995MAR12,1995ThursdayMARCH12Thu,twelfthMar1211:00am
从上面的例子可以看出，定义的格式不同，可以将内容转换成不同的形式。
CLARKTueJun09,1981KINGTueNov19,1981MILLERSatJan23,19823rowsselected.
使用TO_CHAR函数将日期型数据转换成所需的字符串形式。
例5SQL>INSERTINTOEMP(EMPNO,ENAME,HIREDATE)VALUES(7999,’ASMS’,TO_DATE(070395083000’,’MMDDYYHHMISS));
使用TO_DATE函数将字符串转换成日期值。5、空值函数NVL、空值函数提供了对空值正确操作的办法。因为NULL与任何数值运算均为空值，若使用了NVL函数，则可以改变这种情况，其格式为：NVL（参数1，参数2）参数1为列名、变量或表达式等。如果参数1不是空值，NVL函数返回其值；若参数1为空值，NVL函数返回参数2的值。参数1和参数2允许为任何数据类型，函数的返回值要求具有与参数1相同的数据类型。
例6计算30号部门雇员每人每月的总收入是多少。
SQL>SELECTENAME,COMM+SAL,NVL（COMM,0）+SALFROMEMPWHEREDEPTNO=30;
ENAMECOMM+SALNVL（COMM，0）+SAL……………………………………………ALLEN19001900WARD17501750MARTIN26502650BLAKE2850TURNER15001500JAMES9506rowsselected.
由于Blake和James的COMM值为NULL，所以COMM+SAL值也为NULL，使用了空值函数后，结果将不为空。6、其他函数、ORACLE支持的函数中，有的可以支持多种数据类型的值或不要求用户提供参数。（1）GREATEST（参数1[，参数2]……）该函数计算参数1，参数2……中的最大值，参数可以为字符型、日期型或数值型等类型，计算时第一个参数之后的所有参数均被转换到比较之前第一个参数的数据类型。（2）LEAST（参数1[，参数2]……）该函数的使用方法与GREATEST相同，只是它的功能是求参数列表中的最小值。
例7查询哪些雇员的雇员号比经理号小。
SQL>SELECTENAME，EMPNO，MGR，LEAST（EMPNO，MGR）LOWNUMFROMEMPWHEREEMPNO ENAMEEMPNOMGRLOWNUM……………………………………………………………SMITH736979027369ALLEN749976987499WARD752176987521JONES756678397566MARTIN765476987654BLAKE769878397698CLARK7782783977827rowsselected.
(3)DECODE(e，s1，t1[，s2，t2]…[，def])该函数的功能是，e等于s1，若函数返回t1；e等于s2，若函数返回t2；……；否则，函数返回def。表达示e允许是任何数据类型，但要求被比较的各个s具有相同的数据类型。def缺省时，表示缺省值是NULL。
例8、根据输入的值将雇员信息进行排序。SQL>SELECT*FROMEMPORDERBYDECODE（&;ORDERBY，1，ENAME，2，SAL，ENAME）；
Entervaluefororderby：2Old3：ORDERBYDECODE（&;ORDERBY，1，ENAME，2，SAL，ENAME）New3：ORDERBYDECODE（2，1，ENAME，2，SAL，ENAME）
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO……………………………………………………………………………………………………………………7876ADAMSCLERK778812-Jan-951100207521WARDSALESMAN769822-Feb-931250500307654MARTINSALESMAN769828-Feb-9312501400307934MILLERCLERK778223-Jan-941300107844TURNERSALESMAN769808-Sep-9315000307499ALLENSALESMAN769820-Feb-931600300307782CLARKMANAGER783909-Jun-932450107698BLAKEMANAGER783901-May-932850307566JONESMANAGER783902-Apr-932975207788SCOTTANALYST756609-Dec-943000207902FORDANALYST756603-Dec-933000207839KINGPRESIDENT17-Nov-933000107369SMITHCLERK790217-Dec-92800207900JAMESCLERK769803-Dec-939503014rowsselected.
在这个例子中，给参数赋值2，按照DECODE函数值进行排序，即结果应当注意：在这里SAL列被转换成字符型，因为它要与ENAME按SAL列排序。列的类型保持一致，结果按数字型数据的排序转换成字符型数据的排序，因而SMITH，JAMES被排在了最后。（4）USER函数返回当前ORACLE用户的用户名，该函数的调用不需要用户提供参数。1.5.2聚组函数聚组函数一般用来从一个或多个表中的多行返回汇总信息，它具有以下特点：?基于一组行返回一个汇总信息。?调用时仅要求一个参数。?除COUNT（*）QH，计算过程中忽略空值NULL。?能够用于相同数据类型的变量、列名或表达式出现的位置。?适用于数值型数据和某些字符型、日期型数据。通过聚组函数可以对满足条件的数据进行统计、计数等运算。1、聚组函数、SQL语言提供以下几个聚组函数（e为NUMBER类型表达式）：（1）AVG（e）——计算一组行中e值的平均值（2）COUNT（e）——计算一组行中e值为非空值的行数（3）COUNT（*）——计算表中的行数（包括重复值和空值）（4）MAX（e）——计算一组行中e值的最大值（5）MIN（e）——计算一组行中e值的最小值
（6）STDDEV（e）——计算一组行中e值的标准差（7）SUM（e）——计算一组行中e值的总和（8）VARIANCE（e）——计算一组行中e值的方差
例1计算所有雇员的奖金总和。
SQL>SELECTSUM（COMM）FROMEMP；SUM（COMM）22001rowselected.
从这个例子可以看出，聚组函数与单行函数的不同之处是：聚组函数是返回一个汇总信息而不是一条条单行信息，而且它对空值的处理与单行函数也完全不同。聚组函数忽略NULL值，尽管有NULL值也计算一个结果，而不是用空值进行操作。所有的聚组函数都适用于数值型数据，只有MIN，MAX，COUNT可用于任何数据类型。
例2函数MIN适用于字符型数据类型。
SQL>SELECTMIN（ENAME）FROMEMP；MIN（ENAME）ADAMS1rowselected.
例3
聚组函数MIN适用于日期型数据。
SQL>SELECTMIN(HIREDATE)FROMEMP；MIN(HIREDATE)17-DEC-911rowselected.
例4聚组函数MIN适用于数值型数据。
SQL>SELECTMIN（SAL）FROMEMP；MIN（SAL）800rowselected.
1
聚组函数MAX和COUNT的用法与MIN相同。聚组函数, 在调用时有两种选项：ALL和DISTINCT。ALL表示对一组行中所有表达式的值进行计算，这里包括了表达式有重复值的情况，在上面讨论的例子都是采用这种选项。另一个选项DISTINCT表示仅对一组行中不同的表达式的值进行计算，即重复值不计算在内。缺省的选项为ALL，可以忽略不写，若选择DISTINCT选项，则采用下列格式：聚组函数名（DISTINCTe）
例5计算多少人有工作，则只要计算JOB列不为空的记录数即可。
SQL>SELECTCOUNT(JOB)FROMEMP;COUNT(JOB)14rowselected.
1
这里查出的是有工作的人数。其中有的雇员工种相同，即JOB列有重复值的也计算在内了。如果希望查找有多少种不同的工种，则要筛掉JOB列的重复值。
例6
SQL>SELECTCOUNT(DISTINCTJOB)FROMEMP；COUNT（DISTINCTJOB）51rowselected.
从以上两个例子的对比中，读者就能理解DISTINCT的含义了。2、GROUPBY子句、当我们在SELECT子句中使用聚组函数时，又希望同时获得单行函数。例如，希望分别计算出各个部门的工资总和。根据需要查询的内容，写出下列命令：
SQL>SELECTDEPTNO，SUM（SAL）FROMEMP；
这个语句的意图是显示出不同的部门号及该部门的工资总和。但屏幕上将出现下列错误信息：
…ERROR…NOTASINGLEGROUPSETFUNCTION
这是因为单行函数和聚组函数不能混在一起使用，它们将对同一组数据产生不同个数的结果，除非利用SELECT语句的GROUPBY子句查询结果进行分组。如果未使用GROUPBY子句，则ORACLE将为查到的基表或视图中的所有行提供聚组函数，即把整个查询结果视为一组。如果SELECT语句中使用GROUPBY子句，则ORACLE把查询到的基表或视图中的行划分成多个组，并为每组返回一个结果。
例7计算第个部门的平均工资和工资总和。
SQL>SELECTDEPTNO,SUM(SAL),AVG(SAL)FROMEMPGROUPBYDEPTNO;DEPTNOSUM(SAL)AVG(SAL)2916.672175.001566.67
108750.002010875.00309400.003rowsselected.
注意，使用GROUPBY子句后，数据按部门分成三组，每组只返回一条记录。从例子中可以看出，每组至少有一列的列值相同，即在每组中，GROUPBY子句后的那一列必定是相同的。有时还要将各个分组再进行细划，分出不同的小组，那么就可以在GROUPBY子句中使用多个分组标准来进行。
例8、查询每个部门的每个工种的雇员数。
SQL>SELECTDEPTNO，JOB，COUNT（*）FROMEMPGROUPBYDEPTNO，JOB；
DEPTNOJOBCOUNT(*)…………………………………………………10CLERK110MANAGER110PRESIDENT120ANALYST2
20CLERK20MANAGER30CLERK30MANAGER30SALESMAN9rowsselected.
2114
在这个例子中，首先按部门将雇员分成若干组，在同一部门内的雇员再按他们的工种进一步分组，计算出小组的分组值。应当注意，在包含GROUPBY子句的查询中，SELECT子句的列表中的所个体值（除聚组函数外）必须是GROUPBY子句中的表达式或者常量，否则出错。
例如：SQL>SELECTENAME，DEPTNO，SUM（SAL）FROMEMPGROUPBYDEPTNO；SELECTENAME，DEPTNO，SUM（SAL）*ERRORatline1；ORA—00979：notaGROUPBYexpression
在SELECT子句中有ENAME和DEPTNO两个个体值列，而在GROUPBY子句中只有一个DEPTNO。当按部门分组后每组产生一条记录，但每个部门却还有多个雇员的ENAME，所以该命令的执行必定是错误的，应该保证SELECT子句给出的所有列名或表达式在每一个分组中必须具有唯一性。3、HAVING子句、如果需要对各个组返回的结果进行筛选，查询满足一定条件的分组值，可以使用HAVING子句。HAVING子句的作用与WHERE子句相似，都是给出查询条件。所不同的是，WHERE是检查每条记录是否满足条件，而HAVING子句是检查分组后，各组是否满足条件。从这里可以看出，WHERE子句的条件是针对SELECT子句的，而HAVING子句的条件是针对GROUPBY子句的，没有GROUPBY子句就不能使用HAVING子句。
例9、哪些部门的工资总和超过了$9000？
SQL>SELECTDEPTNO，SUM（SAL）FROMEMPGROUPBYDEPTNOHAVINGSUM（SAL）>9000；
DEPTNOSUM(SAL)………………………………20108753094002rowsselected.
执行时，先按DEPTNO分组，分组后并不是每一组都是所需的信息，还需要进一步检查每一组的工资总和是否大于$900。如果大于$900，则是所需的信息，否则不是所需的信息，在这里检查每组是否满足条件使用的就是HAVING子句。如果还希望对各组的结果进行排序，可以在HAVING子句后增加ORDERBY子句，但这里使用的ORDERBY子句必须要用聚组函数或GROUPBY子句中的列。
例10对部门工资总和超过$9000的部门按工资总额由小到大顺序排列。
SQL>SELECTDEPTNO，SUM（SAL）FROMEMPGROUPBYDEPTNOHAVINGSUM（SAL）>9000ORDERBYSUM（SAL）；
DEPTNOSUM(SAL)………………………………30940020108752rowsselected.
一般来说，聚组函数可以出现SELECT子句的列表中，HAVING子句和ORDERBY子句中。在使用时，要注意它们与GROUPBY子句的关系，以免发生错误。4、SELECT命令小结、在SELECT命令中，WHERE子句和HAVING子句还可以一起使用。现将SELECT命令小结如下：SELECT列名1，列名2，…列名nFROM表名1，表名2，…表名n[WHERE列选择条件为真]GROUPBY列名1，列名2，…列名n[HAVING分组条件为真][ORDERBY列名1，列名2，…列名n]；SELECT子句和FROM子句是必选的，FROM子句后也可以是视图名，其他子句都是可选的。HAVING子句是在有GROUPBY子句的前提下才可以使用。系统在执行这个完整的查询语句时，首先根据WHERE子句选择满足条件的行；然后根据GROUPBY对选中的行进行分组，并计算每个组的聚组函数值；接着根据HAVING子句选择某些组作出结果；最后根据ORDERBY子句里的聚组函数值对各组数据进行排序显示。下面给出一个包含上述所有子句的例子，供读者参考。
例11除去职员，哪些部门的工资总和超过了$8000(不包括奖金，并按工资升序显示)？
SQL>SELECTDEPTNO，SUM（SAL）FROMEMPWHEREJOB！=‘CLERK’GROUPBYDEPTNOHAVINGSUM（SAL）>8000ORDERBYSUM（SAL）；
DEPTNOSUM(SAL)………………………………3084502089752rowsselected.
1.6高级查询
1.6.1表的连接
在数据库应用中，经常要同时涉及两个或两个以上的表，需要通过两个以上的表才能构造出所期望的结果，这就要使用连接操作。若被处理的诸表的列之间毫无联系，则利用“笛卡尔积”将各表中的各行组合起来。两个表的“笛卡尔积”形式如下：SELECT列名11，列名12，…，列名1m，列名21，…，列名2n表名1，表名2FROM其中：列名11，列名12，…，列名1m（m>0）是表名1中的列，而列名21，列名22，…，列名2n（n>0）是表名2中的列。进行“笛卡尔积”实际上就是一种无条件连接，这种操作会生成大量的行，其结果却没有多大意义。因而在相关的各表间进行操作时往往要加上限制条件，再进行连接运算。连接运算就是利用各表的相关列之间的比较关系控制各表中的行的组合，根据比较关系的不同，连接运算分为等值连接和非等值连接。另外根据连接对象的不同又有两种特殊的连接：自连接和外连接。1、等值连接、等值连接要求参与联接运算的两个表在公共列上具有相同的值。SELECT语句可以将两个表进行连接操作。连接时，将两个表中的所有记录在有关列上进行比较，检查它们是否满足条件。其命令如下：SELECT…FROM表名1，表名2WHERE表名1.列名2=表名2.列名2；其中：给列名加前缀——表名，是为了避免被连接表中各个被连接列同名时产生二义性。两个基表进行等值连接的过程是：首先从第一个表中取出第一条记录，然后从头到尾扫描另一个表的全部记录，分别检查每条记录是否在连接属性上与第一个表的第一条记录相等。如果相等，则将这两个记录连接，生成新表的一条记录。当处理完第一个表的第一条记录后，再取第二条记录，扫描另一个表的全部记录。如此重复直至处理完第一个表的全部记录。如果连接时还有其他限制条件，则将满足条件的记录进行上述操作。
例1、查看工资高于$2500的雇员及所在部门的情况。
SQL>SELECTENAME，SAL，EMP.DEPTNO，LOC，DNAMEFROMEMP，DEPTWHEREEMP.DEPTNO=DEPT.DEPTNOANDSAL>2500；ENAMESALDEPTNO2030201020LOCDALLASCHICAGODALLASNEWYORKDALLASDNAMERESEARCHSALESRESEARCHACCOUNTINGRESEARCH
JONES2975BLAKE2850SCOTT3000KING5000FORD30005rowsselected.
其中：SELECT子句中的DEPTNO列加了前缀EMP.，这是为了防止不同基表的同名列产生二义性，因为DEPT表中也有DEPTNO列。在连接条件中使用的列是几个被连接表的公共列，它们不要求具有相同的名字。但因为要进行比较操作，所以两个列必须具有相同的定义域，即数据类型
和宽度相同，这是作为连接条件所必须具备的。不仅可以对两个表进行连接操作，还可以对两个以上的表进行连接操作。连接时应遵循下面两条规律：·连接条件数恰好比被连接的表数少1；·一个被联接表的主关键字是多列组成时，则对该主关键字中的每一列均要有一个连联接条件。2、非等值连接非等值连接就是指连接条件中不使用“=”运算符的连接运算。非等值连接能够使用的比较运算符包括=、！=、<、>、<=、>=、BETWEEN…AND和LIKE等。
例2、下面给出一张新的基表：SALGRADE，存放着工资等级的信息。
SALGRADEGRADE12345SQL>SELECTFROMWHEREANDENAMETableLOSAL7001201140120013001HISAL12001400200030009999
需要根据该表查找哪些雇员的工资属于第三级别：
ENAME，SALEMP，SALGRADEGRADE=3SALBETWEENLOSALSAL
ANDHISAL；
ALLEN1600TURNER15002rowsselected.
其中：做为连接条件的比较运算符不是“=”，而是BETWEEN…AND。3、自连接连接操作不仅可以作用在几个不同的基表上，而且，同一个表可以进行自身连接，将同一个表的不同行连接起来。自连接可以把一个表看成有两个副本，即两个相同的表，然后再对这两个表在相关列上进行连接，其连接过程与多表连接完全相同。表自连接时，需要不同的表别名来对同一个表的两个副本加以区分。表自连接的一般命令格式如下：SELECT…FROM表名表别名1，表名表别名2WHERE表别名1.列名1=表别名2.列名2；其中：列名1和列名2标识自连接表中的两个不同列，这两列要求有相同的数据类型和宽度。
例3、指出每个雇员的经理的名字。
SQL>SELECTWORDER.ENAME，MANAGER.ENAMEMANAGERFROMEMPWORKER，EMPMANAGERWHEREWORDER.MGR=MANAGER.EMPNO；ENAMEMANAGERSCOTTJONES
FORDJONESALLENBLAKEWARDBLAKEMARTINBLAKETURNERBLAKEJAMESBLAKEMILLERCLARKADAMSSCOTTJONESKINGBLAKEKINGCLARKKINGSMITHFORD13rowsselected.
这个例子中为EMP分别起了WORDER和MANAGER别名，实际上在自连接时，只要为一个基表起别名以区别连接条件中的列名即可，不必非起两个别名不可。4、外连接在使用前面几种连接时，如果连接条件满足，系统则取有关的数据；如果条件不满足，系统则不取相应的数据。而外连接则不同，它不仅返回两个或两个以上的表中能够直接匹配的行，还返回一个表中无法从其他表中找到直接匹配的行。其命令格式如下：SELECT…FROM表名1，表名2WHERE{表名1.列名1=表名2.列名2(+)|(+)表名1.列名1=表名2.列名2}；其中：（+）是外连接运算符，按它在“=”符号的左边或右边分为左外连接和右外连接，其运算结果是不同的。进行外连接时，若不带（+）运算符的表中的一行不直接匹配于事（+）运算符的表中的任何行，则前者中的行与后者中的一个虚拟空行相匹配，经过连接后被返回。
例4SQL>SELECTENAME,DEPT.DEPTNO，LOCFROMEMP，DEPTWHEREEMP.DEPTNO(+)=DEPT.DEPTNO；ENAMEDEPTNOLOCCLARKMILLERKINGSMITHSCOTTJONESADAMSFORDALLENBLAKETURNERJAMESMARTIN15rows1010102020202020303030303040selected.NEWYORKNEWYORKNEWYORKDALLASDALLASDALLASDALLASDALLASCHICAGOCHICAGOCHICAGOCHICAGOCHICAGOBOSTON
如果DEPT.DEPTNO中有的数值，在EMP.DEPTNO中没有，如DEPT.DEPTNO=40，在做外连接时，结果中会为EMP表的列产生空值。如果外连接运算符（+）放在另一侧，结果将会不同。外连接还允许（+）运算符同时出现于连接条件中“=”符号的两侧。于是，各个被连接表中无法找到匹配的全部行，经过以空值NULL填补相应部分后，
均作为外连接的结果被返回。此外，在一个SELECT语句的WHERE子句中，出现比较运算符NOTIN是一个最慢的逻辑测试，因为经在SELECT子查询中要读遍整个表。
例5、使用NOTIN完成下列查询：
SQL>SELECTFROMWHEREDEPT.DEPTNO，DNAME，LOCDEPTDEPT.DEPTNONOTIN(SELECTDEPTNOFROMEMPWHEREENAME=’CLERK’)；DNAMELOCNEWYORKCHICAGOBOSTON
DEPTNO1030403rows
ACCOUNTINGSALESOPERATIONSselected.
为加快查询，可以利用外连接NOTIN运算符，替换方法如下：·NOTIN形式：SELECT表名1.列名1······FROM表名1WHERE表名1.列名1NOTIN（SELECT列名2FROM表名2WHERE列名3=表达式）；·使用外连接的形式：SELECT表名1.列名1······FROM表名1，表名2WHERE表名1.列名1=表名2.列名2（+）AND表名2.列名2ISNULLAND表名2.列名3（+）=表达式；上面的例子可改用外连接来实现：
SQL>SELECTDEPT.DEPTNO，DNAME，LOCFROMDEPT，EMPWHEREEMP.DEPTNO=EMP.DEPTNO（+）ANDEMP.DEPTNOISNULLANDEMP.ENAME(+)=’CLERK’；DEPTNODNAMELOC103040ACCOUNTINGSALESOPERATIONSNEWYORKCHICAGOBOSTON
因为对两个表进行外连接，就包括了DEPT.DEPTNO=40的数据，EMP.DEPTNO为NULL仅产生那些不出现在EMP表中，并且EMP.ENAME（+）=‘CLERK’增加了名字不叫‘CLERK’但在EMP表中的职员所在的部门。使用外连接来代替NOTIN将会提高查询的速度。1.6.2子查询子查询也称为嵌套查询，它是指允许一条SELECT查询语句作为另一条SQL语句中的一部分。我们通常称被嵌套的SELECT语句为子查询，共外层的SELECT语句为主查询。子查询的作用是，首先检索出一个或多个表的值，其结果并不被
显示，而是传递给其外层语句，作为该语句的查询条件来使用。由于子查询还可以在它的语句中再嵌入子查询，因此子查询可以多层嵌套。子查询的使用一般允许出现以下几种情况：·子查询的结果返回一行或多行数据；·子查询的结果返回一列或多列数据；·允许子查询中使用分组和聚组函数；·子查询可以作为AND，OR等关系运算中的一部分；·子查询允许嵌套；·子查询可以查询多个基表；·子查询中可以使用与主查询相同的或不同的基表。·子查询适用于以下命令中：在SELECT语句的WHERE子句中提供进行比较的值。格式是
SELECT·····FROM表名WHERE列名或列表达式
比较运算符（SELECT列名FROM表名WHERE条件）；
在UPDATE语句中确定赋予表的当前行的一列或多列的值，还可以为WHERE子句中提供进行比较的值，格式是
UPDATE表名SET（列名，列名，·）=（SELECT列名，列名·····FROM表名WHERE条件）；WHERE列名或列表达式比较运算符（SELECT列名FROM表名WHERE条件）；
在DELETE语句的WHERE子句中提供进行比较的值，其格式是
DELETEWHEREFROM表名列名或列表达式比较运算符（SELECT列名FROM表名WHERE条件）；
在INSERT语句中确定往表中加载的行，格式是
INSERTINTO（列名，列名，·）··SELECT（列名，列名，·）··FROM表名WHERE列名或列表达式比较运算符（SELECT列名FROM表名WHERE条件）；
在CREATETABLE命令中确定往表中加载的行，格式是
CREATETABLE新表名ASSELECT列名，列名，···FROM表名WHERE列名或列表达式比较运算符（SELECT列名FROM表名WHERE条件）；
1、单行值子查询当子查询返回单一行时，所有用于单值测试的逻辑运算符都可以使用。
例1、查询工资高于7698号雇员，并且工种与他相同的雇员情况。
SQL>SELECTENAME，SAL，JOBFROMEMPWHERESAL>(SELECTSALFROMEMPWHEREEMPNO=7698)ANDJOB=(SELECTJOBFROMEMPWHEREEMPNO=7698)；DNAMESALJOBJONES29751rowselected.MANAGER
系统在执行时，首先执行子查询，获取7698号雇员的工资$2850，工种为MANAGER，然后将子查询返回的结果带到上层查询语句的WHERE子句中，通过等号（=）和大于号（>）与子查询联系起来。在UPDATE语句中也可以使用子查询。
例2、将所有与7698号雇员同工种的职工的部门号改成7698号雇员所在的部门。
SQL>UPDATEEMPSETDEPTNO=(SELECTDEPTNOFROMEMPWHEREEMPNO=7698)WHEREJOB=(SELECTJOBFROMEMPWHEREEMPNO=7698)；
在该例中，子查询不但可以作为查询的条件，还可以用来提供修改值。在子查询中可以使用聚组函数。
例3、查询工资高于或等于30号部门工资最高额的雇员。
SQL>SELECTENAME，SAL，DEPTNOFROMEMPWHERESAL>=（SELECTMAX（SAL）FROMEMPWHEREDEPTNO=30）；ENAMESALDEPTNOJONES2975LBAKE2850SCOTT3000KING5000FORD30005rowsselected.2030201020
在子查询中使用聚组函数，得到30号部门工资的最高额为$2850，将其结果带到主查询中实现查询。2、多行值子查询如果子查询返回的不是单一行而是一组行时，就是多行值子查询。这种子查询必须用下面的多值比较运算行与其主查询相联系：?[NOT]IN?[NOT]ANY?[NOT]ALL?[NOT]EXISTS上述谓词均可以加NOT表示相反。如果子查询返回多行值却使用单值比较运算符，执行将会出错。
（1）谓词ININ表示属于集合或是某集合中的成员的关系，NOTIN则表示不属于集合或不是集合中的成员。
例4查询工资在$1000到$5000之间的雇员所有部门的所有人员的信息。
SQL>SELECTENAME,SALFROMEMPWHEREDEPTNOIN(SELECTDEPTNO)FROMEMPWHERESALBETWEEN1000AND5000);ENAMESALSMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE2850CLARR2450SCOTT3000RING5000TURNER1500ADAMS1100JAMES950FORD3000MILLER130014rowsselected.
执行时，首先执行嵌入的子查询，EMP表中找出所有工资在$1000到$5000从之间的雇员所在的部门号，执行的结果是一组值（10，20和30）。然后再从EMP表中取出每行数据，分别检查它们的DEPTNO值是否属于这一组值中的一个，即是否等于其中的一个值（等于10，20或30均可）。如果等于则满足条件，被查询出来。（2）ANYANY就是将一个值与子查询返回结果中的一个值进行比较。在ANY前可以使用=、！=、<、>、<=或>=等比较运算符。
例5还是上面的例子使用ANY来实现。
SQL>SELECTENAME,SALFROMEMPWHEREDEPTNO=ANY(SELECTDEPTNOFROMEMPWHERESALBETWEEN1000AND5000);
在这个查询中，ANY后面是一个子查询，这个子查询的结果是{10，20，30}，那么这个查询语句就变成了SELECTENAME，SALFROMEMPWHEREDEPTNO=ANY（10，20，30）；
WHERE子名的条件表示：只要DEPTNO等于集合{10，30}中的任何一个值，20，则条件满足。实际上“=ANY”的作用相当于IN。在ANY前面不仅可以使用等号，也可以使用！=、>、<、>=、<=等运算符。
例6查询工作不为销售员（SALESMAN），并且工资小于其中任一个销售员的雇员信息。
SQL>SELECTEMPNO,ENAME,SALFROMEMPWHERESAL ANDJOB!=’SALESMAN’;EMPNOENAME7369SMITH7876ADAMS7900JAMES7934MILLER4rowsselected.
SAL80011009501300
在这个查询中使用了“、<=或>=等比较运算符。
例7查找工资高于20号部门所有雇员的人员信息。SQL>SELECTEMPNO,ENAME,SAL
FROMEMPWHERESSAL>ALL(SELECTSALFROMEMPWHEREDEPTNO=20);EMPNOENAMESAL7839KING50001rowselected.
在这个查询中，执行ALL后的子查询等到20号部门雇员的工资，实际上该命令就是查找工资比子查询结果中的最大值高的雇员。
例8查询与销售员不在同一个部门工作的雇员信息。
SQL>SELECTEMPNO,ENAME,DEPTNOFROMEMPWHEREDEPTNO!=ALL(SELECTDEPTNOFROMEMPWHEREJOB=‘SALESMAN’；）EMPNOENAMEDEPTNO7369SMITH7566JONES7788SCOTT7782CLARK7839KING7876ADAMS7902FORD7934MILLER8rowsselected.2020201010202010
实际上，这里使用的“！=ALL”等价于NOTIN。（4）EXISTSEXISTS表示一个子查询至少返回一行时条件成立。NOTEXISTS表示一个子查询不返回任何行时条件成立。EXISTS与前面计的谓词IN，ANY，ALL带子查询的方式一样，但有三点不同之处：①它不能匹配一个列或多个列；②EXISTS只能用于子查询中，而IN，ANY，ALL还可以用在与值列表的连接中，如：
SQL>SELECTENAME，JOBFROMEMPWHEREJOB=ANY（‘CLERK’‘ANALYST’，）
③EXISTS一般用于相关的子查询中。关于相关子查询将在后面介绍。
例9、查找那些雇员的部门号在部门表中没有的雇员信息。
SQL>SELECTEMPNO,DEPTNO,ENAMEFROMEMPWHERENOTEXISTS(SELECT*FROMDEPTWHEREDEPTNO=EMP.DEPTNO）；Norowsselected.
使用EXISTS表示只要这个子查询的结果不为空，则满足条件；子查询为空，则条件不满足。在使用子查询时应注意以下几点：①子查询必须用括号括住。②子查询必须只有一旬，或者子查询的多列与主查询中用括号括起来的多个列连接。③单行值子查询可以使用单值或多值运算符，多行子查询必须使用多值运算符。④BETWEEN……AND不能用于子查询。⑤子查询中不能使用ORDERBY子句。⑥子查询可以对主查询中没有使用的基表操作，也可以查询多个基表。
例10、查询在纽约的雇员中，有哪些人的工资高于在DALLAS工作的SCOTT（假设公司中有同名的SCOTT）。
SQL>SELECTENAME，JOB，SAL，EMP.DEPTNOFROMEMP，DEPTWHERELOC=‘NEWYORK’ANDEMP.DEPTNO=DEPT.DEPTNOANDSAL>(SELECTSALFROMEMP，DEPTWHEREEMP.DEPTNO=DEPT.DEPTNOANDENAME=‘SCOTT’ANDDEPT.LOC=‘DALLAS’；）ENAMEJOBSALDEPTNOKINGPRESIDENT50001rowselected.10
⑦子查询不能用在函数调用的参数中。例如：
SELECTMAX（SELECTSALFROMEMPWHEREDEPTNO=30）……
就是错误的，子查询返回的结果不能当作函数调用的参数来使用。⑧查询嵌套最多为255层，对于多层嵌套，系统总是先执行最里面的一层子查询，然后逐层外推，直至全部完成。
例11、
SQL>SELECTENAME，JOB，HIREDATEFROMEMPWHERESAL>(SELECTMAX（SAL）FROMEMPWHEREDEPTNOIN（SELECTDEPTNOFROMDEPTWHEREUPPER（DNAME）LIKE‘SALES%’））；ENAMEJOBHIREDATE
JONESMANAGERSCOTTANALYSTKINGPRESIDENTFORDANALYST4rowsselected.
02-APR-8109-NOV-8117-NOV-8103-DEC-81
执行该查询时，首先执行最底层子查询，查询部门名称与‘SALES%’匹配的部门号，得到DEPTNO=30；将30号部门带到上一级子查询，查出30号部门工资的最大值$2850；最后将$2850作为主查询的条件值完成全部查询。⑨如果使用单值运算符而子查询却没有返回结果时，执行将会出错。3、多列子查询、子查询中不但可以查出一列的值，还可以查多个列。如果子查询中选择多列，则必须保证它与主查询中用括号括起来的多个列相对应。例如，下面给出的子查询形式就是正确的
SELECT列名FROM表名WHERE（列名，列名，???）IN.（SELECT列名，列名，.???.FROM表名WHERE条件）；UPDATE表名SET（列名，列名，???）=(SELECT列名，列名，???...FROM表名WHERE条件）；
..
子查询返回列的个数及类型必须要与主查询列的个数和类型匹配，下面这种形式就是错误的：
..???WHERE(数字列，字符列)=（SELECT日期列，数字列，,???
这种情况是列的类型不一致，所以是错误的。又如：
???WHERE(列名，列名)=（SELECT列名，列名，列名,???
这条语句的错误是在于子查询与主查询括号中列的个数不一样。因此在使用多列子查询时，必须保证与主查询用括号括起来的多个列个数相同、数据类型匹配。下面给出几个多列子查询的例子供读者参考。
例12、查出工资、奖金与20号部门雇员匹配的人员。
SQL>SELECTENAMEFROMEMPWHERE（NVL(COMM，0)，SAL）IN(SELECTNVL(COMM，0)，SALFROMEMPWHEREDEPTNO=20)；ENAMESMITHADAMSJONESSCOTTFORD5rowsselected.
例13将7698号雇员的工种和部门号改为7499号雇员的工种和部门号。
SQL>UPDATEEMPSET（JOB，DEPTNO）=（SELECTJOB，DEPTNO
FROMEMPWHEREDEPTNO=7499)WHEREEMPNO=7698；
4、相关子查询相关子查询是子查询的一种形式。当一个子查询涉及到来自上层查询的列时，该子查询就是相关子查询。它要为主查询中的每一行执行一次子查询，因而关于子查询的每一步均依赖于主查询中特定行中的值。相关子查询一般被用于SELECT，UPDATE或DELETE语句中。（1）用在SELECT语句中。
SELECT选择列表1FROM表名1WHERE列或列表达式样比较运算符（SELECT选择列表2FROM表名2WHERE表名2.列名比较运算符表名1.列名）；
（2）在UPDATE语句中，相关子查询被用来根据另一个表中的行来修改一个表中的行。
UPDATE表名1SET列名=（SELECT列或列表达式FROM表名2WHERE表名2.列名比较运算符表名1.列名）；WHERE条件；
(3)在DELETE语句中，相关子查询被用来根据另一个表中的行的存在性来删除一个表中的一行。
DELETEFROM表名1WHERE列名=（SELECT列或列表达式FROM表名2WHERE表名2.列名比较运算符表名1.列名）；
如果进行比较的列名意义模糊，容易产生二义性，就必须使用表名或表别名的前缀。
例14查出哪些雇员的工资高于他所在的部门的平均工资。
SQL>SELECTDEPTNO，ENAME，SALFROMEMPOUTERWHERESAL>(SELECTAVG（SAL）FROMEMPWHEREDEPTNO=OUTER.DEPTNO)；
DEPTNO302030201020
ENAME
SAL160029752850300050003000
6
ALLENJONESBLAKESCOTTKINGFORDrowsselected.
在这个查询中，为区分主查询与子查询中使用的EMP表，在主查询中为该表起了别名OUTER。系统执行时，首先从主查询的表OUTER中取出第一条记录带到子查询中，执行子查询的SELECT语句，计算出第一条记录的部门号所指的部门雇员平均工资。然后再招待主查询，检查OUTER的第一条记录的SAL是否大于这个平均工资。如果大于，则满足条件，取出这条记录的DEPTNO，ENAME和SAL列的值，否则不取。这样OUTER中的第一条记录就处理完了。接着处理OUTER中的第二条记录，当OUTER中所有的记录都处理完毕，整个查询命令执行结束。
例15将部门名称放到雇员EMP中。先用ALTER命令为EMP表增加一个新列DEPT_NAME。
SQL>ALTERTABLEEMPADD（DEPT_NAMECHAR（15）；）SQL>UPDATEMPSETDEPT_NAME=(SELECTDNAMEFROMDEPTWHEREDEPTNO=EMP.DEPTNO)WHEREEXISTS(SELECTDNAMEFROMEMPWHEREDEPTNO=ENP.DEPTNO)；
只有使用了相关子查询，才能将雇员所在的部门名称添加到雇员表中去。从上面的例子可以看出，常规子查询与相关子查询的区别表现在，前者为整个主查询仅执行一次子查询，而后者却要为主查询的每一行招待一次子查询。
1．6．3集合运算．．集合运算就是把两个或更多的查询结果合并为一个结果。集合运算包括UNION（并操作）、INTERSECT（交操作）和MINUS（差操作）。1、并操作：UNION、并操作：UNION操作是将属于一个查询结果的行或者属于另一个查询结果的行组合起来，构成UNION操作的结果。如果有一个行既属于一个查询结果，又属于另一个查询结果，则只取出其中一行。如图1.1所示。
AAB图1.1例1下面有三个以EMP为基表的视图：
AUNIONB
ORACLE基础
ACCOUNTENAME
ViewSALJOB
CLARKKINGMILLER
245050001300
MANAGERPRESIDENTCLERK
SALESViewEAMEALLENWARDMARTINBLAKETURNERJAMESSAL24505000130016501500950JOBMANAGERPRESIDENTCLERKMANAGERSALESMANCLERK
RESEARCHViewENAMESALJOB
SMITH800CLERKJONES2975MANAGERSCOTT3000ANALYSTADAMS1100CLERKFORD3000ANALYST根据给出的三个视图，查询所有部门中有哪些雇员的工资超过$2000.
SQL>SELECTENAME，SALFROMACCOUNTWHERESAL>2000UNIONSELECTENAME，SALFROMRESEARCHWHERESAL>2000UNIONSELECTENAME，SALFROMSALESWHERESAL>2000；
ENAME
SAL
CLARKFORDJONESKINGSCOTT5rowsselected.
24503000297550003000
-第56页–，共69页
ORACLE基础
2、交操作：INTERSECT、交操作：INTERSECT操作是将既属于一个查询结果的行又属于另一个查询结果的行组成INTERSECT操作的结果。如图1.2所示。
ABAUNIONB
图1.2
例2、根据上面的三个视图查询各个部门中有哪些相同的工种。
SQL>SELECTJOBFROMACCOUNT
INTERSECTSELECTJOBFROMRESEARCH
INTERSECTSELECTJOBFROMSALES；
JOB
CLERKMANAGER2rowsselected.
3、差操作：MINUS、差操作：MINUS操作是从一个查询结果的行中去掉又属于另一个查询结果的行。因而AMINUSB与BMINUSA的结果往往是不同的。见图1.3。
v
AUNIONAB
AUNIONBBBUNIONA
图1.3
例3、查找哪些工种是在财会部中有，而在销售部中没有。
SQL>SELECTJOBFROMMINUSSELECTJOBFROMJOBSALES；ACCOUNT
PRESIDENT
-第57页–，共69页
ORACLE基础
1rowselected.
例4、查找哪些工种在销售部中有，而在财会部中没有。
SQL>SELECTJOBFROMMINUSSELECTJOBFROMJOBACCOUNT；SALES
PRESIDENT1rowselected.
4、关于集合运算、关于集合运算有下述几点说明：（1）参与集合运算的SELECT语句必须都是有效的SELECT语句，各个SELECT语句均选择相同数目和同种数据类型的列；但相应的列允许列名和列宽度不同。在使用MINUS运算时，WHERE子句中的列既要包括在选择列表中，也要包括在被MINUS的SELECT语句的选择列表中。（2）允许多个查询结果进行集合运算，执行次序是先将两个查询结果进行集合运算，其结果再与第三个查询结果进行运算，以此类推，直到将所有的查询结果都进行完操作。利用括弧能够改变集合运算的优先次序。（3）不能选择LONG列进行集合运算。（4）关于集合运算结果的有序性如下：集合运算符隐含着在所有的SELECT语句中的DISTINCT，即集合运算消除重复行，集合运算结果的各行自动地按SELECT语句选择的诸列被排序返回。允许利用ORDERBY子句改变排序方式。但要注意，ORACLE忽略参与集合运算的各个SELECT语句选择的列名，故ORDERBY子句不允许引用列名，而仅能够引用相应。的列在选择列表中的位置（从左至右从1开始编号）
例如：SELECT列名1，列名2FROM表名1UNIONSELECT列名1，列名2FROM表名2ORDERBY列名1；
ORDERBY子句中使用列名1是错误的，是错误的，应该使用列名1在选择列表中的位置编应该为：号，应该为：
SELECT列名1，列名2FROM表名1UNIONSELECT列名1，列名2FROM表名2ORDERBY1；例5、将例1的结果进行排序，先按JOB排序，再按工资、排序。
-第58页–，共69页
ORACLE基础
SQL>SELECTENAME，JOB，SALFROMACCOUNTWHERESAL>2000UNIONSELECTENAME，JOB，SALFROMRESEARCHWHERESAL>2000UNIONSELECTENAME，JOB，SALFROMSALESWHERESAL>2000；
ORDERBY2，3，1；其查询结果如下：
ENAMEFORDSCOTTCLARKJONESKINGJOBANALYSTANALYSTMANAGERMANAGERSAL3000300024502975
PRESIDENT5000
5rowsselected
1.8技巧及高级应用
1．8．1树结构查询．．ORACLE是一个关系数据库管理系统，它用表的形式组织数据，在某些表中的数据还是呈现出树型结构的联系。例如，在前面讲座中和到的雇员信息表EMP，其中含有雇员编号（EMP-NO）和经理号（MGR）两列，通过这两列反映出来的就是雇员之间领导和被领导的关系。有些雇员领导另一些雇员，有些雇员被领导，还有些雇员领导一些人又被别人领导，他们之间的这种关系就是一种树结构，图1.4表示了EMP表雇员间的这种树结构。
KINGEMPNO=7839MGRISNULLMGR=7698EMPNO=7566JONESMGR=7839EMPNO=9698BLAKEMGR=7839DMPNO=7782CLARK
MGR=7566
MGR=7566
MGR=7698
MGR=7698
MFR=7698
MGR=7698
MGR=7698
MGR=7782
SCOTT
FORD
ALLENEMPNO=7499
WARDEMPNO=7521
MARTINEMPNO=9654
TURNEREMPNO=7844
JAMESEMPNO=7900
MILLEREMPNO=7834
EMPNO=7788EMPNO=7902MGR=7788ADAMSEMPNO=7876MGR=7902SMITHEMPNO=7369
图1.4EMP表树结构图
在这个树结构中，如果一个节点有直接的下属节点（如图中的JONES有SCOTT和
-第59页–，共69页
ORACLE基础
FORD），那么称该节点是下属节点的父节点，下属节点为该节点的子节点。通过雇员的EMP-NO和EGR可以看出他们之间的父子节点关系，父节点的EMPNO与子节点的MGR相同。在树结构中，有且仅有一个节点无父节点，如果图中的KING，该节点被称为根结点。从图上的标记可以看出，只有KING和MGR为空值。除根节点外，任何节点均有并且只能有一个父节点，有一个、多个或没有子节点。在扫描树结构表时，需要依次访问树结构的每个节点，一个节点只能访问一次，其访问步骤如下：第一步：从根节点开始；第二步：访问该节点；第三步：判断该节点有无被访问的子节点，若有，则转向它最左侧的未被访问的子节点，并招待第二步，否则执行第四步；第四步：或该节点为根节点，则访问完毕，否则执行第五步；第五步：返回到该节点的父节点，并执行第三步。1、树结构的描述树结构的数据存放在表中。数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR的值等于父节点的EMPNO值。在表的，通过每个节点的父节点，就要第一行中都有一个表示父节点的MGR（除根节点外）以确定整人树结构。在SELECT命令中使用CONNECTBY和STARTWITH子句可以查询表中的树型结构关系。其命令格式如下：SELECT...CONNECTBY{PRIOR列名1=列名2|列名1=PRIOR列名2}[STARTWITH]；其中：CONNCTBY子句说明每行数据将是按层次顺序检索，并规定将表中数据连入树型结构的关系中。PRIOR运算符必须放在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构时的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式、STARTWITH子句为可选项，用来标识哪个节点为查找树型结构的根节点，若该子句被省略，则表示以所有满足查询条件的行为人作为根节点。
例1以树结构方式显示EMP表的数据。
SQL>SELECTEMPNO,ENAME,MGRFROMEMP
CONNECTBYPRIOREMPNO=MGRSTARTWITHENAME=’KING’；EMPNO78397566778878767902736976987499ENAMEKINGJONESSCOTTADAMSFORDSMITHBLAKEALLEN7839756677887566790278397698MGR
-第60页–，共69页
ORACLE基础
752176547844790077827934
WARDMARTINTURNERJAMESCLARKMILLER
769876987698769878397782
14rowsselected.
2、关于PRIOR运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。PRIOR被置于CONNECTBY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
CONNECTBYPRIOREMPNO=MGR
PRIOR运算符被置于CONNECTBY子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：
CONNECTBYEMPNO=PRIORMGR
在这种方式中也应指定一个开始的节点。
例2从SMITH节点开始自底向上查找EMP的树结构。
SQL>SELECTEMPNO，NAME，MGRFROMEMP
CONNECTBYEMPNO=PRIORMGRSTARTWITHENAME=‘SMITH’；
EMPNO7369790275667839
ENAMESMITHFORDJONESKING
MGR790275667839
4rowsselected.
在这种自底向上的查找过程中，只有树中的一枝被显示，这是因为，在树结构中第一个节点只允许有一个父节点，其查找过程是从开始节点起，找到其父节点，再由其父节点向上，找父节点的父节点，这样一直找到根节点为止，结果就是树中一枝的数据。3、定义查找起始节点在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
例3查找JONES直接或间接领导的所有雇员信息。
-第61页–，共69页
ORACLE基础
SQL>SELECTEMPNO，NAME，MGRFROMEMP
CONNECTBYPRIOREMPNO=MGRSTARTWITHENAME=‘JONES’；
EMPNO75667788787679027369
ENAMEJONESSCOTTADAMSFORDSMITH
MGR78397566778875667902
5rowsselected.
STARTWITH不但可以指定一个根节点，还可以指定多个根节点。
例4、查找由FORD和BLAKE领导的所有雇员的信息。
SQL>SELECTEMPNO，NAME，MGRFROMEMPCONNECTBYPRIOREMPNO=MGRSTARTWITHENAMEIN（‘FORD’‘BLAKE’；，）
EMPNO76987499752176547844790079027369
ENAMEBLAKEALLENWARDMARTINTURNERJAMRDFORDSMITH
MGR78397698769876987698769875667902
8owsselected.
自底向上查询树结构时，也要指定一具开始节点，以此开始向上查找其父节点，直到找到根节点，其结果将是结构树中的一枝数据。4、使用LEVEL在具有树结构的表中，第一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2，以此类推。图1.5就表示了树结构的层次。层号KING1
-第62页–，共69页
ORACLE基础
JONES
BLAKE
CLARKMILLER
23
SCOTTADAMS
FORDALLENSMITH
WARDMARTINTURNER
JAMES4
图1.5EMP表树结构层次图
在查询中，可以使用伪列LEVEL显示每行数据的有关层次，LEVEL将返回树型结构中当前行节点的层次，我们可以使用LEVEL来控制对树型结构进行遍历的深度。
例5、显示EMP表中的各行数据及层号。
SQL>SELECTLEVEL，EMPNO=MGRFROMEMPCONNECTBYPRIOREMPNO=MGRSTARTWITHENAMEIN=‘KING’；
LEVE1234567891011121314
EMPNO78397566778878767902736976987499752176547844790077827934
ENAMEKINGJONESSCOTTADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMESCLARKMILLER
MGR
7839756677887566790278397698769876987698769878397782
14rowsselected.
伪列LEVEL为数值型，可以在SELECT命令中用于各种计算。
例6使用LEVEL改变查询结果的显示形式。
SQL>COLUMNEMPLOYEEFORMATA20
-第63页–，共69页
ORACLE基础
SQL>SELECTEMPNO,RPAD(“,LEVE*3)||ENAMEEMPLOYEE,MGRFROMEMPCONNECTBYPRIOREMPNO=MGRSTARTWITHENAMEIN=‘KING’；
EMPNO78397566778878767902736976987499752176547844790077827934
ENAMEKINGJONESSCOTTADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMESCLARKMILLER
MGR
7839756677887566790278397698769876987698769878397782
14rowsselected.
在SELECT子句中使用了函数RPAD，该函数表示以LEVEL*3个空格进行填充。由于不同行处于不同的节点位置，具有不同的LEVEL值，因此，真充的空格数将根据各自的层号确定。空格再与雇员名字拼接，结果显示出这种层次关系，也就是说其雇员名字右侧填充的空格数与它的层数有关。5、节点和分支的裁剪、在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支。使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向上检索时）。
例7显示KING领导下的全体雇员的信息，但除去SCOTT。
SQL>COLUMNEMPLOYEEFORMATA20SQL>SELECTEMPNO,RPAD(“,LEVE*3)||ENAMEEMPLOYEE,MGRFROMEMPWHEREENAME！=‘SCOTT’CONNECTBYPRIOREMPNO=MGRSTARTWITHENAMEIN=‘KING’；
EMPNO7839
ENAMEKING
MGR
-第64页–，共69页
ORACLE基础
756678767902736976987499752176547844790077827934
JONESADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMESCLARKMILLER
783977887566790278397698769876987698769878397782
13owsselected.
在这个查询中，仅剪去了树中的单个节点SCOTT。若希望剪去树结构中的某个分支，则要使用XONNECTBY子句，CONNECTBY子句是限定树型结构中的整个分支，既要剪除分支上的单个节点，也要剪除其后代节点（自顶向下检索时）或前辈节点（自底向上检索时）。例8显示KING领导下的全体雇员信息，除去SCOTT领导的一支。
SQL>SELECTEMPNO，RPAD(“,LEVE*3)||ENAMEEMPLOYEE,MGRFROMEMPCONNECTBYPRIOREMPNO=MGRANDENAME！=‘SCOTT’STARTWITHENAMEIN=‘KING’；
EMPNO783975667902736976987499752176547844790077827934
ENAMEKINGJONESFORDSMITHBLAKEALLENWARDMARTINTURNERJAMESCLARKMILLER
MGR
78397566790278397698769876987698769878397782
12rowsselected.
这个查询结构就与例7不同，除了剪去单个节点SCOTT外，还将SCOTT的子节
-第65页–，共69页
ORACLE基础
点ADAMS剪掉，即把SCOTT这个分支剪掉了。当然WHERE子句可以和CONNECTBY子句联合使用，这样能够同时剪掉单个节点和树中的某个分支。
例9显示KING领导的全体雇员信息，除去雇员SCOTT，以及BLANK领导的一支。
SQL>COLUMNEMPLOYEEPORMATA20SQL>SELECTEMPNO，RPAD(“,LEVE*3)||ENAMEEMPLOYEE,MGRFROMEMPWHEREENAME！=‘SCOTT’CONNECTBYPRIOREMPNO=MGRANDENAME！=‘BLAKE’STARTWITHENAMEIN=‘KING’；
EMPNO7839756678767902736977827934
ENAMEKINGJONESADAMSFORDSMITHCLARKMILLER
MGR
783977887566790278397782
7
rowsselected.
6、排序显示像其他查询一样，在树结构中也可以使用ORDERBY子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序显示。
例10以EMPNO的顺序显示树结构EMP中的数据。
SQL>SELECTEMPNO，ENAME,MGRFROMEMPCONNECTBYPRIOREMPNO=MGRSTARTWITHENAMEIN=‘KING’；ORDERBYEMPNO；
EMPNO
ENAME
MGR
7369749975217566765476987782
SMITHALLENWARDJONESMARTINBLAKECLARK
7902769876987839769878397839
-第66页–，共69页
ORACLE基础
7788783978447876790079027934
SCOTTKINGTURNERADAMSJAMESFORDMILLER
7566
76987788769875667782
14rows
selected.
在使用SELECT语句来报告树结构报表时应当注意，CONNECT子句不能作用于出现在WHERE子句中的表连接。如果需要进行连接，可以先用树结构建立一个视图，再将这个视图与其他表连接，以完成所需要的查询。1.8.2报表高级设计（1）将SQL*PLUS的报表设置参数写入文件在使用SQL*PLUS制作报表时，一般将报表的内容及参数的设置写入一个后缀为.SQL的文件中，然后使用START命令执行该文件。制作报表时，往往希望内容清晰简洁，这可以通过设置下面有关的参数和选项来实现：SETECHOOFF使SQL*PLUS在执行命令文件时，不将命令显示在屏幕上在执行完命令文件后，不将查到的结果数显示到屏幕上SETFEEDBACKOFFSETTERMOUTOFF它将禁止报告结果显示到屏幕上，它一般放置在SPOOL命令之前，因为对于要打印的报表，这将能节省时间以避免烦人的屏幕滚动，文件输出操作还能继续正常地工作此外，还有SETPAGESIZE，SETHEADINGOFF等也将会影响报表的输出效果。（2）关于SELECT子句的补充说明在SELECT子句中不但可以选择列和列表达式进行输出，还可以选择常量和多列组合进行输出。例1
SQL>SELECT1,’Thisisacharacterconstant’FROMEMPWHEREDEPTNO=10;
1’THISISACHARACTERCONSTANT’
1Thisisacharacterconstant1Thisisacharacterconstant1Thisisacharacterconstant3rowsselected.
-第67页–，共69页
ORACLE基础
例2
SQL>SELECT1,’Thisisacharacterconstant’FROMSYSTEM.DUAL
1’THISISACHARACTERCONSTANT’
1Thisisacharacterconstant
1rowsselected.
其中：DUAL为虚表，该表中仅有一行一列。
例3将雇员的名字和工种拼接后输出。
SQL>SELECTEMPNO，ENAME||‘jobis’||JOBFROMEMPWHERESAL>=3000；EMPNOENAME||’JOBIS’||JOB
778878397902
SCOTTjobisANALYSTKINGjobisPRESIDENTFORDjobisANALYST
3rowsselected.
1．8．3从SQL生成SQL。．．。在对众多数据库实体进行一系列相同的SQL操作时，往往要对单个的数据库实体进行重复的SQL语句编程，这不但繁琐，而且不能适应一般性的情况。如果利用SQL生成SQL，就能动态地构造SQL和SQL*PLUS代码，这样在处理对象变化而处理方式和方法不变的情况下，就无须重新编制雷同的SQL语句。我们可以利用若干SELECT语句为主所组成的短小紧凑的SQL命令，自动地根据处理对象构造处理操作所需要的全部SQL语句。利用SQL生成SQL的基本步骤如下：（1）设置SQL*PLUS报表生成格式；（2）允许假脱机输出；（3）利用SELECT语句构造所需要的SQL语句；（4）禁止假脱机输出；（5）执行假脱机输出文件中包含的SQL语句。其中：设置报表生成格式的目的是使得假脱机输出文件仅包含SQL语句。常用的设置命令有：SETHEADINGOFF要求显示查询结果时不显示列标题SETFEEDBACKOFF要求显示查询结果时不显示被选择记录数SETPAGESIZEn设置每页的行数，应设置n足够大，以避免生成代码的长度超出页长，n=0表示不产生新页SETLINESIZEn设置每行的字符数，应设置n足够大，以避免生成代码被执行
-第68页–，共69页
ORACLE基础
SETVERIFYOFFSPOOL{文件名|OFF}START文件名
例如，命令文件内容如下：
SETHEADINGOFFSETFEEDBACKOFFSETECHOOFFSETPAGESIZE5000SPOOLFIRST.SQL
要求执行SQL语句前不显示使用的变量值允许和禁止假脱机输出执行SQL命令文件
SELECT‘SELECT”X”FROMSYSTEM.DUAL;’FROMSYSTEM.DUAL;SPOOLOFFSETHEADINGONSETFEEDBACKONSETECHOONSETPAGESIZE17SPOOLFIRST.LSTSTARTFIRSTSPOOLOFF
执行该文件，产生的FIRST.SQL文件内容如下：
SELECT‘X’FROMSYSTEM.DUAL；
通过STARTFIRST.SQL命令的执行，将屏幕信息定入FIRST.LST中，其内容如下：
SQL>STARTFIRSTSQL>SQL>STLECT‘X’FROMSYSTEM.DUAL；‘_X1rowselected.SQL>SPOOLOFF
这个例子就是先用SQL命令生成一个SQL文件——FIRST.SQL，其中包括了需要的命令，再通过执行文件的内容完成所需的操作。
-第69页–，共69页
