Oracle期末复习

最新推荐文章于 2022-12-15 15:55:09 发布

学徒小昊

最新推荐文章于 2022-12-15 15:55:09 发布

阅读量1k

点赞数 4

分类专栏：期末复习文章标签： oracle 数据库

本文链接：https://blog.csdn.net/LH_991215/article/details/122126595

版权

期末复习专栏收录该内容

3 篇文章 1 订阅

订阅专栏

[必须掌握]Oracle知识点整理

1.Oracle逻辑结构——段、区间、块间的关系，存储分配、管理空间的两个基本单位，块大小的定义位置和参数。

答：
段由区间组成，区间由块组成。
区间是Oracle分配磁盘空间的最小单位（存储分配）。
块是Oracle管理数据库存储空间的最小数据存储单位（管理空间）。
标准的数据块大小在pfile文件由初始参数DB_BLOCK_SIZE指定。

2.Oracle段有哪些

答：
（1）数据段：每个不在簇的表有一个数据段，表中数据都存在数据段中的区间中，每个簇都有一个数据段，簇中每个表的数据都存储在簇的数据段中。
（2）索引段：每个索引都有一个索引段。
（3）临时段：需要临时工作区时创建，执行完后被回收。
（4）回滚段：一个事务只能用一个回滚段，一个回滚段可以对应多个事务。

3.SGA、PGA是什么，SGA的构成

答：
SGA：系统全局区（也叫共享全局区）
PGA：程序全局区。
SGA包含：数据缓冲区、重做日志缓冲区、共享池、Java池、大型池

4.数据字典的含义，Oracle数据字典的隶属用户，存放的表空间。

答：数据字典是 Oracle 存放关于数据库内部信息的地方，其用途是用来描述数据库内部的运行和管理情况。
隶属于sys用户，存放在system表中

5.索引、簇的作用

答：索引是对表的一列或多列进行排序的结构，数据库在搜索排序结构时效率都会大大提高。
簇是有些表共享公共的列，并经常被同时访问，为了提高数据存取的效率，把这些表在物理上存储在一起，得到的表的组合就是簇。

6.归档模式和非归档模式的区别

答：非归档模式只能做冷备份,并且恢复时只能做完全备份。最近一次完全备份到系统出错期间的数据不能恢复。
归档模式可以做热备份,并且可以做增量备份,可以做部分恢复。

7.重做日志文件组的个数和作用

每个数据库至少有两个重做日志文件
重做日志文件组的数量：在oracle数据库中，至少需要两个可用的重做日志文件组，重做日志文件组的最大数量由控制文件中的最大参数MAXLOGFILES指定。每个重做日志文件组中，至少需要一个可用的重做日志文件成员。
重做日志文件主要用于在oracle发生故障的时候和数据库备份文件配合恢复数据库，其中保存了数据库的变更操作信息。

8.控制文件中定义的内容

答：控制文件包含：数据库名称、数据文件和重做日志文件的名称和位置、数据库创建的时间戳、当前的日志序列号、检查点信息。

9.三种构成Oracle数据库的文件是什么

答：控制文件、数据文件、日志文件。

10.增加表空间大小的方法

答：
（1）给表空间增加数据文件。
（2）新增数据文件，允许数据文件自动增长。
（3）允许已存在的数据文件自动增长。
（4）手动改变已存在数据文件的大小。

11.Oracle后台进程——LGWR、DBWn、CKPT、SMON、PMON的作用

LGWR：日志写入进程，把重做日志缓冲区中的内容写入重做日志中。
DBWn：数据库写入进程，把缓冲区中的内容写入到数据文件中。
CKPT：检查点进程，当发生检查点事件时唤醒DBWn进程，更新数据库所有的数据文件和控制文件，并标记最新的检查点，以便下一次更新从最新的检查点开始。
SMON：系统监控进程，当失败的数据库实例重新启动时，SMON进程完成实例的恢复工作。
PMON：进程监控进程，当用户进程失败时，用于完成进程的恢复。

12.Oracle实例是什么，Oracle实例支持的4种状态及各状态下可完成的操作。

答：实例是能够支撑数据库运行的数据库程序(SGA+Oracle进程)。
打开：启动例程，装载并打开数据库。允许用户连接数据库并进行数据访问操作。
关闭：允许用户访问数据库的状态转变为休止状态。终止访问进程，释放Oracle运行内存。
已装载：启动例程并装载数据库，没有打开数据库。用于更改数据库的归档模式、执行恢复操作、数据文件恢复。不允许用户访问
已启动：启动实例，不装载。重新创立控制文件，对控制文件进行恢复或重新创建数据库。

13.概要文件是什么，对用户的作用

答：概要文件(资源文件)：是oracle为了合理地分配和使用系统资源而提出的概念。
作用：概要文件可以限制允许用户使用的资源。

14.参数文件是什么，有什么作用。

答：Oracle参数文件是一个包含一系列参数以及参数对应值的操作系统文件。
作用：决定了数据库的物理结构、内存、数据库的限制以及系统大量的默认值、数据库的各种物理属性、指定数据库控制文件名和路径等信息，是进行数据库设计和性能调优的重要文件。

15.序列是什么，如何使用序列。

序列号是一个Oracle对象，最多可有38个数字。
序列的作用是自动生成整数型数值，作为表中标识字段的值，每插入一条记录时加一。
使用：insert into 表名 value（序列名.currval）

16.角色是什么，角色和用户的关系，如何定义用户和角色，如何将角色、权限授权给用户、如何删除用户。

答：
（1）角色是一组相关权限的集合，为了简化权限管理。
（2）把角色赋给用户，用户就有了该角色的权利。
（3）定义用户：create user 用户名，定义角色：create role 角色名
（4）授权角色给用户：grant 角色名 to 用户名；
授权权限给用户：grant 权限名 on 表名 to 用户名
（5）删除用户：drop user 用户名

17. Create/drop/alter的作用

Create可以用来创建表、触发器、过程和函数。
Drop可以用来删除表，触发器、过程和函数。
Alter可以用来修改表的结构。

18.rowid的作用

答：每一行数据都有一个唯一的标识符，称为rowid；rowid是一种数据类型，
作用：可以帮助oracle数据库快速寻找某一行数据的位置。

19.Select/update/delete命令，子查询、连接、模糊匹配、涉空查询、集函数

20.PL/SQL注释符，循环、分支语句语法，变量的声明和赋值

答：注释符：单行注释符：-- 多行注释符：/**/
声明常量格式：常量名 constant 数据类型 := 值;
声明变量格式：变量名数据类型[(宽度) := 初始值];
分支语句格式一：

IF 条件 THEN
	执行语句....
ELSIF 条件 THEN
	执行语句....
ELSE
	执行语句.....
END IF;

分支语句格式二（例题）：

set serveroutput on;
declare
	day integer := 3;
	result varchar(200);
begin
	resule := case day
		when 1 then '星期一';
		when 2 then '星期二';
		........
		when 7 then '星期天';
		ELSE '数据越界'
		END
	dbms_output.put_line(result);
End

循环语句格式一：

FOR 循环变量IN 初始值..终止值
LOOP
	程序块
END LOOP;
循环语句格式二：
while 条件表达式
LOOP
	程序块1
END LOOP;

21.PL/SQL块、命名PL/SQL块——存储过程、函数、触发器

22.存储过程/函数/触发器的定义语法，调用存储过程和函数的方法

23.游标的定义和使用

24.SQL PLUS是什么，常用的SQL PLUS命令及其功能

答：SQL/Plus是用户和服务器之间的一种接口，用户可以通过它使用SQL语句交互式的访问数据库。

25.区分系统权限和对象权限

系统权限：系统规定用户使用数据库的权限。如创建表，创建索引，连接实例
对象权限：某种权限用户对其他用户的表或视图的存取权限。如：读取视图，可更新某些列

[其他说明]

6.2 用户管理

6.2.1 创建用户

1.在Oracle Enterprise Manager中创建用户

以SYSDBA身份登录到Oracle Enterprise Manager，单击“服务器”超链接，打开服务器管理页面。在“安全性”栏目中单击“用户”超链接，打开“用户管理”页面。单击右侧的“创建”，打开“创建用户”页面，还可以设置表空间。

2.使用CREATE USER语句创建用户

create user 用户名
identified by 口令
default tablespace 默认表空间
temporary tablespace 临时表空间

例6.3：创建管理用户userman

create user userman
identified by userman

创建用户后，系统将同时创建一个同名的方案，用户对同名方案下的数据库对象有管理的权限。

6.2.4 删除用户

1.在Oracle Enterprise Manager中删除用户

在用户管理页面中，选择要删除的用户，单击“删除”，打开确认删除页面。单击“是”，删除指定用户。

2.使用drop user也可以删除指定用户

drop user 用户名

例6.9：删除用户newuser，可以使用

drop user newuser

1.在Oracle Enterprise Manager中创建角色

以SYSDBA身份登录到Oracle Enterprise Manager，单击“服务器”超链接，打开服务器管理页面。在“安全性”栏目中单击“角色”超链接，打开“角色管理”页面。单击右侧的“创建”，打开“创建用户”页面，输入用户名，例如myrole。单击“确定”返回角色列表页面，可以看到新建的角色myrole。单击myrole超链接，可以打开查看角色信息页面。

2.使用CREATE ROLE语句创建角色

create role 角色名
identified by 验证口令

例6.11 创建角色myrole1，验证口令为myrolepwd，语句如下：

create role myrole1
identified by myrolepwd

6.3.5 删除角色

1.在Oracle Enterprise Manager中删除角色

在角色列表页面中，选择要删除的角色，单击“删除”，打开确认删除页面。单击“是”，删除指定角色。

2.使用drop role语句删除指定的角色

例6.15 使用以下语句删除角色myrole

drop role myrole

6.4.1 Oracle权限的分类

1.系统权限

允许用户创建角色：create role
允许用户在任意方案中创建角色：create any role
允许用户删除角色：drop any role
允许将角色授予任何用户或取消授予用户的任何角色：grant any role

例题7.33 查询所有姓李的员工数据

select name as 姓名 from table
where name like '李%';

例题7.33 查询的结果按工资排序

select wage from table
order by wage(加desc是降序)

创建视图：create view XXX as select ........

9.1.1 PL/SQL语言的结构

declare     // 标记声明部分
·······     // 定义常量、变量、类型、游标等
begin		// 标记程序体部分开始
·······		// 编写各种PL/SQL语句、函数和存储过程
exception	// 标记异常处理部分开始
·······
end			// 标记程序体部分结束

示例程序：

set serveroutput on; //使得下面程序能够在SQL/plus中输出
declare
	var_EmpName varchar2(100); // 临时保存读取的用户名
begin
	select Emp_Name into var_EmpName
	from 表名 
	where Emp_id = 4;
	dbms_out.put_line(var_EmpName); //输出变量var_EmpName
end;

变量名称规范：以字符开头，可以包含数字、下划线、$、#，最大长度为30，不区分大小写。
常用类型：char date number varchar2

9.1.1.1 声明常量

声明常量格式：常量名 constant 数据类型 := 值;
例9.1 定义常量conVersion，保存指定产品的版本信息。然后调用dbms_out.put_line

set ServerOutput ON;
declare
	conVersion varchar2(10) := '1.0.11'
begin
	dbms_output.put_line(conVersion);
end;

9.1.1.2 声明变量

声明变量格式：变量名数据类型[(宽度) := 初始值];
例9.2 声明一个变量Database保存数据库的信息，可以使用以下代码：

set serveroutput ON
declare
	Database varchar(20) := 'Oracle 12c';
begin
	dbms_output.put_line(Database);
end

执行部分可以包括以下内容：赋值语句、流程控制语句、SQL语句、游标语句
程序从begin关键字开始，以end关键字结束，每条语句以分号结尾

9.1.1.3 赋值语句

例9.3 在程序运行过程中，对变量进行赋值操作，代码如下：

set serveroutput on;
declare
	Database varchar2(20);
begin
	Database := 'Oracle 12c';
	dbms_output.put_line(Database);
end

9.1.1.4 条件语句

结构格式：

IF 条件 THEN
	执行语句....
ELSIF 条件 THEN
	执行语句....
ELSE
	执行语句.....
END IF;

例9.4 判断数字大小

set serveroutput on;
declare
	num integer := -2;
begin
	IF num > 0 THEN
		dbms_output.put_line('负数');
	ELSIF num < 0 THEN
		dbms_output.put_line('正数');
	ELSE
		dbms_output.put_line('0');
	END IF;
END;

例9.5 判断星期几

set serveroutput on;
declare
	day integer := 3;
	result varchar(200);
begin
	resule := case day
		when 1 then '星期一';
		when 2 then '星期二';
		........
		when 7 then '星期天';
		ELSE '数据越界'
		END
	dbms_output.put_line(result);
End

9.1.1.5 循环语句LOOP…EXIT…END LOOP

模板一

LOOP
	程序块1
	IF 条件表达式 THEN
		EXIT
	END IF
	程序块2
END LOOP;

例9.6 统计1到3的和

set serveroutput on;
declare
	v_num integer := 1;
	v_sum integer := 0;
begin
	LOOP
		v_sum := v_sum + v_num;
		dbms_output.put_line(v_num);
		IF v_num = 3 THEN
			EXIT;
		END IF;
		dbms_output.put_line('+');
		v_num := v_num + 1;
	END LOOP;
	dbms_output.put_line('=');
	dbms_output.put_line(v_sum);
END

模板二

LOOP
	程序块1
	EXIT WHEN 条件表达式
	程序块2
END LOOP;

例9.7

set serveroutput on;
declare
	v_num integer := 1;
	v_sum integer := 0;
begin
	LOOP
		v_sum := v_sum + v_num;
		EXIT WHEN v_num := 3;
		v_num := v_num + 1;
	END LOOP;
	dbms_output.put_line(v_sum);
END;

模板三

while 条件表达式
LOOP
	程序块1
END LOOP;

模板四：

FOR 循环变量IN 初始值..终止值
LOOP
	程序块
END LOOP;

例9.9 还是统计1到3的和

set serveroutput on;
declare
	v_num int;
	v_sum int;
begin
	FOR v_num in 1..3
	LOOP
		v_sum := v_sum + v_num;
	END LOOP;
	dbms_output.put_line('ok');
END

10.1 游标

10.1.1 游标的基本概念

使用显式游标需要以下4个步骤：
（1）说明游标（2）打开游标（3）读取游标（4）关闭游标

例10.1 隐式游标：使用select语句声明隐式游标，从S表中读取name字段(学号为10)到变量tname

set serveroutput on;
declare 
	tname S.name%Type;
begin
	select name into tname 
	from S 
	where sid = 10;
	dbms_output.put_line(tname);
end;

10.1.2 游标控制语句

1.声明游标语句cursor
2.开游标语句open
3.游标取值语句fetch
4.关闭游标语句close

10.1.2.1 声明游标语句

声明格式：

declare 
cursor 游标名(参数列表)
is
select 语句

例10.2 声明一个游标MyCur，读取指定类型的用户信息，代码如下：

declare 
	cursor Mycur(vartype varchar(20))
	is
	select userid, username from users
		where usertype = vartype;

10.1.2.2 打开游标语句OPEN

声明格式：OPEN 游标名(参数列表);
例10.3 打开游标MyCur，读取类型为1的用户信息

OPEN MyCur(1);

显式游标必须先声明，才能打开。

10.1.2.3 游标取值语句FETCH

声明格式：FETCH 游标名 INTO 变量列表;
例10.4 在打开的游标MyCur的当前位置读取数据。

FETCH MyCur into varID, varName;

显式游标必须先打开，才能取值

10.1.2.4 关闭游标语句CLOSE

声明格式：CLOSE 游标名;
使用完显式游标后，应该及时关闭，释放存储空间。

例10.6 游标应用实例：

set Serveroutput ON;
declare
	varId NUMBER;
	varName VARCHAR2(50);
	--定义游标 varType为参数，指定用户类型编号
	CURSOR MyCur(varType NUMBER) 
	is
	select userId, UserName from users
		where UserType = varType;
begin
	OPEN MyCur(1);
	FETCH MyCur INTO varId, varName;
	CLOSE MyCur;
	dbms_output.put_line('用户编号' || varId ||',用户名：' || varName);
end;

10.1.3 游标属性

10.1.3.1 %ISOPEN属性

判断游标是否打开

10.1.3.2 %FOUND属性和%NOTFOUND属性

判断游标是否有效

10.1.3.3 %ROWCOUNT属性

配合条件语句使用，限制游标读取固定行数。

10.1.4 游标FOR循环

10.1.4.1 记录类型

语法：TYPE 记录类型名 is record(字段声明,字段声明,...);
例10.11 声明记录类型User_Record_Type和定义记录变量var_UserRecord，代码如下：

TYPE User_Record_Type IS record
(
	UserId Users.UserId%type,
	UserName Users.UserName%type
);
var_UserRecord User_Record_Type;

例10.12 定义一个与表User结构完全相同的记录变量var_UserRecord1

var_UserRecord1 User % ROWTYPE

10.1.4.2 游标FOR循环

FOR <记录名> IN <游标名> LOOP
	语句一
	语句二
	...
END LOOP

例14 一个经典游标OFR循环的例子：

set serveroutput on;
declare
	CUROSR MyCur(varType number)
	is
	select User_Id, User_Name from Users
		where UserType = varType;
	FOR var_UserRecord IN MyCur(1) LOOP
		dbms_output.put_line('用户编号:' || var_UserRecord.UserID || ',用户名:' || var_UserRecord.UserName);
	END LOOP;
END;

10.1.5 引用游标

引用游标RET是一种动态游标，它比普通的静态游标更加灵活，因为它不依赖指定的查询语句。可以在运行时与不同的查询语句相关联，也可以使用游标变量。
两种类型：强型游标和弱型游标，强型游标返回指定格式的结果集，弱型游标则没有返回类型

这里不太懂

10.1.6 管理游标结果集

10.1.6.1 修改游标结果集中的行

声明语法：

declare
	CURSOR 游标名(参数列表)
	is
	SELECT 语句
	FOR UPDATE OF 待更新列名

调用语法：

UPDATE 表名 set 子句 where CURRENT OF 游标名

例10.19 将所有部门编号为1的员工工资增加100

declare
	CURSOR cur(id number)
	is
	SELECT id, salary from Employment where id == Eid
	FOR UPDATE OF salary
begin
	FOR rec1 IN cur(1) LOOP
		UPDATE Employment set salary = salary + 100
		where CURRENT OF cur;
	END LOOP;
END;

10.1.6.2 删除游标结果集中的行

语法只改变了10.1.6.1里的调用语法中的UPDATE为DELETE FROM

10.2 存储过程

PL/SQL 有以下3种存储过程。
（1）过程：一种基本的存储过程，由过程名、参数和程序体组成。
（2）函数：与过程类似，只是函数有返回值。
（3）程序包：一组相关的PL/SQL过程和函数，由包名、说明部分和包体组成。

10.2.1 过程

可以使用

CREATE OR REPLACE procedure 过程名(参数 in 类型 / 参数 out 类型) 
AS
BEGIN
<过程体>
END [过程名];

例10.21 创建示例过程ResetPwd，此过程是将Users中的指定用户的密码重置为111111，代码如下：

UserMan是方案，User是其中一个表
create or replace procedure UserMan.ResetPwd(v_userId in NUMBER)
as
begin
	UPDATE UserMan.Users SET UserPwd = '111111' 
	where UserId = v_UserId;
END;

例如将编号为1的用户密码重置，可以使用以下代码：

Execute UserMan.ResetPwd(1);
select UserName, UserPwd from UserMan.Users where UserId = 1;

删除过程：DROP PROCEDURE 过程名

10.2.2 函数

语法：

create or replace function 函数名[参数列表][return 函数数据类型]
AS
[局部变量声明]
BEGIN
	过程体
	RETURN 函数值
END;

例10.24 示例函数GetPwd,根据指定用户名返回该用户的密码信息。

CREATE FUNCTION UserMan.GetPwd(v_name in User.UserName % type)
return User.UserPwd % Type
AS
	outpwd Users.UserPwd % Type;
Begin
	select UserPwd INTO outpwd from Users
	where UserName = v_name;
	RETURN outpwd;
END

可以使用下面的程序调用函数：

set serveroutput on;
declare
	varPwd USERMAN.users.userPwd % Type;
begin
	varPwd := UserMan.GetPwd('admin');
	dbms_output.put_line(varPwd);
END;

使用DROP FUNCTION命令删除函数：DROP FUNCTION USERMAN.GetPwd

10.3 触发器

10.3.1 触发器的基本概念

10.3.1.1 触发事件

（1）DML语句事件：INSERT、UPDATE、DELETE等语句
（2）DDL语句事件：CREATE、ALTER、DROP等语句
（3）数据库事件
（4）系统错误

10.3.1.2 触发时间

（1）BEFORE：事件发生之前触发
（2）AFTER：事件发生之后触发

10.3.1.3 触发级别

（1）行触发：对触发事件影响的每一行都执行触发器。
（2）语句触发：对于触发事件只能执行触发器一次。

10.3.2 创建触发器

使用CREATE TRIGGER语句可以创建触发器，创建不同类型触发器的方法略有不同。
（1）创建语句触发器
（2）创建行触发器
（3）创建 instead of 触发器
（4）创建 logon 和 logoff 触发器

10.3.2.1 创建语句触发器

语法：

CREATE OR REPLACE TRIGGER 触发器名[before/after] 触发事件 on 表名/视图名
PL/SQL程序体

例10.32 在以下两表的基础上，建立触发器：

create table Test
(
	id integer,
	name varchar2(50)
);
create table LogTable
(
	log_date DATE,
	action varchar2(50)
);

create or replace Trigger LogUpdaateTrigger after insert or uupdate or delete on Test
declare
	log_action varchar2(50);
begin
	IF inserting Then log_action := 'Insert';
	ELSIF UPDATING THEN log_action := 'Update';
	END IF;
	INSERT INTO LogTable(log_date, action) values(SUSDATE, log_action);
END;

10.3.2.2 创建行触发器

语法：

CREATE OR REPLACE TRIGGER 触发器名 before/after 触发事件 on 表名/视图名
FOR EACH ROW
<PL/SQL程序体>

FOR EACH ROW表示当前触发器为行触发器。

例10.33 当表UserType中的TypeId发生变化时，自动更新表Users中的UserType列的值，从而保证数据的完整性。

CREATE OR REPLACE TRIGGER MyTrigger AFTER UPDATE ON UserType
FOR EACH ROW
BEGIN
	UPDATE Users SET usertype = :new.TypeId 
		where UserType = :old.TypeId;
END;

为了验证触发器的功能，可以在SQL Plus中运行以下命令：

Update UserMan.UserType set TypeId = 3
where TypeId = 1;
COMMIT;

10.3.3.3 创建INSTEAD OF触发器

语法：

CREATE OR REPLACE TRIGGER 触发器名 instead of 触发事件 on 表名/视图名
PL/SQL程序体

10.3.3.4 创建LOGON和LOGOFF触发器

分别是用户登录和用户注销时被触发。
语法：

CREATE OR REPLACE TRIGGER 触发器名 after/before logon on 表名/视图名
PL/SQL程序体

10.3.3 启用和禁用触发器

例10.36 禁用触发器：alter trigger 触发器名 disable
例10.37 启用触发器：alter trigger 触发器名 enable
例10.38 禁用某表所有的触发器：alter table 表名 disable all triggers
例10.39 启用某表所有的触发器：alter table 表名 enable all triggers

10.3.4 编译触发器

触发器如果发生编译错误，可以使用 DBA_ERRORS视图来查看错误信息：

select * from DBA_ERRORs
where type = 'trigger';

例10.41
可以使用下面语句重新编译触发器：

alter trigger 方案名.触发器名 compile;

10.3.5 删除触发器

语法：

drop trigger 方案名.触发器名

学徒小昊

关注

4
点赞
踩
31

收藏

觉得还不错? 一键收藏
0
评论
Oracle期末复习

oracle期末复习
复制链接

扫一扫

专栏目录