《大型数据库系统》实验指导书

《大型数据库系统》课程实验指导书

修订 刘忠民

1

刘晓瑢

实验一 创建、修改和删除表

一、实验目的

? 了解Oracle系统的组织结构 ? 熟悉Oracle企业管理器环境 ? 熟悉SQL*Plus环境

? 掌握用Oracle企业管理器以及SQL*Plus创建、修改、删除表

二、实验环境

一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/manager, sys/change_on_install, scott/tiger）。

三、预备知识

1、复习《数据库系统概论》第三章关系数据库标准语言SQL：

1）学生---课程数据库实例的三张表的ER图。

学生 选修 课程 成绩

2）表的定义3）表的修改 4）表的删除

5）插入、修改、删除数据

2、复习《数据库系统概论》第五章数据库完整性

3、在SQL*Plus中输入完SQL语句后，有3种处理方式： 1）在语句最后加分号（；），并按回车键，则立即执行该语句；

2）语句输入结束后回车换行，然后再按回车键，结束SQL语句输入但不执行该语句； 3）语句输入结束后按回车键，换行后按斜杠（/），则立即执行该语句。 4、编辑缓冲区

由于以命令行的方式编辑SQL缓冲区不太方便，常采用EDIT命令以编辑器打开缓冲区并进行编辑。

四、实验内容

1、查看数据库的系统文件。 2、熟悉Oracle系统环境

3、分别用OEM和SQL*Plus完成以下操作（用SQL语句创建表可参见《数据库系统概论》P85） 注：所有表都创建在scott方案下，在Sql*plus下以scott用户连接数据库

1）创建以下表

◆Student(Sno,Sname,Ssex,Sage,Sdept)：学生表

sno是主码，ssex要有CHECK约束

◆Course(Cno,Cname,Cpno,Ccredit)：课程表

2

其中，cno是主码, cpno表示先行课，参照本表的cno

（注：在用OEM创建cpno引用cno约束时，要先创建表course，再创建约束）

◆Sc(Sno,Cno,Grade)：选课表

主码为（sno,cno）, sno和cno都要有外键约束

2）修改表定义

向Student表中增加Sentrance“入学时间”列，定义为date数据类型 3）插入记录

注意，在插入记录时要满足数据完整性约束

Student表

Sno 98001 98002 98003 98004

Course表 Cno 1 2 3 4 5 6 7 Cname 数据库系统 数学分析 信息系统导论 操作系统原理 数据结构 数据处理基础 C语言 Cpno 5 null 1 6 7 null 6 Ccredit 4 2 3 3 4 4 3 Sname 钱横 王林 李民 赵三 Ssex 男 女 男 女 Sage 18 19 20 16 Sdept Sentrance CS CS IS MA 12-3月-92 12-3月-91 12-3月-90 12-3月-94 注：该表中Cpno参照本表的Cno，即Cpno要么为空，要么是已插入的课程记录，所以

注意记录插入的先后顺序。

SC表 Sno Cno Grade 87 67 90 95 88 98001 1 98001 2 98001 3 98002 2 98002 3 4）修改数据

①将SC表中的成绩都增加10% ②将CS系的学生成绩清零

5）删除记录（注在删除记录前检查是否保存插入记录的Sql语句） ①删除Student表的所有记录

②删除SC表中Cno是2的所有记录 6）用Sql语句恢复所删除的记录

4、该实验中的创建表、修改表语句以及插入记录语句保存到脚本文件中，备用于下次实验恢复表和记录。

5、思考其它的备份数据库的方法。

3

实验二 查询语句运用

一、实验目的

1、 熟练掌握查询语句的用法。 2、 进一步熟悉SQL*Plus工作环境

二、实验环境

一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/manager, sys/change_on_install, scott/tiger）。

三、预备知识

1、复习数据查询语句

四、实验内容

1、打开Sql*Plus，用scott用户连接，密码是tiger. 2、用实验一备份的语句还原表和数据。 3、在SQL*Plus中完成以下查询语句： 1）基本查询语句

例：查询全体学生的学号与姓名

2）带ORDER BY子句的查询

例：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

3）多表查询（连接操作）

例：查询选修2号课程且成绩在90分以上的所有学生。

4)元组变量的使用查询

例：查询与‘王林’在同一个系学习的学生。

5)查询语句中的集合操作

例：求各个课程号及相应的选课人数。

6)组函数与group by子句（having子句） 例：查询选修了3门以上课程的学生学号

7)嵌套查询

例：查询选修了课程号为“数据库系统”的学生学号和姓名。

4

实验三 权限及角色的设置操作

一、实验目的：

1、理解ORACLE中的各系统权限、对象权限及角色的含义。 2、掌握用户的创建和给用户授予适当的权限。

二、实验环境

一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/manager, sys/change_on_install, scott/tiger）。

三、预备知识

1、 Oracle数据库系统中权限分为两类：系统级和对象级。

1）系统级权限：对某一特定类型实体上执行特定操作的权限 2）对象级权限：针对某个数据库对象（表、视图等）的操作权限 详见课本中权限列表。 2、创建用户

create user testuser --用户名为testuser identified by testpwd --口令为testpwd

default tablespace users --缺省表空间使用users表空间 temporary tablespace temp; --临时表空间使用temp表空间 quota unlimited on users --用户使用表空间users的配额限制 quota 10m on temp --用户使用临时表空间temp的配额限制 profile manager； --指定用户使用的概况名为manager

注意：创建新用户是必须为用户建立表空间配额，否则用户没有使用表空间的空间的权利；当用CREATE USER命令建立一个用户时，该用户的权利范围是空的。必须为用户指定一定的权限，用户才能操作数据库。如：为登录Oracle,用户必须有CREATE SESSION系统特权。 3、用户概要文件

用户概要文件决定对数据库资源的使用的限制，例 Create profile manager limit

sessions_per_user unlimited --可建立的会话数目 cpu_per_session unlimited --单位百分之一秒

cpu_per_call 3000 --执行一条SQL语句可用时间 connect_time 45 --会话建立后允许的持续时间(分) logical_reads_per_session default --会话期间可读数据库块数

logical_reads_per_call 1000 --执行一条SQL语句可读数据库块数 private_sga 15k

composite_limit 5000000 --总的资源限制

5

failed_login_attempts 3 --最多允许用户3次登录失败 password_life_time unlimited --可用天数 password_reuse_time unlimited --间隔天数

password_reuse_max unlimited --口令重新使用前必须改变的次数 password_lock_time unlimited； --锁定账户天数 4、与用户有关的视图

ALL_USERS视图：数据库所有用户的信息

USER_TS_QUOTAS：当前用户的表空间使用限额信息 DBA_TS_QUOTAS：所有用户表空间限额 DBA_SYS_PRIVS：所有用户权限信息 5、修改用户

alter user

** default role resource,connect 可以授予用户缺省角色 6、删除用户

drop user 用户名 [cascade];

**加cascade参数不经提示删除模式下实体 7、创建角色

create role rolename; 8、授权

? 授系统权限

grant system_priv | role[,…] to user | role | public [with admin option]；

注：加with admin option选项允许用户将得到的系统权限或角色授予其它用户或角色，给角色授权时不能加with admin option ? 授对象权限

grant objec_priv [(column[,…])][,……] on object to user |role | public [ with grant option];

object_priv包括：alter、delete、index、insert、select、update、 execute（对存储过程）、 references(在表上定义外键约束的权限） 或 grant all on object to user | role | public [ with grant option];

注意：为了给其他用户授予实体权限，此实体必须是你自己拥有的或你已被授予了对此实体的

6

WITH GRANT OPTION权限。 9．收回权限

revoke 语句 10．系统默认角色

? DBA：所有系统权限

? RESOURCE：给开发人员使用，具有有系统权限：create cluster, create index, create procedure,

create sequence, create table, create trigger, create type。

? CONNECT：给最终用户使用，具有系统权限：create session,alter session,create cluster,create

database link,create sequence,create sysnonym,create table,create view

四、实验内容

1．验证create session的作用（注：进入Sql*plus，可以用show user命令查看用户名） 以system用户连接数据库，创建用户testuser; 以testuser登录，连接数据；

以system用户登录，给testuser 用户授予create session权限； 再试用testuser登录。

2．在创建用户时，给用户赋予了表空间的配额，测试系统权限create table与create any table的差异

以system用户连接数据库，给用户testuser授予create table权限；

以testuser用户登录，验证其能否创建自己模式的表，是否能创建scott模式的表（在表名前加scott）;

以system用户登录，为用户testuser授予create any table 的权限，再次验证其是否能创建其它模式的表（注：验证时以testuser用户登录）

3．在创建用户时，不给用户赋予表空间的配额，给用户授予create table权限，测试用户能否建表

以system用户，创建另外一个用户test，在创建时不给用户赋予表空间的配额，给用户授予create table权限；

以tesst用户登录，测试用户能否创建表。

4．测试在没有被授予select any table或select对象权限的情况下，用户能否查询数据 5．测试在没有被授予insert any table或insert对象权限的情况下，用户能否插入新记录 6．测试用户在没有被授予drop any table权限的情况下，能否删除自己模式下的表，能否删除自己在scott用户下建立的表。

7．创建一个角色，给角色赋予create session、create any table、select any table、update any table、

7

drop any table权限。再将角色授予一个有表空间配额的新用户，测试用户的权限。 8．利用system/123用户登录数据库，参照下面的过程来验证用户在没有某个表的数据插入权限的情况下，利用存储过程（用户被授予了执行权限）来插入数据。 a.创建存储过程

create or replace procedure

insertstudent(sno number,sname varchar2,sex varchar2,birthday date,dno varchar2) as begin

insert into system.student values(sno,sname,sex,birthday,dno); end; b.创建用户 create user testuser identified by testuser default tablespace users temporary tablespace temp quota unlimited on users quota 10M on temp; c. 给用户赋权

grant create session to testuser; grant select on student to testuser; grant execute on insertstudent to testuser;

d.利用testuser/testuser帐户登录SQL*Plus，做插入数据操作

insert into system.student values(23400,'lishi','男','10-12月-1979','d01'); 结果失败。

但执行下面的PL/SQL程序：

begin

system.insertstudent(23400,'lishi','男','10-12月-1979','d01'); end;

结果插入数据成功。

8

实验四 PL/SQL程序设计

一、实验目的

1． 掌握PL/SQL程序设计方法。

2． 能利用PL/SQL 程序来解决具体的操作问题 一、实验环境

一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/123, sys/123, scott/tiger） 二、预备知识

1.PL/SQL

PL/SQL(Procedure Language/SQL)语言是Oracle对SQL语言的过化扩充，是一种完整的编程语言；将过程化语言的数据处理能力与SQL语言的访问数据库的能力有机地结合在一起；PL/SQL是一种“后台技术”，它不是用来编写面向用户界面的程序，主要用在编写服务器端程序，如编写存储过程、触发器、包过程等。

PL/SQL程序的基本单元是语句块，所有的PL/SQL程序都是由语句块构成的，语句块之间可以相互嵌套，每个语句块完成特定的功能。

一个完整的PL/SQL语句块由三个部分组成。

PL/SQL块可以分两类，一类称为匿名块，另一类称为命名块。匿名块是指动态生成，只能执行一次的块，不能由其他应用程序调用。命名块是指一次编译可多次执行的PL/SQL程序，包括函数、存储过程、包、触发器等。例如：

CREATE OR REPLACE PROCEDURE showavgsal(p_deptno NUMBER) AS

V_sal number(6,2); BEGIN

SELECT avg(sal) into v_sal from emp where deptno=p_deptno; DBMS_OUTPUT.PUT_LINE(v_sal); END showavgsal; 2.游标

当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，系统会在内存中分配一

9

DECLARE 声明部分、定义变量、数据类型、异常、局部子程序等 BEGIN 执行部分，实现块的功能 EXCEPTION 异常处理部分，处理程序执行过程中产生的异常 END； 个缓冲区，缓冲区中包含了处理过程的必需信息，包括已经处理完的行数、指向被分析行的指针和查询情况下的活动集，即查询语句返回的数据行集。游标是指向该缓冲区的句柄或指针。系统使用游标处理多行数据。

PL/SQL中游标分为两类：显示游标和隐式游标。 显式游标的操作过程，如下： no yes 空 定义游标 打开游标 检索游标 关闭游标 显示游标用于处理返回多行数据的SELECT查询，但所有的SQL语句都有一个执行的缓冲区，隐式游标就是指向该缓冲区的指针，由系统隐含地打开、处理和关闭。隐式游标又称为SQL游标。 三、实验内容

编制PL/SQL程序，完成下面任务

1． 编写一个PL/SQL块，输出所有员工的员工名、员工号、工资和部门号。

2． 查询名为“SMITH”的员工信息，并输出其员工号、工资、部门号。如果该员工不存在，则插

入一条新记录，员工号为2007，员工名为“SMITH”，工资为1500，部门号为10。如果存在多个名为“SMITH”的员工，则输出所有名为“SMITH”的员工号、工资和部门号。

3． 给职工涨工资，少于800元的涨100元，大于等于800元的涨200元。在屏幕上输出工资总额

及涨工资的总额。

10

实验五 存储子程序与触发器的设计

一、实验目的

1． 理解存储子程序与触发器的作用

2． 掌握存储子程序与触发器的创建、调试和使用 二、实验环境

一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/123, sys/123, scott/tiger） 三、预备知识 1、存储子程序

存储子程序是指被命名的PL/SQL块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是PL/SQL程序模块化的一种体现。创建存储子程序要求用户有CREATE PROCEDURE 或CREATE ANY PROCEDURE 权限。 ? 创建存储子程序

创建存储子程序的基本语法为：

CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1_name [mode] datatype [DEFAULT|:=VALUE]

[,parameter2_name [mode] datatype [DEFAULT|:=VALUE],…]) AS|IS

声明部分 BEGIN

执行部分 EXCEPTION 异常部分

END[procedure_name]; ? 存储子程序的调用

? 在SQL*Plus中调用 例：execute show_emp(10) ? 在PL/SQL程序中调用 例：DECLARE

v_avgsal emp.sal%type; v_count NUMBER:

BEGIN

show_emp(20);

return_deptinfo(10,v_avgsal,v_count);

DBMS_OUTPUT.PUT_LINE(v_avgsal||? ?||v_count); END;

使用存储过程大大增强了SQL 语言的功能和灵活性，并可用来保证数据的安全性和完整性：通过存储过程可以使没有权限的用户间接地存取数据库，从而保证数据的安全。 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

11

2、触发器

触发器是一种特殊的存储过程，在插入、删除或修改特定表中的数据时触发执行，比数据库本身标准的功能有更精细和更复杂的数据控制能力。可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据；可以基于数据库中的数据限制用户的操作，例如不允许股票 的价格的升幅一次超过10%；审计用户操作数据库的语句，把用户对数据库的更新写入审计表；实现复杂的数据完整性规则；实现非标准的数据完整性检查和约束，例如，规定某一列的取值范围，提供可变的缺省值；实现复杂的非标准的数据库相关完整性规则，对相关的表进行连环更新；另外还具有报警功能，例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

? 触发器的类型

（1）DML触发器：建立在基本表上的触发器，响应基本表的INSERT、UPDATE、DELETE操作。 创建的语法为：

CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER triggering_event [OF COLUMN_NAME] ON table_name [FOR EACH ROW]

[WHEN trigger_condition] DECLARE 声明部分 BEGIN 执行部分 EXCEPTION 异常部分 END[trigger_name];

（2）INSTEAD OF触发器：建立在视图上的触发器，响应视图上的INSERT、UPDATE、DELETE操作。

（3）系统触发器：建立在系统或模式上的触发器，响应系统事件的DDL（CREATE、ALTER、DROP）操作。

? 触发器的组成

触发器由触发器头部和触发器体两个部分组成，主要包括以下参数： （1）作用对象：触发器作用的对象包括表、视图、数据库和模式。

（2）触发事件：激发触发器执行的事件。如DML、DDL、数据库系统事件等。 （3）触发时间：用于指定触发器在触发事件完成之前还是之后执行。

（4）触发级别：触发级别用于指定触发器响应触发事件的方式。默认为语句级触发器，即触发事件发生后，触发器只执行一次。如果指定为FOR EACH ROW，即为行级触发器，则触发事件每作用于一个记录，触发器就会执行一次。

（5）触发条件：由WHEN子句指定一个逻辑表达式，当触发事件发生，而且WHEN条件为

12

TRUE时，触发器才会执行。

（6）触发操作：触发器执行时所进行的操作。

四、实验内容：

1、定义存储过程，带一个学号参数，功能为：统计指定学号学生新选的课程数量（新选课程为选课表sc中grade字段为空的记录项）。 2、测试刚建的存储过程的功能

3、定义一个行级触发器，当出现删除student表记录的事件时触发，功能为将删除的学生记录保存到一个表bakstudent（sno,sname,sex,birthday,dno,deletedate），其中deletetime（类型为date）字段保存删除操作发生的日期；表bakstudent不存在任何完整性约束。 4、测试刚建的触发器的功能。

13

实验六 分布式数据库操作

一、实验目的

理解Oracle数据库分布式的特点，掌握分布式数据库的一些操作方法。 二、实验环境

假设这样一种应用，有一个公司，公司下有A和B两个分公司，A所有员工信息保存在一台Oracle数据库服务器上（实验中为本机，以下称为A），B所有员工信息保存在另一台Oracle数据库服务器上（以下称为B，具体IP实验时定），现需要同时对两个地方的信息进行修改（如加工资）。 三、预备知识

Oracle数据库管理系统具有分布式数据库功能，分布式数据库的概念不仅仅是能够访问多个数据库，而且多个数据库能够协同工作，维护全局数据库的一致性。Oracle数据库管理系统的分布式数据库功能具有以下特性：

支持分布式查询； 支持分布式事务管理；

具有硬件独立性：安装Oracle 服务器的硬件没有限制； 具有操作系统独立性：在多种操作系统下都能运行； 具有网络独立性：支持多种网络之间的通信。

要访问远程数据库必须通过数据库链，要建立数据库链，必须先在客户端系统配置文件tnsname.ora中建立一个连接串（连接描述符，网络服务名），连接串代表三方面的内容： 1、网络协议（如TCP/IP）

2 、远程数据库服务器的计算机名或网络地址（如IP地址：202.101.210.3) 3、远程数据库名

利用连接串，通过SQL语句建立数据库链，建立数据库链的语法如下：

CREATE DATABASE LINK 数据库链名

CONNECT TO 用户名 IDENTIFIED BY 口令 USING ?连接串名?；

例如：

create database link sales

connect to scott identified by tiger using ?oracle2?;

可以省略CONNECT子句不指定用户账户，系统会把登录账户作为默认的用户账户。

?访问远程数据库

select * from emp@sales;

select ename,tel from dept,emp@sales where dept.dno=emp.dno; update emp@sales set sal=sal+200 where eno=?e01?; insert into emp@sales

14

select * from emp where dno=?d01?; delete from emp@sales where age>60;

**仅仅在需要提供表名的地方需要加 “@数据库链名”

?使用同义词实现位置透明性，同义词是数据库对象，可以为数据库对象（表、视图、序列或其它

同义词等）定义别名。

CREATE SYSNONYM emp1 FOR emp@sales; 那么

select * from emp1； 相当于

select * from emp@sales;

四、实验内容

1． 在本计算机中添加一个Oracle数据库网络服务名“orcacle2”(该网络服务名对应的HOST为教

师指定，可以有两种方式，第一种为直接修改本计算机上的tnsname.ora文件。如下图

改成本机计算机名 改成教师指定计算机名

另一种方式为使用Oracle Net Configuration Assistant。

2． 启动一个SQL*Plus，使用scott/tiger用户账户和服务名orcacle2登录B计算机上的数据库，在

scott模式下以同上的命令创建员工表emp02 (在实验创建表的时候表名后加上自己的学号)，并插入一些数据；

3． 启动另一个SQL*Plus（以下所有操作步骤4~8都在这个sql*plus中完成），使用scott/tiger用户

账户和服务名orcacle登录本计算机上的Oracle数据库，在scott模式下创建员工表emp01，再向表emp01中插入一些数据；

15

create table scott.emp01(eno varchar2(6) primary key, ename varchar2(6), birthday date, sal number(5),

deptno varchar2(4));

4． 创建数据库链：

create database link b02link --b02link为数据库链名（属于scott的模式对象） connect to scott identified by tiger using ?orcacle2?;

5． 用SQL语句通过数据库链b02link操作计算机B上的表。 Select * from emp02@b02link;

Insert into emp02@b02link values(………); Update emp02@b02link set ………… Delete from emp02@b02link where …………

这里举例为操作scott模式下的emp02表，实际应用中也可以是其它的表，这要看有没有操作权限，而有没有权限是看创建的数据库链中对应的用户账户在目标服务器中是否有权限。 6． 创建同义词

create synonym b02emp --b02emp为同义词名（属于scott的模式对象）

for emp02@b02link --（记得在emp02后加上你的学号，因为前面创建这张表的时候加了学号）

7． 再用SQL语句来操作B计算机上scott模式下的emp表 select * from b02emp; insert into b02emp ……; update b02emp …….; delete from b02emp ……;

8． 按照下面的语句编制存储过程raise_salary，功能为给公司的每个员工加工资，再编制调用

raise_salary的PL/SQL程序，然后再用查询语句验证两台计算机上的数据都做了正确的修改。 Create or replace procedure raise_salary(increase number) as Begin

Update emp01 set sal=sal+increase; Update bo2emp set sal=sal+increase; Commit; End;

16

create table scott.emp01(eno varchar2(6) primary key, ename varchar2(6), birthday date, sal number(5),

deptno varchar2(4));

4． 创建数据库链：

create database link b02link --b02link为数据库链名（属于scott的模式对象） connect to scott identified by tiger using ?orcacle2?;

5． 用SQL语句通过数据库链b02link操作计算机B上的表。 Select * from emp02@b02link;

Insert into emp02@b02link values(………); Update emp02@b02link set ………… Delete from emp02@b02link where …………

这里举例为操作scott模式下的emp02表，实际应用中也可以是其它的表，这要看有没有操作权限，而有没有权限是看创建的数据库链中对应的用户账户在目标服务器中是否有权限。 6． 创建同义词

create synonym b02emp --b02emp为同义词名（属于scott的模式对象）

for emp02@b02link --（记得在emp02后加上你的学号，因为前面创建这张表的时候加了学号）

7． 再用SQL语句来操作B计算机上scott模式下的emp表 select * from b02emp; insert into b02emp ……; update b02emp …….; delete from b02emp ……;

8． 按照下面的语句编制存储过程raise_salary，功能为给公司的每个员工加工资，再编制调用

raise_salary的PL/SQL程序，然后再用查询语句验证两台计算机上的数据都做了正确的修改。 Create or replace procedure raise_salary(increase number) as Begin

Update emp01 set sal=sal+increase; Update bo2emp set sal=sal+increase; Commit; End;

16

本文来源：https://www.bwwdw.com/article/kpl.html