oracle 实验

数据库系统概述

数据库工具

Oracle Enterprise Manager Database Express工具

• 企业管理器数据库快捷版，为DBA用户提供基本的数据库系统管理功能
• 在浏览器中访问https://localhost:5500/em/login进入数据库管理登录页面
• 提供安全管理(表空间、还原管理、归档日志、控制文件)、配置管理(初始化参数、内存、当前数据库属性)和性能监控(主机负载、主机内存、主机I/O)

Oracle SQL Developer工具

• 支持DBA进行数据库管理功能操作
• 也能支持DBA进行数据库开发对象操作(创建数据库表、索引、视图、触发器和存储过程等对象)

Database Configuration Assistant工具

• 数据库配置助手，DBCA。
• 可以新建或删除一个数据库，也可以对以有的数据库配置进行修改

SQL Plus工具

• 基于命令行的数据库操作工具
• 可以将SQL语言命令和PL/SQL语言命令提交数据库管理系统执行，实现对数据库进行操作访问和数据库管理
• 可以实现对数据库服务启停控制、数据库创建、用户权限管理和数据表访问等操作

数据库系统结构

关系数据模型

关系数据模型是一种以关系表为数据结构、采用关系操作方式访问数据的数据模型。该数据模型由如下三种部分组成。

• 关系表结构
• 关系操作 [选择，头型，连接··]
• 关系约束 [完整性约束，参照完整性约束，自定义完整性约束]

关系数据库是一种依赖关系模型组织、存储和管理数据的数据库。在数据库中，不仅存放了数据，还存放了数据结构及其数据关系。

多模数据库

• 关系数据库——关系数据模型
• json数据库——json数据模型
• xml数据库——XML数据模型
• 文档数据库——文档数据模型
• 键值
• 图
• 空间
• ···

后面的都是nosql数据库。

数据库系统组成

与其他关系数据库系统一样，都是由用户、数据库、数据库管理系统和数据库应用程序4个部分组成。

数据库用户

数据库用户就是数据库系统的使用人员

数据库应用程序

数据库应用程序是一种在DBMS支持下对数据进行访问处理的应用程序。使用编程语言(如JAVA、C++等)实现对数据库信息的操作访问，但它们需要基于DBMS提供的本地驱动接口或标准接口(如ODBC、JDBC等)才能连接与访问数据库

数据库管理系统

database manage system，DBMS——是一种专门用来传建数据库，管理数据库，维护数据库，并对外提供数据库功能的

主要功能有：

• 创建数据库、数据库表及其他对象
• 读写、修改和删除数据库表数据
• 维护数据库结构
• 执行数据访问规则
• 提供数据库并发控制和安全控制
• 执行数据库备份和恢复
• ···

数据库

在oracle数据库中，主要采用关系表组织与存储数据。除了存储和管理应用的用户数据外，还需要存储与管理数据库本身的元数据、索引数据、运行数据等系统数据

数据库中所包含的各类数据内容：

• 用户数据
• 元数据(数据表的表名称、表属性、列名称、列属性、表之间的数据约束关系)
• 索引数据
• 其他数据

数据库结构

数据库逻辑结构

逻辑存储结构

数据库(Database)->表空间(Tablespace)->段(Segment)->区(Extend)->数据块(Data Block)

表空间

是在数据库下的定级结构单元，用于存储各种数据库对象的段数据

表空间类型：

• SYSTEM表空间：用于存储整个数据库的元数据和系统运行数据
• STSAUX表空间：用于存储软件选件功能模块使用数据
• TEMP表空间：用于存储临时对象数据
• UNDO表空间：用于存储数据库还原数据，以便进行恢复处理
• USERS表空间：用于存储用户和应用数据

段

表空间中划分的不同存储区域，例如表空间划分为存放数据的数据段，存放临时数据的临时段和存放回滚数据的回滚段。

区

由若干连续数据块组成的存储区，用于存放特定数据类型的数据。对象分配空间是以区为单位的，一个数据库对象至少包含一个区。每个区又由多个数据块组成。

数据块

是数据库存取的最小存储单元，由若干数据字节组成。通常Oracle数据块是操作系统数据块的倍数

数据库物理结构

数据库物理结构如下：

• 数据文件(xxx.DBF) -> 存储数据库对象结构及其数据
• 控制文件(xxx.CTL) -> 记录数据库文件位置、数据库名称、创建时间和日志记录序号等控制信息
• 重做日志文件(xxx.LOG) -> 记录数据库事务的日志信息。数据库被破坏时，可以利用日志文件的数据来恢复数据库

数据字典

在Oracle数据库库系统中，系统数据库信息、数据库对象信息和用户管理信息等元数据都需要在数据库中进行记录，以便在运行中使用。

在Oracle数据库系统中，数据库出用来存储用户数据外，还会使用系统数据表存放系统元数据。通常将数据库中存放系统元数据的数据库表及其视图集合称为数据字典。存放在SYSTEM表空间，用途为：

• 通过存取数据字典，可访问数据库中所有数据库对象及其存储结构信息
• 当系统执行数据库对象操作的DDL语句后，在数据字典中记录与维护该对象的元数据

静态数据字典视图

前缀	说明
ALL_	所有数据库用户都可以访问，只要提供用户有关的对象信息
DBA_	仅有DBA访问，主要提供系统和对象信息
USER_	仅有数据库用户访问，内容因用户而不同

一般名称尾部还有$字符。

动态数据字典视图

主要提供数据库运行状态数据查看，如当前数据库内存使用与分配信息。文件状态信息、任务调度与作业进展信息等。主要是以V$或V_$为前缀

数据库操作语言

SQL语言

Oracle对标准SQL进行了扩展。扩展 的Oracle SQL语句可以分为以下几种类型：

• 对象定义语言语句(Data Definition Language ,DDL)
• 数据库操纵语言语句(Data Manipulation Language，DML)
• 事务控制语句(Transacation Control Statements)
• 会话控制语句(Session Control Statements)
• 系统控制语句(System Control Statement)
• 嵌入SQL语句(Embedded SQL Statements)

PL/SQL语言

是Oracle数据库在SQL语言基础上进行过程扩展处理的编程语言，支持所有Oracle的SQL语句，函数和数据类型，并能进行循环、分支和嵌套等过程编程处理。

JAVA语言

后端数据库程序编程除了使用PL/SQL语言外，也可以使用JAVA进行过程编程处理。Oracle数据库支持JAVA应用开发、部署和存储。

---

数据库与表空间

数据库

完整的Oracle数据库通常由两部分组成：Oracle数据库和数据库实例。

• 数据库是一系列物理文件的集合（数据文件，控制文件，联机日志，参数文件等）；
• Oracle数据库实例则是一组Oracle后台进程/线程以及在服务器分配的共享内存区。

在启动Oracle数据库服务器时，实际上是在服务器的内存中创建一个Oracle实例（即在服务器内存中分配共享内存并创建相关的后台内存），然后由这个Oracle数据库实例来访问和控制磁盘中的数据文件。Oracle有一个很大的内存快，成为全局区（SGA）。

普通数据库

从物理结构来看，Oracle数据库由若干文件组成，主要包括==数据文件、控制文件和日志文件==。创建数据库实际上就是在操作系统中建立这些数据库文件，以便数据库服务器利用这些文件对数据库进行数据存储与管理。

多租户数据库

在云计算服务领域中，多租户技术是指多个租户共用一个相同的应用或服务时，仍可确保用户之间的数据隔离。云服务软件与传统软件的数据库实现技术的区别在于云服务软件采用了多租户数据库模式技术解决租户之间的数据隔离。在多租户数据库模式下，各个租户虽然运行在同一数据库系统中，但它们只能查看和修改自己的数据集合，租户之间相互独立，彼此互不影响。实现多租户数据库模式主要有以下三个方案

==租户使用独立数据库==

每个租户在使用应用系统服务时，采用各自独立的数据库解决租户之间的数据隔离问题。

特点是：有助于数据模型的拓展设计，满足不同租户的独特需求，用户数据隔离级别最高，安全性最好，但成本高

==租户共享数据库、隔离Schema==

租户使用应用系统服务时，采用统一数据库，在数据库中通过不同的用户Schema解决租户之间的数据隔离问题。

特点是：为安全性要求较高的租户提供了一定程度的逻辑数据隔离，但并不是完全隔离；如果出现故障，数据恢复比较困难，因为恢复数据库将牵扯到其他租户的数据；如果需要跨租户统计数据，也存在一定的困难。

==租户共享数据库、共享Schema==

租户共享数据库、共享Schema，但在表中童年过Ten-andID解决租户之间的数据隔离。

特点是：共享程度最高，隔离级别最低，需要在设计开发时加大对安全处理的开发量；数据备份和恢复最难，需要逐表逐条备份还原。

模式

在oracle数据库系统中，容器数据库 (Container Database，CDB) 是指能够容纳0个、1个或多个插接式数据库(Pluggable Databases，PDB)的数据库。结构如下图

数据表空间

在oracle数据库逻辑结构中，表空间是数据库之中最高层次的逻辑存储结构，即数据库是由若干表空间构成。在创建数据库时，系统自动为每个数据库建立几个默认的表空间，此外，用户还可以为应用创建及管理自己的表空间。

表空间是一种逻辑容器，它用于组织各种数据库对象(表、索引、视图、存储过程触发器等)的逻辑存储，并管理相关的数据文件。表空间在oracle数据库的逻辑结构组织和物理结构管理中扮演重要的作用

Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。

数据文件是物理的，一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间。

创建表空间语法：

Create TableSpace 表空间名称 DataFile 表空间数据文件路径 Size 表空间初始大小 Autoextend on

查看已经创建好的表空间：

select default_tablespace, temporary_tablespace, d.username from dba_users d

数据库对象

数据库对象是构成数据库的组成元素。一个Oracle数据库可以存储多种类型对象，如表、索引、视图、处处过程、触发器、序列、同义词和包等。在使用oracle数据库前，必须在数据库中创建基本的数据库对象，并由不同用户schema分别组织这些对象集合。当在数据库中创关键表对象后，就可以对表对象进行数据增删改查访问操作。

数据库表

表是关系数据库中最基本的数据库对象，它是存储数据的基本逻辑容器。一个我让车开了关系数据库通常需要建立较多的表。其中一些用于存储描述数据库结构的元数据和系统运行数据，这些被称为系统表或数据字典。另外一些表用于存储用户的应用数据，这些表被统称为用户表。

表类型

==关系表==

关系表是oracle数据库中使用最多的一类数据库表。该类表按照关系模型方式处理数据访问。在创建oracle关系表时，默认按堆方式存储数据，有时也将它称为堆。在用堆方式存储数据时，表中的行数据由B-树索引指针定位，该行数据可在磁盘块中任意顺序位置存放。

==对象表==

对象表是一种在oracle数据库中以对象数据类型方式所创建的表。对象表中，每行分别储存处不同用户的数据。

==临时表==

临时表与常规表(关系表。对象表)一样也是用于存储数据的表对象，但是临时表在数据库中仅用于残存数据。当数据库实例种植后，系统将清除临时表及其数据。

==索引组织表==

索引组织表按照表的逐渐索引值顺序在磁盘块中组织进行数据存储，并且将行数据存放在索引叶结点存储块中。索引组织表支持应用对该表中进行范围查询，可实现高性能访问和高空存储空间访问率。

==外部表==

外部表是一类在数据库中存储元数据，而外部表数据本身存储在数据库之外的文件中。外部表在数据库中通常为中毒。

用户schema

在一个oracle数据库中，通常会存储很多不用用户的数据库对象，如表、索引、视图、触发器、存储过程、序列和同义词等。为了便于各个用户的数据库对象集合的组织管理，在数据库中需要先创建用户，同时系统也会创建与该用户同名的schema，作为该用户拥有数据库对象的逻辑容器。

索引

索引(index)是一种针对表中指定列的值进行排序的数据结构，使用它可以加快表中数据的查询访问。例如，一个雇员信息表中有数万个雇员的行数据。若要对该表进行雇员信息查询，最基本的信息查询方式是全表检索，即将表中所有雇员的行数据一一取出，并与查询条件逐一对比，然后返回满足条件的行数据。这样的数据查询会带来DBMS的大量开销。当在有索引的表中进行数据查询时，首先在该表的索引结构中查找符合条件的索引值，然后再根据索引值所关联的地址指针在磁盘数据块中直接定位到对应的数据记录，从而实现快速数据查询。

为什么添加了索引之后，会加快查询速度呢？
图书馆：如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多，这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的。
所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。

范例：给 person 表的 name 建立索引
create index pname_index on person(name);
范例：给 person 表创建一个 name 和 gender 的索引
create index pname_gender_index on person(name, gender);

视图

视图是一种建立在基础表或其他视图之上的虚拟表。用户同样可以使用SQL语句对视图进行数据插入、修改、删除和年数据查询处理。

视图就是提供一个查询的窗口，所有数据来自于原表
语法 ： CREATE VIEW 视图名称 AS 子查询

视图的作用？
第一：视图可以屏蔽掉一些敏感字段。
第二：保证总部和分部数据及时统一。（总部操纵实际的表，分部查询视图这样保证数据的统一）

---查询语句创建表
create table emp as select * from scott.emp;
select * from emp;
---创建视图【必须有dba权限】
create view v_emp as select ename, job from emp;
---查询视图
select * from v_emp;
---修改视图[不推荐]，实际的数据也会改变
update v_emp set job='CLERK' where ename='ALLEN';
commit;
---创建只读视图
create view v_emp1 as select ename, job from emp with read only;

后端编程

PL/SQL概述

PL/SQL是过程语言(Procedural Language) 与结构化查询语言(SQL)结合而成的编程语言，它是标准SQL语言的功能不成。SQL语言适合关系关系型数据库，但是无法满足应用程序对数据更复杂的处理需求。PL/SQL语言支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构，用于创建存储过程、函数、书法器、PL/SQL包和用户自定义函数，给SQL语言的执行添加程序逻辑。oracle PL/SQL在企业及应用程序中的应用广泛，而且oracle的一些功能部件也是使用PL/SQL编写的。

结构快

--PL/SQL块基本结构：
DECLARE
 	   variable_name [CONSTANT] type[(size)] [NOT NULL] [:=value];  
       -- DECLARE 可选,声明部分: 
       --在此声明PL/SQL用到的变量,常量,类型,游标 以及局部的存储过程和函数;
BEGIN
       -- BEGIN~end 必须项,执行部分:
       -- 在此编写执行的过程 及 SQL语句,即程序的主要部分;
       EXCEPTION
                -- EXCEPTION 可选,指定出现错误时需要执行的操作
END;
        -- end: 表示Pl/sql块的结束,别忘了分号结尾;
        -- variable_name:变量名称
		-- [CONSTANT]   :可选表示是否为常量
		-- type         :表示变量的数据类型
		-- [(size)]     :可选,数据类型的长度
		-- [NOT NULL]   :可选,该变量是否可以为空
		-- [:=value]    :可选,使用 := 给对于对象赋值 初始值;

-- 上面是PL/SQL块的基本结构, BEGIN~END; 是必须的 声明部分和异常处理部分并不是必须的..
-- 是PL/SQL中的单行注释    /**/ 多行注释;

==demo==

-- scott用户下emp举例
select * from emp;

--PL/SQL 根据输入id,获得当前用户名 JOB
-- 方式一:PL/SQL块 注意分号~
declare
       --声明变量 id ename job, 每一个变量之间 ; 分号分隔...
       --id 通过 :=值 进行赋默认值,并通过 &xxx由Oracle工具输入参数;
       v_id number :=&myid;  --还可以把: &myid手动输入换位固定的 :=7934; 固定的id号;
       v_ename varchar2(50);
       v_JOB varchar2(50);
begin
      select ename , job 
             into v_ename,v_JOB 
             from emp where empno = v_id;
      dbms_output.put_line('用户名'||v_ename||'职位: '||v_JOB);
end;
--  还可以通过: select into                          给变量进行赋值 
--  select 列1,列2 into 变量1,变量2 form 表          形式进行下标自动映射赋值..因此列类型和变量类型 顺序都要尽量对应
-- dbms_output.put_line();                           Oracle还可以通过该语句进行数据打印输出...


-- 方式二: 解决了不清楚变量与列的类型
declare
       v_id    emp.empno%type :=&id; 
       v_ename emp.ename%type;
       v_JOB   emp.job%type;
       v_emp   emp%Rowtype;
begin
      select ename , job 
             into v_ename,v_JOB
             from emp where empno = v_id;
      dbms_output.put_line('用户名'||v_ename||'职位: '||v_JOB);
      
      select * into v_emp from emp where empno = v_id;
      dbms_output.put_line('用户名'||v_emp.ename||'职位: '||v_emp.job);
end;
-- 表.列%type:  可以在不清楚列类型时候给设置类型;
-- 表%ROWTYPE:  可以在不知道表中列个数 和 数据类型时候,可以通过  表%ROWTYPE 获取一个表类型变量...

实验一：“图书借阅管理系统”Oracle 数据库创建与维护

实验原理

在 windows 系统下，利用 database Configuration assistance 创建相应数据库，并使用 sql developer 和 sql plus 对 Oracle 数据库进行相应的管理等操作。

实验步骤

• 图书借阅管理系统数据库 Lib 创建。
• 在 Oracle 数据库服务器中对 Lib 数据库启停控制。
• Lib 数据库连接与基本 DBA 功能查看访问。
• 对 Lib 数据库进行初始化配置参数修改。
• 在 Lib 数据库中，对默认 user 表空间增加数据文件。
• 在 Lib 数据库中，创建用户表空间 T_Lib。
• 在 Lib 数据库中，修改用户表空间 T_Lib 配置参数。
• 在 Lib 数据库中，删除表空间 T_Lib。

实验过程

数据库创建

创建成功，管理口令的地方把sys的口令改了。

数据库连接

由于本人SQL developer莫名其妙不见了，所以下载了PL/SQL Developer作为数据库连接工具。

发现没有开启监听程序还是啥，打开Oracle Net Config Assistant 重新配置监听程序，设置默认端口1521。

然后使用navicat进行连接，注意sys用户是需要在高级的地方设置身份！

创建表空间

create tablespace T_lib datafile 'E:\app\oracle\oradata\LIB\DATAFILE\T_lib.DBF' size 8M autoextend on next 5M maxsize 10M;

修改表空间配置。设置表空间自动扩容，设置最大容量

alter database DATAFILE 'E:\app\oracle\oradata\LIB\DATAFILE\T_lib.DBF' autoextend on next 200M maxsize 500M;

删除表空间

drop tablespace T_lib;

实验二：“图书借阅管理系统”Oracle 数据库对象创建与 SQL 操作

实验原理

数据库对象的创建应在 sys 账户登陆的情况下，在 sqldeveloper 中选择新建用户，设
置用户名，授予用户所需的权限并设定密码。创建完数据库对象后即可在 sqldeveloper 中
创建该数据库对象所对应的链接，然后进入到该链接对该数据库对象中的表进行相应的创
建、修改、删除、查询、统计等操作

实验步骤

• 在Lib数据库中，创建一个方案对象 Library_DBA。
• 设计 Lib 数据库各关系表结构，以及视图、索引、序列等。
• 编程与执行 SQL 程序，在 Library_DBA 方案中，创建 Lib 数据库的表、视图、索引、序列等对象。
• 在 Library_DBA 方案中，对数据库表进行数据插入、数据修改、数据删除 SQL 操作。
• 在 Library_DBA 方案中，对数据库表进行数据查询、数据统计 SQL 操作

实验过程

创建方案对象

在navicat中创建一个用户，点击用户，选择新建用户，搜了一下创建报错公共用户名或角色无效的解决方法就是在用户名前加C##事实上也确实创建成功了C##LIBRARY_DBA

设置服务器权限

用刚才创建的用户登录

注意点击高级，选择角色为default

编写SQL语句

在 Library_DBA 空间中设计并编写 sql 程序，使其创建出名为reader\employee\title\book\loan\reserve 的五个表。设计表时注意表之间的约束关系，防止表创建不成功。创建的先后顺序为：reader\title 表，再 book 表，最后 loan\reserve 表。使用 create关键字进行创建。

--创建借阅者信息表
create table reader (
Read_ID char(18) primary key,
Read_Name varchar(20) not null,
Read_Addr varchar(50) null,
Read_Tel varchar(11) NULL
);
--创建员工信息表EMPLOYEE
create TABLE EMPLOYEE (
Empl_ID char(4) PRIMARY KEY,
Empl_Name Varchar(20) NOT NULL,
Empl_Gender char(2) NULL,
Empl_Birthday Date NULL,
Empl_Tel varchar(11) NULL
);
--创建图书目录TITTLE
create table TITLE (
Titl_ID Number GENERATED AS IDENTITY,
Titl_Name varchar(80) NOT NULL,
Titl_Type Varchar(30) NOT NULL,
CONSTRAINT Titl_PK PRIMARY KEY(Titl_ID)
);
--创建图书信息表BOOK
create TABLE BOOK(
Book_ID NUMBER GENERATED AS IDENTITY,
Book_Name Varchar(80) NOT NULL,
Book_ISBN Char(20) NOT NULL,
BOOK_Title Number null,
Book_Author varchar(60) NULL,
Book_PubDate Date NULL,
Book_Money DECIMAL(4,1) NULL,
Book_Inlf SmallInt NOT NULL,
CONSTRAINT Book_PK PRIMARY key(Book_ID),
CONSTRAINT Book_FK FOREIGN KEY(Book_Title) REFERENCES TITLE(Titl_ID)
);
--创建借阅记录表LOAN
create TABLE LOAN(
Loan_ID NUMBER  GENERATED AS IDENTITY,
Loan_Type char(2) not null,
Loan_BookID number not null,
Loan_ReaderID char(18) not null,
Loan_Date Date not null,
Loan_Note varchar(30) null,
CONSTRAINT Loan_PK PRIMARY key(Loan_ID),
CONSTRAINT Loan_FK1 FOREIGN KEY(Loan_BookID) REFERENCES BOOK(Book_ID),
CONSTRAINT Loan_FK2 FOREIGN KEY(Loan_ReaderID) REFERENCES READER(Read_ID)
);
--创建预定图书表RESERVE
create TABLE RESERVE(
Rese_ID number GENERATED AS IDENTITY,
Rese_BookID number NOT null,
Rese_ReaderID char(18) not null,
Rese_Date date not null,
Rese_Note varchar(30) null,
CONSTRAINT Rese_PK PRIMARY key(Rese_ID),
CONSTRAINT Rese_FK1 FOREIGN KEY(Rese_BookID) REFERENCES BOOK(Book_ID),
CONSTRAINT Rese_FK2 FOREIGN KEY(Rese_ReaderID) REFERENCES READER(Read_ID)
);

创建索引

1. 索引是什么？
   (1) 一种供服务器在表中快速查找一行的 ‘数据库结构’
   (2) 可以理解为：一本书中的 ‘目录’
   2. 索引的优点
      (1) 加快数据的 ‘检索速度’
      (2) 可以保证列值的 ‘唯一性’（unique、主键）
      (3) 实现表与表之间的 ‘参照完整性’（外键）
      (4) 在使用 order by、group by 子句时，可以减少排序和分组的时间

--在图书表中，创建书名列索引Book_Name_Idx
create index Book_Name_Idx on BOOK(Book_Name);
--在图书表中，创建书目列索引Book_Title_Idx
create INDEX Book_Title_Idx on BOOK(Book_Title);
--在借阅记录表中，创建借还类型索引Loan_Type_Idx
create BITMAP index Loan_Type_Idx on LOAN(Loan_Type);
--在借阅记录表中，创建日期列索引Loan_Date_Idx
create index Loan_Date_Idx on LOAN(Loan_Date DESC);
--在图书预定表中，创建日期列索引 Rese_Date_idx
create index Rese_Date_Idx on RESERVE(Rese_Date DESC);

创建视图对象

--创建查询当前可借图书视图Book_Inlf_View
create view Book_Inlf_View as select Book_Name,Book_ISBN,Book_Inlf from BOOK where Book_Inlf = 1; --0代表不可借，1代表可以借
--创建查询当前借书读者列表视图Loan_Reader_View
create view Loan_Reader_View as select R.READ_ID,R.Read_Name,B.Book_Name,L.Loan_Date from LOAN L,READER R,BOOK B where L.Loan_Type = '借' and L.loan_BookID = B.Book_ID and L.Loan_ReaderID = r.Read_ID with READ ONLY;

创建序列对象

--在Library_DBA用户方案中，创建序列Titl_ID_Seq
create sequence Titl_ID_Seq start with 1 increment by 10;
--在Library_DBA用户方案中，创建序列Book_ID_Seq
create sequence Book_ID_Seq start with 1000 increment by 1;
--在Library_DBA用户方案中，创建序列Rese_ID_Seq
create sequence Rese_ID_Seq;

修改表对象结构

alter table C##LIBRARY_DBA.reader add read_birthday date;

删除数据库序列

DROP sequence Rese_id_seq;

插入数据

insert into reader values('510111111111111111','p1ych','成都','18788881881',to_date('2010-01-01','yyyy-mm-dd') );
insert into reader values('520111111111111111','p2ych','长度','18788388882',to_date('2011-01-01','yyyy-mm-dd') );
insert into reader values('530111111111111111','p3ych','成都','18788848883',to_date('2012-01-01','yyyy-mm-dd') );
insert into reader values('540111111111111111','p4ych','查到','18788885884',to_date('2013-01-01','yyyy-mm-dd') );
insert into reader values('550111111111111111','p5ych','程度','18788883885',to_date('2014-01-01','yyyy-mm-dd') );
insert into reader values('560111111111111111','p6ych','彻底','18788883886',to_date('2015-01-01','yyyy-mm-dd') );
insert into reader values('570111111111111111','p7ych','出的','18788882887',to_date('2016-01-01','yyyy-mm-dd') );
insert into reader values('580111111111111111','p8ych','错的','18788884888',to_date('2017-01-01','yyyy-mm-dd') );
insert into reader values('590111111111111111','p9ych','成电','18788882889',to_date('2018-01-01','yyyy-mm-dd') );
insert into reader values('500111111111111111','p0ych','抄的','18788882880',to_date('2019-01-01','yyyy-mm-dd') );

实验三：“图书借阅管理系统”Oracle 数据库后端 PL/SQL 编程

实验原理

通过 PL/SQL 编程实现图书借阅数据库 Lib的后端数据处理功能。通过 PL/SQL 编程，后端主要的数据处理功能包括了数据库存储对象的创建、存储过程查看、代码修改、代码编译、编程调用、存储过程结果删除；以及数据库触发器的创建、存储过程查看、代码修改、代码编译、编程调用、存储过程结果删除。

实验步骤

• 存储过程编程与对象创建
• 存储过程查看、代码修改、代码编译
• 编写PL/SQL主程序调用存储过程执行
• 存储过程删除
• 触发器编程与创建
• 触发器查看、代码修改、代码编译
• 触发器应用
• 触发器使能控制
• 触发器删除

实验过程

存储过程编程

存储过程创建

在Library_DBA方案下，编程并创建名为CHANGE_PHONE的存储过程。该存储过程将进行Reader身份信息的确认。如果Reader的id和phone正确,则进行相应的修改，否则，将拒绝修改，并输出相应错误提示信息。

create or replace procedure change_phone
(
    Read_ID in char,
    Read_name in varchar2,
    read_tel in varchar2,
    new_tel in varchar2,
    status_code out number,
    status_text out varchar2
)
is
    readid char(18) := Read_ID;
    readname varchar2(20) := Read_name;
    readtel varchar2(11) := read_tel;
    newtel varchar2(11) := new_tel;
    tempreadname varchar2(20);
    tempreadtel varchar2(11);
    err_readname exception;
    err_readtel exception;
begin
    status_code := 0;
    status_text := '';   -- 初始化

    select reader_name, reader_tel into tempreadname, tempreadtel from reader where reader_id = readid;

    if readname <> tempreadname then    -- 判断输入的读者姓名是否正确
        raise err_readname;
    end if;

    if readtel <> tempreadtel then      -- 判断输入的电话号码是否正确
        raise err_readtel;
    end if;

    update reader set reader_tel = newtel where reader_id = readid; -- 更新电话号码
    commit;

exception
    WHEN NO_DATA_FOUND THEN
        status_code := 1;
        status_text := '读者信息不存在';
    WHEN err_readname THEN
        status_code := 1;
        status_text := '读者姓名错误';
    WHEN err_readtel THEN
        status_code := 1;
        status_text := '电话号码错误';
    WHEN OTHERS THEN
        status_code := 1;
        status_text := substr(SQLERRM, 1, 100);
end change_phone;
/

运行成功。

存储过程查看、代码修改、代码编译。

可以在数据字典USER_OBJECTS中查看该存储过程的基本信息。

SQL语句为：

select Object_name,object_type,created,status from user_objects where object_type = 'procedure' and object_name = 'change_phone';

结果截图：

还可以编写SQL程序，在数据字典USER_OBJECTS中执行，对该存储过程进行代码信息的查询，查看存储过程代码信息。

select * from user_source where name = 'STANDARD' and type = 'PACKAGE';  --选择了另一段代码查看

存储过程使用

decleare
    status number;
    status_text varchar2(100);
begin
    change_phone('201601010101', '张三', '12345678901', '12345678901', status, status_text);
    IF status <> 0 THEN
        dbms_output.put_line(status_text);
    ELSE
    dbms_output.put_line('ok!');
    end if;
end;

存储过程删除

drop procedure change_phone;

成功执行该SQL语句后，change_phone存储过程从数据库中被删除。

触发器编程

触发器创建及编译

考虑到数据的安全性，在READER表中编程创建一个删除触发器（T_READER_DEL）。该触发器将执行以下任务：当READER表的任一行将被删除时，触发器就会把该行数据记录保存在读者信息删除备份表（READER_DEL_BAK）中，并且在该备份表中记录对READER表做删除操作的用户和删除操作的时间。

首先创建借阅者信息删除备份表reader_del_bak

create table reader_del_bak (
    Read_ID char(18) not null,
    read_name varchar(20) not null,
    read_addr varchar(50) null,
    read_tel varchar(11) null,
    Del_ID varchar(20) null,
    Del_date DATE null
);

并创建名为T_READER_DEL的触发器。

注意这里一定要用library_dba去操作，也就是normal对象创建触发器。

create or replace TRIGGER T_READER_DEl
after delete on reader
for each row
begin   
    insert into Reader_Del_Bak values
    (:old.read_ID,
    :old.read_name,
    :old.read_addr,
    :old.read_tel,
    user,
    sysdate
    );
    commit;
exception
    when others then
        dbms_output.put_line('error = '||to_char(sqlcode));
        dbms_output.put_line('error message = '||sqlerrm);
end T_READER_DEl;

查询触发器

示例：

select object_name,object_type,created,status from user_objects where object_type = 'TRIGGER' and object_name = 'DBMS_SET_PDB';

使用触发器

dd

删除触发器

drop TRIGGER T_READER_DEL;

实验四：“图书借阅管理系统”Oracle数据库系统管理

实验原理

使用Oracle SQL Developer或SQL Plus数据库工具对图书借阅管理系统数据库进行系统中的用户对特定数据库的权限的管理，以及使用数据泵工具程序完成数据库备份或恢复操作。

实验步骤

• 在图书借阅管理系统数据库Lib中，编写运行SQL语句实现“读者（R_LIB_READER）”角色创建。
• 针对“读者（R_LIB_READER）”角色，编写运行SQL程序赋予其相应的系统权限与数据库对象访问权限，要求最小范围地赋予其相应的系统权限，并合理地赋予其对READER表、BOOK表、RESERVE表访问权限。
• 编写运行概要文件LIB_READER_PROF创建SQL语句，实现数据库用户的资源访问和口令操作限制。
• 编写运行创建读者用户U_LIB_READER的SQL语句，并分派角色R_LIB_READER和概要文件LIB_READER_PROF。
• 在以上用户权限基础上，再编写运行SQL语句实现U_LIB_READER用户被赋予访问TITLE表对象的SELECT权限。
• U_LIB_READER用户登录连接Lib数据库，编写运行SQL语句验证U_LIB_READER用户对READER表、BOOK表、RESERVE表的增、删、查改访问权限。
• 以SYS用户，编写运行SQL语句查看所创建的用户、概要文件和角色对象元数据。
• 使用数据泵工具方法实现Lib数据库逻辑备份。
• 对Lib数据库中某对象进行删除破坏，然后使用数据泵工具方法实现Lib数据库逻辑恢复。

特别要求：Lib数据库中各个对象名称都需要添加本人学号作为后缀，如“R_LIB_READER_20220348021”格式。

实验过程

用户权限管理

用户、概要文件和角色的创建

由于LIBRARY_DBA用混具有DBA权限，不以提供给普通使用者访问数据库，因此需要为普通使用这种创建特定的用户，这里新创建一个名为U_LIB_READER_2022090914031的用户，将来可以使用改用户登录并查看LIBRARY_DBA方案下的READER表。

• 在工具中选择其他用户目录并点击，选择添加用户

注：Navicat创建用户对用户名有限制，这里创建C##U_LIB_READER_2022090914031用户

• 对新创建的用户要设置特定的资源访问限制，因此需要新创建一个名为C##LIB_READER_PROF的概要文件。由于navicat并没有创建概要文件的GUI界面，故采用SQL语句来创建。

• 对新创建的用户要设置特定的权限，可以通过觉得的方式来赋予权限，因此新创建一个名为R_LIB_READER_2022090914031的角色

分配概要文件和权限的赋予与回收

• 把C##LIB_READER_PROF概要文件赋予U_LIB_READER_2022090914031用户，并使资源生效

ALTER USER C##U_LIB_READER_2022090914031 PROFILE C##LIB_READER_PROF;

修改动态参数 RESOURCE_LIMIT为TRUE的SQL语句如下

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE

删除创建的用户、概要文件和角色

删除用户C##U_LIB_READER_2022090914031,profile和pole

数据库备份与恢复

应对数据库安全物理威胁，防范数据丢失或损坏的主要解决方案是数据备份与恢复技术。可以使用数据泵的导出导入功能实现对数据的逻辑备份，或使用RMAN的自动备份技术实现对数据的物理备份。

数据泵备份与恢复

直接点击navicat上方导航栏中的数据泵，选择数据泵导出，即可

点击运行，就开始导出备份。

备份结束后，删除READER表中的所有数据，然后进行LIBRARY_DBA方案的导入操作。

导入步骤与导出相同。

RMAN备份与恢复

• 备份准备。首先使用ARCHIVE LOG LIST命令查看当前数据库，已确认当前处于归档模式。

• 登录RMAN，使用RMAN备份数据文件

backup datafile 1;

• 用RMAN恢复数据文件的操作

登录RMAN，将要恢复的回溯局文件脱机，

sql 'ALTER DATABASEDATAFILE 6 OFFLINE;

重建数据文件

RESTORE DATAFILE 6;

恢复数据文件

RECOVER DATAFILE 6;

使数据文件联机

sql ’ALTERDATABASEDATAFILE6 OUTLINE;