Oracle复习大纲

1、复习大纲² 教材上的所有选择题第3、5、6、7、8、9、10、13、15章的选择题。² SQL语言基础MERGE语句、在FROM子句中使用子查询、在DDL语句中使用子查询、使用WITH子句的子查询。² PL/SQL程序设计实验4（存储过程、函数、包、触发器、匿名程序块）、游标。一. Oracle数据库系统结构1.Oracle数据库系统结构1) 实例(1) 内存结构a) 系统全局区SGA：数据高速缓冲区、日志缓冲区、共享池、大型池、Java池、流池等b) 程序全局区PGA c) 用户全局区UGA(2) Oracle进程：后台进程、前台进程（包括服务进程、用户进程）2

2、) 数据库(1) 逻辑结构：表空间、数据段、数据区和数据块(2) 物理结构：数据文件、控制文件、重做日志文件（日志文件）、归档文件、初始化参数文件、跟踪文件、口令文件、警告文件、备份文件等2.Oracle数据库的逻辑结构1) 表空间TableSpace （最大的逻辑数据存储层次）2) 数据段Segment3) 数据区Extent4) 数据块Block（最小的逻辑数据存储层次）3.Oracle数据库的物理结构1) 数据文件2) 控制文件3) 重做日志文件4) 归档文件5) 初始化参数文件6) 跟踪文件7) 口令文件8) 警告文件9) 备份文件4.Oracle服务 OracleCSService

3、OracleDBControlORCL OracleOraDb10g_home1iSQL*Plus OracleOraDb10g_home1TNSListener OracleServiceORCL 其中，ORCL为Oracle服务名(SID)，Listener为监听程序名5.监听程序配置文件listener.ora和本地Net服务名配置文件tnsnames.ora1) Oracle客户端程序Net Configuration Assistant完成的监听程序配置结果保存的文件名为listener.ora2) 本地Net服务名配置结果保存的文件名为tnsnames.ora6.Oracle的主要

4、内存区域系统全局区SGA、程序全局区PGA 、用户全局区UGA7.Oracle的后台进程包括处理器监视进程PMON、系统监视器SMON、数据库写入器DBWr、日志写入器LGWR、归档器ARCn、检查点进程CKPT等。8.表空间分类1) 系统表空间(1) 系统表空间SYSTEM(2) 辅助系统表空间SYSAUX2) 非系统表空间(1) 撤销表空间UNDOTBS1(2) 临时表空间TEMP(3) 用户表空间：USERS、EXAMPLE（实例表空间）3) 大文件表空间、小文件表空间一个大文件表空间只包含一个大数据文件。9. 关闭数据库shutdown1) 正常关闭：shutdown normal 命

5、令2) 立即关闭：shutdown immediate命令3) 异常关闭：shutdown abort命令10.启动数据库startup1) 正常启动：打开一个现场，将数据库装入，然后打开数据库供用户使用；2) 安装和非安装启动：(1) 安装启动：选项是MOUNT，只将数据库装入现场，而不打开数据库(2) 非安装启动：选项是NOMOUNT，只建立数据库现场，不装入现场，不打开数据库供用户使用3) 独占和共享启动(1) 独占启动：选项是EXCLUSIVE，只允许一个例程使用该数据库(2) 共享启动：选项是SHARED，允许多个例程使用数据库(3) 这两者只在选择了并行选件时才有效，它们都装入数据

6、库并打开。4) 约束启动选项是RESTRICT，只能为有特殊权限的数据库管理员使用。5) 强制启动选项是FORCE6) 带初始化参数文件的启动11. Oracle系统的常用管理工具1) SQL*Plus2) SQLPlus Worksheet 3) Enterprise Manager Console（EMC）4) 浏览器模式下的管理工具(1) OEM（企业管理器）(2) iSQL*Plus5) PL/SQL Developer，英宇世纪公司（Allround Automations）二. 安全管理、grant/revoke1.用户的类型及职责：1） 超级用户（internal、sys）：创建

7、一个新的数据库、启动和关闭数据库、修改数据库运行模式、完成数据库的备份与恢复、修改数据库的结构、创建用户、权限管理等2） 数据库管理员（sys、system）：具有DBA角色的用户，可以执行数据库内部的任意操作3） 其它用户（如scott等）：可以进行数据库开发、创建任何实体、不具有修改数据库结构的权限2. Oracle数据库初始用户1) SYS：是数据库中具有最高权限的数据库管理员，可以启动、修改和关闭数据库，拥有数据字典；2) SYSTEM：是一个辅助的数据库管理员，不能启动和关闭数据库，但可以进行其他一些管理工作，如创建用户、删除用户等。3) SCOTT：用于测试网络连接的用户，其口令为

8、tiger。4) PUBLIC：实质上是一个用户组，数据库中任何一个用户都属于该组成员。要为数据库中每个用户都授予某个权限，只需把权限授予PUBLIC就可以了。3. CONNECT、RESOURCE、DBA角色的区别（1） CONNECT ( 连接角色)只可登录oracle ,不可以创建数据库实体。可以进行 SELECT ，INSERT，UPDATE和DELETE操作。（2） RESOURCE (资源角色)可以创建实体（表，视图等），不可以创建修改数据库结构。可以进行CONNECT所做的工作，此外，还可以进行CREATE TABLE、CREATE SEQUENCE、CREATE PROCEDU

9、RE、CREATE TRIGGER、CREATE INDEX及CREATE CLUSTER等。（3） DBA(数据库管理员角色)拥有全部特权，是系统最高权限，只有它才能修改数据库结构 。可以进行除 RESOURCE外，还可以进行数据库的管理操作。4.权限用户对数据库进行操作的权利，包括系统权限、对象权限。5.角色和权限的关系权限和角色都是安全性机制的内容之一。两者的相同点是都允许用户执行某些操作。不同点是后者是前者的集合。权限之间不能授予，但是角色之间可以授予。角色还可以拥有口令。因此，使用角色是比使用权限更高一层的安全管理机制。6.概要文件的概念Oracle的资源管理是通过概要文件(prof

10、ile)来实现的。概要文件实际上是一个具有一定名称的资源限定集，通过概要文件为用户设置资源限额来限制用户对数据库或实例中资源的使用，可以为每类用户设置一个概要文件，而不必在用户上单独设置。7.修改用户alter user解锁UNLOCK/加锁LOCK选项，ALTER USER scott ACCOUNT UNLOCK|LOCK。8.删除用户drop user级联删除选项CASCADE可以同时删除该用户拥有的所有对象。9.GRANT语句1) 为用户授予系统权限，使用WITH ADMIN OPTION选项表示该用户可以将这种系统权限转授予其他用户。格式：GRANT 系统权限名 TO 用户名|角色名

11、|PUBLIC WITH ADMIN OPTION2) 为用户授予对象权限，使用WITH GRANT OPTION选项表示该用户可以将这种对象权限转授予其他用户。格式：GRANT 对象权限名（列名） ON 对象 TO 用户名|角色名|PUBLIC WITH GRANT OPTION10.权限有关的数据字典视图1) USER_SYS_PRIVS查看分配给某个用户的系统权限信息2) USER_TAB_PRIVS_MADE和USER_COL_PRIVS_MADE查看该用户分配给其他用户的对象权限信息3) USER_TAB_PRIVS_RECD和USER_COL_PRIVS_RECD查看其他用户分配给

12、该用户的对象权限信息。三. 表1. Oracle数据库系统支持的表的种类1) 关系表包括堆表、外部表、索引组织表、临时表、分区表、簇表、散列簇表2) 对象表3 )XML type 表2.外部表（用户权限：CREATE ANY DIRECTORY、CREATE TABLE）的概念外部表是指在Oracle数据库之外的文件系统中存储的只读表，使用外部表，无须将数据复制到数据库中并且强制更新，可以让数据依然保留在普通文件中，并且允许数据库对其进行实地读取。因此，外部应用可以采用它认为合适的方法更新数据。应该具有 CREATE ANY DIRECTORY 和 CREATE TABLE系统权限。3.创建表

13、CREATE TABLE的子句ON COMMIT PRESERVE | DELETE选项的作用。ON COMMIT 子句控制临时表中数据的持续时间。其中，DELETE选项表示临时表中的数据将在事务结束时被删除，PRESERVE选项表示临时表中的数据将在会话结束时被删除。四. SQL语言基础1. 子查询的分类和使用规则按照子查询的执行方式，可以分为相关子查询和不相关子查询；按照子查询返回结果集的特点，可以分为单行子查询，多行子查询，多列子查询。使用子查询的基本规则是：子查询必须使用括号括起来，否则无法判断子查询语句的开始和结束。子查询中不能包括Order by子句。子查询允许嵌套多层，但是最多嵌

14、套255层。在子查询中可以使用两种比较运算符：单行运算符和多行运算符。2. 连接查询：笛卡尔积、内连接、外连接、自连接、交叉连接3. 连接查询和子查询在很多查询情况下，既可以使用子查询，也可以使用连接查询。相对而言，连接查询的效率高于对应的子查询的效率。但是，经常情况是连接查询语句的复杂程度远远高于子查询语句的复杂程度。如果能够使用连接查询完成的操作，应该尽可能地使用连接查询，而不使用子查询。4. 高级查询：集合查询、层次查询、情景查询、分析查询5. 单行子查询：内层子查询只返回单行单列值，采用的运算符是=。6. 多行子查询：内层子查询返回多行值，采用的运算符包括 IN、ANY 和ALL。7.

15、 TRUNCATE TABLE语句和不带 WHERE 的 DELETE语句的区别和联系联系：两个语句功能是一样的，都是删除表中的所有数据；区别：TRUNCATE TABLE 速度更快，占用的日志更少。1) TRUNCATE TABLE 直接释放数据页，在事务日志中只记录数据页的释放；2) DELETE 是一行一行地删除，在事务日志中要记录每一条记录的删除。8. 为什么TRUNCATE TABLE语句不能代替无WHERE的 DELETE语句？1) 要保留标识的情况下不能用 TRUNCATE TABLE，因为 TRUNCATE TABLE 会重置标识；2) 需要使用触发器的情况下不能使用 TRUN

16、CATE TABLE ，它不会激发触发器；3) 对于由 FOREIGN KEY 约束引用的表（即主键所在的表，不是外键所在的表）不能使用 TRUNCATE TABLE；4) 对于参与了索引视图的表不能使用 TRUNCATE TABLE ，注意指索引视图，并非普通视图。以下9-13为必须掌握的基本SQL语句，9. MERGE语句包括了两个数据操纵命令，如果合并条件成立则执行UPDATE 命令，如果合并条件不成立则执行INSERT命令。现有表source_emp和target_emp，表中数据如下。利用source_emp表中的数据更新target_emp表中的数据，对target_emp表中存在

17、的员工信息进行更新，对不存在的员工进行信息插入。 SELECT * FROM source_emp;EMPNO ENAME DEPTNO-100 JOAN 10110 SMITH 20120 TOM 30SELECT * FROM target_emp;EMPNO ENAME DEPTNO-100 MARRY 2020 JACK 40 -合并语句MERGE INTO target_emp t USING source_emp s ON (t.empno=s.empno)WHEN MATCHED THEN UPDATE SET t.ename=s.ename,t.deptno=s.deptnoW

18、HEN NOT MATCHED THEN INSERT VALUES(s.empno,s.ename,s.deptno)；-合并语句执行后的结果SELECT * FROM target_emp;EMPNO ENAME DEPTNO-100 JOAN 1020 JACK 40110 SMITH 20120 TOM 30 10. 数据复制语句（将某个查询结果插入到一个目标表中）1) INSERT INTO SELECT语句语句格式：Insert into目标表名(column1, column2,.) 子查询要求目标表必须存在，子查询子句为SELECT语句。2) CREATE TABLE AS语句

19、，利用子查询创建表及复制数据语句格式：CREATE TABLE NewTable(column1, column2,.) AS 子查询子查询子句为SELECT语句。例句见【12.在DDL语句中使用子查询】11. 在FROM子句中使用子查询当在FROM子句中使用子查询时，该子查询被作为视图对待，必须为该子查询指定别名。例1 查询各个员工的员工号、员工名及其所在部门平均工资。SELECT empno,ename,d.avgsal FROM emp,(SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) dWHERE emp.deptno=d

20、.deptno;例2 查询各个部门号、部门名、部门人数及部门平均工资。SELECT dept.deptno,dname, d.amount,d.avgsal FROM dept, (SELECT deptno,count(*)amount, avg(sal) avgsal FROM emp GROUP BY deptno) d WHERE dept.deptno=d.deptno; 12.在DDL语句中使用子查询可以在CREATE TABLE和CREATE VIEW语句中使用子查询来创建表和视图。CREATE TABLE emp_subquery AS SELECT empno,ename,s

21、al FROM emp;CREATE VIEW emp_view_subqueryASSELECT * FROM emp WHERE sal>2000;13.使用WITH子句的子查询如果在一个SQL语句中多次使用同一个子查询，可以通过WITH子句给子查询指定一个名字，从而可以实现通过名字引用该子查询，而不必每次都完整写出该子查询。见例2。例1 查询人数最多的部门的信息。SELECT * FROM deptWHERE deptno IN (SELECT deptno FROM emp GROUP BY deptno HAVING count(*)>=ALL( SELECT count

22、(*) FROM emp GROUP BY deptno);例2 【例1】中相同的子查询连续出现了两次，因此可以按下列方式编写查询语句。WITH deptinfo AS(SELECT deptno,count(*) num FROM emp GROUP BY deptno) SELECT * FROM dept WHERE deptno IN(SELECT deptno FROM deptinfo WHERE num=(SELECT max(num) FROM deptinfo); 五. PL/SQL1.对象定义：属性和方法2.集合类型：记录、可变数组、PL/SQL表、关联数组和嵌套表3.嵌套

23、表的概念 嵌套表是能够在数据库表的列中存储的对象集合。嵌套表与索引表非常类似。它们之间最主要的区别在于，嵌套表可以存储在数据库的列中，而索引表不行。使用嵌套表非常类似于在用户数据库的列中存储一个记录数组。4.控制语句的种类1） 选择结构（1） IF语句(IFTHENELSEIFELSEEND IF)（2） CASE语句(CASE WHEN.THENEND CASE)2） 循环结构（1） 简单循环（LOOP.END LOOP）（2） WHILE循环（WHILELOOPEND LOOP）（3） FOR循环（FORLOOP.END LOOP）3） 跳转结构(<<标号>>GOT

24、O 标号)5.游标的概念和分类1） 游标的概念游标（CURSOR）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。使用游标时，SELECT语句查询的结果可以是单条记录，多条记录，也可以是零条记录。游标工作区中，存在着一个指针（POINTER）,在初始状态它指向查询结果的首记录。2） 游标的分类（1） 显式游标用户定义、操作，用于处理返回多行数据的SELECT查询。显式游标是必须通过编写必要的PL/SQL例程来进行管理的游标。显式游标操作步骤：定义游标、打开游标、从游标中获取数据、处理数据、关闭游标。（2） 隐式游标由系统自动进行操作，用于处理DML语句和返

25、回单行数据的SELECT查询。一种是在用户的PL/SQL中使用数据操纵语言时，由ORACLE预先定义的名称为SQL的隐式游标（ SQL 游标 ）；另一种是CURSOR FOR LOOP的隐式游标。6.程序包的概念包是包含一个或多个子程序单元（过程、函数等）的容器，是一种全局结构，类型有数据库内置包和用户创建的包，由包规范和包体组成。包规范声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。 包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，但是它们是私有元素，只能由同

26、一包体中其他过程和函数使用。7.触发器的概念触发器是一种特殊类型的存储过程，编译后存储在数据库服务器中。当特定事件发生时，由系统自动调用执行，而不能由应用程序显式地调用执行。触发器不接受任何参数。触发器主要用于维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束，并对数据库中特定事件进行监控和响应。8.触发器的分类1） DML触发器建立在基本表上的触发器，响应基本表的INSERT，UPDATE，DELETE操作。DML触发器的种类（1） 语句级前触发器（2） 语句级后触发器（3） 行级前触发器（4） 行级后触发器2） INSTEAD OF触发器建立在视图上的触发器，响应视图上的INSER

27、T，UPDATE，DELETE操作。3） 系统触发器建立在系统或模式上的触发器，响应系统事件和DDL（CREATE，ALTER，DROP）操作。 9.DML触发器的执行顺序1） 如果存在，则执行语句级前BEFORE触发器。2） 对于受触发事件影响的每一个记录：（1） 如果存在，则执行行级前触发器；（2） 执行当前记录的DML操作（触发事件）；（3） 如果存在，则执行行级后触发器。3） 如果存在，则执行语句级后AFTER触发器。 10. 存储过程和触发器的区别和联系联系：两者都是使用PL/SQL编写，都可以完成指定的任务，触发器是一种特殊类型的存储过程。区别：存储过程需要手工执行，可以有输入、输

28、出参数。触发器是自动触发执行的，它包括了触发事件、触发时间、触发动作，它没有输入、输出参数，但是可以引用数据修改前后的状态。11.触发器中的前映像:old和后映像:new数组变量:old 和:new 仅适用于行触发器for each row，这两个变量是系统自动提供的数组变量。:new用来记录新插入的值，:old用来记录被删除的值。使用insert语句触发只有:new里有值，使用delete语句触发只有:old里有值，使用update语句触发:new和:old里都有值12. 游标程序实例1） 利用简单循环统计并输出各个部门的平均工资。DECLARE CURSOR c_dept_stat IS

29、SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno; v_dept c_dept_stat%ROWTYPE;BEGINOPEN c_dept_stat;LOOP FETCH c_dept_stat INTO v_dept; EXIT WHEN c_dept_stat%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dept.deptno|' '| v_dept.avgsal); END LOOP; CLOSE c_dept_stat;END;2） 利用WHILE循环统计并输出各个部门的平均工资。DECL

30、ARE CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno; v_dept c_dept_stat%ROWTYPE;BEGIN OPEN c_dept_stat; FETCH c_dept_stat INTO v_dept; WHILE c_dept_stat%FOUND LOOP DBMS_OUTPUT.PUT_LINE(v_dept.deptno|' '| v_dept.avgsal); FETCH c_dept_stat INTO v_dept; END LOOP; CLOSE c_dept_stat;END; 3） 利用FOR循环统计并输出各个部门的平均工资。DECLARE CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno;BEGIN FOR v_dept IN c_dept_stat LOOP DBMS_OUTPUT.P