第一章
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 数据库系统，将 ORACLE RDBMS 装入内存中，命令 执行后，就可以运行工具软件了。 （4） 在操作系统提示符下执行 SQL*PLUS： $ SQLPLUS [ RETURN] SQL*PLUS 将显示它的版本号、日期和版权信息，并提示你输入用户名。 因为 ORACLE 保护对它所有数据的访问，所以与它连接通常需要一个用户标识 （user-name）和口令（password） 。 Enter user-name： 输入用户名后按[RETURN]，SQL*PLUS 提示你输入口令： Enter password： 如果口令输入正确， SQL*PLUS 将宣布你已经与 ORACLE 连接上了， 然后显示： SQL> 说明现在你已经进入 SQL*PLUS 中，并等待你的指示。 如果输入的用户名或口令不正确，在向 ORACLE 三次注册失败后，屏幕上 将出现终止服务信息，并退出 SQL*PLUS。 在进入 SQL*PLUS 时，也可以在命令行上直接输入用户名和口令，中间以 “/”分隔，比如，你的用户名是 SCOTT，口令是 TIGER，那么你输入下列命令： $ SQLPLUS SCOTT/TIGER
就可以进入 SQL*PLUS 中。 当你想停止工作并离开 SQL*PLUS，则可以输入 EXIT 或 QUIT 命令。 SQL>EXIT 1．1．3 预备知识 1、基表 ORACLE 是一种关系型数据库系统，它以关系的方式组织数据，这里所说 的关系，我们称之为基表。一个数据库可以由若干个表组成，用户对数据库的操 作也就是对表的操作。 ORACLE 用基表的形式存储信息，如表 1.1 所示。
表 1.1 EMP 表
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SMITH CLERK 7369 7902 17-Dec-80 800 20 ALLEN SLESMAN 7698 20-Feb-81 1600 7499 300 30 WARD SALESMAN 7698 22-Feb-81 7521 1250 500 30 JONES MANAGER 7839 2-Apr-81 7566 2975 20 7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30 BLAKE MANAGER 7839 1-May-81 7698 2850 30 每个基表都有一个表名，以便识别。每个基表都包含一个或一个以上的列， 每列包含一类信息，并都有一个列名，以描述保存在那一列中的信息的种类。表 名和列名构成了基表的框架，也就是基表的分类信息。具体信息一行一行存放， 表的每一行表示一组独立的数据，它由具有不同列值的各列组成。 2、基表之间的关系 在数据库中，数据与数据之间是存在着联系的，我们可以将某个基表中的 信息与其他基表中的信息建立关系，例如，在前面的 EMP 表中，每个雇员均有 部门编号（DEPTNO） ，它对应于 DEPT 表中的部门编号（DEPTNO） ，见表 1.2。
表 1.2 DEPT 表 DEPTNO 40 30 20 10 DNAME OPERATIONS SALSE RSESARCH ACCOUNTING LOC BOSTON CHICAGO DALLAS NEW YORK
这样可便于信息的独立组织，因为我们可以在 EMP 表中存放雇员信息，而 无需在基中存放 DEPT 表中有关部门的信息。当然，我们还可以在多个基表之间 建立联系。 3、数据字典 数据字典是 ORACLE 关系数据库管理系统（RDBMS）最重要的成分之一， 是数据库系统的宝贵资源。 数据库管理员 （DBA） 使用它来监视 ORALCE RDBMS 的使用，并协助用户完成他们的工作。数据字典是由 ORALCE 自动建立并更新 的一组基表和视图，在这里记录了用户的姓名、基表和视图的定义、用户权限以 及数据存储等方面的信息。 数据字典中包括下列信息的描述： （1） ORACLE 所有用户的标识及用户的存取权限。 （2） 每个用户所拥有的数据库实体（表、空间定义、视图、索引、聚簇
和同义词）的定义。 （3） 为数据库实体分配的空间情况。 我们可以通过查询语句查看数据字典中的有关内容。例如：用户需要查看 当前用户创建的所有基表、视图和同义词清单，可以使用下列命令： SQL>SELECT * FROM CAT； CAT 表中存放的就是有关用户创建的基表、视图和同义词情况的描述。 数据字典由几个基础表及派生出来的视图组成。基础表是由 ORACLE RDBMS 自动更新，用户基本上不访问基础表，ORACLE RDBMS 不断地更新数 据字典的基础表，而视图总是引用基础表中的最新数据。 一般来说，用户不能直接更新 ORACLE 的数据字典，因为用户操作不当、 删除或修改了某些数据字典的内容，就会造成数据库的破坏。 1．1．4 SQL*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> SELECT EMPNO,ENAME,JOB,SAL,FROM EMP WHERE SALSELECT EMPNO,ENAME,JOB,SAL 2 FROM EMP 3 WHERE SALCOLUMN SAL FORMAT $99,999 HEADING SALARY
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>HELP ACCEPT
2、DESCRIBE 命令 当我们需要对基表进行操作的时候，往往需要获取关于基表的结构信息， 可以使用 DESCRIBE 命令查看基表的结构： SQL>DESCRIBE DEP 命令执行结果如下：
NAME DEPTNO DNAME LOC NULL? NOT NULL TYPE NUMBER (2) CHAR (14) CHAR (13)
基表的结构信息包括： NAME 列名 NULL 标识该列是否允许为空值，如缺省则表示允许为空值 TYPE 说明该列的数据类型， 可以是字符型：CHAR（w） ，VARCHAR2（w） ； 数字型：NUMBER（w，d） 日期型：DATE 原始数据型：RAW 以及 LONG 型等等。 其中 w 表示列的宽度，d 表示小数点之后的位数 还可以使用 DESCRIBE 命令获取有关函数、过程以及包括描述，例如：要 求显示有关函数 AFUNC 的信息： SQL>DESCRIBE afunc 结果是 function afunc(F1 CHAR，F2 NUMBER)return NUMBER；
1．1．6 编辑 SQL*PLUS 命令的使用 1、命令行编辑命令 我们可以使用 SQL*PLUS 的一些行编辑命令编辑 SQL 缓冲区中的 SQL 命 令，表 1.3 列出了编辑功能的 SQL8PLUS 命令。 表 1.3
命 APPEND text 令 A text 缩 写 text 行尾增加 意 义
CHANGE/old/new CHANGE/text CLEAR BUFFER DEL INPUT INPUT text LIST LIST n LIST * LIST LAST LIST m n
C/old/new C/text CL BUFF 无 I I text L Ln 或n L* LAST Lmn
在当前行中将 old 改为 new 从当前行中删除 text 删除 SQL 缓冲区的所有行 删除当前行 增加一行或多行 增加一个由 text 组成的行 显示 SQL 缓冲区的所有行 显示一行 显示当前行 显示最后一行 显示多行（从 m 到 n）
（1）显示 SQL 缓冲区的命令 使用 LIST 命令可以显示当前 SQL 缓冲区的内容。 如果 LIST 命令后面不带 任何参数， 将显示 SQL 缓冲区中的所有行； 如果 LIST 后面带一个参数 （如 LIST n） ，则显示第 n 行的内容；如果 LIST 后面带有两个参数（LIST m n） ，那将显示多
行，即从 m 行到 n 行。例如： SQL>LIST 1 SELECT EMPNO，ENAME，JOB，SAL 2 * FROM EMP WHERE SQL SELECT DPTNO，ENAME，SAL 2 FROM EMP 3 WHERE DEPTNO=10；
系统给出出错信息：
SELECT DPTNO，ENAME，SAL * ERROR at Line 1： ORA-0904：invalid column name
使用 CHANGE 命令进行修改：
SQL>CHANGE/DPTNO/DEPTNO
改正后屏幕上显示：
1* SELECT DEPTNO，ENAME，SAL
这时可用 RUN 命令重新运行修改后的 SQL 命令。 （3）增加新行 使用 INPUT 命令可以在 SQL 缓冲区增加一个或多个新行。如果只输入 INPUT，则在当前行之后插入多个新行；如果输入 INPUT text，则在当前行之后 插入由 text 组成的新行。
例如，输入 INPUT 命令： SQL>INPUT 4
这时在 INPUT 命令之后出现一个行号，这个行号表明所要插入的行，此时
可输入一个或多个新行。输入一行内容后按 RETURN 键，SQL*PLUS 给出一个 新行号，如屏幕所示：
4 5 ORDER BY SAL
再按 RETURN 键，表示插入结束，可用 RUN 命令重新运行来检验最终的 查询结果。 （4）在一行后追加内容 使用 APPEND 命令可以在 SQL 缓冲区中的当前行未尾追加内容。首先用 LIST 命令将需要追加内容的行置为当前行，然后使用 APPEND text 命令在当前 行未尾追加内容。例如，要在第四行未尾追加 DESC 选项，先将第四行置为当前 行：
SQL>LIST 4 4* ORDER BY SAL
然后输入下列命令追加 DESC：
SQL>APPEND DESC 4* ORDER BY SAL DESC
最后用 RUN 命令重新运行。 （5）删除 SQL 缓冲区中的行 使用 DEL 命令可以删除 SQL 缓冲区中的当前行， 当前行以下各行的行号顺 序减 1，如果把第一行设为当前行，连续多次输入 DEL 后，可以删除 SQL 缓冲 区中的所有行。删除 SQL 缓冲区中所有行也可以使用 CLEAR BUFFER 命令， 它的作用是将 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>SAVE DEPTINFO
这时 SQL*PLUS 响应：
Create file DEPTINFO
文 件 名 后 缀 缺 省 为 .SQL 。 这 时 用 户 需 要 保 存 的 内 容 已 经 存 入 了 DEPTINFO.SQL 文件中，可以对文件进行编辑、运行。 如果想将修改过的内容保存到原有的命令文件中，则在 SAVE 命令后增加 REPLACE 选项：
SQL>SAVE DEPTINFO REPLACE
如果想将 SQL 缓冲区的内容追加到已经存在的命令文件未尾，则在 SAVE 命令后增加 APPEND 选项：
SQL>SAVE file—name APPEND
4、GET 命令 GET 命令把磁盘上的命令文件内容调入当前缓冲区，并显示在屏幕上，格 式是
SQL>GET file—name
文件后缀缺省为.SQL，文件被调入缓冲区后，可以对它进行编辑、运行。 5、运行命令文件 START 命令将指定文件调入缓冲区并执行文件内容：
SQL>START file—name
文件后缀缺省为.SQL。也可以使用“@”命令运行文件：
SQL>@ file—name
1．2 数据操纵语言
数据操纵语言（DML：Data Manipulation Language）给用户或应用程序提 供访问数据库系统的接口。 1．2．1 数据库的基本查询 SQL 语言中最主要、最核心的部分是它的查询功能。所谓查询就是从数据 库中提取满足用户需要的数据， 查询是由 SELECT 命令实现的。 SQL 语言中， 在 许多操作都涉及到 SELECT 命令。例如，半 SELECT 命令查询到的数据插入到 另外一张基表中；使用 SELECT 命令用满足条件的数据创建一个视图等等。因 此 SELECT 命令也是 SQL 语言中最灵活、最复杂的命令。我们将从简单的 SELECT 命令开始，逐步深入讨论。 通常，一个 SELECT 命令可以分解成三个部分：查找什么数据，从哪里查 找，查找条件是什么。因此，SELECT 命令可以分成以下几个子句：SELECT 子 句和 FROM 子句 （这两个子句是每条 SELECT 命令必须有的） WHERE 子句 ， （是 可选的） ，另外带可以选择 GROUP BY 子句、HAVING 子句和 ORDER BY 子句 等。它的基本语法是 SELECT FORM WHERE GROUP BY HAVING ORDER BY 1、SELECT 子句 SELECT 子句的指出要查找的数据，FROM 子句的指出 查找过程中涉及到的基表，这两个子句必不可少。
例 1、查找整个基表的内容。 SQL>SELECT * FROM EMP；
EMPNO 7369 7499
ENAME SMITH ALLEN
JOB CLERK SALESMAN
MGR 7902 7698
HIREDATE 17-Dec-94 20-Feb-95
SAL 800 1600
COMM 300
DEPTNO 20 30
7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934
WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK 7788 CLERK 7698 ANALYST CLERK
7698 7839 7698 7839 7839 766 7698
22-Feb-95 02-Apr-95 28-Sep-95 01-May-95 09-Jun-95 09-Nov-95 17-Nov-95 08-Sep-95 23-Sep-95 03-Dec-95
1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300
500 1400
30 20 30 30 10 20 10
0
30 20 30 20 10
7566 7782
03-Dec-94 23-Jan-94
14 rows selected.
“*”号意味着查找表中所有列。 若 SELECT 子句中选择一个列名，则将得到这一列的所有数据。
例 2、查看雇员名字。 SQL>SELECT ENAME FROM EMP；
ENAME SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14rows selected.
若查找多列数据，则用逗号将各个列名分隔开。
例 3、查看雇员编号、名字和工种。 SQL>SELECT EMPNO，ENAME，JOB FROM EMP；
EMPNO 7369 7499 7521 7566 7654 7698 7782 7788
ENAME SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT
JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST
7839 7844 7876 7900 7902 7934
KING TURNER ADAMS JAMES FORD MILLER
PRESIDENT SALESMAN CLERK 7788 CLERK 7698 ANALYST CLERK
14 rows selected.
系统在显示查询结果时，各列的显示顺序由它们在 SELECT 子句中出现的 顺序决定。如例 3 中希望显示顺序是 JOB，ENAME，EMPNO，则可以输入
SQL>SELECT JOB，ENAME， EMPNO FROM EMP；
JOB CLERK SALESMAN SALESMAN
ENAME SMITH ALLEN WARD
EMPNO 7369 7499 7521
„„„„„„
14 rows selected.
在显示查询结果时，各列的标题就是基表中的列名，如果希望显示的列标 题不同于列名，可在 SELECT 子句中定义列名的别名，方法是在列名后加一个 空格，然后写上它的别名。
例 4、SQL>SELECT ENAME EMPLOYEE FROM EMP；
EMPLOYEE SMITH ALLEN WARD
„„„„ 14 rows selected.
如果 SELECT 子句中有表达式，则这个表达式就是显示信息时的标题，当 然，也可以为表达式定义一个别名，便于明确表达式的意义。
例 5、计算奖金占工资的比率。 SQL>SELECT ENAME，SAL，COMM，COMM/SAL“C/S RATIO” FROM EMP；
ENAME MARTIN WARD ALLEN
SAL 1250 1250 1600
COMM 1400 500 300
C/S RATIO
1.12 0.4 0.1875
…… 14 rows selected.
„„
……
当别名中有诸如空格或斜线（/）这样的字符时，必须用双引号括起来。注 意：别名只能用在 SELECT 子句中，其他子句不能使用，并且别名仅在使用它 的 SELECT 命令中有效，而不影响其他查询语句的结果。 2、WHERE 子句 前面讨论的都是无条件查询，即选中基表中的所有行，我们还可以通过 SELECT 命令中的 WHERE 子句来选择指定的行。
例 6、查找在 10 号部门工作的雇员。 SQL>SELECT ENAME FROM EMP WHERE DEPTNO=10； ENAME CLARK KING IMILLER 3 rows selected.
其中“DEPTNO=10”就是查询条件。系统在执行这种条件查询时，逐行地 对表中的数据进行比较，检查它们是否满足条件，如果满足条件，则取出该行的 有关信息，如不满足，则不取该行的信息。使用 WHERE 子句时，应注意列值的 表示方法，若该列为字符型，需使用单引号（ ）将字符串括起来，如：WHERE ‘’ ENAME=‘SMITH’ ，而且应注意单引号内的字符串要区分大小写形式。若该列 为数字型，则不必使用引号，如 WHERE DEPTNO=20。此外列值还可以取其他 列（或其他基表的列）的值，如：WHERE EMP.DEPTNO=DEPT.DEPTNO，这 里列值取另一个基表中的列，这实际上是一种连接形式。关于联接将在 1.6.1 小 节中具体论述。 在 HWERE 子句的条件中可以使用下列比较运算符：
运算符 = ！=或 > >= SELECT ENAME，JOB FROM EMP WHERE JOB=‘CLERK’ OR JOB=‘ANALYST’ ；
如果 JOB 列还可以取多个值，则还需要多个 OR 操作，如果使用 IN，就可 以用一个简单的 WHERE 子句表示这种要求：
SQL>SELECT ENAME，JOB FROM EMP WHERE JOB IN （ ‘CLERK’‘ANALYST’； ， ）
ENAME SMITH SCOTT ADAMS
JOB CLERK ANALYST CLERK
JAMES CLERK IFORD ANALYST MILLER CLERK 6 rows selected.
谓词 IN 的作用就是检查列值是否等于它后面括弧内的一组值中的某一个， 如果等于其中某一个值，则被查询出来。 我们还可以使用 NOT IN 来表示与 IN 完全相反的含义，即选择那些不在列 表中的行。
例 8、查找不是职员和分析员的雇员姓名。 SQL>SELECT ENAME，JOB FROM EMP WHERE JOB NOT IN （ ‘CLERK’‘ANALYST’； ， ）
（2）BETWEEN„AND 在查找中，如果要求某列的数值在某个区间范围内，则可以使用 BETWEEN„AND。
例 9、查找哪些雇员的工资在$2000 到$3000 之间，可以写成 SQL>SELECT ENAME，JOB，SAL FROM EMP WHERE SAL BETWEEN 2000 AND 3000；
ENAME JONES BLAKE CLARK SCOTT FORD 5 rows selected.
JOB MANAGER MANAGER MANAGER ANALYST ANALYST
SAL 2975 2850 2450 3000 3000
与此相反，NOT BETWEEN„„AND 则选择列值不在该范围内的信息。
例 10、SQL>SELECT ENAME，JOB，SAL FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3000；
（3）LIKE（模式匹配） 在查找中，有时需要对字符串进行比较。在比较中，有的要求两个字符串 完全相同，有的要求部分字符相同，而其余的字符可以任意。LIKE 就可以用来 搜索所有的数据，来查找与你描述的模式相匹配的行。 LIKE 提供两种字符串匹配方式： 一种用下划线（_）表示，称为定位标志；另一种用百分号（%）表示，称 为通配符。 在检测一个字符串时，如果有一个字符可以任意，则在该字符串位置上用 下划线表示。
例 11、选择名字以 W 开头，后面仅有三个字母的雇员信息。
显然，在查找中，要求名字的第一个字母是 W，后面仅有三个字母，可以 是任意字母，因此后面三位可以用三个下划线来表示：
SQL>SELECT ENAME，DEPTNO FROM EMP WHERE ENAME LIKE‘W_ _ _’ ；
ENAME DEPTNO WARD 30 1 row selected.
若在查找时只需包含某个字符串，其他字符任意的情况下，可用通配符% 来表示。
例 12、SQL>SELECT ENAME，DEPTNO
FROM EMP WHERE ENAME LIKE‘S%’ ；
ENAME DEPTNO SMITH 20 SCOTT 20 2 rows selected.
通配符%还可以与下划线一起使用。
例 13、查找名字以字母 M 开头，以 R 结尾，并且第三个字母为 L 的雇员名字、工种和所在部门号。
那么，在查找中，名字的第一个字母是 M，第三个字母是 L，第二个字母 可以是任意的，该位置可用一个下划线表示。最后一个字母是 R，而第三个字母 和最后一个字母之间可以是任意长度的字符串，所以在字母 L 和 R 之间使用通 配符%，其命令语句如下：
SQL>SELECT ENAME，JOB FROM EMP WHERE ENAME LIKE‘M_L%R’ ；
ENAME DEPTNO MILLER CLERK 1 row selected.
总之，我们可以使用通配符%匹配任意长度的字符串，使用‘_‘匹 配一个任意字符。此外我们还可以使用 NOT LIKE 选择列值不匹配的行。
例 14、查找哪些雇员的工种名不以 SALES 开头。 SQL>SELECT ENAME，JOB FROM EMP WHERE JOB NOT IN LIKE‘SALES%’ ；
（4）IS NULL 和 IS NOT NULL 我们使用的数据库一般是反映实际的具体情况，但有时某些数据在实际情 况中还不存在，例如：新来的雇员还未分配到部门工作，那么他的 DEPTNO 就 不存在，他的 DEPTNO 值既不等于 0 也不等于其他值，而是一个未知的值，我 们称它的 DEPTNO 值为空值。空值实际是指一种未知的、不存在的或不可应用 的数据，通常用 NULL 表示。NULL 仅仅是一个符号，它不等同于零，所以它不 能象零那样进行算术运算。NULL 不能与等号之类的运算符连用，而应该使用关 键字 IS。
例 15、选择没有奖金的雇员信息（即奖金为空值的雇员信息） SQL>SELECT ENAME，JOB FROM EMP WHERE COMM IS NULL；
ENAME JOB SMITH CLERK JONES MANAGER BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK 10 rows selected.
若查找列值为非空的信息，则使用 IS NOT NULL。
例 16、选择有奖金的雇员。 SQL>SELECT ENAME，JOB FROM EMP WHERE COMM IS NOT NULL；
（5）逻辑运算符 NOT，AND，OR 在 WHERE 子句中，也可以通过逻辑运算符连接多个条件，构成一个更复 杂的条件进行查询。在 WHERE 子句中可以使用三种逻辑运算符：
运算符 NOT AND OR 名称含义 逻辑非选择不满足条件的行 逻辑与选择列值同时满足多个条件的行 逻辑或选择列值满足任一条件的行
在 WHERE 子句中，关系比较符的优先级高于逻辑运算符。在逻辑运算符 中，逻辑非（NOT）的优先级最高，逻辑与（AND）次之，逻辑或（OR）优先 级最低。
例 17、查找 20 号部门里不是职员的雇员信息。 SQL>SELECT ENAME，JOB FROM EMP WHERE DEPTNO=20 AND JOB！=‘LERK’ ；
ENAME JOB JONES MANAGER SCOTT ANALYST FORD ANALYST 3 records selected.
例 18、查找在 20 号部门工作或不是职员的雇员信息。 SQL>SELECT ENAME，JOB，DEPTNO FROM EMP WHERE DEPTNO=20 OR JOB！=‘CLERK’ ；
ENAME JOB EPNTO SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK FORD ANALYST 13 rows selected.
20 30 30 20 30 30 10 20 10 30 20 20
综合上所述，在 WHERE 子句中，可以使用关系运算符、逻辑运算符以及 特殊的运算符 LIKE 等构成条件，当条件满足时，则取有关的数据。这些运算符 的优先顺序如下： ①=， ！=，，>，>=，[ASC/DESC][，[ASC/DESC]]„„； 在 ORDER BY 子句中，指出查询结果数据按该列排序，选项 [ASC/DESC]表示按升序还是降序排列，选择 ASC 为升序显示，选择 DESC 为降 序显示，该项缺省为升序形式。
例 19、按字母顺序显示雇员的名字。 SQL>SELECT ENAME FROM EMP ORDER BY ENAME；
ENAME ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD 14 rows selected.
如果按降序排列，必须加上关键字“DESC” 。
例 20、按部门号降序显示。 SQL>SELECT * FROM EMP ORDER BY DEPTNO DESC；
DEPTNO DNAME 40 OPERATIONS 30 SALSE 20 RESEARCH 10 ACCOUNTING 4 rows selected.
LOC BOSTON CHICAGO DALLAS NEW YORK
如果按多列进行排序时，应分别指出它们相应的列名及有关的递增或递减 方式。选择按多列排序，首先由第一个列名确定顺序，若第一排序列值相同，再 按第二个列名排序，第二排序列值相同，再按第三列排序，依此类推。
例 21、查找工资高于$2000 的雇员信息，按部门号和雇员名字排序。 SQL>SELECT ENAME，SAL，DEPTNO FROM EMP WHERE SAL>2000 ORDER BY DEPTNO，ENAME；
ENAME CLARK SCOTT KING FORK
SAL 12450 3000 5000 3000
DEPTNO
10 20
JONES 2975 BLAKE 2850 6 rows selected.
20 30
注意：如果排序列含有空值（如上述的 DEPTNO） ，则含有空值的行首先显 示，而不管选择升序还是降序形式。 GROUP BY 子句和 HAVING 子句将放到 1.5.2 节“聚组函数”具体论述。 4、表达式 在 SQL 命令中还可以使用加（+） 、减（—） 、乘（*） 、除（/）运算符构成 的表达式，其中乘、除运算的优先级高于加、减运算。算术表达式的处理顺序从 左到右， 括弧可以改变处理的顺序。 例如， （SAL+COMM） =12*SAL+COMM。 12* ！ 这些运算符可以用于 SQL 命令的 SELECT 子句、WHERE 子句、ORDER BY 子 句以及后面还要讲述的 GROUP BY 子句及 HAVING 子句中， 但不能在 FROM 子 句中使用运算符。
例 22、选择奖金高于其工资的 5%的雇员。 SQL>SELECT ENAME，SAL，COMM，COMM/SAL FROM EMP WHERE COMM>0.05*SAL ORDER BY COMM/SAL DESC；
ENAME SAL MARTIN 1250 WARD 1250 ALLEN 1600 3 rows selected.
COMM 1400 500 300
COMM/SAL 1.12 0.4 0.1875
1．2．2 数据更新：插入、修改和删除 在数据库应用中，经常要对存放的数据进行更新操作，以满足不断变化的 需求，因此对数据进行插入、修改和删除就成为必不可少的一项工作。 1、数据的插入 当需要为一个基表增加新的数据时，就要向基表中插入数据，这是由 INSERT 命令来实现的。 INSERT 命令有两种用法：一种是将指定的具体数值插入表中，通过关键字 VALUES 来实现； 另一种是将其他表中满足条件的数据插入到一个表中， 这是通 过 SELECT 子句实现的。 在插入数据时，应首先确认基表已经创建，然后确定基表的结构，基表的 各列顺序、类型以及是否为非空（NOT NULL） ，可以通过 DESCRIBE 命令来查 看，以保证插入数据的类型与基表列的类型匹配。若插入字符型和日期型数据， 要用单引号括起来。 （1）用 VALUES 插入。 向基表中插入指定数据的 INSERT 命令格式是 INSERT INTO [(列名表)] VALUES（） ； 其中：指出要插入数据的基表名；指出在新插入行中的哪些列 要插入数据， 这是可选项， 如果选择， 则表示相应的列要插入数据， 如果不选择， 则默认表中所有的列均要插入数据；指出要插入列的具体值。
例 1、为 DEPT 表插入一行新数据。 SQL>INSERT INTO DEPT
VALUES （10， ‘ACCOUNTING’‘NEW YORK’； ， ） DEPT Table
DEPTNO 10
DNAME LOC ACCOUNTING NEW YORK
注意：当不选择时，中的值应与基表的各列一一对应。如 果在 INSERT 命令中给出列名，则要求中的值与中的列名一一 对应。
例 2、 SQL>INSERT INTO DEPT（DNAME，DEPTNO） VALUES （ ‘ACCOUNTING’ ，10） ； DEPT Table
DEPTNO 10
DNAME LOC ACCOUNTING
插入时，表名中的列如果未在 INSERT 的中出现，那么这些列则 为空值，如上例中的 LOC 列为空值。也可以为某列插入“NULL” ，将该列置为 空。插入空值的列，必须保证该列在建立时没有被定义成 NOT UNLL 属性，否 则系统拒绝插入。 （2）用 SELECT 命令插入。 INSERT 命令可以使用 SELECT 语句从其他基表中选择数据，插入基表中。 其命令格式是 INSERT INTO[(列名表)] SELECT 语句；
例 3、 SQL>INSERT INTO EMP（EMPNO，ENAME，DEPTNO） SELECT ID，NAME，DEPARTMENT FROM OLD_EMP WHERE DEPARTMENT IN （10，20，30，40） ；
同使用 VALUES 类似，在使用 SELECT 命令时，应保证 INSERT 中的各列与 SELECT 子句中选中的各列一一对应，类型匹配。 （3）INSERT 命令中使用参数。 INSERT 命令中还可以使用参数来接收用户输入的数据，每个参数以&为前 缀，后接参数名（可以取列名） 。
例 4、 SQL>INSERT INTO DEPT VALUES （&DEPTNO，&DNAME，&LOC） ；
在执行该命令时，SQL*PLUS 将提示用户输入每个参数。重复执行该命令 可以接收不同的数据，快速地向表中插入多条记录。 若参数对应的是日期型或字符型数据时，可以在参数上加引号，这样用户 输入数据时就不用再输入引号了。
例 5、 SQL>INSERT INTO DEPT VALUES （&DEPTNO， ‘&DNAME’‘&LOC’； ， ）
（4）插入日期型数据。 在插入日期型数据时，日期必须带单引号且以缺省的 ORACLE 的日期格 式出现，即‘DD-MON-YY’格式。
例 6、加入一名雇员。 SQL>INSERT INTO EMP （EMPNO，ENAME，HIREDATE） VALUES （7963， ‘STONE’‘07-APR-95’； ， ）
如果想输入当前的系统时间，可以使用 SYSDATE。如果要插入一个非缺省 格式的日期，可以使用 TO-DATE 函数进行转换。关于 TO-DATE 函数将在 1.5
函数一节中详细论述。 2、数据的修改 在 ORACLE 中，对数据的修改是使用 UPDATE 命令来实现的。UPDATE 命令的格式如下： UPDATE SET=，=„„ [WHERE ]； UPDATE 命令对由指定的基表进行修改。修改时，对表中满足条件 的行将用的值替换相应列的值。
例 7、将 Martin 提升为经理。 SQL>UPDATE EMP SET JOB=‘MANAGER’ WHERE ENAME=‘MARTIN’ ；
如果例 7 中不使用 WHERE 子句，则 EMP 表中的每一行都将被置为 SET 子句中的数值。 UPDATE 命令不但可以修改一行的某一列数据， 还可以修改若干 行的几列数据。
例 8、将所有销售人员的工资增加 20%，并转入部门 40。 SQL>UPDATE EMP SET DEPTNO=40，SAL=SAL*（1+0.02） WHERE JOB=‘SALESMAN’ ；
（1）在 UPDATE 中使用 SELECT 命令。 在 UPDATE 命令中可以嵌入 SELECT 语句设置修改值。
例 9、 SQL>UPDATE EMP SET （SAL，COMM）= （SELECT SALARY，COMM FROM OLD_EMP WHERE DEPARTMENT=10） WHERE DEPTNO=10；
需要修改的列必须出现在括号内，并以逗号隔开。被设置的值通过括号内 的 SELECT 语句获得，注意：应保证 SET与 SELECT 子句中选择的各列 一一对应，并且 SELECT 语句最多返回一行结果，若未返回任何行，则被修改 的各行的有关列被置成 NULL。 （2）用 NULL 进行修改。 修改数据时，也可以将基表中的某列设置为 NULL，这里使用空值是用等 号而不是“IS” 。
例 10、 SQL>UPDATE EMP SET COMM=NULL WHERE JOB=‘SALESMAN’ ；
3、数据的删除 对表中数据进行删除是使用 DELETE 命令实现的， DELETE 命令格式如下： DELETE FROM [WHERE ]； DELETE 命令是从由指定的表中删除满足的行。WHERE 是可选项， 若只删除指定的几行， 则必须使用 WHERE 子句， 如果 WHERE 子句缺省，则删除表中的全部数据。
例 11、Martin 离开公司，应从公司中除名。
SQL>DELETE FROM EMP WHERE ENAME=‘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．4 SQL*PLUS 的环境命令
1．4．1 环境设置命令 SQL*PLUS 的环境特征参数一般由系统自动设置，用户可以根据需要将环 境参数设置成自己所需要的值，SET 命令和 SHOW 命令主要就是完成这项功能 的。 SET 命令将 SQL*PLUS 的环境特征参数设置为开关状态或者某个确定的值。 使用 SHOW 命令将显示这些参数的值。 1、SET 命令 使用 SET 命令可以改变 SQL*PLUS 环境特征参数的值。其命令格式是： SET 其中选项是指环境参数的名称，指该参数被设置成 ON 还是 OFF，或是某个具体的值。
例 1、设置自动提交状态。 SQL>SET AUTOCOMMIT ON
下面给出几个常用的环境参数设置（其中有下划线者为系统的缺省值） ： （1）SET AUTOCOMMIT{OFF|ON|IMMEDIATE} OFF——关闭自动提交功能。 ON 或 IMM——打开自动提交功能。 （2）SET ECHO{OFF|ON} ON——SQL*PLUS 执行命令文件时，将命令本身显示在屏幕上。 OFF——执行命令文件时，命令本身不显示在屏幕上。 （3）SET FEEDBACK{OFF|ON} ON——查询结束时，给出查询结果的记录数信息。 OFF——查询结束时，没有查询结果的记录数信息。 （4）SET HEADING {OFF|ON} ON——各列的标题（包括文字和下划线）在结果报表上显示。
OFF——各列的标题不在报表上显示。 （5）SET LINESIZE{80|n} 该 项 设置 SQL*PLUS 的行 宽， 即一 行所能 显 示的 最大 字符 数。 当 用 SQL*PLUS 命令制图报表标题为居中或右对齐时， 系统在计算标题的合适位置时 也要用到这个参数。该参灵敏的缺省值为 80 个字符，最大值为 999。 （6）SET PAGESIZE{14|n} 该参数设置每页输出的行数，包括 TTITLE（头标题） 、BTITLE（底标题） 、 COLUMN（列标题）和空行。该项缺省值是每页 14 行。 （7）SET PAUSE{OFF|ON|text} ON——在显示输出每一页后，等待用户按 RETURN 键继续显示。 OFF——表示每页显示之间不停顿。 Text——在显示每一页后停顿，等待用户按 RETUNR 键，并在屏幕下 方显示 text 的提示信息。 （8）SET BUFFER {buffer} 设置{buffer}为当前的命令缓冲区。通常情况下，SQL 缓冲区为当前命令缓 冲区。
例 2、建立名为 A 的缓冲区： SQL>SET BUFFER A
由于 SQL 缓冲区只能存放一条 SQL 命令，所以可以用 SET BUFFER 命令 设置其他命令缓冲区，来存放多条 SQL 命令和 SQL*PLUS 命令。 用户可以使用 SQL*PLUS 的行编辑——LIST，DEL，APPEND，CHANGE 等命令对该缓冲区中的所有行操作， 也可以用 SAVE 命令将该缓冲区中的内容保 存到一个文件中，并可以用 GET 命令将文件中的内容取回到缓冲区。但该命令 缓冲区不能直接执行 SQL 或 SQL*PLUS 命令，而是通过将其中的内容保存到文 件中，再使用 START 命令来完成。不论是来自于命令行还是命令文件，只要执 行一个 SQL 命令，命令缓冲区就被自动置回为 SQL 缓冲区。但其他缓冲区中的 内容依然存在，可以再次使用 SET BUFFER 命令进入所需要的缓冲区。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>SHOW ALL
该命令显示 SQL*PLUS 全部环境参数的值。
例 4、 SQL>SHOW AUTOCOMMIT
该命令显示自动提交状态的设置情况。
例 5、 SQL>SHOW USER
该命令将显示当前的用户标识，即当前的用户名。
例 6、 SQL>SHOW TTITLE
该命令显示当前报表使用的头标题的内容。 关于 SHOW 命令的详细说明请参见第二部分的第一章。 1．4．2 用 SQL*PLUS 生成报表 SQL*PLUS 通常被认为是一种交互式的报表生成器，它使用 SQL 命令从 ORACLE 数据库中获取信息，使用 SQL*PLUS 进行设置，就能产生精炼的、有 良好格式的报表，使你很容易地对题目、列标题、部分及总和进行控制，对数字 和文字重新编排格式。
例 1、需要生成下面一张简单报表：
Mon Mar12 SAMPLE REPORT for HITECH CORP DEPARTMENT 10 sum 20 SALARY $2,450.00 $5,000.00 $7,450.00 FORD $3,000.00 SCOTT $3,000.00 JONES $2,975.00 $8,975.00 BLAKE $2,850.00 $2,850.00 STRICTLY CONFIDENTIAL NAME CLARK KING
page1
sum 30 sum
可以使用下面的命令来完成： SQL>COLUMN DEPTNO HEADING DEPARTMENT SQL>COLUMN ENAME HEADING NAME SQL>COLUMN SAL HEADING SALARY SQL>COLUMN SAL FROMAT $99,999.00 SQL>TTITLE ‗SAMPLE REPORT for|HITECH CORP‘ SQL>BTITLE ‗STRICTLY CONFIDENTIAL‘ SQL>BREAK ON DEPTNO SQL>COMPUTE SUM OF SAL ON DEPTNO SQL>SELECT DEPTNO，ENAME，SAL FROM EMP WHERE SAL>2000 ORDER BY DEPTNO； 应注意一点，SQL*PLUS 格式命令的效果只有通过运行 SQL 命令才能够看 到。与制作报表有关的 SQL*PLUS 命令见表 1.4。 表 1.4
命 TTITLE BTITLE 令 定 义 为报表的每一页设置头标题 为报表的每一页设置底标题
COLUMN BREAK COMPUTE SET LINESIZE SET PAGESIZE SET NEWPAGE SET HEADSEP
设置列的标题和格式 将报表中的数据分组显示并设置组间间隔 计算分组数据的汇总值 设置报表每行允许的最大字符数 设置每页的最大行数 设置页与页之间的空行数 设置标题分隔符
1、设置标题：TTITLE 和 BTITLE 从例 1 中能够看出，我们可以为报表的每一页设置头标题和底标题（也就 是表头和表尾） ，它们是通过 TTITLE 和 BTITLE 命令来实现的。它们的命令格 式如下： TTITLE[位置说明][OFF|ON] BTITLE[位置说明][OFF|ON] 关于标题的位置说明可以使用下列子句：
子 句 COL n SKIP n LEFT CENTER RIGHT 举 例 COL 72 SKIP 2 LEFT CENTER RIGHT 说 明 让标题信息从当前行左边的第 n 个位置开始显示 打印 n 个空行，如果 n 未指明，打印一个空行；如果 n 为 0，则不 打印空行；如果 n 大于 1，则为两行文字间加入 n-1 个空行 标题信息靠左放置 标题信息居中放置 标题信息靠右放置
例 2、 SQL>TTITLE CENTER ‗ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT‘ SQL>BTITLE CENTER ‗COMPANY CONFIDENTIAL‘ SQL>SELECT DEPTNO，ENAME，SAL FROM EMP WHERE DEPTNO=30；
ACME WIDGET SALES DEPARTMENT PERSONNEL REPORT DEPTNO ENAME SAL ················· ················· ·············· ··············· ················· 30 ALLEN 1600 WARD 1250 MARTIN 1250 BLAKE 2850 TURNER 1500 JAMES 950 COMPANY CONFIDENTIAL 6 rows selected.
如果想将上表的标题变换成更清楚的形式， 可以增加一些子句， 并使用 SET LINESIZE 进行设置；
SQL>TTITLE CENTER‘ACME WIDGET‘ SKIP 1>CENTER= = = = = = = = = = = = = = = = = = =SKIP 1 LEFT ‗PERSONNEL REPORT‘>RIGHT ‗SALES DEPARTMENT‘ SKIP 2 SQL>SET LINESIZE 60 SQL>/
报表显示如下：
ACME WIDGET = = = = = = = = = = = = = = = = = = = = = = = == = = = = = = = = = PERSONNEL REPORT SALES DEPARTMENT DEPTNO ENAME SAL ················································································
ALLEN WARD MARTIN BLAKE TURNER JAMES COMPANY CONFIDENTIAL 6 rows selected.
30
1600 1250 1250 2850 1500 950
关键字 LEFT， RIGHT 和 CENTER 确定了其后紧跟的内容在页上显示位置； SKIP 表示在打印完该行后将打印多少空行；行未尾的破折号“—”表明标题命 令未完，紧接下一行内容，在单引号内的正文将被如实打印。这个例子中最后一 行数据与表尾之间没有定义空行， 若需要在它们之间加入空行， 可以使用 SKIP n 子句，如例 3 所示。
例 3、 SQL>BTITLE SKIP 1 CENTER ‗COMPANY CONFIDENTIAL‘
另外还可以使用 COL 子句对表头和表尾的位置进行缩排。例如：COL1 表 示标题显示在第一列的位置， 即靠左侧放置。 COL15 表示标题显示在第 15 列上， 左侧留出 14 个空格。当需要将一行标题分成两行显示时，可以使用标题分隔符 ——竖线（|） ，SQL*PLUS 可以根据标题中出现的分隔符“|”将标题的内容分多 行显示。在后面讲述的 COLUMN 命令的 HEADING 子句中也可以使用分隔符 “|” 。
例 4、要将标题显示成下列形式： Mon Mar 12 page 1 SAMPLE REPORT for HITECH CORP
则要使用下述命令：
SQL>TTITLE ‘SAMPLE REPORT for|HITECH CORP’
若某些键盘上没有“|” ，可以使用 SET HEADSEP 命令，将标题分隔符设置 成另一个字符，例如：
SQL>SET HEADSEP！
那么标题分隔符就补设置为一个感叹号（!） 。 应当注意，若选择一个可能会出现在标题或列标题中的字符为分隔符，将 会导致意料不到的分割。 如果标题文本超过 500 个字符，可以使用 SQL*PLUS 的 DEFINE 命令，将 各行的文本内容保存在不同的变量中。例如：
SQL>DEPIN LINE1=‘This is the first line……‘ SQL> DEPIN LINE2=‘This is the second line……‘ SQL> DEPIN LINE3=‘This is the third line……‘
在 TTITLE 和 BTITLE 中可以使用上面定义的变量.。 SQL>TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE3 另外， 还可以控制页号的显示位置和格式。 SQL.PNO 是一个用来存储当面号 的变量，通过该变量可以对面号进行控制。
例 5、 SQL>TTITLE LEFT‘ACME WIDGET‘PAGE:‘FORMAT 999 SQL.PNO SKIP2 SQL>/
ACME WIDGET S
PAGE：1
DEPTNO ENAME AL „„„„„„„„„„„„„„„„„„
30
ALLEN WARD MARTIN BLAKE TURNER JAMES COMPANY CONFIDENTIAL 6 rows selected.
1600 1250 1250 2850 1500 950
如果不定义页号显示格式，SQL.PNO 的宽度为 9 位。 若希望显示标题定义的情况，可以使用不带子句的命令： TTITLE BTITLE 若取消定义的标题在报表上显示，但不清除已定义的标题，可以使用命令 TTITLE OFF BTITLE OFF 若想恢复定义的标题在报表上显示，使用命令 TTITLE ON BTITLE ON 若想清除已经定义的标题，使用命令 TTITLE CLEAR BTITLE CLEAR 2．设置报表尺寸 每页报表中都包含表头、列标题、查询的结果和有尾信息。报表尺寸的设置 对于这些内容的正确显示都是十分必要的。系统缺省的报表尺寸如下：每页报表 表头空一行；每页输出内容为 14 行（包括表头和表尾之间的所有内容） ；每行所 能显示的字符数为 80。可以通过 SET 命令改变上述设置。 （1）SET NEWPAGE 命令 该命令设置每一页的表头与每一页开始位置之间的空行数， 实际上就是页与 页之间的行数，命令如下： SET NEWPAGE 行数 如果设置行数为 0，系统将在每页的日期前产生一个顶部格式字符（通常是 16 进制的 13） 。大部分打印机立即响应这个字符并将打印头移至下一页的开始， 即报表打印的起始位置。 如果将 NEWPAGE 设置变大，SQL*PLUS 输出的信息行就会减小。而每页 的总行数不变。 （2）SET PAGESIZE 命令 该命令设置每页的输出行数，包括表头、表尾、列标题和查询出的信息。对 于一般的打印纸，该值通常设置为 66。命令如下： SET PAGESIZE 行数 SET PAGESIZE 命令一般与 SET NEWPAGE 相关使用。 （3） SET LINESIZE 命令 该命令控制出现在一行上的最大字符数。命令如下： SET LINESIZE 字符数 如果一行查询结果的总宽度超过了 LINESZIE 设置的行宽，SQL*PLUS 将 把多出的列折行输出。 LINESIZE 的大小还会影响表头、 日期和页码的放置位置，
因为表头的居中显示和居右显示要根据 LINESIZE 的值确定。
例 6、 重新设置新的报表尺寸： SQL>SET PAGESIZE 66 SQL>SET NEWPAGE 0 SQL>SET LINESIZE 32
若要恢复系统缺少设置，则执行下列命令：
SQL>SET PAGESIZE 14 SQL>SET NEWPAGE 1 SQL>SET LINESIZE 80
3．设置列 COLUMN 使用 SQL*PLUS 的 COLUMN 命令可以改变列标题及各列数据的显示格式。 （1）设置列标题 SQL*PLUS 使用列名和列表达式名作为列标题的缺省形式， 如果需要改变列 标题，可以使用 COLUMN 的 HEADING 子句： COLUMN 列名 HEADING 列标题
例 7、将查询 EMP 表的结果生成报表，并为各列定义明确的标题： SQL>COLUMN DEPTNO HEADING Department SQL>COLUMN ENAME HEADING Employee SQL>COLUMN SAL HEADING Salary SQL>COLUMN COMM HEADING Commission SQL>SELECT DEPTNO，ENAME,SAL，COMM FROM EMP WHERE JOB=‘SALESMAN‘；
查询结果显示如下：
Department Employee Salary Commission „„„„„„„„„„„„„„„„„„„„„„„„„„ 30 ALLEN 1600 300 WARD 1250 500 MARTIN 1250 1400 TURNER 1500 0 4 rows selected.
设置的这些列标题一直有效，直到他们被重新设置或用户退出 SQL*PLUS. 还可以使用 SET UNDERLINE 命令为列标题设置不同的形式的下划线，例如：
SQL>SET UNDERLINE= SQL>/
Department Employee Salary Commission „„„„„„„„„„„„„„„„„„„„„„„„„„ 30 ALLEN 1600 300 30 WARD 1250 500 30 MARTIN 1250 1400 30 TURNER 1500 0 4 rows selected.
要恢复原来的下划线，使用下列命令：
SQL>SET UNDERLINE‘－’
（2）设置列的格式 所有的数据类型都有缺省的显示格式，如果需要数据指定的格式显示，可 以使用 COLUMN 命令，命令格式是 COLUMN 列名 FORMAT 格式 数值型数据使用 9 作为位数描述符，并可以加入逗号、$、尖括号、、/或前 导 0 等字符。
例 8、显示工资值 SAL，加入$和， ，并使用 0 表示个位。 SQL>COLUMN SAL FORMAT $99,990 SQL>/
Department Employee Salary Commission „„„„„„„„„„„„„„„„„„„„„„„„„„ 30 ALLEN $1,600 300 30 WARD $1,250 500 30 MARTIN $1,250 1400 30 TURNER $1,500 0 4 rows selected.
字符型数据的缺省显示宽度为该字符数据定义的宽度，如果字符列的标题宽 度超过字符数据宽度，则显示的宽度以列标题为准。 LONG 型显示的宽度由 SET LONG 命令设置， 其缺省设置宽度为每行 80 个 字符。DATE 型一般缺省显示格式为 DD－MON－YY，宽度为 9 个字符。如果 DATE 型数据没有使用 TO－CHAR 函数进行转换，则该数据缺省的格式由参数 文件中的初始化参数确定。 CHAR， VARCHAR2） VARCHAR〕 LONG 以及 DATE ， 型数据使用 COLUMN 语句，以字母 A 作为格式描述符，来改变数据的显式格 式， 而且这些数据以左对齐方式显示， 如果定义的数据显示格式宽度比列标题短， 列标题将会被截断。
例 9 给 ENAME 列定义标题为 EMPLOYEE NAME，显示宽度为 4 个字符。 SQL>COLUMN ENAME HEADING‘Employee|Name‘ SQL>COLUMN ENAME FORMAT A4 SQL>/ Empl
Department Name Salary Commission „„„„„„„„„„„„„„„„„„„„„„„„„„ 30 ALLE $1,600 300 N 30 WARD $1,250 500 30 MART $1,250 1400 IN 30 TURN $1,500 0 ER 4 rows selected.
由于 ENAME 以 4 个字符宽度显示， 其列标题 “Employee‖被截断成 “Empl” , 而其数据宽度大于 4 个字符的， 则要折行显示。 如果需要将超出宽度的内容截断， 可以使用 SETWRAP OFF 命令来设置，其缺省状态为 ON，即不截断状态。 （3）参照列的显示属性 当用户想为更多的列定义相同的显示属性时，可以使用 COLUMN 的 LIKE 子句来简化命令语句。 SQL*PLUS 的 LIKE 子句可以为新的一列拷贝已经定义的 列显示属性。
例 10、为 COMM 列定义与 SAL 列相同的显示格式，并为 COMM 列定义标题“Bonus‖: SQL>COLUMN COMM LIKE SAL HEADING Bonus SQL>/
Empl Department Name Salary Bonus „„„„„„„„„„„„„„„„„„„„„„„„„„ 30 ALLE $1,600 $300 N 30 WARD $1,250 $500
MART IN 30 TURN ER 4 rows selected.
30
$1,250 $1,500
$1,400 0
（4）显示和重置列的显示属性 若想显示某一列的显示属性，可以使用命令 COLUMN 列名 若想显示所有列的显示属性，则使用命令 COLUMN 如果想将某列的显示属性重置成缺省形式，可以使用 COLUMN 命令的 CLEAR 子句： COLUMN 列名 CLEAR 若希望将所有列的显示属性重新置成缺省的形式，则使用下列命令： SQL>CLEAR COLUMNS columns cleared. 另外，还可以使用 COLUMN 的 OFF，ON 子句对列的显示属性进行抑制或 恢复。使用 OFF 子句 COLUMN 列名 OFF 表示 SQL*PLUS 将以缺省格式作为列的显示属性，但并不取消列属性，使 用 ON 子句，则重新恢复列的显示属性： COLUMN 列名 ON 4、BREAK 和 COMPUTE 命令 当在 SELECT 命令中使用 ORDER BY 子句时， 会将数据按某一列的值排序， 该列值相同的各行数据将会排列在一起输出，为了使输出的内容更为有用和清 晰，可以使用 BREAK 和 COMPUTE 命令，将数据分成小的记录子集，计算各 个子集的汇总值，并用空格将各个子集分隔开。例如，下面的查询中可以使用 BREAK 和 COMPUTE 命令：
SQL>SELECT DEPTNO，ENAME，SAL FROM EMP WHERE SALBREAK ON DEPTNO SQL>SELECT DEPTNO，ENAME，SAL FROM EMP WHERE SALBREAK ON DEPTNO SKIP 1 SQL>/
DEPTNO ENAME SAL „„„„„„„„„„„„„„„„„„„„ 10 CLARK 2450 MILLER 1300 20 SMITH ADAMS 800 1100 1600 950 1500 1250 1250
ALLEN JAMES TURNER WARD MARTIN 9 rows selected.
30
如果希望在每行信息之间插入空行或换成新页，则使用下列命令： BREAK ON ROW SKIP n
BREAK ON ROW SKIP PAGE 注意：SKIP PAGE 间隔的行数是你所定义一页的行数。 若 ORDER BY 子句中使用了多列，则也要在多列上使用 BREAK 命令，并 设置不同的间隔大小。
例 13、希望不同 DEPTNO 值之间空一页，不同 JOB 之间空一行。 SQL>BREAK ONDEPTNO SKIP PAGE ON JOB SKIP 1 SQL>SELEC DEPTNO,JOB,ENAME,SAL FROM EMP WHERE SALBREAK ON DEPTNO SKIP PAGE ON JOB SKIP 1 SQL>COMPUTE SUM OF SAL ON DEPTNO SQL>SELECT DEPTNO，JOB，ENAME，SAL FROM EMP WHERE SALBREAK ON REPORT SQL>COMPUTE SUM OF SAL ON REPORT SQL>SELECT ENAME，SAL FROM EMP WHERE JOB=‘SALESMAN‘；
ENAME SAL ————— —————
ALLEN 1600 WARD 1250 MARTIN 1250 TURNER 1500 ————— ————— sum 5600 4 rows selected.
另外还可以计算全部数据的汇总值及各个分组的汇总值： BREAK ON break 列名 ON REPORT COMPUTE fumction OF 列名 ON break 列名 COMPUTE fumction OF 列名 ON REPORT 还可以计算多个相同类型列的汇总值。
例 16、计算所有销售员的工资、资金汇总值。 SQL>COMPUTE SUM OF SAL COM ON REPORT SQL>.SELECT ENAME，SAL，COMM FROM EMP WHERE JOB=‘SALESMAN‘；
ENAME SAL COMM ……………………………………………… ALLEN 1600 300 WARD 1250 500 MARTIN 1250 1400 TURNER 1500 0 ……………………………………………… sum 5600 2200 4 rows selected.
还可以计算某列的多个汇总值： COMPUTE fumction fumction„„ OF 列名 ON break 列名
例 17、列出 30 号部门的雇员及他们工资平均值和总和。 SQL>BREAK ON DEPTNO SQL>COMPUTE AVG SUM OF SAL ON DEPTNO SQL>.SELECT DEPTNO，ENAME，SAL FROM EMP WHERE DEPTNO=30 ORDER BY DEPTNO，SAL；
DEPTNO ENAME SAL ………………………………………… 30 JAMES 950 WARD 1250 MARTIN 1250 TURNER 1500 ALLEN 1600 BLAKE 2850 „„„„„„„„„„„„„„„„ avg 1566.67 sum 9400 6 rows selected.
如果需要查看已经定义的 COMPUTE 命令，可以输入 SQL>COMPUTE 我们还可以使用带 COMPUTES 子句的 CLEAR 清除 COMPUTE 的设置： SQL>CLEAR COMPUTES Computes cleared
（5）BREAK 和 COMPUTE 小结 从上面的论述可以看出，COMPUTE 命令与 BREAK 命令密切相关。 COMPUTE 命令一般是计算由 BREAK ON 具体指定的那一部分内容的汇总值。 COMPUTE 命令必须有相应的 BREAK 命令， 且关键字 ON 后面的内容应该一致。 反之，BREAK 命令却可以单独使用，不必有 COMPUTE 命令对应，但使用这两 个命令必须遵循以下原则： ·每一个 BREAK ON 必须要有一个相关的 ORDER BY 子句。 ·每一个 COMPUTE 必须有一个相关的 BREAK ON。 正确地掌握这两条原则就可以顺利地使用这两条命令。 另外，COLUMN、BREAK、COMPUTE 命令只作用于在 SELECT 语句中 出现的列，对其他内容不起作用。
例 18、 SQL>COLUMN JOB FORMAT A10 SQL>BREAK ON JOB SKIP PAGE SQL>SELECT DEPTNO，ENAME，SAL FROM EMP WHERE SALCOLUMN MGR NEW-VALUE MGRVAR NOPRINT
首先将 MGR 列的内容放到变量 MGRVAR 中。NOPRINT 关键字告诉
SQL*PLUS，当打印 SQL 语句的执行结果不打印该列，即在查询结果中不显示 MGR 列。然后就可以使用变量 MGRVAR 设置报表的表头和分组间隔，完成报 表打印。
SQL>TTITLE LEFT ‘Manager：‘ MGRVAR SKIP 2 SQL>BREAK ON MGR SKIP PAGE SQL>BTITLE OFF SQL>SELECT MGR，ENAME，SAL，DEPTNO FROM EMP WHERE MGR IN(7698,7839) ORDER BY MGR；
Manager：7698 ENAME SAL DEPTNO „„„„„„„„„„„„„„„„ ALLEN 1600 30 WARD 1250 30 TURNER 1500 30 MARTIN 1250 30 JAMES 950 30 Manager：7839 ENAME SAL DEPTNO „„„„„„„„„„„„„„„„ JONES 2975 20 BLAKE 2850 30 CLARK 2450 10 8 rows selected.
若想将列值作为表尾标题，可以使用命令 COLUMN 列名 OLD-VALUE 变量名 关键字 OLD-VALUE 的用法与 NEW-VALUE 相似，这里不再重述。 6、查询结果的存储和打印 使用 SQL*PLUS 的 SPOOL 命令可以将查询结果存储到文件中或直接送打 印机输出。将内容送到文件中的命令是 SPOOL 文件名 SQL*PLUS 将该命令之后屏幕上出现的所有信息存贮到指定的文件中，其 中包括命令执行中的提示信息和错误信息。除非文件名中有“.扩展名” ，否则文 件名的后缀由系统设置，后缀名随系统而定，一般后缀多为“.LST” “.LIS” 。 如果将查询结果直接送到打印机输出，可以用 SQL>SPOOL OUT SPOOL 将一直存储或打印信息，直到执行下列命令： SQL>SPOOL OFF
1.5
SQL*PLUS 中使用的函数
SQL 函数是用于处理一个数据项并返回结果的运算。 函数可以接受零个或多 个参数，带不同参数的函数完成不同的运算，并产生一个或多个结果。函数的一 般格式是 函数名（参数） 如果参数是多个，则为
函数名（参数 1，参数 2，……参数 N） 其中：参数是用户提供的常量或者变量。 如果调用函数的参数数据类型不同于函数所要求的数据类型，则 ORACLE 在执行这个函数前，将这个参数的数据类型隐式地转换为函数所要求的数据类 型。如果调用的函数带有空值参数，则函数自动返回一个空值。 通常根据函数值的数据类型可将函数划分为三类： ·数值函数 ·字符串函数 ·日期函数 如果根据函数返回的查询结果是一行或多行，可将函数分成两类： ·单行函数 ·聚组函数 单行函数和聚组函数的根本区别在于行数上的不同。 单行函数返回查询的表 或视图中的一行，它对表或视图进行水平方向（横向）计算，所产生的结果对于 表的每行均有一个值。 聚组函数返回一组查询的行，它对表或视图进行垂直方向 （纵向）计算，所产生的结果是一个单值。 1.5.1 单行函数 单行函数可以出现在 SELECT 命令的 SELECT 子句、WHERE 子句、ORDER BY 子 句、START WITH 子句和 CONNECT BY 子句中。 单行函数具有以下特点： ·基于单行返回结果。 ·要求零个或多个参数。 ·参数允许是其他单行函数产生的结果。 ·对查询返回的各行进行分别计算。 ·能够用于相同数据类型的变量、列名或表达式出现的地方。 下面根据函数值的类型不同分别讨论。 1、 字符串函数 字符串函数接收字符型输入数据，返回字符型或数值型结果。表 1.5 列出了 SQL*PLUS 提供的字符串函数。 表 1.5 函数 功能 注释 ASCII (char) 计算 char 的第一个字符 函数返回值取决于计算机 的 ASCII 码 值 或 系统采用的字符集 EBCDIC 码值 CHAR (n) 计 算 ASCII 码 值 或 参数 n 依赖于计算机系统 EBCDIC 值是 n 的字符 采用的字符集，n 的取值在 0-127 或 0-254 之间 INITCAP (char) 将 char 串中的每个单词 单词之间用数字、空格、逗 的首字母变成大写，其 号、顿号、冒号、分号、句 余字母变为小写 号、!、@、#、$、等符号分 隔
求 char1 中从 m 位置起 m,n 缺省值为 1。 n>0 时， 当 char2 第 n 次出现的位 表示从 char1 的首部起始 置 （从左向右） 正向搜索； nSELECT EMPNO，UPPER(ENAME)，INITCAP (ENAME) FROM EMP WHERE DEPTNO=10；
EMPNO UPPER(ENAME) INITCAP(ENAME) ………………………………………………………… 7782 CLARK Clark 7839 KING King
7934 MILLER 3 rows selected.
Miller
2、 数值函数 数 值 函 数 接 受 数 值 型 输 入 数 据 ， 返 回 数 值 型 的 结 果 ， 表 1.6 列 出 了 SQL*PLUS 提供的数值函数。 表 1.6 函数 功能 注释 ABS（n） 计算 n 的绝对值 n 许允是数值型常数、 数值型列 名、包含一个有效数值的文字 串或包含一个有效数值的字符 型列名 CEIL(n) 计算大于或等于 n 的最小整 数学中的向上取整运算 数 FLOOR(n) 计算大于或等于 n 的最大整 数学中的向下取整运算 数 MOD(m,n) 计算 m 除以 n 的余数 n=0 MOD（m,1）=0 说明 m 是一个 时，返回 m 整数 POWER(m,n) 计算 m 的 n 次方 n 要求为整数，否则出错 ROUND(m,[,n]) n>0 时， m 四舍五入到小 n=0 或 n 被省略表示对 m 进行 将 数点右 取整。边 n 位；n0， 函数返回 1； n=0, 判断 n 的正负。数返回 0；n0 时，将 m 小数点右边 n n=0 或 n 被省略表示对 m 进 位后各 行取整。位截断；nSELECT ENAME, ROUND (SAL/25,0),ROUND (SAL/25,-1) FROM EMP WHERE DEPTNO=10;
ENAME ROUND（SAL25，0） ROUND（SAL/25，-1） …………………………………………………………………… CLARK 98 100 KING 200 200 MILLR 52 50 3 rows selected.
3、 日期函数 除了 MONTHS-BETWEEN 之外，日期函数返回日期型结果。 表 1.7 列出了 SQL*PLUS 提供的日期函数，其中 d 表示日期值。 表 1.7 函数 功能 注释 ADD-MONTHS（d,n） 计算 d 加上 n 个月的日 n 求为整数。N>0 时，返回 d 之
LAST-DAY(d) MONTHS-BETWEEN (d1,d2) NEXT-DAY(d,s)
SYSDATE
例3
后 n 个月的日期。 n0 时， 表示 d1 晚于 d2； 的月数 返回结果SELECT ENAME,SAL,NEXT-DAY(SYSDATE,‘FRIDAY‘)AS-OF FROM EMP WHERE DEPTNO=20;
ENAME SAL AS-OF …………………………………………… SMITH 1800 21-Nov-95 JONES 2975 21-Nov-95 SCOTT 3000 21-Nov-95 ADAMS 1100 21-Nov-95 FORD 3000 21-Nov-95 5 rows selected.
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 分类 日期 定义 格式 月份 格式 Dd Dy Day Ddspth Mm 说明 用数字表示几号 星期几的缩写 星期几全拼形式 序数词表示几号 用数字表示月份 例子 12 fri Friday Twelfth 03
（指 12 号） （星期五缩写） （星期五全拼） （12 号） （数字 3 月）
定义 格式 年份 格式
Mon Month Yy yyyy
月份的英文缩写形式 月份的英文全拼形式 两个数字表示年份 四个数字表示年份
mar march 95 1995
（三月份缩写） （三月份全拼） （即 1995 年） （即 1995 年）
可以根据上面给出的格式来定义不同形式的日期，注意字母大小字的意义 不同，如：day,DAY，Day 转换的结果将是不同的大小写内容。举例说明如下：
格式说明 ‗Mon dd, yyyy‘ ‗MON dd, yyyy‘ ‗Day MONTH dd‘ ‗Dy,ddspth‘ ‗Mon dd hh:mi am‘ 例 4 查询 10 号部门雇员的信息。
SQL>SELECT ENAME，TO_CHAR （HIREDATE, ‗Dy Mon dd,yyyy‘）HIRED FROM EMP WHERE DEPTNO=10; ENAME HIRED
内 容 Mar 12, 1995 MAR 12, 1995 Thursday MARCH 12 Thu, twelfth Mar 12 11:00 am
从上面的例子可以看出，定义的格式不同，可以将内容转换成不同的形式。
CLARK Tue Jun 09,1981 KING Tue Nov 19,1981 MILLER Sat Jan 23,1982 3 rows selected.
使用 TO_CHAR 函数将日期型数据转换成所需的字符串形式。
例5 SQL>INSERT INTO EMP (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>SELECT ENAME , COMM+SAL, NVL （COMM, 0）+SAL FROM EMP WHERE DEPTNO=30;
ENAME COMM+SAL NVL（COMM，0）+SAL …………………………………………… ALLEN 1900 1900 WARD 1750 1750 MARTIN 2650 2650 BLAKE 2850 TURNER 1500 1500
JAMES 6 rows selected.
950
由于 Blake 和 James 的 COMM 值为 NULL，所以 COMM+SAL 值也为 NULL，使用了空值函数后，结果将不为空。 6、 其他函数 ORACLE 支持的函数中，有的可以支持多种数据类型的值或不要求用户提 供参数。 （1）GREATEST （参数 1[，参数 2]……） 该函数计算参数 1，参数 2……中的最大值，参数可以为字符型、日期型或数 值型等类型， 计算时第一个参数之后的所有参数均被转换到比较之前第一个参数 的数据类型。 （2）LEAST （参数 1[，参数 2]……） 该函数的使用方法与 GREATEST 相同， 只是它的功能是求参数列表中的最 小值。
例7 查询哪些雇员的雇员号比经理号小。
SQL>SELECT ENAME，EMPNO，MGR， LEAST（EMPNO，MGR） LOWNUM FROM EMP WHERE EMPNO SELECT * FROM EMP ORDER BY DECODE （&ORDERBY，1，ENAME，2，SAL，ENAME） ；
Enter value for orderby：2 Old 3：ORDER BY DECODE（&ORDERBY，1，ENAME，2，SAL，ENAME） New 3：ORDER BY DECODE（2，1，ENAME，2，SAL，ENAME）
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO „„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„„ 7876 ADAMS CLERK 7788 12-Jan-95 1100 20 7521 WARD SALESMAN 7698 22-Feb-93 1250 500 30 7654 MARTIN SALESMAN 7698 28-Feb-93 1250 1400 30 7934 MILLER CLERK 7782 23-Jan-94 1300 10 7844 TURNER SALESMAN 7698 08-Sep-93 1500 0 30
7499 ALLEN 7782 CLARK 7698 BLAKE 7566 JONES 7788 SCOTT 7902 FORD 7839 KING 7369 SMITH 7900 JAMES 14 rows selected.
SALESMAN MANAGER MANAGER MANAGER ANALYST ANALYST PRESIDENT CLERK CLERK
7698 7839 7839 7839 7566 7566 7902 7698
20-Feb-93 09-Jun-93 01-May-93 02-Apr-93 09-Dec-94 03-Dec-93 17-Nov-93 17-Dec-92 03-Dec-93
1600 2450 2850 2975 3000 3000 3000 800 950
300
30 10 30 20 20 20 10 20 30
在这个例子中，给参数赋值 2，按照 DECODE 函数值进行排序，即结果 按 SAL 列排序。 应当注意： 在这里 SAL 列被转换成字符型， 因为它要与 ENAME 列的类型保持一致，结果按数字型数据的排序转换成字符型数据的排序，因而 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>SELECT SUM（COMM） FROM EMP； SUM（COMM） 2200 1 row selected.
从这个例子可以看出， 聚组函数与单行函数的不同之处是：聚组函数是返回
一个汇总信息而不是一条条单行信息， 而且它对空值的处理与单行函数也完全不 同。聚组函数忽略 NULL 值，尽管有 NULL 值也计算一个结果，而不是用空值 进行操作。 所有的聚组函数都适用于数值型数据，只有 MIN，MAX，COUNT 可用于 任何数据类型。
例2 函数 MIN 适用于字符型数据类型。
SQL> SELECT MIN（ENAME） FROM EMP； MIN（ENAME） ADAMS 1 row selected.
例3
聚组函数 MIN 适用于日期型数据。
SQL> SELECT MIN(HIREDATE) FROM EMP； MIN(HIREDATE) 17-DEC-91 1 row selected.
例 4 聚组函数 MIN 适用于数值型数据。
SQL > SELECT MIN（SAL） FROM EMP； MIN（SAL） 800 row selected.
1
聚组函数 MAX 和 COUNT 的用法与 MIN 相同。 聚组函数在调用时有两种选项：ALL 和 DISTINCT。ALL 表示对一组行中 所有表达式的值进行计算， 这里包括了表达式有重复值的情况，在上面讨论的例 子都是采用这种选项。 另一个选项 DISTINCT 表示仅对一组行中不同的表达式的 值进行计算，即重复值不计算在内。缺省的选项为 ALL，可以忽略不写，若选 择 DISTINCT 选项，则采用下列格式： 聚组函数名（DISTINCT e）
例 5 计算多少人有工作，则只要计算 JOB 列不为空的记录数即可。
SQL>SELECT COUNT (JOB) FROM EMP; COUNT(JOB) 14 row selected.
1
这里查出的是有工作的人数。其中有的雇员工种相同，即 JOB 列有重复值 的也计算在内了。如果希望查找有多少种不同的工种，则要筛掉 JOB 列的重复 值。
例6
SQL>SELECT COUNT(DISTINCT JOB) FROM EMP； COUNT （DISTINCT JOB） 5 1 row selected.
从以上两个例子的对比中，读者就能理解 DISTINCT 的含义了。
2、 GROUP BY 子句 当我们在 SELECT 子句中使用聚组函数时，又希望同时获得单行函数。例 如， 希望分别计算出各个部门的工资总和。 根据需要查询的内容， 写出下列命令：
SQL>SELECT DEPTNO，SUM（SAL） FROM EMP；
这个语句的意图是显示出不同的部门号及该部门的工资总和。 但屏幕上将出 现下列错误信息：
…ERROR…NOT A SINGLE GROUP SET FUNCTION
这是因为单行函数和聚组函数不能混在一起使用，它们将对同一组数据产生 不同个数的结果， 除非利用 SELECT 语句的 GROUP BY 子句查询结果进行分组。 如果未使用 GROUP BY 子句，则 ORACLE 将为查到的基表或视图中的所有行 提供聚组函数， 即把整个查询结果视为一组。 如果 SELECT 语句中使用 GROUP BY 子句，则 ORACLE 把查询到的基表或视图中的行划分成多个组，并为每组 返回一个结果。
例 7 计算第个部门的平均工资和工资总和。
SQL >SELECT DEPTNO,SUM(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO SUM(SAL) AVG (SAL) 2916.67 2175.00 1566.67
10 8750.00 20 10875.00 30 9400.00 3 rows selected.
注意，使用 GROUP BY 子句后，数据按部门分成三组，每组只返回一条记 录。从例子中可以看出，每组至少有一列的列值相同，即在每组中，GROUP BY 子句后的那一列必定是相同的。 有时还要将各个分组再进行细划，分出不同的小 组，那么就可以在 GROUP BY 子句中使用多个分组标准来进行。
例 8、查询每个部门的每个工种的雇员数。
SQL>SELECT DEPTNO，JOB，COUNT（*） FROM EMP GROUP BY DEPTNO，JOB；
DEPTNO JOB COUNT(*) „„„„„„„„„„„„„„„„„„„ 10 CLERK 1 10 MANAGER 1 10 PRESIDENT 1 20 ANALYST 2 20 CLERK 2 20 MANAGER 1 30 CLERK 1 30 MANAGER 4 30 SALESMAN 9 rows selected.
在这个例子中，首先按部门将雇员分成若干组，在同一部门内的雇员再按 他们的工种进一步分组，计算出小组的分组值。 应当注意，在包含 GROUP BY 子句的查询中，SELECT 子句的列表中的所 个体值（除聚组函数外）必须是 GROUP BY 子句中的表达式或者常量，否则出
错。
例如： SQL>SELECT ENAME，DEPTNO，SUM（SAL） FROM EMP GROUP BY DEPTNO； SELECT ENAME，DEPTNO，SUM（SAL） * ERROR at line 1； ORA—00979：not a GROUP BY expression
在 SELECT 子句中有 ENAME 和 DEPTNO 两个个体值列， 而在 GROUP BY 子句中只有一个 DEPTNO。当按部门分组后每组产生一条记录，但每个部门却 还有多个雇员的 ENAME，所以该命令的执行必定是错误的，应该保证 SELECT 子句给出的所有列名或表达式在每一个分组中必须具有唯一性。 3、HAVING 子句 如果需要对各个组返回的结果进行筛选，查询满足一定条件的分组值，可 以使用 HAVING 子句。HAVING 子句的作用与 WHERE 子句相似，都是给出查 询条件。所不同的是，WHERE 是检查每条记录是否满足条件，而 HAVING 子 句是检查分组后，各组是否满足条件。 从这里可以看出，WHERE 子句的条件是针对 SELECT 子句的，而 HAVING 子句的条件是针对 GROUP BY 子句的，没有 GROUP BY 子句就不能使用 HAVING 子句。
例 9、 哪些部门的工资总和超过了$9000？
SQL>SELECT DEPTNO，SUM（SAL） FROM EMP GROUP BY DEPTNO HAVING SUM（SAL）>9000；
DEPTNO SUM(SAL) ……………………………… 20 10875 30 9400 2 rows selected.
执行时，先按 DEPTNO 分组，分组后并不是每一组都是所需的信息，还需 要进一步检查每一组的工资总和是否大于$900。 如果大于$900， 则是所需的信息， 否则不是所需的信息， 在这里检查每组是否满足条件使用的就是 HAVING 子句。 如果还希望对各组的结果进行排序，可以在 HAVING 子句后增加 ORDER BY 子句，但这里使用的 ORDER BY 子句必须要用聚组函数或 GROUP BY 子句 中的列。
例 10 对部门工资总和超过$9000 的部门按工资总额由小到大顺序排列。
SQL>SELECT DEPTNO，SUM（SAL） FROM EMP GROUP BY DEPTNO HAVING SUM（SAL）>9000 ORDER BY SUM（SAL） ；
DEPTNO SUM(SAL) ……………………………… 30 9400 20 10875 2 rows selected.
一般来说，聚组函数可以出现 SELECT 子句的列表中，HAVING 子句和 ORDER BY 子句中。在使用时，要注意它们与 GROUP BY 子句的关系， 以免 发生错误。 4、SELECT 命令小结 在 SELECT 命令中，WHERE 子句和 HAVING 子句还可以一起使用。现将 SELECT 命令小结如下： SELECT 列名 1，列名 2，…列名 n FROM 表名 1，表名 2，…表名 n [WHERE 列选择条件为真] GROUP BY 列名 1，列名 2，…列名 n [HAVING 分组条件为真] [ORDER BY 列名 1，列名 2，…列名 n]； SELECT 子句和 FROM 子句是必选的，FROM 子句后也可以是视图名，其 他子句都是可选的。 HAVING 子句是在有 GROUP BY 子句的前提下才可以使用。 系统在执行这个完整的查询语句时，首先根据 WHERE 子句选择满足条件 的行；然后根据 GROUP BY 对选中的行进行分组，并计算每个组的聚组函数值； 接着根据 HAVING 子句选择某些组作出结果； 最后根据 ORDER BY 子句里的聚 组函数值对各组数据进行排序显示。 下面给出一个包含上述所有子句的例子，供读者参考。
例 11 除去职员， 哪些部门的工资总和超过了$8000(不包括奖金， 并按工资升序显示)？
SQL>SELECT DEPTNO，SUM（SAL） FROM EMP WHERE JOB！=‘CLERK’ GROUP BY DEPTNO HAVING SUM（SAL）>8000 ORDER BY SUM（SAL） ；
DEPTNO SUM(SAL) ……………………………… 30 8450 20 8975 2 rows selected.
1.6 高级查询
1.6.1 表的连接 在数据库应用中， 经常要同时涉及两个或两个以上的表，需要通过两个以上的表 才能构造出所期望的结果， 这就要使用连接操作。若被处理的诸表的列之间毫无 联系，则利用“笛卡尔积”将各表中的各行组合起来。两个表的“笛卡尔积”形 式如下： SELECT 列名 11，列名 12，…，列名 1m，列名 21，…，列名 2n FROM 表名 1，表名 2 其中：列名 11，列名 12，…，列名 1m（m>0）是表名 1 中的列，而列名 21，列名 22，…，列名 2n（n>0）是表名 2 中的列。 进行“笛卡尔积”实际上就是一种无条件连接，这种操作会生成大量的行，
其结果却没有多大意义。因而在相关的各表间进行操作时往往要加上限制条件， 再进行连接运算。 连接运算就是利用各表的相关列之间的比较关系控制各表中的行的组合， 根据比较关系的不同， 连接运算分为等值连接和非等值连接。另外根据连接对象 的不同又有两种特殊的连接：自连接和外连接。 1、等值连接 等值连接要求参与联接运算的两个表在公共列上具有相同的值。SELECT 语 句可以将两个表进行连接操作。 连接时，将两个表中的所有记录在有关列上进行 比较，检查它们是否满足条件。其命令如下： SELECT … FROM 表名 1，表名 2 WHERE 表名 1.列名 2=表名 2.列名 2； 其中： 给列名加前缀——表名，是为了避免被连接表中各个被连接列同名时 产生二义性。 两个基表进行等值连接的过程是：首先从第一个表中取出第一条记录，然 后从头到尾扫描另一个表的全部记录， 分别检查每条记录是否在连接属性上与第 一个表的第一条记录相等。如果相等，则将这两个记录连接，生成新表的一条记 录。当处理完第一个表的第一条记录后，再取第二条记录，扫描另一个表的全部 记录。 如此重复直至处理完第一个表的全部记录。 如果连接时还有其他限制条件， 则将满足条件的记录进行上述操作。
例 1、查看工资高于$2500 的雇员及所在部门的情况。
SQL>SELECT ENAME ，SAL，EMP.DEPTNO，LOC，DNAME FROM EMP，DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL>2500； ENAME SAL DEPTNO 20 30 20 10 20 LOC DALLAS CHICAGO DALLAS NEW YORK DALLAS DNAME RESEARCH SALES RESEARCH ACCOUNTING RESEARCH
JONES 2975 BLAKE 2850 SCOTT 3000 KING 5000 FORD 3000 5 rows selected.
其中：SELECT 子句中的 DEPTNO 列加了前缀 EMP.，这是为了防止不同 基表的同名列产生二义性，因为 DEPT 表中也有 DEPTNO 列。 在连接条件中使用的列是几个被连接表的公共列，它们不要求具有相同的 名字。但因为要进行比较操作，所以两个列必须具有相同的定义域，即数据类型 和宽度相同，这是作为连接条件所必须具备的。 不仅可以对两个表进行连接操作，还可以对两个以上的表进行连接操作。 连接时应遵循下面两条规律： ·连接条件数恰好比被连接的表数少 1； ·一个被联接表的主关键字是多列组成时，则对该主关键字中的每一列均 要有一个连联接条件。 2、非等值连接 非等值连接就是指连接条件中不使用“=”运算符的连接运算。非等值连接 能够使用的比较运算符包括=、 ！=、、=、BETWEEN…AND 和 LIKE 等。
例 2、下面给出一张新的基表：SALGRADE，存放着工资等级的信息。
SALGRADE GRADE 1 2 3 4 5 SQL>SELECT FROM WHERE AND ENAME Table LOSAL 700 1201 1401 2001 3001 HISAL 1200 1400 2000 3000 9999
需要根据该表查找哪些雇员的工资属于第三级别：
ENAME，SAL EMP，SALGRADE GRADE=3 SAL BETWEEN LOSAL SAL
AND HISAL；
ALLEN 1600 TURNER 1500 2 rows selected.
其中：做为连接条件的比较运算符不是“=” ，而是 BETWEEN…AND。 3、自连接 连接操作不仅可以作用在几个不同的基表上，而且，同一个表可以进行自 身连接，将同一个表的不同行连接起来。 自连接可以把一个表看成有两个副本，即两个相同的表，然后再对这两个 表在相关列上进行连接，其连接过程与多表连接完全相同。 表自连接时，需要不同的表别名来对同一个表的两个副本加以区分。表自 连接的一般命令格式如下： SELECT… FROM 表名 表别名 1，表名 表别名 2 WHERE 表别名 1.列名 1=表别名 2.列名 2； 其中：列名 1 和列名 2 标识自连接表中的两个不同列，这两列要求有相同 的数据类型和宽度。
例 3、指出每个雇员的经理的名字。
SQL>SELECT WORDER.ENAME，MANAGER.ENAME MANAGER FROM EMP WORKER，EMP MANAGER WHERE WORDER.MGR=MANAGER.EMPNO； ENAME MANAGER SCOTT JONES FORD JONES ALLEN BLAKE WARD BLAKE MARTIN BLAKE TURNER BLAKE JAMES BLAKE MILLER CLARK ADAMS SCOTT JONES KING BLAKE KING CLARK KING SMITH FORD 13 rows selected.
这个例子中为 EMP 分别起了 WORDER 和 MANAGER 别名，实际上在自连接时， 只要为一个基表起别名以区别连接条件中的列名即可，不必非起两个别名不可。 4、外连接 在使用前面几种连接时，如果连接条件满足，系统则取有关的数据；如果 条件不满足，系统则不取相应的数据。而外连接则不同，它不仅返回两个或两个 以上的表中能够直接匹配的行， 还返回一个表中无法从其他表中找到直接匹配的 行。其命令格式如下： SELECT… FROM 表名 1，表名 2 WHERE {表名 1.列名 1=表名 2.列名 2(+) | (+)表名 1.列名 1=表名 2.列名 2}； 其中： （+）是外连接运算符，按它在“=”符号的左边或右边分为左外连接和右 外连接，其运算结果是不同的。进行外连接时，若不带（+）运算符的表中的一 行不直接匹配于事（+）运算符的表中的任何行，则前者中的行与后者中的一个 虚拟空行相匹配，经过连接后被返回。
例4 SQL>SELECT ENAME,DEPT.DEPTNO，LOC FROM EMP，DEPT WHERE EMP.DEPTNO(+)=DEPT.DEPTNO； ENAME DEPTNO LOC CLARK MILLER KING SMITH SCOTT JONES ADAMS FORD ALLEN BLAKE TURNER JAMES MARTIN 15 rows 10 10 10 20 20 20 20 20 30 30 30 30 30 40 selected. NEW YORK NEW YORK NEW YORK DALLAS DALLAS DALLAS DALLAS DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO BOSTON
如 果 DEPT.DEPTNO 中 有 的 数 值 ， 在 EMP.DEPTNO 中 没 有 ， 如 DEPT.DEPTNO=40，在做外连接时，结果中会为 EMP 表的列产生空值。如果外 连接运算符（+）放在另一侧，结果将会不同。 外连接还允许（+）运算符同时出现于连接条件中“=”符号的两侧。于是， 各个被连接表中无法找到匹配的全部行，经过以空值 NULL 填补相应部分后， 均作为外连接的结果被返回。 此外，在一个 SELECT 语句的 WHERE 子句中，出现比较运算符 NOT IN 是一个最慢的逻辑测试，因为经在 SELECT 子查询中要读遍整个表。
例 5、使用 NOT IN 完成下列查询：
SQL>SELECT FROM WHERE DEPT.DEPTNO，DNAME，LOC DEPT DEPT.DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE ENAME=‘CLERK‘)； DNAME LOC NEW YORK
DEPTNO 10
ACCOUNTING
30 40 3 rows
SALES OPERATIONS selected.
CHICAGO BOSTON
为加快查询，可以利用外连接 NOT IN 运算符，替换方法如下： ·NOT IN 形式： SELECT 表名 1.列名 1··· ··· FROM 表名 1 WHERE 表名 1.列名 1 NOT IN （SELECT 列名 2 FROM 表名 2 WHERE 列名 3=表达式） ； ·使用外连接的形式： SELECT 表名 1.列名 1··· ··· FROM 表名 1，表名 2 WHERE 表名 1.列名 1=表名 2.列名 2（+） AND 表名 2.列名 2 IS NULL AND 表名 2.列名 3（+）=表达式； 上面的例子可改用外连接来实现：
SQL>SELECT DEPT.DEPTNO， DNAME，LOC FROM DEPT，EMP WHERE EMP.DEPTNO=EMP.DEPTNO（+） AND EMP.DEPTNO IS NULL AND EMP. ENAME(+)=‘CLERK‘； DEPTNO DNAME LOC 10 30 40 ACCOUNTING SALES OPERATIONS NEW YORK CHICAGO BOSTON
因 为 对 两 个 表 进 行 外 连 接 ， 就 包 括 了 DEPT.DEPTNO=40 的 数 据 ， EMP.DEPTNO 为 NULL 仅产生那些不出现在 EMP 表中， 并且 EMP.ENAME （+） =‘CLERK’增加了名字不叫‘CLERK’但在 EMP 表中的职员所在的部门。使 用外连接来代替 NOT IN 将会提高查询的速度。 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 子句中提供进行比较的值，其格式是
DELETE WHERE FROM 表名 列名或列表达式 比较运算符（SELECT 列名 FROM 表名 WHERE 条件） ；
在 INSERT 语句中确定往表中加载的行，格式是
INSERT INTO（列名，列名，·） ·· SELECT （列名，列名，·） ·· FROM 表名 WHERE 列名或列表达式 比较运算符（SELECT 列名 FROM 表名 WHERE 条件） ；
在 CREATE TABLE 命令中确定往表中加载的行，格式是
CREATE TABLE 新表名 AS SELECT 列名，列名，· ·· FROM 表名 WHERE 列名或列表达式 比较运算符（SELECT 列名 FROM 表名 WHERE 条件） ；
1、 单行值子查询 当子查询返回单一行时，所有用于单值测试的逻辑运算符都可以使用。
例1、 查询工资高于 7698 号雇员，并且工种与他相同的雇员情况。
SQL>SELECT ENAME，SAL，JOB FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE EMPNO=7698) AND JOB=(SELECT JOB FROM EMP WHERE EMPNO=7698)； DNAME SAL JOB JONES 2975 1 row selected. MANAGER
系统在执行时，首先执行子查询，获取 7698 号雇员的工资$2850，工种为
MANAGER，然后将子查询返回的结果带到上层查询语句的 WHERE 子句中，通过等 号（=）和大于号（>）与子查询联系起来。 在 UPDATE 语句中也可以使用子查询。
例 2、将所有与 7698 号雇员同工种的职工的部门号改成 7698 号雇员所在的部门。
SQL>UPDATE EMP SET DEPTNO=(SELECT DEPTNO FROM EMP WHERE EMPNO=7698) WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7698)；
在该例中，子查询不但可以作为查询的条件，还可以用来提供修改值。 在子查询中可以使用聚组函数。
例 3、查询工资高于或等于 30 号部门工资最高额的雇员。
SQL>SELECT ENAME，SAL，DEPTNO FROM EMP WHERE SAL>=（SELECT MAX（SAL） FROM EMP WHERE DEPTNO=30） ； ENAME SAL DEPTNO JONES 2975 LBAKE 2850 SCOTT 3000 KING 5000 FORD 3000 5 rows selected. 20 30 20 10 20
在子查询中使用聚组函数，得到 30 号部门工资的最高额为$2850，将其结果 带到主查询中实现查询。 2、多行值子查询 如果子查询返回的不是单一行而是一组行时，就是多行值子查询。这种子 查询必须用下面的多值比较运算行与其主查询相联系： •[ NOT ] IN •[ NOT ] ANY •[ NOT ] ALL •[ NOT ] EXISTS 上述谓词均可以加 NOT 表示相反。 如果子查询返回多行值却使用单值比较运算符，执行将会出错。 （1）谓词 IN IN 表示属于集合或是某集合中的成员的关系，NOT IN 则表示不属于集合或 不是集合中的成员。
例 4 查询工资在$1000 到$5000 之间的雇员所有部门的所有人员的信息。
SQL>SELECT ENAME ,SAL FROM EMP WHERE DEPTNO IN(SELECT DEPTNO) FROM EMP WHERE SAL BETWEEN 1000AND 5000); ENAME SAL SMITH 800 ALLEN 1600
WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARR 2450 SCOTT 3000 RING 5000 TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 14 rows selected.
执行时， 首先执行嵌入的子查询， EMP 表中找出所有工资在$1000 到$5000 从 之间的雇员所在的部门号，执行的结果是一组值（10，20 和 30） 。然后再从 EMP 表中取出每行数据，分别检查它们的 DEPTNO 值是否属于这一组值中的一个，即 是否等于其中的一个值（等于 10，20 或 30 均可） 。如果等于则满足条件，被查 询出来。 （2）ANY ANY 就是将一个值与子查询返回结果中的一个值进行比较。在 ANY 前可以使 用=、 ！=、、=等比较运算符。
例 5 还是上面的例子使用 ANY 来实现。
SQL >SELECT ENAME ,SAL FROM EMP WHERE DEPTNO=ANY(SELECT DEPTNO FROM EMP WHERE SAL BETWEEN 1000AND 5000);
在这个查询中，ANY 后面是一个子查询，这个子查询的结果是{10，20，30}，那么这个 查询语句就变成了 SELECT ENAME，SAL FROM EMP WHERE DEPTNO=ANY（10，20，30） ；
WHERE 子名的条件表示： 只要 DEPTNO 等于集合{10， 30}中的任何一个值， 20， 则条件满足。实际上“=ANY”的作用相当于 IN。在 ANY 前面不仅可以使用等号， 也可以使用！=、>、=、SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL、=等比较运算符。
例 7 查找工资高于 20 号部门所有雇员的人员信息。 SQL> SELECT EMPNO,ENAME,SAL
FROM EMP WHERE SSAL>ALL(SELECT SAL FROM EMP WHERE DEPTNO=20); EMPNO ENAME SAL 7839 KING 5000 1 row selected.
在这个查询中，执行 ALL 后的子查询等到 20 号部门雇员的工资，实际上该 命令就是查找工资比子查询结果中的最大值高的雇员。
例 8 查询与销售员不在同一个部门工作的雇员信息。
SQL>SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE DEPTNO!=ALL(SELECT DEPTNO FROM EMP WHERE JOB=‘SALESMAN’； ） EMPNO ENAME DEPTNO 7369 SMITH 7566 JONES 7788 SCOTT 7782 CLARK 7839 KING 7876 ADAMS 7902 FORD 7934 MILLER 8 rows selected. 20 20 20 10 10 20 20 10
实际上，这里使用的“！=ALL”等价于 NOT IN。 （4）EXISTS EXISTS 表示一个子查询至少返回一行时条件成立。NOT EXISTS 表示一个 子查询不返回任何行时条件成立。EXISTS 与前面计的谓词 IN，ANY，ALL 带子查 询的方式一样，但有三点不同之处： ① 它不能匹配一个列或多个列； ② EXISTS 只能用于子查询中，而 IN，ANY，ALL 还可以用在与值列表的 连接中，如：
SQL>SELECT ENAME，JOB FROM EMP WHERE JOB=ANY（ ‘CLERK’‘ANALYST’ ， ）
③EXISTS 一般用于相关的子查询中。 关于相关子查询将在后面介绍。 例 9、查找那些雇员的部门号在部门表中没有的雇员信息。
SQL>SELECT EMPNO, DEPTNO, ENAME FROM EMP WHERE NOT EXISTS(SELECT * FROM DEPT WHERE DEPTNO=EMP.DEPTNO） ； No rows selected.
使用 EXISTS 表示只要这个子查询的结果不为空，则满足条件；子查询为 空，则条件不满足。 在使用子查询时应注意以下几点： ①子查询必须用括号括住。 ②子查询必须只有一旬，或者子查询的多列与主查询中用括号括起来的
多个列连接。 ③单行值子查询可以使用单值或多值运算符，多行子查询必须使用多值 运算符。 ④BETWEEN …… AND 不能用于子查询。 ⑤子查询中不能使用 ORDER BY 子句。 ⑥ 子查询可以对主查询中没有使用的基表操作，也可以查询多个基表。
例 10、查询在纽约的雇员中，有哪些人的工资高于在 DALLAS 工作的 SCOTT（假设公司 中有同名的 SCOTT） 。
SQL>SELECT ENAME，JOB，SAL，EMP.DEPTNO FROM EMP，DEPT WHERE LOC=‘NEW YORK’ AND EMP.DEPTNO= DEPT.DEPTNO AND SAL> (SELECT SAL FROM EMP，DEPT WHERE EMP.DEPTNO= DEPT.DEPTNO AND ENAME=‘SCOTT’ AND DEPT.LOC=‘DALLAS’； ） ENAME JOB SAL DEPTNO KING PRESIDENT 5000 1 row selected. 10
⑦子查询不能用在函数调用的参数中。例如：
SELECT MAX（SELECT SAL FROM EMP WHERE DEPTNO=30）……
就是错误的，子查询返回的结果不能当作函数调用的参数来使用。 ⑧查询嵌套最多为 255 层，对于多层嵌套，系统总是先执行最里面的一层 子查询，然后逐层外推，直至全部完成。
例 11、
SQL>SELECT ENAME，JOB，HIREDATE FROM EMP WHERE SAL> (SELECT MAX（SAL） FROM EMP WHERE DEPTNO IN （SELECT DEPTNO FROM DEPT WHERE UPPER （DNAME） LIKE‘SALES%’） ）； ENAME JOB HIREDATE JONES MANAGER SCOTT ANALYST KING PRESIDENT FORD ANALYST 4 rows selected. 02-APR-81 09-NOV-81 17-NOV-81 03-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>SELECT ENAME FROM EMP WHERE （NVL(COMM，0)，SAL）IN (SELECT NVL(COMM，0)，SAL FROM EMP WHERE DEPTNO =20)； ENAME SMITH ADAMS JONES SCOTT FORD 5 rows selected.
例 13 将 7698 号雇员的工种和部门号改为 7499 号雇员的工种和部门号。
SQL>UPDATE EMP SET（JOB，DEPTNO）=（SELECT JOB，DEPTNO FROM EMP WHERE DEPTNO =7499) WHERE EMPNO=7698；
4、相关子查询 相关子查询是子查询的一种形式。当一个子查询涉及到来自上层查询的列 时，该子查询就是相关子查询。它要为主查询中的每一行执行一次子查询，因而 关于子查询的每一步均依赖于主查询中特定行中的值。相关子查询一般被用于 SELECT，UPDATE 或 DELETE 语句中。 （1）用在 SELECT 语句中。
SELECT 选择列表 1
FROM 表名 1 WHERE 列或列表达式样比较运算符 （SELECT 选择列表 2 FROM 表名 2 WHERE 表名 2.列名 比较运算符 表名 1.列名） ；
（2）在 UPDATE 语句中，相关子查询被用来根据另一个表中的行来修改一 个表中的行。
UPDATE 表名 1 SET 列名= （SELECT 列或列表达式 FROM 表名 2 WHERE 表名 2.列名 比较运算符 表名 1.列名） ； WHERE 条件；
(3)在 DELETE 语句中，相关子查询被用来根据另一个表中的行的存在性来 删除一个表中的一行。
DELETE FROM 表名 1 WHERE 列名= （SELECT 列或列表达式 FROM 表名 2 WHERE 表名 2.列名 比较运算符 表名 1.列名） ；
如果进行比较的列名意义模糊，容易产生二义性，就必须使用表名或表别名 的前缀。
例 14 查出哪些雇员的工资高于他所在的部门的平均工资。
SQL>SELECT DEPTNO，ENAME，SAL FROM EMP OUTER WHERE SAL>(SELECT AVG（SAL） FROM EMP WHERE DEPTNO =OUTER.DEPTNO)；
DEPTNO 30 20 30 20 10 20
ENAME
SAL 1600 2975 2850 3000 5000 3000
6
ALLEN JONES BLAKE SCOTT KING FORD rows selected.
在这个查询中，为区分主查询与子查询中使用的 EMP 表，在主查询中为该 表起了别名 OUTER。系统执行时，首先从主查询的表 OUTER 中取出第一条记 录带到子查询中，执行子查询的 SELECT 语句，计算出第一条记录的部门号所 指的部门雇员平均工资。然后再招待主查询，检查 OUTER 的第一条记录的 SAL 是否大于这个平均工资。如果大于，则满足条件，取出这条记录的 DEPTNO， ENAME 和 SAL 列的值，否则不取。这样 OUTER 中的第一条记录就处理完了。 接着处理 OUTER 中的第二条记录， 当 OUTER 中所有的记录都处理完毕，整 个查询命令执行结束。
例 15 将部门名称放到雇员 EMP 中。 先用 ALTER 命令为 EMP 表增加一个新列 DEPT_NAME。
SQL>ALTER TABLE EMP ADD （DEPT_NAME CHAR （15）； ）
SQL>UPDAT EMP SET DEPT_NAME=(SELECT DNAME FROM DEPT WHERE DEPTNO=EMP.DEPTNO) WHERE EXISTS (SELECT DNAME FROM EMP WHERE DEPTNO =ENP.DEPTNO)；
只有使用了相关子查询， 才能将雇员所在的部门名称添加到雇员表中去。从 上面的例子可以看出， 常规子查询与相关子查询的区别表现在，前者为整个主查 询仅执行一次子查询，而后者却要为主查询的每一行招待一次子查询。
1．6．3 集合运算 集合运算就是把两个或更多的查询结果合并为一个结果。集合运算包括 UNION（并操作） 、INTERSECT（交操作）和 MINUS（差操作） 。 1、并操作：UNION UNION 操作是将属于一个查询结果的行或者属于另一个查询结果的行组合 起来，构成 UNION 操作的结果。如果有一个行既属于一个查询结果，又属于另 一个查询结果，则只取出其中一行。如图 1.1 所示。
A A B 图 1.1 例 1 下面有三个以 EMP 为基表的视图：
A UNION B
ORACLE 基础 ACCOUNT ENAME View SAL JOB
CLARK KING MILLER
2450 5000 1300
MANAGER PRESIDENT CLERK
SALES View EAME ALLEN WARD MARTIN BLAKE TURNER JAMES SAL 2450 5000 1300 1650 1500 950 JOB MANAGER PRESIDENT CLERK MANAGER SALESMAN CLERK
RESEARCH View ENAME SAL JOB
SMITH 800 CLERK JONES 2975 MANAGER SCOTT 3000 ANALYST ADAMS 1100 CLERK FORD 3000 ANALYST 根据给出的三个视图，查询所有部门中有哪些雇员的工资超过$ 2000.
SQL>SELECT ENAME，SAL FROM ACCOUNT WHERE SAL>2000 UNION SELECT ENAME，SAL FROM RESEARCH WHERE SAL>2000 UNION SELECT ENAME，SAL FROM SALES WHERE SAL>2000；
ENAME
SAL
CLARK FORD JONES KING SCOTT 5 rows selected.
2450 3000 2975 5000 3000
- 第56页 –，共 69 页
ORACLE 基础
2、交操作：INTERSECT INTERSECT 操作是将既属于一个查询结果的行又属于另一个查询结果的行组成 INTERSECT 操作的结果。如图 1.2 所示。
A B A UNION B
图 1.2
例2、 根据上面的三个视图查询各个部门中有哪些相同的工种。
SQL>SELECT JOB FROM ACCOUNT
INTERSECT SELECT JOB FROM RESEARCH
INTERSECTSELECT JOB FROM SALES；
JOB
CLERK MANAGER 2 rows selected.
3、差操作：MINUS MINUS 操作是从一个查询结果的行中去掉又属于另一个查询结果的行。因而 A MINUS B 与 B MINUS A 的结果往往是不同的。见图 1.3。
v
A UNIONA B
A UNION B B B UNION A
图 1.3
例 3、查找哪些工种是在财会部中有，而在销售部中没有。
SQL>SELECT JOB FROM MINUS SELECT JOB FROM JOB SALES； ACCOUNT
PRESIDENT
- 第57页 –，共 69 页
ORACLE 基础 1 row selected.
例 4、查找哪些工种在销售部中有，而在财会部中没有。
SQL>SELECT JOB FROM MINUS SELECT JOB FROM JOB ACCOUNT； SALES
PRESIDENT 1 row selected.
4、关于集合运算 关于集合运算有下述几点说明： （1） 参与集合运算的 SELECT 语句必须都是有效的 SELECT 语句， 各个 SELECT 语句均选择相同数目和同种数据类型的列；但相应的列允许列名和列宽度不同。在使 用 MINUS 运算时， WHERE 子句中的列既要包括在选择列表中， 也要包括在被 MINUS 的 SELECT 语句的选择列表中。 （2）允许多个查询结果进行集合运算，执行次序是先将两个查询结果进行集合运 算，其结果再与第三个查询结果进行运算，以此类推，直到将所有的查询结果都进行 完操作。利用括弧能够改变集合运算的优先次序。 （3）不能选择 LONG 列进行集合运算。 （4）关于集合运算结果的有序性如下： 集合运算符隐含着在所有的 SELECT 语句中的 DISTINCT，即集合运算消除重复 行，集合运算结果的各行自动地按 SELECT 语句选择的诸列被排序返回。允许利用 ORDER BY 子句改变排序方式。但要注意，ORACLE 忽略参与集合运算的各个 SELECT 语句选择的列名，故 ORDER BY 子句不允许引用列名，而仅能够引用相应 的列在选择列表中的位置（从左至右从 1 开始编号） 。
例如： SELECT 列名 1，列名 2 FROM 表名 1 UNION SELECT 列名 1，列名 2 FROM 表名 2 ORDER BY 列名 1；
ORDER BY 子句中使用列名 1 是错误的， 应该使用列名 1 在选择列表中的位置编 号，应该为：
SELECT 列名 1，列名 2 FROM 表名 1 UNION SELECT 列名 1，列名 2 FROM 表名 2 ORDER BY 1； 例 5、将例 1 的结果进行排序，先按 JOB 排序，再按工资、姓名排序。
- 第58页 –，共 69 页
ORACLE 基础 SQL>SELECT ENAME，JOB，SAL FROM ACCOUNT WHERE SAL>2000 UNION SELECT ENAME，JOB，SAL FROM RESEARCH WHERE SAL>2000 UNION SELECT ENAME，JOB，SAL FROM SALES WHERE SAL>2000；
ORDER BY 2，3，1； 其查询结果如下：
ENAME FORD SCOTT CLARK JONES KING JOB ANALYST ANALYST MANAGER MANAGER SAL 3000 3000 2450 2975
PRESIDENT 5000
5 rows selected
1.8 技巧及高级应用
1．8．1 树结构查询 ORACLE 是一个关系数据库管理系统，它用表的形式组织数据，在某些表中的数 据还是呈现出树型结构的联系。例如，在前面讲座中和到的雇员信息表 EMP，其中含 有雇员编号（EMP-NO）和经理号（MGR）两列，通过这两列反映出来的就是雇员之 间领导和被领导的关系。有些雇员领导另一些雇员，有些雇员被领导，还有些雇员领导 一些人又被别人领导，他们之间的这种关系就是一种树结构，图 1.4 表示了 EMP 表雇 员间的这种树结构。
KING EMPNO=7839 MGR IS NULL MGR=7698 EMPNO=7566 JONES MGR=7839 EMPNO=9698 BLAKE MGR=7839 DMPNO=7782 CLARK
MGR=7566
MGR=7566
MGR=7698
MGR=7698
MFR=7698
MGR=7698
MGR=7698
MGR=7782
SCOTT
FORD
ALLEN EMPNO=7499
WARD EMPNO=7521
MARTIN EMPNO=9654
TURNER EMPNO=7844
JAMES EMPNO=7900
MILLER EMPNO=7834
EMPNO=7788 EMPNO=7902 MGR=7788 ADAMS EMPNO=7876 MGR=7902 SMITH EMPNO=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 命令中使用 CONNECT BY 和 START WITH 子句可以查询表中的树型 结构关系。其命令格式如下： SELECT... CONNECT BY {PRIOR 列名 1=列名 2|列名 1=PRIOR 列名 2} [START WITH]； 其中：CONNCT BY 子句说明每行数据将是按层次顺序检索，并规定将表中数据连入 树型结构的关系中。PRIOR 运算符必须放在一侧表示父节点，在另一侧表示子节点， 从而确定查找树结构时的顺序是自顶向下还是自底向上。 在连接关系中， 除了可以使用 列名外，还允许使用列表达式、START WITH 子句为可选项，用来标识哪个节点为查 找树型结构的根节点， 若该子句被省略， 则表示以所有满足查询条件的行为人作为根节 点。
例 1 以树结构方式显示 EMP 表的数据。
SQL>SELECT EMPNO,ENAME,MGR FROM EMP
CONNECT BY PRIOR EMPNO=MGR START WITH ENAME=‘KING‘； EMPNO 7839 7566 7788 7876 7902 7369 7698 7499 ENAME KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN 7839 7566 7788 7566 7902 7839 7698 - 第60页 –，共 69 页 MGR
ORACLE 基础 7521 7654 7844 7900 7782 7934 WARD MARTIN TURNER JAMES CLARK MILLER 7698 7698 7698 7698 7839 7782
14 rows selected.
2、关于 PRIOR 运算符 PRIOR 被放置于等号前后的位置，决定着查询时的检索顺序。 PRIOR 被置于 CONNECT BY 子句中等号的前面时，则强制从根节点到叶节点的 顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
CONNECT BY PRIOR EMPNO=MGR
PRIOR 运算符被置于 CONNECT BY 子句中等号的后面时，则强制从叶节点到根 节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。 例如：
CONNECT BY EMPNO=PRIOR MGR
在这种方式中也应指定一个开始的节点。
例 2 从 SMITH 节点开始自底向上查找 EMP 的树结构。
SQL>SELECT EMPNO，NAME，MGR FROM EMP
CONNECT BY EMPNO=PRIOR MGR START WITH ENAME=‘SMITH’ ；
EMPNO 7369 7902 7566 7839
ENAME SMITH FORD JONES KING
MGR 7902 7566 7839
4 rows selected.
在这种自底向上的查找过程中，只有树中的一枝被显示，这是因为，在树结构中第 一个节点只允许有一个父节点，其查找过程是从开始节点起，找到其父节点，再由其父 节点向上，找父节点的父节点，这样一直找到根节点为止，结果就是树中一枝的数据。 3、定义查找起始节点 在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节 点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
例 3 查找 JONES 直接或间接领导的所有雇员信息。
- 第61页 –，共 69 页
ORACLE 基础
SQL>SELECT EMPNO，NAME，MGR FROM EMP
CONNECT BY PRIOR EMPNO=MGR START WITH ENAME=‘JONES’ ；
EMPNO 7566 7788 7876 7902 7369
ENAME JONES SCOTT ADAMS FORD SMITH
MGR 7839 7566 7788 7566 7902
5rows selected.
START WITH 不但可以指定一个根节点，还可以指定多个根节点。
例 4、查找由 FORD 和 BLAKE 领导的所有雇员的信息。
SQL>SELECT EMPNO，NAME，MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH ENAME IN（ ‘FORD’‘BLAKE’； ， ）
EMPNO 7698 7499 7521 7654 7844 7900 7902 7369
ENAME BLAKE ALLEN WARD MARTIN TURNER JAMRD FORD SMITH
MGR 7839 7698 7698 7698 7698 7698 7566 7902
8ows selected.
自底向上查询树结构时，也要指定一具开始节点，以此开始向上查找其父节点，直 到找到根节点，其结果将是结构树中的一枝数据。 4、使用 LEVEL 在具有树结构的表中， 第一行数据都是树结构中的一个节点， 由于节点所处的层次 位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论 从哪个节点开始，该起始根节点的层号始终为 1，根节点的子节点为 2，以此类推。图 1.5 就表示了树结构的层次。 层号 KING 1
- 第62页 –，共 69 页
ORACLE 基础
JONES
BLAKE
CLARK MILLER
2 3
SCOTT ADAMS
FORD ALLEN SMITH
WARD MARTIN TURNER
JAMES 4
图 1.5 EMP 表树结构层次图
在查询中，可以使用伪列 LEVEL 显示每行数据的有关层次，LEVEL 将返回树型结 构中当前行节点的层次，我们可以使用 LEVEL 来控制对树型结构进行遍历的深度。
例 5、显示 EMP 表中的各行数据及层号。
SQL>SELECT LEVEL，EMPNO=MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH ENAME IN=‘KING’ ；
LEVE 1 2 3 4 5 6 7 8 9 10 11 12 13 14
EMPNO 7839 7566 7788 7876 7902 7369 7698 7499 7521 7654 7844 7900 7782 7934
ENAME KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES CLARK MILLER
MGR
7839 7566 7788 7566 7902 7839 7698 7698 7698 7698 7698 7839 7782
14 rows selected.
伪列 LEVEL 为数值型，可以在 SELECT 命令中用于各种计算。
例 6 使用 LEVEL 改变查询结果的显示形式。
SQL>COLUMN EMPLOYEE FORMAT A 20 - 第63页 –，共 69 页
ORACLE 基础 SQL>SELECT EMPNO,RPAD(―,LEVE*3)||ENAME EMPLOYEE,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH ENAME IN=‘KING’ ；
EMPNO 7839 7566 7788 7876 7902 7369 7698 7499 7521 7654 7844 7900 7782 7934
ENAME KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES CLARK MILLER
MGR
7839 7566 7788 7566 7902 7839 7698 7698 7698 7698 7698 7839 7782
14 rows selected.
在 SELECT 子句中使用了函数 RPAD，该函数表示以 LEVEL*3 个空格进行填充。 由于不同行处于不同的节点位置，具有不同的 LEVEL 值，因此，真充的空格数将根据 各自的层号确定。空格再与雇员名字拼接，结果显示出这种层次关系，也就是说其雇员 名字右侧填充的空格数与它的层数有关。 5、节点和分支的裁剪、 在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支。 使用 WHERE 子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不 影响其后代节点（自顶向下检索时）或前辈节点（自底向上检索时） 。
例 7 显示 KING 领导下的全体雇员的信息，但除去 SCOTT。
SQL>COLUMN EMPLOYEE FORMAT A 20 SQL>SELECT EMPNO,RPAD(―,LEVE*3)||ENAME EMPLOYEE,MGR FROM EMP WHERE ENAME！=‘SCOTT’ CONNECT BY PRIOR EMPNO=MGR START WITH ENAME IN=‘KING’ ；
EMPNO 7839
ENAME KING
MGR
- 第64页 –，共 69 页
ORACLE 基础 7566 7876 7902 7369 7698 7499 7521 7654 7844 7900 7782 7934 JONES ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES CLARK MILLER 7839 7788 7566 7902 7839 7698 7698 7698 7698 7698 7839 7782
13ows selected.
在这个查询中，仅剪去了树中的单个节点 SCOTT。若希望剪去树结构中的某个分 支， 则要使用 XONNECT BY 子句， CONNECT BY 子句是限定树型结构中的整个分支， 既要剪除分支上的单个节点，也要剪除其后代节点（自顶向下检索时）或前辈节点（自 底向上检索时） 。 例 8 显示 KING 领导下的全体雇员信息，除去 SCOTT 领导的一支。
SQL>SELECT EMPNO，RPAD(―,LEVE*3)||ENAME EMPLOYEE,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME！=‘SCOTT’ START WITH ENAME IN=‘KING’ ；
EMPNO 7839 7566 7902 7369 7698 7499 7521 7654 7844 7900 7782 7934
ENAME KING JONES FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES CLARK MILLER
MGR
7839 7566 7902 7839 7698 7698 7698 7698 7698 7839 7782
12 rows selected.
这个查询结构就与例 7 不同，除了剪去单个节点 SCOTT 外，还将 SCOTT 的子节
- 第65页 –，共 69 页
ORACLE 基础
点 ADAMS 剪掉，即把 SCOTT 这个分支剪掉了。 当然 WHERE 子句可以和 CONNECT BY 子句联合使用，这样能够同时剪掉单个节 点和树中的某个分支。
例 9 显示 KING 领导的全体雇员信息，除去雇员 SCOTT，以及 BLANK 领导的一支。
SQL>COLUMN EMPLOYEE PORMAT A20 SQL>SELECT EMPNO，RPAD(―,LEVE*3)||ENAME EMPLOYEE,MGR FROM EMP WHERE ENAME！=‘SCOTT’ CONNECT BY PRIOR EMPNO=MGR AND ENAME！=‘BLAKE’ START WITH ENAME IN=‘KING’ ；
EMPNO 7839 7566 7876 7902 7369 7782 7934
ENAME KING JONES ADAMS FORD SMITH CLARK MILLER
MGR
7839 7788 7566 7902 7839 7782
7
rows selected.
6、排序显示 像其他查询一样，在树结构中也可以使用 ORDER BY 子句，改变查询结果的显示 顺序，而不必按照遍历树结构的顺序显示。
例 10 以 EMPNO 的顺序显示树结构 EMP 中的数据。
SQL>SELECT EMPNO，ENAME ,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH ENAME IN=‘KING’ ； ORDER BY EMPNO；
EMPNO
ENAME
MGR
7369 7499 7521 7566 7654 7698 7782
SMITH ALLEN WARD JONES MARTIN BLAKE CLARK
7902 7698 7698 7839 7698 7839 7839 - 第66页 –，共 69 页
ORACLE 基础 7788 7839 7844 7876 7900 7902 7934 SCOTT KING TURNER ADAMS JAMES FORD MILLER 7698 7788 7698 7566 7782 7566
14 rows
selected.
在使用 SELECT 语句来报告树结构报表时应当注意，CONNECT 子句不能作用于出 现在 WHERE 子句中的表连接。如果需要进行连接，可以先用树结构建立一个视图， 再将这个视图与其他表连接，以完成所需要的查询。 1.8.2 报表高级设计 （1）将 SQL*PLUS 的报表设置参数写入文件 在使用 SQL*PLUS 制作报表时，一般将报表的内容及参数的设置写入一个后缀 为.SQL 的文件中，然后使用 START 命令执行该文件。制作报表时，往往希望内容清晰 简洁，这可以通过设置下面有关的参数和选项来实现： SET ECHO OFF 使 SQL*PLUS 在执行命令文件时，不将命令显示在屏 幕上 SET FEEDBACK OFF 在执行完命令文件后，不将查到的结果数显示到屏幕上 SET TERMOUT OFF 它将禁止报告结果显示到屏幕上，它一般放置在 SPOOL 命令之前，因为对于要打印的报表，这将能节省时间以 避免烦人的屏幕滚动，文件输出操作还能继续正常地工 作 此外，还有 SET PAGESIZE，SET HEADING OFF 等也将会影响报表的输出效果。 （2）关于 SELECT 子句的补充说明 在 SELECT 子句中不但可以选择列和列表达式进行输出，还可以选择常量和多列 组合进行输出。 例1
SQL>SELECT1,‘This is a character constant‘ FROM EMP WHERE DEPTNO=10; 1‘THISISACHARACTERCONSTANT‘
1 This is a character constant 1 This is a character constant 1 This is a character constant 3 rows selected.
- 第67页 –，共 69 页
ORACLE 基础
例2
SQL>SELECT1,‘This is a character constant‘ FROM SYSTEM.DUAL 1‘THISISACHARACTERCONSTANT‘
1 This is a character constant
1 rows selected.
其中：DUAL 为虚表，该表中仅有一行一列。
例 3 将雇员的名字和工种拼接后输出。
SQL>SELECT EMPNO，ENAME|| ‗job is‘|| JOB FROM EMP WHERE SAL>=3000； EMPNO ENAME ||‘JOBIS‘||JOB
7788 7839 7902
SCOTT job is ANALYST KING job is PRESIDENT FORD job is ANALYST
3 rows selected.
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 语句。常用的 设置命令有： SET HEADING OFF 要求显示查询结果时不显示列标题 SET FEEDBACK OFF 要求显示查询结果时不显示被选择记录数 SET PAGESIZE n 设置每页的行数，应设置 n 足够大，以避免生成 代码的长度超出页长，n=0 表示不产生新页 SET LINESIZE n 设置每行的字符数，应设置 n 足够大，以避免生成代 码被执行
- 第68页 –，共 69 页
ORACLE 基础
SET VERIFY OFF SPOOL {文件名|OFF} START 文件名
例如，命令文件内容如下：
SET HEADING OFF SET FEEDBACK OFF SET ECHO OFF SET PAGESIZE 5000 SPOOL FIRST.SQL
要求执行 SQL 语句前不显示使用的变量值 允许和禁止假脱机输出 执行 SQL 命令文件
SELECT ‗SELECT‖X‖FROM SYSTEM.DUAL;‘ FROM SYSTEM.DUAL; SPOOL OFF SET HEADING ON SET FEEDBACK ON SET ECHO ON SET PAGESIZE 17 SPOOL FIRST.LST START FIRST SPOOL OFF
执行该文件，产生的 FIRST.SQL 文件内容如下：
SELECT ‗X‘ FROM SYSTEM.DUAL；
通过 START FIRST.SQL 命令的执行，将屏幕信息定入 FIRST.LST 中，其内容如下：
SQL>START FIRST SQL> SQL>STLECT ‗X‘ FROM SYSTEM.DUAL； ‗ _ X 1 row selected. SQL>SPOOL OFF
这个例子就是先用 SQL 命令生成一个 SQL 文件——FIRST.SQL， 其中包括了需要 的命令，再通过执行文件的内容完成所需的操作。
- 第69页 –，共 69 页