大型数据库Oracle》实验指导书(0710)

1、大型数据库oracle编撰闽江学院计算机实验教学中心印制目 录实验一 oracle数据库管理与工具使用2实验二 表连接、高级子查询6实验三 管理存储结构、用户、权限安全、模式和表12实验四 约束、索引、视图、序列和同义词16实验五 pl/sql编程基础20实验六 触发器、过程、函数和程序包27实验七 管理存储结构、备份与恢复33实验八 sql*plus和sql基本查询39实验九 管理存储结构、用户、权限安全、模式和表42实验十 创建和管理表，视图，约束46实验十一 序列、索引、同义词48 实验一 oracle数据库管理与工具使用一、 实验目的1. 掌握oracle的启动、关闭、连接和管理初始化

2、参数2. 熟悉em和sql*plus的使用3. 了解用户和模式的概念，并查询hr模式表的数据二、实验环境一台pc机，安装widows xp操作系统，oracle 10g或11g数据库软件。三、实验内容 1. 启动和关闭window操作系统的oracle服务2. 使用sql*plus与oracle数据库建立连接3. 使用sql*plus启动和关闭oracle数据库4. 使用em和sql*plus显示和设置初始化参数文件5. 使用em和sql*plus解锁hr模式，并使用sql*plus编写查询该模式中表的sql四、实验步骤1）进入windows xp操作系统 控制面板-管理工具-服务启动以下几个

3、服务：oracleservice（数据库名）oracleoradb10g_home1tnslisteneroracleoradb10g_home1isql*plusoracledbconsoleorcl（启动em需要的服务）2）使用sql*plus连接oracle数据库进入windows xp操作系统 oracle - oradb10g_home1-应用程序开发-sql plus输入：用户名:sys/orcl as sysdba 按确定。 输入select * from hr.employees; 按回车可以查询该表数据3）使用sql*plus用sys用户关闭 oracle数据库输入并执行如下

4、sql语句：shutdown immediate输入select * from hr.employees; 按回车无法查询该表数据4）创建并启动与数据库对应的实例 startup nomount 按回车5）为未加载数据库的实例加载数据库alter database mount; 按回车6）将处于未打开状态的数据库设置为打开状态alter database open; 按回车7) 关闭数据库然后直接启动实例，加载数据库，打开数据库shutdown immediate；startup;8) 使用sql*plus显示所有初始化参数 show parameter9) 使用em查询和设置动态初始化参数进

5、入ie，http:/服务器:1158/em数据库管理-初始化参数10) 解锁hr用户使用em用system登录，通过方案-用户-编辑hr用户为解锁，同时修改密码。使用sqlplus 用system登录alter user hr account unlock;alter user hr identified by hr;11) 使用hr用户连接sqlplus,connect hr/rh;select table_name from user_tables;select * from employeesselect * from 客户信息;查询结果应该包括客户信息的所有列，并且包括该表的所有记录行

6、。2）下面的sql语句将只返回“客户姓名”、“客户地址”和“联系电话”列。select 客户姓名，客户地址，联系电话from 客户信息;这个查询结果将包含指定的列，并且包含该表所有的记录。3）在下面的select语句中，将使用where子句以限定检索的客户信息。select 客户信息，客户地址，联系电话from 客户信息where 所属业务员编号1001由于添加了where子句，所以查询结果现在只包含业务员编号为“1001”的客户信息。4）下面创建的select语句用于对“客户信息”表中的信息分组，输入并执行如下语句： select 所属业务员编号 as 业务员编号，count（客户编号） a

7、s 客户数from 客户信息group by 所属业务员编号对应于每一个业务员编号都返回一行，并且每行中都包含所属的客户数。5）将having子句添加到刚才执行的select语句中，输入并执行如下sql语句。select 所属业务员编号 as 业务员编号，count（客户编号） as 客户数from 客户信息group by 所属业务员编号having count（客户编号）=3;使用sql*plus函数：6）下面的sql语句使用一组数据演示trunc函数与round函数之间的区别和相同点。select trunc（46.326，2），round（46.326，2）from dual;运行该语

8、句中，trunc函数的返回值为46.32，round函数的返回值为46.33。select trunc（46.326），round（46.326）from dual;运行该语句中，trunc函数和round函数将返回相同的值46。五、实验小结通过本次实验学会了使用sql*plus与数据库建立连接，使用select语句检索数据。实验二 表连接、高级子查询一、 实验目的1.理解什么是连接操作2.如何进行简单的连接操作3.掌握如何进行内连接、外连接、交叉连接4.创建返回多行的子查询5.创建返回单值的子查询6.掌握嵌套子查询的使用二、实验设备一台pc机，安装widows xp操作系统，oracle 1

9、0g或11g数据库软件。三、实验内容 1. 通过查询school模式中的表，以练习各种连接操作的使用，特别是具有外键关联的表。在sql*plus环境下，打开sql*plus，连接到school模式。1）第一个操作是用逗号分隔连接，将表student和class连接起来，查询学生及所在班级信息。2) 在上面语句的基础上连接department表,以显示所在院系名称.3) 修改上一个select语句,将其改为内连接中的等值连接.4) 修改上一个select语句,将其改为连接中的自然连接,并且限制只显示”法律系”的学生信息.5) 修改上一个select语句,将其改为右连接条件.本练习使用逗号分隔连接

10、,交叉连接,内连接和外连接查询多个表,可以看连接查询为操作数据库提供了很大的灵活性.但是连接并不是唯一的访问多个表的方法.子查询的功能与连接一样,也可以实现多表查询.2.本练习将使用子查询数据.子查询很大程度上是通过关键字连接在一起的,这里将使用多个关键字连接子查询,以便查询多个表. sql*plus , 连接到school模式.1) 创建的第一个select 语句将要返回”06法2班”所有的学生信息. 2) 修改上一个查询语句,使用关键字exists连接子查询,并且要求显示的数据相同.3) 下面的语句将使用嵌套查询显示属于”法律系”的所有学生信息.4) 现在再创建一个select 语,该语句

11、查询”刑法通则”课程的学习成绩最高的学生信息.5) 上面的语句通过使用大于等于(=)比较运算符和all关键字实现,另外一种简单的方法是使用统计函数max实现.四、实验步骤1.1）输入并执行如下sql语句：select s.sid , s.sname , s.birth , s.sclass ,s.saddress , c.department , c.cnamefrom student s , class cwhere s.sclass=c.cid;2）输入并执行如下sql语句:select s.sid , s.sname , s.sclass,s.saddress , c.departmen

12、t , c.cname, d.dnamefrome student s,class c , department dwhere s.sclass=c.cidand c.department=d.did;3）输入并执行如下sql语句:select s.sid , s.sname , s.sbirth, s.sclass,s.saddress , c.deoartment , c.cname , d.dnamefrom student s join class con s.sclass=c.cidjoin department don c.depatment=d.did;4）输入并执行如下sql语

13、句:select s.sid , s.sname , s.sbirth , s.sclass,s.saddress , c.department, c.cname,d.dnamefrom student s natural join class c natural join department dwhere d.dname=法律系 ;5) 输入并执行如下sql语句:select s.sid , s.sname , s.sbirth , s.sclass, s.saddress , c.department , c.cname , d.dnamefrom student s right joi

14、n class con s.sclass=c.cidright join department don c.department=d.did;2. 1）输入并执行如下语句;select * from studentwhere sclass in(select cid from classwhere cname = 06法2班此语句使用子查询查询class表,以获取名为” 06法2班”的cid列表.in关键字再把该返回值与student表中的cid列表进行比较.2）输入并执行如下sql语句:select * from student swhere exists(select * from cla

15、ss cwhere c.cname= 06法2班and s.sclass=c.cid);执行该语句获取的学生信息与上一个语句获取的学生信息相同,均为属于” 06法2班”的学生信息.3）输入并执行如下查询语句:select * from studentwhere sclass in (select cid from classwhere department in(select did from departmentwhere dname=法律系);4）输入并执行如下sql语句:select * from studentwhere sid in(select sid from gradewher

16、e score=all(select score from gradewhere coid in(select coid from courseswhere coname=刑法通则);在上面的语句中,最内层的select语句用于查询课程名为”刑法通则”的课程代号,然后上层select语句根据该课程代号查询该课程的所有成绩;次外层select 语句则使用大于等于(=)比较算符和all关键字,求出大于等于全部成绩的所有学生编号;最后由select 语句列出这些学生信息.5）输入并执行如下sql语句,比较两者的运算结果.select * from studentwhere sid in(select

17、 sid from gradewhere score=(select max(score) from gradewhere coid=(select coid from courses where coname=刑法通则); 由于使用统计函数max,可以确定返回的最高成绩只有一个,所以可以使用单值子查询;另外由于每门课程只对一个代号,虽然这里没有使用统计函数,但是也可以使用单值子查询.由此可以看出,sql语句是非常灵活的,可以由多种方法实现相同的功能.五、实验小结通过本次实验理解了什么是连接操作及如何进行简单的连接操作。掌握了如何进行内连接、外连接、交叉连接。实验三 管理存储结构、用户、权限安

18、全、模式和表一、 实验目的1. 创建表空间2. 创建用户3. 创建角色使得安全模式的设置和管理容易4. 使用grant和revoke语句授予和撤销对象权限5. 创建表和管理表二、实验设备一台pc机，widows操作系统，oracle 10g三、实验内容 1创建一个表空间exer_tabspace,采用本地化管理，分配初始空间为100mb，使用空间配额不受限制，文件名：exer_tabspace.dbf，放在数据文件相应目录。2创建一个用户tempuser，其口令为oracle，默认表空间为exer_tabspace，临时表空间为temp，对表空间没有配额限制。3. 创建一个用户配置文件temp

19、profile，包含的资源及口令限制如下：该用户最多可以建立3个并发的会话连接用户执行语句使用的cpu最长时间为20分钟空闲时间超过15分钟后，断开与用户的连接限制用户每次调用sql语句时，能够读取的数据库块数200限制用户在登录到oracle数据库时允许失败的次数24. 为用户tempuser指定配置文件5. 向用户授予连接数据库系统权限。6向用户授予对对象“vendition.商量信息”的select权限，并以用户tempuser连接到数据库，以查询“商品信心”表。7撤销向用户tempuser授予的系统权限，向用户授予connect，resource角色。四、实验步骤1. 创建一个表空间e

20、xer_tabspace1）connect system/orcl;2) create smallfile tablespace exer_tabspace datafile f:oracleproduct10.2.0oradataorclexer_tabspace.dbf size 100m logging extent management local segment space management auto2. 创建一个用户tempusercreate user tempuseridentified by oracle default tablespace userstemporary

21、 tablespace tempquota unlimited on users;3创建一个用户配置文件tempprofilecreate profile tempprfile limitsessions_per_user 3cpu_per_call 2000idle_time 15logical_reads_per_call 200failed_login_attempts 24为用户tempuser指定配置文件alter user tempuser profile tempprofile;5向用户授予连接数据库系统权限grant create session to tempuser;6向用

22、户授予对对象“vendition.商量信息”的select权限，并以用户tempuser连接到数据库，以查询“商品信心”表。grant select on vendition.商品信息 to tempuser;connect tempuser/oracle;select * from vendition.商品信息；7撤销向用户tempuser授予的系统权限，向用户授予connect，resource角色。connect system/orcl;revoke select on vendition.商品信息 from tempuser;revoke create session from tem

23、puser;grant connect to tempuser;grant resource to exercise;8创建基本表(1)登录到tempuser模式，在其中创建一个学生信息表student和一个班级信息表class。connect tempuser/oracle;create table student(id varchar2(20),name varchar2(8),sex number(1),classid varchar2(20)tablespace users;create table class(id varchar2(20),principal varchar2(20

24、),tel varchar2(20)tablespace users;(2)修改新创建表student的存储参数。alter table student movestorage(next 32kmaxextents unlimitedpctincrease 0)(3).修改表class的数据块参数。alter table classpctfree 40pctused 40；五、实验小结通过本次实验学会了创建表空间，用户，创建角色使得安全模式的设置和管理容易，使用grant和revoke语句授予和撤销对象权限。创建表和修改表的存储参数。实验四 约束、索引、视图、序列和同义词一、 实验目的1.理解

25、如何在表中定义各种约束，以保证数据的完整性2. 理解索引的作用，了解oracle提供的主要索引类型，以及各自的特点，掌握如何创建各种索引3.在用户模式中创建视图、可更新视图，理解视图的相关性4. 对序列对象进行管理5管理同义词二、 实验设备一台pc机，widows操作系统，oracle 10g三、 实验内容1.创建表student的主键，外键，唯一和其他约束。2.为school模式中的student表创建索引，并查看索引的使用情况。1） 在student表的sname列上创建b树非唯一索引：2） 在student的班级信息列上创建位图索引：3） 查看oracle的执行路径。4. 在school

26、模式中创建视图，查询视图的定义，并对视图进行更新。1）创建一个视图student_math。这个视图基于school模式中的student表，并且该视图只包括那些系别为“数学系”的学生信息。在创建视图时使用with check option，防止更新视图时，输入非数学系的学生信息。2）创建一个连接视图department_class,它包含class表中的classname列和department表的depname列，在wherer子句中将两个表连接在一起。3）查看视图各个列是否允许更新。5. 创建序列，序列的开始数字为100，每次递增10，无限增长。6. 创建公有同义词emp 为hr.emp

27、loyees 四、实验步骤1.创建表student的主键，外键，唯一和其他约束。1）在tempuser模式中创建表student，classconnect tempuser/oracle;create table student(id varchar2(20),name varchar2(8),sex number(1),classid varchar2(20)tablespace users;create table class(id varchar2(20),principal varchar2(20),tel varchar2(20)2）使用sql或可视化工具创建表student的主键，

28、外键，唯一和其他约束。2.为school模式中的student表创建索引，并查看索引的使用情况。1） 连接到school模式，使用如下语句在student表的sname列上创建b树非唯一索引：connect school/school;create index sname_index on student(sname) tablespace users;2） 在student的班级信息列上创建位图索引：create bitmap index sclass_index on student(sclass) tablespace users;3）输入并执行如下语句，查看该语句是否会使用索引。 se

29、t autotrace traceonly select * from student where sname=刘丽；可以看出使用该select语句使用了索引sname_index4. 在school模式中创建视图，查询视图的定义，并对视图进行更新。1）创建一个视图student_math。这个视图基于school模式中的student表，并且该视图只包括那些系别为“数学系”的学生信息。在创建视图时使用with check option，防止更新视图时，输入非数学系的学生信息。create or replace view student_math as select * from school

30、.student where sclass in ( select ame from class c,department d where c.department=d.didand d.dname=数学系） with check option；2）创建一个连接视图department_class,它包含class表中的classname列和department表的depname列，在wherer子句中将两个表连接在一起。输入并执行下列sql语句：cteate or replace view department_class asselect d.dname,amefrom class c,d

31、epartment dwhere c.department=d.did;3）查看视图各个列是否允许更新。输入并执行语句：col owner format a20col table_name format a20col column_name format a20select *from user_updatable_columnswhere table_name=upper(department_class);5. 创建序列，序列的开始数字为100，每次递增1，无限增长。输入并执行如下语句：create sequence incr_idstart with 100increment by 1n

32、omaxvaluenocycle order;6. 创建公有同义词hr_emp 为hr.employees输入并执行如下语句：create public synonym hr_emp for hr.employees 五、实验小结通过本次实验初步掌握如何创建表各种约束，以保证数据的完整性理解索引的作用,掌握创建各种索引。掌握了创建视图，创建序列和同义词。实验五 pl/sql编程基础一、 实验目的1.学习pl/sql程序块的结构2.pl/sql程序中使用的数据类型3.如何在pl/sql中使用各种变量和常量4.pl/sql程序的条件判断语句5.pl/sql程序的循环控制语句6.在pl/sql程序中

33、使用游标处理表中的信息二、实验设备一台pc机，widows操作系统，oracle 10g三、实验内容 1.在pl/sql程序中，为了处理数据库中存储的数据，用户可以使用系统预定义的标量变量，也可以使用自定义的复合变量。本练习将演示如何使用各种类型的变量：1）使用标量变量。标量变量是最简单的变量，它只能存储单个值，它的数据类型也是系统预定义的。2）使用%type类型的变量。使用%type类型的变量后，如果用户随后修改数据库中该列的结构，则该类型变量的值也随之改变。3）使用记录变量。使用记录变量首先需要用户定义记录变量的类型，然后才可以声明记录类型的变量。4) 使用%rowtype变量。%rowt

34、ype变量的结构与数据库中行的结构完全相同。它可以存储数据表中一个完整的行。2.使用游标 在程序中访问数据库表最通用的方法是嵌入sql语句，由于sql语句一般以集合的形式返回结果，而程序设计语言并不能处理集合形式的数据。因此，需要使用游标架起这两者之间连接的“桥梁”。本练习访问数据库最简单的方法是使用for cursor。该方法作为一种1 ) 隐式游标，它不需要用户显示定义、打开等操作，就可以浏览数据库中的表。现在使用该语句浏览school 模式中的student 表。2 ) 使用显示游标。这次使用不带参数的显示游标，浏览school模式中的student表。3) 使用游标变量。游标变量是动态

35、的，可以在打开游标时指定游标所使用的select语句。4) 下一个要使用的游标是更新游标。更新游标需要使用 for update 子句。四、实验步骤1.在pl/sql程序中，为了处理数据库中存储的数据，用户可以使用系统预定义的标量变量，也可以使用自定义的复合变量。1）在sql*plus中连接到school模式: connect school/school;输入如下语句：set serveroutput on; declarevar_score number;beginvar_score:=89;update grade set score=var_scorewhere sid=s0603720

36、1 and coid=c0154;end;在该程序中定义一个number类型的变量，为其赋值89。当运行时，程序块中的update 语句将使用此值修改grade中的分数score 字段。2） sql*plus中输入如下语句： declarevar_name student.sname%type;beginselect snameinto var_namefrom studentwhere sid=s06037201;dbms_output.put_line(var_name);end;运行该程序将显示编号为s06037201的学生姓名。3）在sql*plus中输入如下程序，以显示某学生信息：d

37、eclaretype student_info is record(stu_id student.sid%type,stu_name student.sname%type);rec_student student_info;beginselect sid,snameinto rec_studentfrom studentwhere sid=s06037201;dbms_output.put_line(rec_student.stu_id);dbms_output.put_line(rec_student.stu_name);end;4) sql*plus中输入如下程序，以显示student表中

38、一个完整的行。declarevar_student student%rowtype;beginselect*into var_studentfrom studentwhere sid=s06037242;dbms_output.put_line(var_student.sid)dbms_output.put_line(var_student.sname);dbms_output.put_line(var_student.sclass);end;2.使用游标 1 ) 隐式游标，它不需要用户显示定义、打开等操作，就可以浏览数据库中的表。现在使用该语句浏览school 模式中的student 表。s

39、et serveroutput onbeginfor studenttable in(select sid ,sname,sbirthfrom student)loopdbms_output.put(studenttable.sid);dbms_output.put(studenttable.sname);dbms_output.put_line(studenttable.sbirth);end loop;end;2 ) 使用显示游标。这次使用不带参数的显示游标，浏览school模式中的student表。在sql*plus中输入并执行如下语句：declarecursor student_cur

40、 isselect * from student;student_row student%rowtype;beginopen student_cur;loopfetch student_cur into student_row;exit when student_cur%notfound;dbms_output.put_line(student_row.sid);dbms_output.put_line(student_row.sname);end loop;close student_cur;end;3) 使用游标变量。游标变量是动态的，可以在打开游标时指定游标所使用的select语句。 在

41、sql*plus中输入并执行如下sql 语句，该语句通过游标变量浏览student 表：declaretype student_rc is ref cursor;v_rc student_rc;student_row student%rowtype;beginopen v_rc for select* from student;loopfetch v_rc into student_row;exit when v_rc%notfound;dbms_output.put_line(student_row.sid);dbms_output.put_line(student_row.sname);e

42、nd loop;close v_rc;end;4) 下一个要使用的游标是更新游标。更新游标需要使用 for update 子句。在sql*plus中输入并执行如下语句：declarecursor grade_cur is select * from gradefor update of score nowait;var_score number;beginfor r in grade_cur loopif r.score studentid); end; 执行结果将显示指定编号的学生姓名、所在班级和系的名称。5） 输入并执行如下语句：create or replace package user

43、_pkg is tupe student_info is record( id student.sid%type, name student.sname%type, birth student.sbirth%type); procedure printstudentinfo; function getstudentinfo(id in varchar2) return student_info;end user_pkg;执行这段代码将返回“程序包已经创建”的信息。6）输入并执行如下语句： create or replace package body user_pkg is cursor stu

44、dent_cur is select sid,sname,sbirth,saddresss from student; studentinfo student_info; begin open student_cur; fetch student_cur into studentinfo; while student_cur%found loop dbms_output.put(v_sthdentinfo.id | ); dbms_output.put(v_ | ); dbms_output.put(v_sthdentinfo.birth| ); dbms_output.put_line(v_sthdentinfo.addr | ); end loop; close student_cur; exception when invalid_cursor then dbms_output.put_lin