计算机实用技术
Part I
Oracle SQL
Part II
Oracle 体系结构
谢金国 朱敏
南京航空航天大学计算中心
2005年9月
计算机实用新技术(Part I)
目 录
- 2 -
计算机实用技术(Part II)
Part I ORACLE SQL
1. 什么是SQL？
SQL，翻译成中文就是“结构化查询语言”。其发音是“S-Q-L”，也可以读
作“sequel”。
SQL是一种计算机语言，它是为了从存储在关系数据库中的数据中获得信息
而设计的，即通过SQL可以从大量收集的数据中查找想要的信息。
SQL不同于其他计算机语言，我们使用SQL描述想要的信息类型，然后计算机
会确定出获得它的最好过程，并运行这个过程。这就是所谓的“说明性”计算机
语言，因为它注重的是结果：我们可以详细说明结果的外表特征。计算机允许使
用任意数据处理方法，只要它获得正确结果。
其他计算机语言大部分是“过程性”的，如汇编、Pascal、C、Java等。我
们使用这些语言描述应用于数据的过程，无需描述结果。结果是将过程应用到数
据后所得到的东西。
这好比我们清晨去一家咖啡店。如果使用SQL支持的说明性方法，你只要说：
“我想来一杯咖啡和一个炸面包圈。”而使用过程性方法，你不能那样说，你必
须说出如何得到结果，并且给它一个特定的过程。也就是说，你必须说出如何制
作咖啡以及炸面包圈。因此，对于咖啡，你必须说：“将一些烘烤好的咖啡豆磨
成粉，添加开水，冲泡一会儿，再将它倒入一个茶杯，然后给我。”对于炸面包
圈，你必须阅读菜谱。显然，说明性方法更贴近于我们通常说话的方式，并且它
更容易于为大多数人所使用。
相对于其他计算机语言而言，SQL是易于使用的，实际上这也正是SQL如此流
行和重要的主要原因。
信息自身并不强大，只有在需要的人使用它的时候，它才变得强大。SQL是
传递信息的工具。
- 3 -
计算机实用技术(Part II)
注释：关于SQL
? SQL是从关系数据库中获得信息的说明性语言。
? SQL会告诉获得什么信息，而不是告诉如何获得信息。
? SQL的基本知识是易于掌握的。
? SQL使得人们能够控制信息。
? SQL允许人们使用新的方式来处理信息。
? SQL通过向需要的人们提供信息来使信息变得强大。
2. 什么是关系数据库？为什么要使用它？
关系数据库是一种在计算机中用于组织数据的方法。
SQL是使人们将数据组织到关系数据库中的一个主要原因，使用SQL，你可以
毫不费劲地从数据中获得信息，这是非常重要的。
另外一个原因是：许多人可以在同一时间使用关系数据库中的数据。有时，
几百或者几千人可以一起共享数据库中的数据。所有人都可以看见数据，并且所
有人都可以更改数据（如果他们有这样的权限的话）。从商业观点来看，这提供
了一个协调所有员工的方法，并且让每一个人都使用相同的信息主体。
第三个原因是：关系数据库是为希望信息可以随时间进行更改而设计的。信
息可能需要重新组织或者新的信息需要被添加，关系数据库的设计目标是为了使
这种类型的更改变得容易。大多数计算机系统很难更改。因为它们假设你在开始
构造之前就知道所有的请求。
从计算机语言的观点来看，关系数据库的灵活性和SQL的可用性使得开发新
的计算机应用程序要比传统技术更为迅速。
开发关系数据库的想法出现于20世纪70年代早期，用来处理大量数据和数以
百万计的纪录。最初，关系数据库被想象成后端处理器，它向使用过程性语言（如
C语言或COBOL语言）编写的计算机应用程序提供信息。即使到现在，关系数据库
还遗留着这些痕迹。
不过今天，这种想法变得如此成功，以致于整个信息系统常常被构造为关系
数据库，并且无需很多过程代码（除了支持输入格式）。最初被开发为过程性代
码做配角的关系数据库现在已经唱了主角，许多过程性代码已经不再需要。
- 4 -
计算机实用技术(Part II)
在关系数据库中，所有数据都保存在表里，表是由列和行组成的二维结构。
在使用了一段时间的表以后，你会发现表为处理数据提供了一个非常好的结构。
它们易于更改，可以在同一时间与所有用户共享数据，并且可以在表数据上运行
SQL。许多人开始从表的角度考虑他们的数据，表已经成为处理数据时的主要手
段。
今天，人们使用小的个人数据库来保护地址簿、为音乐磁带做目录、组织藏
书或者纪录他们的财务情况。商业上应用的数据库也被建立为关系数据库。许多
人更愿意将他们的数据存入一个数据库中，即使其中只有少量的记录。
关系数据库的起源
? 关系数据库最初是在20世纪70年代开发的，开发目的是为了以一致并且
相关的方式来组织大量信息。
? 关系数据库允许几千人在相同时间使用相同信息。
? 关系数据库总是保持信息的实时性和一致性。
? 关系数据库使单位中所有级别的人（从秘书到副总裁）都可以容易地获
得信息。它们使用SQL、表单、标准化的报告和临时报告，及时地将信
息传递给人们。
? 关系数据库是作为信息服务器后端设计的。这意味着大部分人将不会直
接使用数据库，而用另一层的软件。这个软件从数据库中获得信息，然
后传递给需要的人们。
? 关系数据库使人们能够在需要信息的时候获得当前信息。
今天——关系数据库任何改变
? 除了已经描述的大型数据库之外，现在还有许多处理较少信息量的小型
数据库，它们可以被个人使用或几个人分享。
? 关系数据库非常成功，易于使用，使用它的应用程序要比原先设想的应
用范围大许多。
? 现在许多人直接使用数据库，而不是通过另一个软件层来使用。
? 许多人更愿意将他们的数据保存在数据库中。他们觉得关系数据库为处
理各种类型的数据提供了一个实用且高效的框架。
- 5 -
计算机实用技术(Part II)
3. 为什么学习SQL？
SQL被运用于100多种软件产品中。一旦学会了SQL，你将能够使用所有这些
产品。当然，你需要稍微了解一下每种产品的特性，很快你就会感觉到它们很熟
悉并且知道如何使用。你可重复使用这些技巧。
主要SQL产品 其他SQL产品（和基于SQL的产品）
Oracle MYSQL
Microsoft Access SQLbase
IBM DB2 Cold Fusion
Microsoft SQL Server SAP
Informix Business Objects
SQL Windows ODBC
Sybase Ingres
SAS sql procedure Ocelot SQL
Foxpro OsloData
dBase PostgreSQL
Tandem SQL Rapid SQL
XDB
SQL/DS
Mini SQL
Empress
Interbase
Progress
Supra
SQL Report Writer
Paradox
Delphi
VAX SQL
Essbase
Beagle SQL
GNU SQL Server
Just Logic/SQL
PrimeBase
Altera SQL Server
DataScope
PowerBuilder
SQL被广泛使用的原因之一是：相对于其他许多计算机语言来说，SQL易于学
习。另一个原因是：它打开了关系数据库的大门，而关系数据库提供了许多便利。
一些人说：SQL是关系数据库最好的特性，并且SQL是关系数据库获得成功的原因。
- 6 -
计算机实用技术(Part II)
而另一些人说：是关系数据库使得SQL成功。大部分人同意SQL和关系数据库是一
个成功组合的观点。
SQL是最成功的说明性计算机语言——一种你可以对它说你想要什么，而不
是告诉它如何得到你想要东西的语言。虽然也有其他一些说明性语言和报告生成
工具，但它们大部分在功能上有更多的限制。SQL更强大，可以应用于更多的场
合。
在SQL基础上易于构建最终用户程序，帮助不懂SQL语言的用户从数据库中获
得信息。
4. SQL语句的分类
SQL使用一种很简单的语法，易于学习和使用。SQL语句可分为5类，简要概
括如下：
● 查询语句 用于检索数据库表中存储的行。可以使用SQL的SELECT语句
编写查询语句。
● 数据操纵语言(Data Manipulation Language, DML)语句 用于修改表
的内容。DML语句有3种：
· INSERT 向表中添加行。
· UPDATE 修改行的内容。
· DELETE 删除行。
● 数据定义语言(Data Definition Language, DDL)语句 用于定义构成
数据库的数据结构，例如表。DDL语句有5种基本类型：
· CREATE 创建数据库结构。例如，CREATE TABLE 语句用于创建一个
表；CREATE USER 用于创建一个数据库用户。
· ALTER 修改数据库结构。例如，ALTER TABLE 语句用于修改一个表。
· DROP 删除数据库结构。例如，DROP TABLE 语句用于删除表。
· RENAME 更改表名。
· TRUNCATE 删除表的全部内容。
● 事务控制(Transaction Control, TC)语句 用于将对行所作的修改永
久性地存储到表中，或者取消这些修改操作。TC语句有3种：
- 7 -
计算机实用技术(Part II)
· COMMIT 永久性地保存对行所作的修改。
· ROLLBACK 取消对行所作的修改。
· SAVEBACK 设置一个“保存点”，可以将对行所作的修改回滚到此处。
● 数据控制语言(Data Control Language, DCL)语句 用于修改数据库结
构的操作权限。DCL 语句有2种：
· GRANT 授予用户对数据库结构（例如表）的访问权限。
· REVOKE 收回用户对数据库结构（例如表）的访问权限。
有很多方法都可以运行SQL语句，并从数据库中返回结果，其中包括使用
Oracle Forms、Delphi、PowerBuilder等设计的程序；还可以通过JDBC在Java
程序中加入SQL语句。
Oracle还有一个名为SQL*Plus的工具，可以使用这个工具从键盘输入SQL语
句，或者提供一个包含SQL语句的文件，并在SQL*Plus中运行这些语句。通过
SQL*Plus，可以与数据库进行“对话”，因为可以输入SQL语句，并查看数据库
所返回的结果。
5. 使用SQL*Plus
SQL*Plus有两个版本：一个是Windows版本，另外一个是命令行版本。可以
在任何Oracle数据库的操作系统上使用命令行版本的SQL*Plus。
● 启动Windows版本的SQL*Plus
如果使用Windows操作系统，双击桌面的SQL*Plus 图标，或单
击 开始 菜单，并选择 程序 | Oracle | Application
Development | SQL*Plus 来启动SQL*Plus（如图 1 ）。
图1 从
程序菜单启动SQL*Plus
- 8 -
计算机实用技术(Part II)
图 2 显示了在Windows操作系统上运行SQL*Plus时出现的Log On对话框。在用户
名称(User Name)中输入：s30101，在口令(Password)中输入：student，主机字
符串(Host String) 输入： oracle9i 用来告诉SQL*Plus数据库在哪里运行，然
后单击 确定。
图 2 SQL*Plus登录窗口
成功登录到数据库中之后，就会看到SQL*Plus窗口，可以通过这个窗口与数据库
进行交互。SQL*Plus窗口如图 3 所示。
图 3 SQL*Plus
- 9 -
计算机实用技术(Part II)
● 启动命令行版本的SQL*Plus
要启动命令行的SQL*Plus，可以使用sqlplus命令。sqlplus命令的完整语法
如下：
Sqlplus [user_name[/password[@host_sting]]]
其中：
· user_name 指定数据库的用户名。
· password 指定该数据库用户的密码。
· host_string 指定要连接的数据库。
下面是执行sqlplus命令的例子：
sqlplus s30101/student@oracle9i
● 使用SQL*Plus执行SELECT 语句
使用SQL*Plus登录到数据库之后，输入下面的SELECT语句，会返回数据库中
的当前时间：
SELECT SYSDATE FROM dual;
SYSDATA 是一个内置Oracle 函数，它返回当前日期，dual 表是Oracle的一
个内置表，该表只包含一行可以使用dual表来执行一些简单的查询，这些查询的
结果并非从特定表中获得。
SQL> SELECT sysdate
2 FROM dual;
SYSDATE
---------
27-SEP-05
SQL>
通过输入EDIT命令，可以编辑SQL*Plus中的最后一条SQL语句。在输错SQL
语句或想修改SQL语句时，这种功能非常有用。在Windows系统中输入EDIT命令后，
就会启动记事本，然后就可以使用记事本来编辑SQL语句。在退出记事本并保存
SQL语句时，SQL语句就会被传递到SQL*Plus中，可以用/重新执行该条SQL语句。
- 10 -
计算机实用技术(Part II)
6. 用于示例的表 (EMP和 DEPT)
后面课程中在介绍SQL 语句时所引用的都是以下两个表。
EMP表 (14条记录) ，存放员工的基本信息。
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 790217-Dec-80 1000 20
7499 ALLEN SALESMAN 769820-Feb-81 1800 300 30
7521 WARD SALESMAN 769822-Feb-81 1450 500 30
7566 JONES MANAGER 783902-Apr-81 3175 20
7654 MARTIN SALESMAN 769828-Sep-81 1450 1400 30
7698 BLAKE MANAGER 783901-May-81 3050 30
7782 CLARK MANAGER 783909-Jun-81 2650 10
7788 SCOTT ANALYST 756609-Dec-82 3200 20
7839 KING PRESIDENT 17-Nov-81 5200 10
7844 TURNER SALESMAN 769808-Sep-81 1700 0 30
7876 ADAMS CLERK 778812-Jan-83 1300 20
7900 JAMES CLERK 769803-Dec-81 1150 30
7902 FORD ANALYST 756603-Dec-81 3200 20
7934 MILLER CLERK 778223-Jan-82 1500 10
DEPT表 (4条记录) ，存放部门的信息
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
7. 退出SQL*Plus
可以使用EXIT命令退出SQL*Plus。
SQL> EXIT
- 11 -
计算机实用技术(Part II)
8. 基本的SQL语句
SQL> SELECT job, SUM(sal) PAYROLL
2 FROM emp
3 WHERE job NOT LIKE 'SALES%'
4 GROUP BY job
5 HAVING SUM(sal)>5000
6 ORDER BY SUM(sal);
SQL语句的简单规则
? 除非特别说明，SQL 语句不区分大小写
? SQL语句可以在一行或多行输入
? 关键词不能跨行分开或缩写
? 为了方便阅读或编辑，语句通常被分为若干行
? Tab和缩排用来增加代码的可读性
? 通常关键词用大写；其他的词，如表名和列名用小写
? 在SQL*Plus 里，在SQL 提示后输入第一条SQL 语句，而随后的行被编号。 这叫做SQL buffer 。
无论何时在buffer里当前的语句只有一条
? 在语句的结尾用分号(;) 。
排除重复的行
SQL> SELECT DISTINCT deptno, job
2 FROM emp;
DEPTNO JOB
------ ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
...
9 rows selected.
SQL和SQL*Plus 的比较
SQL SQL*Plus
是一种语言，用来和服务器通信存取数据的 识别SQL 语句，并把它们发送给服务器
是建立在美国国家标准协会 (ANSI) 的标准SQL
之上的
是Oracle 为了执行SQL 语句提供的接口
可以操纵定义在数据库里的数据和基表 不允许操纵存储在数据库里的数据的值
可以输入一行或多行存储在SQL buffer 里 一次只输入一行；不存储在SQL buffer 里
不能有连接符 如果命令超过一行可以用破折号(-)作为连接符
不能缩写 可以缩写
在使用终止符后将立刻执行 不需要终止符；命令会立刻执行
可以用函数来处理数据格式的工作 用命令来改变数据的格式
- 12 -
计算机实用技术(Part II)
9. Where, Order by子句
IS NULL操作
SQL> SELECT ename, mgr
2 FROM emp
3 WHERE mgr IS NULL;
ENAME MGR
---------- ---------
KING
BETWEEN操作
SQL> SELECT ename, sal
2 FROM emp
3 WHERE sal BETWEEN 1000 AND 1500;
ENAME SAL
---------- ---------
MARTIN 1250
TURNER 1500
WARD 1250
ADAMS 1100
MILLER 1300
IN操作
SQL> SELECT empno, ename, sal, mgr
2 FROM emp
3 WHERE mgr IN (7902, 7566, 7788);
EMPNO ENAME SAL MGR
--------- -------- -------- --------
7902 FORD 3000 7566
7369 SMITH 800 7902
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788
LIKE操作
SQL> SELECT ename
2 FROM emp
3 WHERE ename LIKE '_A%';
ENAME
----------
JAMES
WARD
NOT操作
SQL> SELECT ename, job
2 FROM emp
3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
ENAME JOB
---------- ---------
KING PRESIDENT
MARTIN SALESMAN
- 13 -
计算机实用技术(Part II)
ALLEN SALESMAN
TURNER SALESMAN
WARD SALESMAN
按多列进行排序
SQL> SELECT ename, deptno, sal
2 FROM emp
3 ORDER BY deptno, sal DESC;
ENAME DEPTNO SAL
---------- --------- ---------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
FORD 20 3000
...
14 rows selected.
10. SQL函数
单行函数
? 操纵数据项
? 接受变量并返回一个值
? 对每一行的返回值起作用
? 每行返回一个结果
? 改变数据类型
? 能够被嵌套
函数 结果
LOWER(‘Oracle Education’) oracle education
UPPER(‘Oracle Education’) ORACLE EDUCATION
INITCAP(‘ORACLE education’) Oracle Education
CONCAT(‘Certified’, ‘DBA’) CertifiedDBA
SUBSTR(‘Administrator’, 1, 5) Admin
LENGTH(‘Administrator’) 13
INSTR(‘Oracle’, ‘c’) 4
LPAD(sal, 10, ‘*’) ******5000
ROUND(78.926, 2) 78.93
TRUNC(78.926, 2) 78.92
MOD(2100, 500) 100
SQL> SELECT 'The job title for '||INITCAP(ename)||' is '
2 ||LOWER(job) AS "EMPLOYEE DETAILS"
3 FROM emp;
EMPLOYEE DETAILS
-----------------------------------------
The job title for King is president
The job title for Blake is manager
The job title for Clark is manager
...
14 rows selected.
- 14 -
计算机实用技术(Part II)
Date函数
函数 结果 说明
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194 计算两个日期之间的月数
ADD_MONTHS ('11-JAN-94',6) '11-JUL-94' 计算指定日期后若干月的日期
NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95' 计算指定日期后下一天的日期
LAST_DAY('01-SEP-95') '30-SEP-95' 计算指定月份最后一天的日期
ROUND('25-JUL-95','MONTH') 01-AUG-95 舍入日期
ROUND('25-JUL-95','YEAR') 01-JAN-96
TRUNC('25-JUL-95','MONTH') 01-JUL-95 截取日期
TRUNC('25-JUL-95','YEAR') 01-JAN-95
SQL> SELECT empno, hiredate,
2 MONTHS_BETWEEN(SYSDATE, hiredate) TENURE,
3 ADD_MONTHS(hiredate, 6) REVIEW,
4 NEXT_DAY(hiredate, 'FRIDAY'), LAST_DAY(hiredate)
5 FROM emp
6 WHERE MONTHS_BETWEEN (SYSDATE, hiredate)<200;
EMPNO HIREDATE TENURE REVIEW NEXT_DAY LAST_DAY
--------- --------- --------- --------- --------- ---------
7839 17-NOV-81 192.24794 17-MAY-82 20-NOV-81 30-NOV-81
7698 01-MAY-81 198.76407 01-NOV-81 08-MAY-81 31-MAY-81
...
11 rows selected.
带日期的TO_CHAR 函数
要素 说明
SCC or CC 世纪；以S 为前缀，用- 表示公元前的日期
Years in dates YYYY or SYYYY 年；以S 为前缀，用- 表示公元前的日期
YYY or YY or Y 年的后3 、2 或1 位数字
Y,YYY 在这个位置加逗号的年
IYYY, IYY, IY, I 以ISO 标准为基础的 4, 3, 2, 或 1 位数字的年
SYEAR or YEAR 完整拼写的年；以S 为前缀，用- 表示公元前的日期
BC or AD 加上BC/AD指示
B.C. or A.D. 加上带句点的BC/AD指示
Q 四分之一年
MM 2位数字的月
MONTH 用9 个字符长度（不够用空格填充）拼写的月
MON 以三个字母的缩写表示的月
RM 罗马数字表示的月
WW or W 年或月的星期
DDD or DD or D 年、月或星期的某日
DAY 用9 个字符长度（不够用空格填充）拼写的日
DY 以三个字母的缩写表示的日
J 侏略日； 从公元前4713年12月31日开始计算
AM or PM 加上上下午指示
A.M. or P.M. 加上带句点的上下午指示
HH or HH12 or HH24 时、时 (1–12)或时(0–23)
MI 分 (0–59)
SS 秒 (0–59)
SSSSS 午夜之后的秒数 (0–86399)
- 15 -
计算机实用技术(Part II)
TH 序数( 例如， DDTH for 4TH)
SP 完整拼写的数字( 例如， DDSP for FOUR)
SPTH or THSP 完整拼写的序数( 例如, DDSPTH for FOURTH)
fm 清除空格或打头的零
SQL> SELECT ename,
2 TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE
3 FROM emp;
ENAME HIREDATE
---------- -----------------
KING 17 November 1981
BLAKE 1 May 1981
CLARK 9 June 1981
JONES 2 April 1981
MARTIN 28 September 1981
ALLEN 20 February 1981
...
14 rows selected.
SQL> SELECT ename,
2 TO_CHAR(hiredate, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM')
3 HIREDATE
4 FROM emp;
ENAME HIREDATE
---------- ------------------------------------------------
KING Seventeenth of November 1981 12:00:00 AM
BLAKE First of May 1981 12:00:00 AM
...
14 rows selected.
带数字的TO_CHAR 函数
要素 说明 例子 结果
9 数字的位数 (9 的数目决定显示的宽度) 999999 1234
0 首位加零显示 099999 001234
$ 浮动的美元符号 $999999 $1234
L 浮动的当地货币符号 L999999 FF1234
. 小数点的位置 999999.99 1234.00
, 逗号的位置 999,999 1,234
MI 减号置于右边 ( 负数 999999MI 1234-
PR 作为插入成分插入的负数 999999PR <1234>
EEEE 科学计数法 ( 格式必须是4 个E) 99.999EEEE 1.234E+03
V 10的n 次幂 (n = V 后9 的个数) 9999V99 123400
B 显示零值为空格，而不是0 B9999.99 1234.00
SQL> SELECT TO_CHAR(sal,'$99,999') SALARY
2 FROM emp
3 WHERE ename = 'SCOTT';
SALARY
--------
$3,000
- 16 -
计算机实用技术(Part II)
NVL函数
? 将空值转换为一个既定的值
? 数据类型可以是日期、字符和数字
? 数据类型一定要匹配
? NVL(comm,0)
? NVL(hiredate,'01-JAN-97')
? NVL(job,'No Job Yet')
SQL> SELECT ename, sal, comm, (sal*12)+comm
2 FROM emp;
ENAME JOB (SAL*12)+COMM
---------- --------- -------------
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN 16400
...
14 rows selected.
SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0)
2 FROM emp;
ENAME SAL COMM (SAL*12)+NVL(COMM,0)
---------- --------- --------- --------------------
KING 5000 60000
BLAKE 2850 34200
CLARK 2450 29400
JONES 2975 35700
MARTIN 1250 1400 16400
ALLEN 1600 300 19500
...
14 rows selected.
DECODE函数
SQL> SELECT job, sal,
2 DECODE(job, 'ANALYST', SAL*1.1,
3 'CLERK', SAL*1.15,
4 'MANAGER', SAL*1.20,
5 SAL)
6 REVISED_SALARY
7 FROM emp;
JOB SAL REVISED_SALARY
--------- --------- --------------
PRESIDENT 5000 5000
MANAGER 2850 3420
MANAGER 2450 2940
...
14 rows selected.
Nesting函数
SQL> SELECT ename,
2 NVL(TO_CHAR(mgr),'No Manager')
3 FROM emp
4 WHERE mgr IS NULL;
- 17 -
计算机实用技术(Part II)
ENAME NVL(TO_CHAR(MGR),'NOMANAGER')
---------- -----------------------------
KING No Manager
11. 表的联接
等值联接
SQL> SELECT emp.empno, emp.ename, emp.deptno,
2 dept.deptno, dept.loc
3 FROM emp, dept
4 WHERE emp.deptno=dept.deptno;
EMPNO ENAME DEPTNO DEPTNO LOC
----- ------ -------- ------ ---------
7839 KING 10 10 NEW YORK
7698 BLAKE 30 30 CHICAGO
7782 CLARK 10 10 NEW YORK
7566 JONES 20 20 DALLAS
...
14 rows selected.
非等值联接
SALGRADE表
GRADE LOSAL HISAL
----- ----- -------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> SELECT e.ename, e.sal, s.grade
2 FROM emp e, salgrade s
3 WHERE e.sal
4 BETWEEN s.losal AND s.hisal;
ENAME SAL GRADE
---------- --------- ---------
JAMES 950 1
SMITH 800 1
ADAMS 1100 1
...
14 rows selected.
外联接
SQL> SELECT e.ename, d.deptno, d.dname
2 FROM emp e, dept d
3 WHERE e.deptno(+) = d.deptno
4 ORDER BY e.deptno;
ENAME DEPTNO DNAME
---------- --------- -------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
...
40 OPERATIONS
- 18 -
计算机实用技术(Part II)
15 rows selected.
自联接
SQL> SELECT worker.ename||' works for '||manager.ename
2 FROM emp worker, emp manager
3 WHERE worker.mgr = manager.empno;
WORKER.ENAME||'WORKSFOR'||MANAG
-------------------------------
BLAKE works for KING
CLARK works for KING
JONES works for KING
MARTIN works for BLAKE
...
13 rows selected.
使用表的别名
SQL> SELECT e.empno, e.ename, e.deptno,
2 d.deptno, d.loc
3 FROM emp e, dept d
4 WHERE e.deptno=d.deptno;
12. 分组函数
分组函数的类型
函数 说明
AVG([DISTINCT|ALL]n) 计算n 的平均值，忽略空值
COUNT({*|[DISTINCT|ALL]表达式})
统计行数，如果有表达式，则统计空值以外的行
数。 用 *将选择统计所有的行，包括重复的和空
的行。
MAX([DISTINCT|ALL] 表达式) 取表达式的最大值，忽略空值
MIN([DISTINCT|ALL] 表达式) 取表达式的最小值，忽略空值
STDDEV([DISTINCT|ALL]x) 计算n 的均方差，忽略空值
SUM([DISTINCT|ALL]n) 计算n 的和，忽略空值
VARIANCE([DISTINCT|ALL]x) 计算n 的方差， 忽略空值
SQL> SELECT AVG(sal), MAX(sal),
2 MIN(sal), SUM(sal)
3 FROM emp
4 WHERE job LIKE 'SALES%';
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
-------- --------- --------- ---------
1400 1600 1250 5600
SQL> SELECT COUNT(*)
2 FROM emp
3 WHERE deptno = 30;
COUNT(*)
---------
6
- 19 -
计算机实用技术(Part II)
GROUP BY子句
SQL> SELECT deptno, AVG(sal)
2 FROM emp
3 GROUP BY deptno;
DEPTNO AVG(SAL)
------ ---------
10 2916.6667
20 2175
30 1566.6667
SQL> SELECT deptno, job, sum(sal)
2 FROM emp
3 GROUP BY deptno, job;
DEPTNO JOB SUM(SAL)
------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
...
9 rows selected.
使用分组功能而导致的非法查询
SQL> SELECT deptno, COUNT(ename)
2 FROM emp;
SELECT deptno, COUNT(ename)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> SELECT deptno, AVG(sal)
2 FROM emp
3 WHERE AVG(sal) > 2000
4 GROUP BY deptno;
WHERE AVG(sal) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
HAVING子句
SQL> SELECT deptno, max(sal)
2 FROM emp
3 GROUP BY deptno
4 HAVING max(sal)>2900;
DEPTNO MAX(SAL)
------ ---------
10 5000
20 3000
- 20 -
计算机实用技术(Part II)
13. 子查询
使用子查询指南
SQL> SELECT ename
2 FROM emp
3 WHERE sal >
4 (SELECT sal
5 FROM emp
6 WHERE empno=7566);
ENAME
----------
KING
FORD
SCOTT
? 子查询（内部查询）在主查询前只执行一次。
? 子查询的结果被用于主查询（外部查询）。
? 子查询要用圆括号括起。
? 子查询要放在比较运算符的右边。
? 子查询里不要加ORDER BY 子句。
? 单行的子查询用于单行操作。
? 多行的子查询用于多行操作。
单行比较运算
运算符 含义
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于
SQL> SELECT ename, job
2 FROM emp
3 WHERE job =
4 (SELECT job
5 FROM emp
6 WHERE empno = 7369)
7 AND sal >
8 (SELECT sal
9 FROM emp
10 WHERE empno = 7876);
ENAME JOB
---------- ---------
MILLER CLERK
- 21 -
计算机实用技术(Part II)
多行比较运算
操作 含义
IN 等于列表中的任何一个值
ANY 同子查询返回的各个值进行比较
ALL 同子查询返回的每个值进行比较
SQL> SELECT empno, ename, job
2 FROM emp
3 WHERE sal < ANY
4 (SELECT sal
5 FROM emp
6 WHERE job = 'CLERK')
7 AND job <> 'CLERK';
EMPNO ENAME JOB
----- ------ --------
7654 MARTIN SALESMAN
7521 WARD SALESMAN
SQL> SELECT empno, ename, job
2 FROM emp
3 WHERE sal > ALL
4 (SELECT avg(sal)
5 FROM emp
6 GROUP BY deptno);
EMPNO ENAME JOB
----- ------ ---------
7839 KING PRESIDENT
7566 JONES MANAGER
7902 FORD ANALYST
7788 SCOTT ANALYST
SQL> SELECT ename, deptno, sal, comm
2 FROM emp
3 WHERE (sal, NVL(comm,-1)) IN
4 (SELECT sal, NVL(comm,-1)
5 FROM emp
6 WHERE deptno = 30);
ENAME DEPTNO SAL COMM
---------- --------- --------- ---------
JAMES 30 950
WARD 30 1250 500
MARTIN 30 1250 1400
TURNER 30 1500 0
ALLEN 30 1600 300
BLAKE 30 2850
6 rows selected.
14. SQL*Plus
替换变量
? 以符号(&) 为前缀的变量提示用户给此变量赋值
- 22 -
计算机实用技术(Part II)
SQL> SELECT empno, ename, sal, deptno
2 FROM emp
3 WHERE empno = &employee_num;
Enter value for employee_num: 7369
EMPNO ENAME SAL DEPTNO
--------- ---------- --------- ---------
7369 SMITH 800 20
? 日期和字符型的值要用单引号
SQL> SELECT ename, deptno, sal*12
2 FROM emp
3 WHERE job='&job_title';
Enter value for job_title: ANALYST
ENAME DEPTNO SAL*12
---------- --------- ---------
SCOTT 20 36000
FORD 20 36000
ACCEPT命令
ACCEPT dept PROMPT 'Provide the department name: '
SELECT *
FROM dept
WHERE dname = UPPER('&dept')
/
Provide the department name: Sales
DEPTNO DNAME LOC
--------- -------------- -------------
30 SALES CHICAGO
定制SQL*Plus 的环境
SET变量及其值 描述
ARRAY[SIZE] {20| n} 设置用来取数据库数据区的大小
COLSEP {_|text} 设置两列之间的字符宽度。 默认值是一个空
FEED[BACK] {6|n|OFF|ON} 当查询返回的值是多个时，用于设置显示的记录数
HEA[DING] {OFF|ON} 决定报表中是否显示列头
LIN[ESIZE] {80|n} 设置每行显示的字符宽度
LONG {80|n} 设置显示LONG 型值时的最大宽度
PAGES[IZE] {24|n} 指定每页输出的行数
PAU[SE] {OFF|ON|text} 允许控制终端的卷动 ( 每次暂停后要按[Return] 键)
TERM[OUT] {OFF|ON} 决定输出是否显示在屏幕上
COL[UMN] [column option] 控制列的格式
TTI[TLE] [text|OFF|ON] 指定每页的页眉
BTI[TLE] [text|OFF|ON] 指定每页的页脚
BRE[AK] [ON report_element] 压缩重复的值；用回车换行分割数据行
- 23 -
计算机实用技术(Part II)
15. 数据操纵语言 Data Manipulation Language (DML)
INSERT语句
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
? 一次只能插入一行
? 插入新行要包含每列的值
? 值要按照表中默认列的顺序排列
? 也可以在INSERT 语句中指定列的顺序
字符型和日期型的值药用单引号括起。
SQL> INSERT INTO dept (deptno, dname, loc)
2 VALUES (50, 'DEVELOPMENT', 'DETROIT');
1 row created.
? 用空值插入行
? 隐含的方法：在列表中省略此列。
? 直接的方法：直接填入空值。
SQL> INSERT INTO dept (deptno, dname )
2 VALUES (60, 'MIS');
1 row created.
SQL> INSERT INTO dept
2 VALUES (70, 'FINANCE', NULL);
1 row created.
UPDATE语句
UPDATE table
SET column = value [, column = value]
[WHERE condition];
? 如果需要的话，一次可以更新多行。
? 当用特定WHERE 子句时，可以修改特定行的数据。
SQL> UPDATE emp
2 SET deptno = 20
3 WHERE empno = 7782;
1 row updated.
? 如果省略WHERE 子句，表中所有行的数据都会被修改。
SQL> UPDATE employee
2 SET deptno = 20;
14 rows updated.
DELETE语句
DELETE [FROM] table
[WHERE condition];
? 表中的数据可以用DELETE语句来删除。
- 24 -
计算机实用技术(Part II)
? 当用特定WHERE 子句时，可以删除特定行的数据。
SQL> DELETE FROM department
2 WHERE dname = 'DEVELOPMENT';
1 row deleted.
? 如果省略WHERE 子句，表中所有行的数据都会被删除。
SQL> DELETE FROM department;
4 rows deleted.
事务
? 由下列语句之一构成：
? 一组用于修改数据的数据操纵(DML)语句
? 一个数据定义(DDL)语句
? 一个数据控制(DCL) 语句
? 开始：当第一个可执行的SQL 语句被执行时
? 结束于下列情况之一：
? 提交(COMMIT) 或回退(ROLLBACK)
? DDL或DCL语句执行后（自动提交）
? 用户退出
? 系统崩溃
? 下列情形会发生一个自动提交：
? 一个DDL 语句被发送后
? 一个DCL 语句被发送后
? 一个正常的从SQL*Plus 退出，而没有直接发送COMMIT 或ROLLBACK 。
? 一个自动的回退发生在非正常的结束SQL*Plus 或系统出错。
COMMIT或ROLLBACK之前
? 数据以前的状态能够被恢复。
? 通过SELECT 语句，当前用户能够察看DML 操作的结果。
? 而其他得用户不能看到当前用户的结果。
? 受作用的行会被锁定；其他的用户不能修改受作用的行的数据。
COMMIT之后
SQL> COMMIT;
Commit complete.
? 数据库的数据会被永久的改变。
? 数据以前的状态会永久的丢失。
? 所有的用户都能看到结果。
? 受作用的行的锁会被释放；其他用户可以操纵这些行。
? 所有的保存点会(savepoints) 被删除。
ROLLBACK之后
SQL> ROLLBACK;
Rollback complete.
? 数据修改被取消。
? 恢复到数据以前的状态。
? 受作用的行的锁被释放。
- 25 -
计算机实用技术(Part II)
SAVEPOINT
? 可以用SAVEPOINT 语句在当前的事务里创建一个标记。
? 通过使用ROLLBACK TO SAVEPOINT 语句回退到那个标记的位置。
SQL> UPDATE...
SQL> SAVEPOINT update_done;
Savepoint created.
SQL> INSERT...
SQL> ROLLBACK TO update_done;
Rollback complete.
16. 管理表
数据库的表和列的命名规则
? 要一字母开始
? 可以是1–30字符的长度
? 只能用A–Z, a–z, 0–9, _, $, 和#
? 同一个用户的对象名称不能重复
? 不能用Oracle 服务器的保留字
CREATE TABLE语句
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr], ..., ...);
? schema (方案) 和所有者的名称一样
? table 表的名称
? DEFAULT expr 如果在INSERT 语句中不赋值的话，用这个默认值
? column 列的名称
? datatype 列的数据类型和长度
SQL> CREATE TABLE dept
2 (deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13));
Table created.
SQL> DESCRIBE dept
Name Null? Type
--------------------------- -------- ---------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
数据类型
数据类型 说明
VARCHAR2(size)
变长的字符数据 （需要指明size 的最大的值。 size默认和最小值是1 ，最
大值是4000。)
CHAR(size)
定长的字符数据，长度为size 定义的字节数（size 默认和最小是 1，最大
值是2000。）
NUMBER(p,s)
精度为p ，小数位为s的数字；精度是十进制数的总的宽度，小数位是小
数点右边的数字的宽度（精度的范围从1 到 38，小数位的范围从-84 到
1278。）
DATE 公元前4712年1 月1 日到公元9999年12月31日之间的日期和时间 。
- 26 -
计算机实用技术(Part II)
LONG 变长的字符数据，可长达2GB 。
CLOB 单字节字符数据，可长达4GB 。
RAW(size)
长度为size 的二进制原始数据。 size最大值是2000。 (需要指明size 的最大
的值。)
LONG RAW 变长的二进制原始数据，可长达2GB 。
BLOB 可长达4GB 的二进制数据。
BFILE 存储在外部文件的二进制数据；可长达4GB 。
用子查询创建表
SQL> CREATE TABLE dept30
2 AS
3 SELECT empno, ename, sal*12 ANNSAL, hiredate
4 FROM emp
5 WHERE deptno = 30;
Table created.
SQL> DESCRIBE dept30
Name Null? Type
----------- -------- -----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
ANNSAL NUMBER
HIREDATE DATE
增加列
SQL> ALTER TABLE dept30
2 ADD (job VARCHAR2(9));
Table altered.
? 新增的列变成最后的列
EMPNO ENAME ANNSAL HIREDATE JOB
--------- ---------- --------- --------- ----
7698 BLAKE 34200 01-MAY-81
7654 MARTIN 15000 28-SEP-81
7499 ALLEN 19200 20-FEB-81
7844 TURNER 18000 08-SEP-81
...
6 rows selected.
修改列
SQL> ALTER TABLE dept30
2> MODIFY (ename VARCHAR2(15));
Table altered.
? 可以增加数字类型的列的宽度和精度。
? 若列为空值或表中没有数据行，则可以缩减列的宽度。
? 若列为空值，可以修改列的数据类型。
? 若列为空值或不修改它的大小，则可以将CHAR 类型的列转换为 VARCHAR2 的数据类型或
将VARCHAR2 类型的列转换为 CHAR 的数据类型。
? 修改列的默认值只会对随后表的插入产生影响。
- 27 -
计算机实用技术(Part II)
删除表
SQL> DROP TABLE dept30;
Table dropped.
? 表中所有的数据和结构都会被删除。
? 任何以前的事务会被提交。
? 所有的索引会被删除。
? 这个语句不能回退。
表的重命名
SQL> RENAME dept TO department;
Table renamed.
? 执行RENAME 语句可以修改表、视图、序列或同义词的名称。
? 一定要是对象的所有者才可以这么做。截去表
SQL> TRUNCATE TABLE department;
Table truncated.
? 从表中移除所有的行
? 释放此表占用的存储空间
? 使用TRUNCATE 之后，移除的行不能回退
17. 约束
? 列级别的约束
column [CONSTRAINT constraint_name] constraint_type,
? 表级别的约束
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
非空的约束
SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(7,2) NOT NULL);
唯一值的约束
SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE(dname));
- 28 -
计算机实用技术(Part II)
主键的约束
SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE (dname),
6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));
外键的约束
SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(7,2) NOT NULL,
10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11 REFERENCES dept (deptno));
检查的约束
..., deptno NUMBER(2),
CONSTRAINT emp_deptno_ck
CHECK (DEPTNO BETWEEN 10 AND 99),...
增加约束
SQL> ALTER TABLE emp
2 ADD CONSTRAINT emp_mgr_fk
3 FOREIGN KEY(mgr) REFERENCES emp(empno);
Table altered.
删除约束
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_mgr_fk;
Table altered.
? 删除DEPT 表的 PRIMARY KEY 约束， 删除相连的EMP.DEPTNO列的FOREIGN KEY约束。
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
Table altered.
启用和停用约束
? 执行ALTER TABLE 语句的 DISABLE 子句可以撤销完整性约束。
? 应用CASCADE 选项可以取消对完整约束的依赖。
SQL> ALTER TABLE emp
2 DISABLE CONSTRAINT emp_empno_pk CASCADE;
Table altered.
SQL> ALTER TABLE emp
- 29 -
计算机实用技术(Part II)
2 ENABLE CONSTRAINT emp_empno_pk;
Table altered.
? 如果创建了UNIQUE key 或PRIMARY KEY的约束， UNIQUE或PRIMARY KEY的索引会被自
动创建。
通过USER_CONSTRAINTS查看约束
SQL> SELECT constraint_name, constraint_type,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = 'EMP';
CONSTRAINT_NAME C SEARCH_CONDITION
------------------------ - -------------------------
SYS_C00674 C EMPNO IS NOT NULL
SYS_C00675 C DEPTNO IS NOT NULL
EMP_EMPNO_PK P
...
18. 视图
视图的优点
? 限制对数据库的存取，因为视图显示的只是数据库被选择的部分。
? 用户通过简单的查询就能获得复杂查询的结果。例如， 视图可以让不会写联接（join ）语句
的用户查询多个表的信息。
? 为专门的用户和应用程序提供数据的独立性。可以用一个视图来获得多个表的数据。
? 提供用户分组，以便按照特定的标准存取数据。
简单的视图和复杂的视图
功能 简单的视图 复杂的视图
表的数目 一个 一个或多个
包涵函数 否 是
包涵分组数据 否 是
通过视图来进行DML 操作 是 不总是
例 1 : 创建一个简单的视图
SQL> CREATE VIEW salvu30
2 AS SELECT empno EMPLOYEE_NUMBER, ename NAME, sal SALARY
3 FROM emp
4 WHERE deptno = 30;
View created.
SQL> SELECT *
2 FROM salvu30;
EMPLOYEE_NUMBER NAME SALARY
--------------- ---------- ---------
7698 BLAKE 2850
7654 MARTIN 1250
7499 ALLEN 1600
7844 TURNER 1500
- 30 -
计算机实用技术(Part II)
7900 JAMES 950
7521 WARD 1250
6 rows selected.
2 : 创 建一个复杂的视图 例
SQL> CREATE VIEW dept_sum_vu
2 (name, minsal, maxsal, avgsal)
3 AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
4 FROM emp e, dept d
5 WHERE e.deptno = d.d eptno
6 GROUP BY d.dname;
View created.
QL> SELECT * S
_sum_vu; 2 FROM dept
NAME MINSAL MAXSAL AVGSAL
-------------- --------- --------- ---------
ACCOUNTING 1300 5000 2916.6667
RESEARCH 800 3000 2175
SALES 950 2850 1566.6667
WITH CHECK OPTION & WITH READ ONLY
SQL> CREATE OR REPLACE VIEW empvu20
2 AS SELECT *
3 FROM em p
4 WHERE deptn o = 20
5 WITH CHECK OPTION CONSTRAINT empvu20_ck;
View created.
? 通过使用WITH CHECK OPTION可以保证DML 语句在视图范围内得到支持
的约束。 ? 任何修改视图中部门号码的尝试都会失败，因为它违反了WITH CHECK OPTION
SQL> CREATE OR REPLACE VIEW empvu10
2 (employee_number, employee_name, job_title)
3 AS SELECT empno, ename, job
4 FROM emp
5 WHERE dept no = 10
6 WITH READ ONLY;
View created.
? 通过在视图的定 义里加上WITH READ ONLY选项，可以保证没有DML 操作发生。
序列可以自动生成顺序的号码。
? 在视图中执行DML 都会返回Oracle 服务器错误ORA-01752 。
19. 序列
? 通过定义
CR ATE SEQUENCE sequence E
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
- 31 -
计算机实用技术(Part II)
1 : 创建一个名为DEPT_DEPTNO 的序例 列，用来作为表DEPT 的 primary key，在新增数据时
QL> REATE SEQUENCE dept_deptno
使用。
CS
2 INCREMENT BY 1
3 START WITH 91
4 MAXVALUE 100
5 NOCACHE
6 NOCYCLE;
Seque ce createdn .
SQL> INSERT INTO dept(deptno, dname, loc)
2 VALUES (dept_deptno.NEXTVAL,
3 'MARKETING', 'SAN DIEG O');
1 row created.
20. 索引
? 方案对象
务器引用，通过使用指针的方式来提高检索行的效率。
和维护。
约束时，一个唯一值的索引会被自动创建。
1
Q CRE IND emp_ename_idx
? 被Oracle 服
? 通过使用快速路径定位数据，从而降低磁盘的I/O 。
? 独立于其所引用的表。
? 由Oracle 服务器自动引用
? 当在表中定义了PRIMARY KEY或UNIQUE key
? 用户可以创建关于列的非唯一值的索引以提高存取行的速度。
例 : 提高EMP表的ENAME 列的查询操作的速度
L> ATE EX S
2 ON emp(ename);
Index cre date .
创建索引指南
? 此列经常在W HERE子句或联接条件中使用。
ERE子句或联接条件中一起使用。
21. 同义词
用户所有的表。
L> CREATE SYNONYM d_sum
? 此列包涵很多的值。
? 此列包涵很多的空值。
? 两个或多个列经常在WH
? 此表很大，大部分查询只检索占其2–4% 行的数据
? 此表不经常更新。
? 引用由其他
? 缩短对象名称的长度。
QS
2 FOR dept_sum_vu;
Synonym C trea ed.
- 32 -
计算机实用技术(Part II)
22. 用户权限
系统权限
系统权限 授权操作
CREATE SESSION 连接数据库
CREATE TABLE 在用户的方案中创建表
CREATE SEQUENCE 在用户的方案中创建序列
CREATE VIEW 在用户的方案中创建序视图
CREATE PROCEDURE 在用户的方案中创建序存储过程、函数或包
? 系统管理员(DBA) 能够给用户授予特别的系统权限。
SQL> GRANT create table, create sequence, create view
2 TO scott;
Grant succeeded.
对象权限
对象权限 表 视图 序列 存储过程
ALTER √ √
DELETE √ √
EXECUTE √
INDEX √
INSERT √ √
REFERENCES √
SELECT √ √ √
UPDATE √ √
? 各对象的权限是不同的。
? 对象的所有者拥有其对象的所有权限。
? 对象的所有者可以将其对象的某个权限授予其他用户。
例 1 : 授予EMP表的查询权限。
SQL> GRANT select
2 ON emp
3 TO sue, rich;
Grant succeeded.
例 2 : 将指定列的更新权限授予用户和角色。
SQL> GRANT update (dname, loc)
2 ON dept
3 TO scott, manager;
Grant succeeded.
例 3 : 给予用户传递此权限的特权。
SQL> GRANT select, insert
2 ON dept
3 TO scott
- 33 -
计算机实用技术(Part II)
4 WITH GRANT OPTION;
Grant succeeded.
例 4 : 允许系统所有的用户查询Alice的 DEPT表。
SQL> GRANT select
2 ON alice.dept
3 TO PUBLIC;
Grant succeeded.
例 5 : 收回Scott 用户DEPT 表的SELECT 和 INSERT权限。
SQL> REVOKE select, insert
2 ON dept
3 FROM scott;
Revoke succeeded.
? 由WITH GRANT OPTION 授予的权限也将会被收回。
- 34 -
计算机实用技术(Part II)
习题
1、你要建立从Oracle 提取数据的SQL语句，下列哪个SQL语句无效？
A．select NAME,JERSEY_NO where JERSEY_NO = 6;
B．select NAME,JERSEY_NO from PLAYERS;
C．select * from PLAYERS where JERSEY_NO = 6;
D．select JERSEY_NO from PLAYERS;
2、你要在一个事务的SQL*Plus会话中改变一些数据。下列哪个选项通常不表示
事务结束？
A．发出update语句
B．发出commit语句
C．发出rollback语句
D．结束会话
3、你从表中删除了1700行，要保存数据库中的改变，用下列哪个语句？
A．savepoint
B．commit
C．rollback
D．set transaction
4、数据库逻辑数据模型中生成一些表。可以在生成或改变表时将下列哪个约束
作为列约束而不能作为表约束？
A．唯一约束
B．外键约束
C．检查约束
D．非NULL约束
5、Oracle中使用SQL操作，下列DATE函数大部分返回DATE数据类型，只要一个不
返回，是哪个？
A．NEW_TIME
B．LAST_DAY
C．ADD_MONTHS
D．MONTHS_BETWEEN
6、对BANK_ACCT表发出包含order by子句的select语句，下列哪个order by子句
会产生错误？
A．order by acctno DESC;
B．order by 1;
C．order by sqrt(1);
D．order by acctno ASC;
- 35 -
计算机实用技术(Part II)
7、执行查询select 5 + 4 from DUAL, 之前从没有在DUAL表中插入数据。下列
哪个语句能最正确描述DUAL表？
A．包含两个结构名的字典视图
B．表中有一行一列，用于各种运算
C．包含两个索引名的字典视图
D．表中有零行两列，用于各种运算
8、发出下列语句：
select DECODE(ACCTNO,12345,’CLOSED’,654321,’SEIZED’,
590395,’TRANSFER’,’ACTIVE’) from BANK_ACCT
如果ACCTNO的值为503952，这个语句显示什么信息？
A．ACTIVE
B．TRANSFER
C．SEIZED
D．CLOSED
9、执行下列SQL语句时，Oracle返回什么结果：
select ADD_MONTHS(’28-APR-97’,120) from DUAL?
A．28-APR-03
B．28-APR-07
C．28-APR-13
D．28-APR-17
10、 2037年6月26日为星期一，你在晚上十点半对Oracle数据库发出下列语句：
ALTER SESSION SET NLS_DATE_FORMAT =
‘DAY MONTH DD, YYYY: HH:MIAM’;
然后发出下列语句：
Select SYSDATE from DUAL;
Oracle返回什么结果？
A．26-JUN-37
B．Jun 26, 2037,22:30
C．26-JUN-2037
D．MONDAY JUNE 26,2037: 10:30PM
11、 你要连接两个表A与B中的数据，形成一个结果集，并在会话中显示这个结
果。表A与B有一个共享列，在两个表中都称为C。下列哪个选项中where子句
可以显示表A中C列为5的数据，即使表B中没有相应数值？
A．where A.C = 5 AND A.C = B.C
B．where A.C = 5 AND A.B = B.C(+)
C．where A.C = 5 AND A.C(+) = B.C(+)
D．where A.C = 5
- 36 -
计算机实用技术(Part II)
12、 表列中有一组值，要对其进行组运算。下列每个函数对所有行的数据进行
组运算，只有哪个例外？
A．avg( )
B．sqrt( )
C．count( )
D．stddev( )
13、 你要使用nvl( )函数，下列关于nvl( )函数的说法哪一句是错误的？
A．nvl( )函数在传入的第一个值为NULL时返回第二个值
B．nvl( )处理许多不同数据类型的值
C．nvl( )在第一个值不等于第二个值时返回NULL
D．传入nvl( )的两个值应有相同数据类型
14、 下列语句生成序列：
CREATE SQUENCE MY_SEQ
START WITH 394
INCREMENT BY 12
NOMINVALUE
NOMAXVALUE
NOCACHE
NOCYCLE
用户三次发出取得NEXTVAL的SQL语句，然后四次发出取得CURRVAL的SQL语句。
这时序列的当前值为多少？
A．406
B．418
C．430
D．442
15、 生成视图之后，你发现缺了几列。可以用下列哪个语句把这些列加进视图
中？
A．alter view
B．create or replace view
C．insert into view
D．create view
16、 你要对Oracle数据库发出下列update语句：
UPDATE BANK_ACCT SET NAME = ‘SHAW’;
这个表中更新哪个记录？
A．第一个记录
B．所有记录
C．最后一个记录
D．没有记录
- 37 -
计算机实用技术(Part II)
17、 生成表之后，你发现还需要几个新列。要增加新列，应发出哪个语句？
A．create or replace table
B．alter table
C．create table
D．truncate table
18、 INVENTORY表有三列UPC_CODE、UNITS与DELIV_DATE，主键为UPC_CODE。要
通过视图增加新记录。这个视图用下列代码生成：
CREATE VIEW DAY_INVENTORY_VW
AS SELECT UPC_CODE,UNITS,DELIV_DATE
FROM INVENTORY
WHERE DELIV_DATE = SYSDATE
ORDER BY UPC_CODE
生成上述视图时会发生什么情形？
A．Oracle返回错误，说明视图中不允许用order by子句
B．Oracle返回错误，说明创建视图需要with check option子句
C．Oracle返回错误，说明select语句必须放在括号中
D．Oracle顺利生成视图
19、 你要搜索列中的文本数据，但只记得部分字符串。下列哪个SQL操作可以
使用通配符比较？
A．in
B．exists
C．between
D．like
20、 检查SQL*Plus会话的下列记录：
INSERT INTO INVENTORY ( UPC_CODE, PRODUCT )
VALUES (503949353,’HAZELNUT COFFEE’);
INSERT INTO INVENTORY ( UPC_CODE, PRODUCT )
VALUES (593923506,’SKIM MILK’);
INSERT INTO INVENTORY ( UPC_CODE, PRODUCT )
VALUES (402392340,’CANDY BAR’);
SAVEPOINT INV1;
UPDATE INVENTORY SET UPC_CODE = 50393950
WHERE UPC_CODE = 402392340;
UPDATE INVENTORY SET UPC_CODE = 4104930504
WHERE UPC_CODE = 402392340;
COMMIT;
UPDATE INVENTORY SET PRODUCT = (
SELECT PRODUCT FROM INVENTORY
WHERE UPC_CODE = 50393950)
WHERE UPC_CODE = 593923506;
- 38 -
计算机实用技术(Part II)
ROLLBACK;
一系列运算之后，下列哪个UPC代码在INVENTORY表中没有记录？
A．593923506
B．503949353
C．4104930504
D．50393950
21、 你从Oracle数据中删除一个表。发出drop table命令删除表时，与这个表
具有对象相关性的视图发生什么？
A．视图和表一起自动删除。
B．与表同一结构中的视图和表一起自动删除，而与表不在同一结构中的视图并
不和表一起自动删除。
C．与表同一数据库中的视图和表一起自动删除，而与表不在同一数据库中的视
图并不和表一起自动删除。
D．与表具有对象相关性的视图和表一起自动删除，而与表不具有对象相关性的
视图并不和表一起自动删除。
22、 你要向新的安全管理员介绍Oracle数据库的Oracle安全模型。Oracle数据
库安全模型有哪两个组件？
A．口令验证与提供权限
B．口令验证与生成数据库对象
C．生成数据库对象与生成用户
D．口令验证与生成用户
23、 下列哪个用法不是having子句的正确用法？
A．对返回的数据排序
B．根据已知条件排除某些数据组
C．根据未知条件包括某些数据组
D．根据已知条件包括某些数据组
24、 首次登录Oracle访问EMP表时，要求用户SNOW改变口令。可以使用下列哪
个语句改变口令？
A．alter user
B．alter table
C．alter role
D．alter index
25、 Oracle中发出下列语句：
SELECT * FROM EMP WHERE DEPT IN
(SELECT DEPT FROM VALID_DEPTS
WHERE DEPT_HEAD = ‘SALLY’
ORDER BY DEPT);
Oracle如何响应这个SQL语句？
- 39 -
计算机实用技术(Part II)
A．Oracle返回所选数据。
B．Oracle从EMP返回数据，而不从VALID_DEPTS返回。
C．Oracle从VALID_DEPTS返回数据，而不从EMP返回。
D．Oracle返回错误。
26、 SQL*Plus中编写SQL语句时，下面哪个SQL语句是有效的SQL语句？
A．SELECT nvl(sqrt(59483))) FROM DUAL;
B．SELECT to_char(nvl(sqrt(59483),0)) FROM DUAL;
C．SELECT to_char(nvl(sqrt(59483),’VALID’)) FROM DUAL;
D．SELECT to_char(nvl(sqrt(59483),’0’)) FROM DUAL;
- 40 -
计算机实用技术(Part II)
Part II Oracle 体系结构
23. Oracle体系结构主要组件概览
Oracle 体系结构包括以下几个主要的组件（见上图），随后的课程我们会
对它们进行讨论。
? Oracle 服务器：Oracle 服务器中有多种进程、内存结构和文件；但当处理
SQL语句时，并非都使用它们。有一些用于改造数据库性能，确保数据库能够
在软件或硬件错误事件中得以恢复，或者执行维护数据库所需的其它任务。
Oracle 服务器由一个 Oracle例程和一个 Oracle 数据库组成。
? Oracle 例程：Oracle 例程是后台进程和内存结构的组合。必须启动例程才
能访问数据库中的数据。每次启动例程都会分配系统全局区 (SGA) 并启动
Oracle后台进程。SGA 是用于存储数据库信息的内存区，该信息为数据库进
程所共享。后台进程代表调用进程执行功能。它们把为每个用户运行的多个
Oracle 程序所处理的功能统一起来。后台进程执行 I/O 并监控其它 Oracle
进程以增加并行性，从而使性能和可靠性更加优越。
? Oracle 数据库：Oracle 数据库由被称作数据库文件的操作系统文件构成，
它为数据库信息提供实际的物理存储。数据库文件用于确保数据保持一致以
及能够在例程失败的事件中得以恢复。
? 其它文件：非数据库文件用于配置例程、验证用户权限以及在磁盘失败的事
件中恢复数据库。
? 用户和服务器进程： 执行 SQL 语句时，用户和服务器进程是其中涉及的主
要进程；但是，其它进程也会有助于服务器完成 SQL 语句的处理。
? 其它进程： 根据配置情况还可能包括其它的进程，如 Advanced Queuing、 Real
Application Clusters、 Shared Server、 Advanced Replication等。
- 41 -
计算机实用技术(Part II)
24. Oracle 服务器：
? 是一种对象关系数据库管理系统，它为信息管理提供开放、综合、集成的方
法。
? 由 Oracle 例程和 Oracle数据库组成。
Oracle 服务器是信息管理的关键。通常情况 下，
Oracle 服务器必须在多用户环境下可靠地管理大 量的
数据以便许多用户能够同时存取同一数据。所有 这些
都必须在高性能的处理下完成，Oracle 服务器还 必须
防止非法的访问和提供有效的从失败中恢复的解 决方
案。
25. Oracle 例程
? 是一种访问 Oracle 数据库
的方法
? 总是打开一个且只打开一个
数据库
? 由内存和后台进程机构组成
Oracle 例程由 SGA 内存结
构和用于管理数据库的后台进
程组成。例程是通过使用对每个
操作系统特定的方法来进行标识的。例程一次只能打开和使用一个数据库。
与例程连接：
? 建立用户连接
? 创建会话
用户在向 Oracle 提交 SQL 语句之前，必须同例程连接起来。
? 用户启动 SQL*Plus 之类的工具，或者运行使用 Oracle Forms 之类的工具
开发的应用程序。这个应用程序或者工具就在用户进程中执行。
? 在最基本的配置中，当用户登录到 Oracle 服务器时，运行 Oracle 服务器
的计算机上就会创建一个进程。这个进程称为服务器进程。服务器进程代表
在客户机上运行的用户
进程与 Oracle 例程通
信。服务器进程代表用
户执行SQL 语句。
连接
连接是用户进程和
Oracle 服务器之间的通信
路径。数据库用户可以用下
面三种方式之一连接到
Oracle 服务器：
? 用户登录到运行 Oracle 例程的操作系统上，然后启动访问该系统中的数据
库的应用程序或工具。通信路径是使用主机操作系统上的交互进程通信机制
建立的。
- 42 -
计算机实用技术(Part II)
? 用户在本地计算机上启动应用程序或工具，然后通过网络连接到运行 Oracle
例程的计算机。在这项称为客户机-服务器的配置中，网络软件用于用户和
Oracle 服务器之间进行的通信。
? 在三层连接中，用户计算机通过网络与应用程序或网络服务器进行通信，而
该应用程序或网络服务器又通过网络与运行 Oracle 例程的计算机连接。例
如，用户在网络计算机上运行浏览器来使用位于 NT 服务器上的应用程序，
这个 NT 服务器从在 UNIX 主机上运行的 Oracle 数据库中检索数据。
会话
会话是用户与 Oracle 服务器的一种特定连接。当用户由 Oracle 服务器验证时
会话开始，当用户退出或出现异常终止时会话结束。对某个具体的数据库用户来
说，如果他从很多工具、应用程序或者终端同时登录，则可能有很多并发会话。
除了一些专用数据库管理工具以外，启动数据库会话还要求 Oracle 服务器可供
使用。
注： 这里所说的在用户和服务器进程之间存在一对一交流的连接类型，称为专用
服务器连接。使用多线程服务器 (MTS) 配置时，有可能出现多个用户进程共享
服务器进程。
26. Oracle 数据库：
? 作为一个单元处理的数据集合
? 由三种文件类型组成
数据库的一般用途是存储和检索相关信息。 Oracle 数据库有一个逻辑结构
和一个物理结构。数据库的物理结构是数据库中操作系统文件的集合。Oracle 数
据库由三种文件类型组成：
? 数据文件包含数据库中的实际数据
? 重做日志包含对数据库所做的更改记录，这样万一出现故障可以启用数据恢
复。
? 控制文件包含维护和验证数据库完整性的必要信息
其它关键文件
Oracle 服务器也使用一些其它文件，这些文件并不是数据库的一部分：
? 参数文件定义 Oracle 例程的特性。例如，它包含调整 SGA 中一些内存结构
大小的参数。
? 口令文件认证哪些用户有权限启动和关闭 Oracle 例程。
? 归档的重做日志文件是重做日志文件的脱机副本这些副本，可能对于从介质
失败中进行恢复很必要。
- 43 -
计算机实用技术(Part II)
物理结构
Oracle 数据库的物理结构包括 3 种类的文件：控制文件、数据文件和在线
的重做日志文件。
27. 内存结构
Oracle 的内存结构由两块内存区域组成：
? 系统全局区(System Global Area, SGA)：在例程启动时分配，是 Oracle 例
程的基本组件
? 程序全局区(Program Global Area PGA)：在服务器进程启动时分配系统全
局区
? SGA 由几种内存结构组成：
? 共享池
? 数据库缓冲区高速缓存
? 重做日志缓冲区
? 其它结构（例如，锁管理、统计数据等）
? 还有两种附加的内存结构可以在 SGA 中进行配置：
? 大型池
? Java 池
28. 系统全局区 (SGA)
SGA 又叫系统全局区，用于存储数据库信息的内存区，该信息为数据库进程所共
享。它包含Oracle 服务器的数据和控制信息。它是在 Oracle 服务器所驻留的
计算机的虚拟内存中得以分配。
下面的语句可以用来查看 SGA 内存的分配：
SQL> SHOW SGA:
Total System Global Area 36437964 bytes
Fixed Size 6543794 bytes
Variable Size 19521536 bytes
Database Buffers 16777216 bytes
Redo Buffers 73728 bytes
29. 共享池
? 用来存储：
? 最近使用的SQL 语
句信息
? 最近使用的数据
定义
? 它由两个关键的影响性
能的内存结构组成：
? 库高速缓存
? 数据字典高速缓存
? 大小由初始化参数
SHARED_POOL_SIZE 指定
- 44 -
计算机实用技术(Part II)
共享池
共享池环境包含固定的和可变的两个部分。固定部分保持相对稳定的大小，
而可变部分的会基于用户和程序的需求增加和减少。固定部分和可变部分的实际
大小是基于初始化参数以及Oracle内部的算法。
共享池的大小
因为共享池用于能全局共享的对象，如可重用的SQL 执行计划、PL/SQL 包 、
过程、函数以及游标信息等，所以它的大小必须适应固定的和可变的区域的要求。
共享池的内存分配由初始化参数SHARED_POOL_SIZE来确定。它可以用ALTER
SYSTEM SET 动态的改变大小。分析之后，可以对它做出调整，但是总的SGA 大
小不能超出 SGA_MAX_SIZE。
库高速缓存
库高速缓存大小是基于共享池的大小。内存在分析语句或调用程序单元时分
配。如果共享池太小了，那么语句会接着重新载入到库高速缓存，这样会影响到
执行。库高速缓存由最近最少使用(LRU)算法来管理。 当缓存满的时候，最近最
少使用的执行路径和分析树将被从库高速缓存中移除，以便提供空间给新的需
求。如果 SQL 或PL/SQL语句没有重新使用，那么它们最终会从库高速缓存中超
龄释放。
库高速缓存包括两种结构：
? 共享的SQL 区： 共享的SQL 区存储和共享的是运行于数据库上SQL语句的执
行计划和分析树。当相同的SQL 第二次运行时，能够利用共享SQL 区的信息
加速运行。为了保证那些SQL 语句无论何时能够使用共享的SQL区，文本、模
式以及绑定的变量必须完全相同。
? 共享的PL/SQL 区： 共享的PL/SQL 区存储和共享的是最近执行的PL/SQL 语
句。 分析和编译程序单元以及过程（函数、包和触发器）都存储在这个区域
之中。
数据字典高速缓存
数据字典高速缓存也称为字典高速缓存或行高速缓存，数据库的信息（用户
帐户数据、数据文件名、段名、区的分配、表的描述以及用户的权限等）都存储
在数据字典的表中。当服务器需要这些信息时，读取数据字典的表，然后这些返
回的数据存储在数据字典高速缓存中。
数据字典高速缓存的大小
总的大小依赖于共享池的大小，并在数据库内部来管理。如果数据字典高速
缓存太小了，那么数据库不得不反复查询数据字典中的表，以获得服务器所需的
信息。这些查询称作重复调用，它们要比直接查询数据字典高速缓存慢，因为不
使用SQL 。
- 45 -
计算机实用技术(Part II)
30. 数据库缓冲区高速缓存
? 存储从数据文件
中读取的数据块
的副本
? 当取得和更新数
据时能够获得优
异的性能
? 通过最近最少使
用(LRU)算法来管
理
? DB_BLOCK_SIZE 决定初始块的大小
数据库缓冲区高速缓存
处理查询时，服务器进程在数据库缓冲区高速缓存中查找任何所需的块。如果未
在数据库缓冲区高速缓存中找到这个块，服务器进程就从数据文件读取这个块，
并且在缓冲区高速缓存中放置一个副本。由于对同一个块的后续请求可以在内存
中找到这个块，因此这些请求可能不需要物理读取。Oracle 服务器使用最近最
少使用算法来释放近期未被访问的缓冲区，以便在缓冲区高速缓存中为新块腾出
空间。
31. 重做日志缓冲区
? 记录所有对数据库的数
据块的更改
? 主要的用途是恢复
? 更改在重做条目中记录
? 重做条目包含用于重建
或重做更改的信息
? 大小由 LOG_BUFFER 参数
定义
重做日志缓冲区
重做日志缓冲区是一个循环缓冲区，记录对数据文件块的更改。信息存储在重做
条目中，重做条目包含重建数据所必须的信息，它们是之前的 INSERT、UPDATE、
DELETE、CREATE、ALTER或DROP操作更改的。
重做日志缓冲区的大小
重做日志缓冲区的大小由 LOG_BUFFER 初始化参数定义。
大型池
? SGA 中一个可选的内存区
? 分担共享池的作用
? 用于：
? 会话内存 (用户全局区，UGA)，用于共享服务器
? 输入/输出的 (I/O) 服务器进程
? 备份和恢复操作或RMAN
? 并行执行的信息缓存
- 46 -
计算机实用技术(Part II)
PARALLEL_AUTOMATIC_TUNING set to TRUE
? 不使用LRU 列表
? 大小由LARGE_POOL_SIZE 确定
? 可以动态改变大小
32. 大型池
通过从大型池中为共享服务器、 Oracle XA或并行查询缓冲区分配会话内存，
Oracle 可以将共享池主要用在高速缓存共享的SQL 语句上。这样可以减轻共享
池中内存区的负担。共享池不必为了共享服务器的会话信息、I/O 以及备份和恢
复操作而放弃用于高速缓存SQL 语法分析树的内存。
备份和恢复
当设置参数BACKUP_DISK_IO= n 以及BACKUP_TAPE_IO_SLAVE=TRUE时，恢复管理器
(RMAN) 将使用大型池。如果配置了大型池，但它不足够大时，从大型池分配内
存会失败。 RMAN 会在报警日志文件中写下一条错误信息并且不使用 I/O 从动
进程来备份或恢复。
并行执行
如果PARALLEL_AUTOMATIC_TUNING设置为TRUE，大型池就会被使用，否则，这些
缓存将分配给共享池。
大型池的大小
大型池的字节大小由参数LARGE_POOL_SIZE 来定义。这个参数可以用ALTER
SYSTEM SET 命令来动态地改变。
SQL> ALTER SYSTEM SET LARGE_POOL_SIZE=24MB
大型池和LRU 列表
大型池没有LRU 列表。它不同于共享池，共享池使用LRU 列表来预定空间。
33. Java 池
? 服务于Java 命令的分析要求
? 如果安装和使用时需要
? 大小由 JAVA_POOL_SIZE 参数定义
Java 池是可选的设置，但在安装和使用Java
时需要。它的大小以字节为单位设置，用
JAVA_POOL_SIZE 参数。在 Oracle9 i 中， Java
池默认的大小是 24 MB。
34. 程序全局区 (PGA)
? 用于每个连接到Oracle数据库的用户进程
的保留存储区
? 进程创建时分配
? 进程结束时收回
? 仅仅被一个进程使用
程序全局区 (PGA)
程序全局区或进程全局区 (PGA) 是一个内存
区域，它包含单个服务器进程或单个后台进程
- 47 -
计算机实用技术(Part II)
的数据和控制信息。PGA在进程创建时分配，进程结束时收回。与几个进程共享
的 SGA 正相反，PGA 是只被一个进程使用的区域。
PGA的内容
PGA 内存的内容是不同的，这取决于例程运行在是专用服务器模式还是共享服务
器模式下。通常PGA 的内存包括以下几个部分：
? 私用的SQL 区： 包括如绑定信息以及运行时内存的结构等数据。每个发出SQL
语句的会话都有一个私用的SQL 区。每个提交相同SQL 语句的用户都有他或
她自己的私用SQL 区，它使用单独的共享SQL 区。这样，许多私用的SQL 区
可以和相同的共享SQL 区一起使用。私用的SQL 区的游标分为两部分：
- 固定区：包括是绑定信息，只有在游标关闭时才释放。
- 运行区：作为执行要求的第一步创建。对于INSERT、UPDATE 和DELETE 语
句，运行区在语句执行后就释放了。而对于查询，运行区只有在所有的行
都获得或查询取消时才释放。
私用SQL 区的分配依赖于为会话创建的连接类型。 在专用服务器环境中，
私用 SQL 区在它们的服务器进程的PGA 中分配。在共享服务器环境中，私用SQL
区在SGA 中分配。
私用SQL 区的管理是用户进程的职责。一个用户进程可以分配的私用SQL 区
的数量总是受到初始化参数 OPEN_CURSORS的限制。其默认值是50。
? 会话内存：由内存构成，分配用来保存会话的变量以及其他与会话相关的信
息。 在共享服务器环境中，会话内存是共享的不是私用的。
? SQL 工作区： 用于内存密集操作，如：排序、Hash连接、 Bitmap合并和Bitmap
创建。工作区的大小可以控制和调整。
从 Oracle9 i开始，工作区的大小可以自动和全局管理。这可以通过设置
参数WORKAREA_SIZE_POLICY为AUTO来实现，它是默认的。
专用服务器模式和共享服务器模式的内存分配不同
PGA 内存的内容是不同的，这取决于例程运行在是专用服务器模式还是共享服务
器模式下。通常PGA 的内存包括以下几个部分：
35. 进程结构
Oracle 采用多种类型的进程：
? 用户进程：数据库用户请求连接到Oracle服务器时启动
? 服务器进程：与Oracle例程连接，用户建立会话时启动
? 后台进程：Oracle 例程启动时启动
用户进程
? 请求和Oracle服务器进行交互的程序
? 必须首先创建连接
- 48 -
计算机实用技术(Part II)
? 不能直接和Oracle服务器进行交互
36. 用户进程
一个需要从数据库中获取信息的数据库用户，必须首先同Oracle 服务器建立连
接。这个连接要求使用数据库接口的工具，如 SQL*Plus，然后就开启了用户进
程。用户进程不直接和Oracle 服务器进行交互，而是通过用户程序接口 (UPI) 生
成调用，它会创建一个会话，并开启一个服务器进程。
37. 服务器进程
? 直接和Oracle服务器进行交互的程序
? 执行调用生成和返回结果的工作
? 可以是专用或共享服务器
服务器进程
一旦用户创建了连接，一个服务器进程就开始处理用户进程的请求。服务器进程
可是专用服务器进程也可以是共享服务器进程。在专用服务器环境中，服务器进
程只处理单个用户进程。一旦用户进程结束了，服务器进程也就结束了。在共享
服务器环境中，服务器进程处理多个用户进程，服务器进程使用Oracle 程序接
口 (OPI) 同Oracle 服务器进行通讯。
- 49 -
计算机实用技术(Part II)
38. 后台进程
维护和增强物理结构和内存结构之间的关系：
? 主要的后台进程：
DBWn PMON CKPT
LGWR SMON
? 可选的后台进程：
ARCn LMDn QMNn
CJQ0 LMON RECO
Dnnn LMS Snnn
LCKn Pnnn
后台进程
Oracle结构中有五个主要的后台进程，我们会在稍后讨论它们。除了所列主要的
进程之外，Oracle还有一些可选的后台进程，当它们的选项使用时就会运行。这
些可选进程没有列入这次课程内容，除了后台进程ARCn。下面列出的是一些可选
的后台进程：
? ARCn：归档
? CJQ0：作业队列协调进程
? Dnnn：调度进程
? LCKn：RAC Lock 管理器——例程锁
? LMDn：RAC DLM 监视器——远程锁
? LMON：RAC DLM 监视器——全局锁
? LMS：RAC全局缓存服务
? Pnnn：并行查询从动进程
? QMNn：高级队列
? RECO：恢复进程
? Snnn：共享的服务进程
39. 数据库写入程序 (DBWn)
服务器进程在缓冲区高速缓存中记录回退和数据块的更改。数据库写入程序
(DBWn) 将灰数据缓冲区从数据库缓冲区高速缓存写入数据文件。它确保有足够
数量的空闲缓冲区（即当服务器进程需要读
取数据文件中的块时可以覆盖的缓冲区）在
数据库缓冲区高速缓存中可用。由于服务器
进程只在缓冲区高速缓存中进行更改，因此
数据库性能得到改善。
DBWn 延迟写入数据文件直到发生下列事件
之一：
? 增加的或正常的检查点
? 灰数据缓冲区的数量达到阈值
? 当进行扫描而无法找到任何空闲缓冲区
时，进程扫描了指定数量的块
? 出现超时
- 50 -
计算机实用技术(Part II)
? Real Application Clusters (RAC) 环境中的ping请求
? 将正常的表空间或临时表空间置于脱机
? 将表空间置于只读模式
? 删除或截断表
? 备份表空间
40. 日志写入程序 (LGWR)
重做日志写入程序 (LGWR)
LGWR 在下列情况下执行从重做日志缓冲
区到重做日志文件的连续写入：
? 当提交事务处理时
? 当重做日志缓冲区的三分之一已满时
? 当重做日志缓冲区中记录了超过 1 MB
的更改时
? 在 DBWn 将数据库缓冲区高速缓存中
修改的块写入数据文件以前
? 每三秒钟
因为恢复操作需要重做，所以 LGWR 只在
重做写入磁盘后确认 COMMIT 命令。
LGWR 也能调用 DBWn 写入数据文件。
41. 系统监控程序 (SMON)
如果 Oracle 例程失败，那么 SGA 中尚
未写入磁盘的所有信息都会丢失。例如，操作
系统的失败导致例程失败。例程丢失后，后台
进程 SMON 在数据库重新打开时自动执行例程
恢复。
恢复例程需要进行以下步骤：
1. 前滚以恢复尚未记入数据文件但已经记入
联机重做日志中的数据。由于例程失败过
程中 SGA 的丢失，这些数据尚未写入磁盘。
在这个进程中，SMON读取重做日志文件并
将重做日志中记录的更改应用到数据块
中。由于所有提交的事务处理都已被写入重做日志，因此该进程完全恢复这
些事务处理。
2. 打开数据库以允许用户登录。未被未恢复事务处理锁定的任何数据都立即可
用。
3. 回退未提交的事务处理。它们由 SMON 回退，或在访问锁定的数据时由单个
服务器进程回退。
SMON 也执行一些空间维护功能：
? 它联合或合并数据文件中空闲空间的邻近区域。
? 它回收临时段将它们作为数据文件中的空闲空间返回。临时段用于在SQL 语
句处理过程中存储数据。
- 51 -
计算机实用技术(Part II)
42. 进程监控程序 (PMON)
进程失败后，后台进程 PMON 通过下面的方法
进行清理：
? 回退用户的当前事务处理
? 释放当前保留的所有表锁或行锁
? 释放用户当前保留的其它资源
? 重启被异常挂起的调度进程
43. 检查点 (CKPT)
功
能：
? 在
检查点时向DBWn发出信号
? 用检查点信息更新数据文件头
? 用检查点信息更新控制文件
44. 归档 (ARCn)
? 可选的后台进程
? 当设置为ARCHIVELOG模式时，自动
归档联机重做日志文件
? 保留数据库的所有更改记录
ARCn是可选的后台进程，然而它对
于磁盘丢失后的数据库恢复起着至
关重要的作用。当联机重做日志文
件填满时，Oracle 服务器开始写入
下一个联机重做日志文件。从一个
重做日志到另一个的切换过程称为日志切换。ARCn 进程在每次日志切换时启动
已满日志组的备份或归档。在日志能够重新使用之前，它自动将联机重做日志归
档，以便对数据库做的所有更改得以保留。这样即使磁盘驱动器破坏也能够将数
据库恢复到出错时的程度
将重做日志文件归档
DBA 必须做出的一个重要决策是配置数据库以 ARCHIVELOG 模式还是以
NOARCHIVELOG 模式操作。
NOARCHIVELOG 模式：在 NOARCHIVELOG 模式中，每发生一次日志切换都会覆盖
联机重做日志文件。LGWR 直到重做日志组的检查点完成才覆盖该组。这确保当
发生例程崩溃时提交的数据能够得以恢复。在例程崩溃过程中只丢失 SGA。 磁
盘没有任何丢失，只有内存会丢失。例如，操作系统的崩溃引起例程崩溃。
ARCHIVELOG 模式：如果配置数据库使它以 ARCHIVELOG 模式运行，那么已满的
联机重做日志文件的非活动组必须归档之后才能够再次使用。因为对数据库所做
的更改记录在联机重做日志文件中，所以 DBA 能够使用数据文件的物理备份和
归档的联机重做日志文件来恢复数据库，而不会由于任何单个出错点（包括磁盘
的丢失）而丢失任何已提交数据。通常将生产数据库配置为以ARCHIVELOG 模式
- 52 -
计算机实用技术(Part II)
运行。
45. 逻辑结构
? 描述如何使用数据库的理空间
? 层次结构由表空间、段、区和块组成
Logical Structure
逻辑层次结构包括如下部分：
? 一个Oracle 数据库至少包含一个表空间
? 一个表空间包含一个或多个段
? 一个段是由若干区组成的。
? 一个区是由若干逻辑块组成的。
? 块是读或写操作的最小单元。
Oracle 数据库结构包括构成数据库的逻辑结构和物理结构。
? 物理结构包括构成数据库的控制文件、联机重做日志文件和数据文件。
? 逻辑结构包括表空间、段、区和数据块。
Oracle 服务器通过表空间和逻辑存储结构（包括段区和数据块），使您能够实
现磁盘空间使用的细粒度控制。
46. 表空间
Oracle 数据库内的数据存储在表空间内。
? Oracle 数据库可以从逻辑上分组到称为表空间的更小的逻辑空间区。
? 一个表空间在某一时刻只能属于一个数据库。
? 每个表空间由一个或多个称为数据文件的操作系统文件组成。
? 表空间可以由一个或多个段组成。
? 表空间可以在数据库运行时联机。
? 除了 SYSTEM 表空间或者有活动回退段的表空间，其它表空间可以脱机，不
影响数据库运行。
? 表空间可以在可读写和只读状态之间切换。
47. 数据文件（不是逻辑结构）
? Oracle 数据库内的每个表空间由一个或者多个称为数据文件的文件组成。这
些物理结构与在其上运行 Oracle 服务器的操作系统是一致的。
? 一个数据文件只能属于一个表空间。
? 通过分配指定数量的磁盘空间加上少量的开销，Oracle 服务器创建表空间数
据文件。
? 数据文件创建后，数据库管理员可以更改其大小或者指定数据文件应随着表
- 53 -
计算机实用技术(Part II)
空间内对象的增长而动态增长。
48. 段
? 段是为表空间内特定逻辑存储结构分配的空间。
? 表空间可以由一个或多个段组成。
? 段无法跨越表空间；但是段可以跨越属于同一表空间的多个数据文件。
? 每个段由一个或多个区组成。
49. 区
按区向段分配空间。
? 一个或多个区组成一个段。
? 当段创建后它至少由一个区组成。
? 随着段增长将向该段添加区。
? DBA 可以手动向段添加区。
? 一个区就是一组连续的 Oracle 块。
? 一个区可能不跨越数据文件，但必须存在于一个数据文件内。
50. 数据块
Oracle 服务器以所谓的 Oracle 块或者数据块为单元管理数据文件中的存储空
间。
? Oracle 数据库内的数据存储在数据块内，数据块为最精细的粒度等级。
? Oracle 数据块是 Oracle 服务器能够分配、读或写的最小存储单元。
? 一个数据块对应一个或多个从现有数据文件中分配的操作系统块。
? 每个 Oracle 数据库的数据块大小由初始化参数 DB_BLOCK_SIZE 在创建数据
库时指定。
? 数据块大小应当是操作系统块大小的整数倍以避免不必要的 I/O。
? 数据块大小最大值取决于操作系统。
51. 处理 SQL 语句
? 使用以下进程与例程连接:
? 用户进程
? 服务器进程
? 所用的 Oracle 服务器组件取决于 SQL 语句的类型：
? 查询返回行
? DML 语句日志更改D
? 提交以确保事务恢复
? 某些 Oracle 服务器组件不参与 SQL 语句处理
52. 处理查询
? 语法分析:
? 搜索相同的语句
? 检查语法、对象名称和权限
? 语法分析期间锁定所用对象
- 54 -
计算机实用技术(Part II)
? 创建和存储执行计划
? 绑定：获得变量的值
? 执行：执行语句
? 提取：返回行给用户进程
53. 处理 DML 语句
? 语法分析：分析阶段与处理查询时所用的分析阶段相同。
? 绑定：绑定阶段与处理查询时所用的绑定阶段相同。
? 执行：
? 如果缓冲区高速缓存中尚不存在数据和undo块，那么服务器进程就会
从数据文件中将它们读入缓冲区高速缓存。
? 服务器进程在将要修改的行上放置锁。Undo块用于存储成图象前的数
据，以便必要的情况下 DML 语句能够回退。
? 数据块更改记录数据的新值。
? 服务器进程将成图象前的数据记录到undo块中，并且更新数据块。这
两种更改都是在数据库缓冲区高速缓存中进行的。缓冲区高速缓存中
的任何已更改块都标记为灰数据缓冲区——即与磁盘中相应的块不同
的缓冲区。
? DELETE 或 INSERT 命令的处理使用类似的步骤。DELETE 命令的成图
象前数据包含已删除的行中的列值，而 INSERT 命令的成图象前数据
中包含行的位置信息。
54. 处理 DDL 语句
DDL语句（数据定义语言）的执行不同于 DML 语句（数据操纵语言）。因为成功
的DDL 语句要求写入数据字典。 对于这些语句，语法分析实际包括了语法分析、
数据字典搜索以及执行。 事务管理、会话管理以及系统管理的SQL 语句处理时
用到了语法分析和执行阶段，再次执行它们时，只需运行另一个执行即可。
- 55 -
计算机实用技术(Part II)
习题：
1-18，从后面的括号里按照下图的顺序选择正确的答案填入：
A、控制文件；B、库高速缓存；C、数据服务器；D、用户进程；
E、重做日志缓冲区；F、重做日志文件；G、SMON；H、服务器进程；
I、LGWR；J、数据文件；K、数据字典高速缓存；L、CKPT；M、PMON；
N、数据缓冲区高速缓存； O、RECO；Q、SQL；R、DBWn；S、LCK0；
0.T、例程；U、SGA；V、共享池；W、PGA；
1、 （ ） 7、 （ ） 13、 （ ）
2、 （ ） 8、 （ ） 14、 （ ）
3、 （ ） 9、 （ ） 15、 （ ）
4、 （ ） 10、 （ ） 16、 （ ）
5、 （ ） 11、 （ ） 17、 （ ）
6、 （ ） 12、 （ ） 18、 （ ）
19、用户执行SQL 命令更新EMP 表中的行。哪个进程执行此语句？（ ）
A. 用户进程
B. 服务器进程
C. DBWn
D. LGWR
- 56 -
计算机实用技术(Part II)
20、用户执行SQL 命令更新EMP 表中的行。上一个问题中确定的进程在什么位置
进行更改？（ ）
A. 数据文件
B. 数据库缓冲区高速缓存
C. 共享池
D. 参数文件
21、以下哪些文件不是数据库的一部分？（ ）
A. 重做日志文件
B. 控制文件
C. 口令文件
D. 数据文件
22、以下哪些内存区域不是SGA 的一部分？（ ）
A. 数据库缓冲区高速缓存
B. PGA
C. 重做日志缓冲区
D. 共享池
23、以下哪些内存区域用于高速缓存数据字典信息？（ ）
A. 数据库缓冲区高速缓存
B. PGA
C. 重做日志缓冲区
D. 共享池
24、以下哪些阶段用于处理DML 语句？（多选）（ ）
A. 分析
B. 执行
C. 提取
25、当用户执行提交时，在Oracle 服务器向用户返回“提交已完成”消息之前，
将在以下哪些文件中记录更改？（ ）
A. 重做日志文件
B. 控制文件
C. 口令文件
D. 数据文件
- 57 -
| 课件名称： | 计算机实用技术 |
| 课件分类： | 计算机 |
| 课件类型： | 参考资料 |
| 文件大小： | 679.1KB |
| 下载次数： | 0 |
| 评论次数： | 0 |
| 用户评分： | 0 |
