《数据库原理》实验教学指导书.doc

1、目 录第一部分 大纲和计划1.1 实验教学大纲 11.2 实验实施计划表 2第二部分 实验说明2.1 实验一：了解Oracle中的基本知识 32.2 实验二：Oracle中的查询 42.3 实验三：Oracle中的表 52.4 实验四：PL/SQL编程(一) 62.5 实验五：PL/SQL编程(二) 72.6 实验六：PL/SQL编程(三) 82.7 实验七：PL/SQL编程(四) 92.8 实验八：触发器设计 10第三部分 实验补充教材3.1 实验一：了解Oracle中的基本知识 113.2 实验二：Oracle中的表 123.3 实验三：Oracle中的查询 153.4 实验四：PL/SQ

2、L编程(一) 153.5 实验五：PL/SQL编程(二) 183.6 实验六：PL/SQL编程(三) 223.7 实验七：PL/SQL编程(四) 263.8 实验八：触发器设计 31第一部分 大纲和计划1.1数据库原理实验教学大纲一、课程名称：数据库原理（Principle of Database System）二、课程编码：三、课程总学时： 16 学时 理论： 0 学时；实验： 16 学时四、课程总学分： 学分五、适用专业和开课学期：计算机科学与技术专业，第4学期。六、实验的目的与任务：通过ORACLE中用户及表的创建、SQL查询、ORACLE中的表、PL/SQL编程、触发器设计等实验，使学

3、生掌握数据库技术的基本原理在大型数据库中的应用。七、主要仪器设备及台（套）数：计算机八、主要实验教材（指导书）及参考用书：数据库原理及应用(Oracle)实用教程 刘甫迎、王道学、党晋蓉主编 清华大学出版社2004年10月第1版 九、成绩考核方式及评分标准：教师根据学生通过FTP上交的实验报告确定其完成数量和质量，进一步综合学生的上机态度进行评分。实验成绩占课程总成绩的30%。十、实验开出率：100%十一、实验项目与要求：序号实验项目名称时数项目要求项目类型项目性质每台（套）仪器人数目的要求实验耗材定额（按自然班）1了解ORACLE的基本知识2必修操作验证1掌握用户及表的创建方法，熟悉ORAC

4、LE的运行环境2ORACLE中的查询2必修操作验证1重点掌握复杂查询的实现方法3ORACLE中的表2必修操作验证1大致了解ORACLE中的各种表4PL/SQL编程8必修操作设计1掌握简单的PL/SQL编程的方法5触发器设计2必修操作设计1了解各种触发器的设计方法注：1项目要求：必修、选修、其他；2项目类型：演示、操作、模拟；3项目性质：验证、综合、设计、研究。12 数据库原理实验大纲及其实施计划表课程综述本课程重点讲解数据模型、数据库系统结构、关系数据库、SQL语言、数据库安全性和完整性、关系数据库设计理论、数据库设计、数据库编程、查询优化、并发控制和恢复、数据库管理系统以及数据库技术新进展等

5、内容。通过本课程的学习，学生不但可以掌握数据库技术的基本原理，而且还能够大型数据库的基本应用方法。实验设置总体说明 本课程设计了八个实验，通过ORACLE中用户及表的创建、SQL查询、ORACLE中的表、PL/SQL编程、触发器设计等实验，学生可以掌握数据库技术的基本原理在大型数据库中的应用。实验名称(选做实验后面加*号)1了解Oracle的基本知识2Oracle中的表3Oracle中的查询4PL/SQL编程（一）5PL/SQL编程（二）6PL/SQL编程（三）7PL/SQL编程（四）8触发器设计9实验占课程总课时、学分比例情况实验占课程总学时的25%学分占课程总学分的15%实验资料存档形式(

6、纸质/光盘)光盘第二部分 实验说明实验说明21 实验一 了解Oracle的基本知识实验目的：1 熟悉ORACLE的环境，学习使用SQL*Plus与ORACLE进行交互。2 掌握用户的建立、删除、角色的授权以及用户密码的修改。3 掌握连接数据库以及断开连接的方法。4 建立基本的数据库表，学习简单的查询。5学会表结构的修改、显示与删除。6学习提交与回滚的方法。实验环境（硬/软件要求）：安装Oracle 9i实验内容：1 为自己建立用户帐号，练习如何修改用户参数。2 以新建的帐号登录数据库，建立教材第75页中的四张表结构并输入相应表内容。实验主要步骤：1 建立用户。2 改变用户参数。3 建立表结构。

7、4 修改表结构。5 显示表结构。6 记录的输入及修改。7提交。评分标准：实验报告占70%，上机态度占30%实验报告形式(纸质/电子)：电子22 实验二 Oracle中的表实验目的：1熟悉Oracle中的各种表。2熟悉并练习临时表的使用方法。3学会修改表属性的方法。4熟练掌握表结构的修改和删除、索引的创建与删除。5熟悉数据词典中的几个视图的查看方法。 实验环境（硬/软件要求）：安装Oracle 9i实验内容：1 按补充教材建立临时表，注意？处的返回结果。2 把当前用户的默认表空间改为USERS，并把四张表移到USERS表空间中。3完成以下与表结构相关的操作： （1）复制表S的结构生成一张新表，取

8、名为SS； （2）在SS表中加入属性TEL（CHAR型）； （3）将SS表中的属性STATUS类型改为SMALLINT型。4完成以下与索引相关的操作： （1）在SS表上建立SNAME的唯一索引； （2）在SS表上按CITY升序、SNAME降序建立唯一索引； （3）删除以上索引； （4）删除SS表。5熟悉并使用数据词典中的几个视图，查看自己帐户已创建的所有表的信息。实验主要步骤：1实验前根据实验内容做好书面准备。2上机验证准备好的命令正确与否，修改错误直至正确。评分标准：实验报告占70%，上机态度占30%实验报告形式(纸质/电子)：电子23 实验三 Oracle中的查询实验目的：1熟练掌握各种S

9、QL语句的使用方法。2掌握视图的建立与删除。实验环境（硬/软件要求）：安装Oracle 9i实验内容：1 实现第三章作业中的第5题的SQL查询。2 完成以下查询：（1）按所驻城市升序、供应商号降序显示所有的供应商信息。（2）显示各供应商供应的零件种类数，要求按供应种类的多少降序显示，显示的内容包括供应商号，零件种类数（提示：结果中应包括未供应零件的供应商）（3）显示所有供应了“蓝色”零件数量在200以上（含200）的所有供应商信息，显示内容包括供应商号、供应商名字、所驻城市和供应数量。（4）找出供应零件总量超过1000（含1000）的所有供应商号，要求按供应总量多少降序排列。（5）找出零件供应

10、总数量最多的供应商号。3 实现第三章作业第11题中的视图建立及查询，最后删除新建的视图。实验主要步骤：1实验前根据实验内容做好书面准备。2上机验证准备好的命令正确与否，修改错误直至正确。评分标准：实验报告占70%，上机态度占30%实验报告形式(纸质/电子)：电子24 实验四 PL/SQL编程（一）实验目的：1熟悉脚本的编写及执行。2了解编程的结构以及变量的声明方法以及记录类型的使用。实验环境（硬/软件要求）：安装Oracle 9i实验内容：1熟悉脚本的使用方式。2调试例、3，了解PL/SQL的编程风格、变量的声明方法以及记录类型的使用方法。3查询输出S1供应商的Sname及City。实验主要步

11、骤：1输入程序。2运行程序并分析结果。3修改程序，再运行程序，分析结果。评分标准：实验报告占70%，上机态度占30%实验报告形式(纸质/电子)：电子25 实验五 PL/SQL编程（二）实验目的：1掌握索引表的定义及引用。2掌握索引表中的记录引用方法。3掌握游标的使用方法。实验环境（硬/软件要求）：安装Oracle 9i实验内容：1调试例3，记录显示结果。2模仿例5，分别显示供应商S2以及S5的供货情况（包括PNO、JNO、QTY）。3模仿例6，显示表S中每一个供应商的号码及名称。实验主要步骤：1编程序并上机调试运行。2修改程序再运行，检查结果是否正确。评分标准：实验报告占70%，上机态度占30

12、%实验报告形式(纸质/电子)：电子26 实验六 PL/SQL编程（三）实验目的：1掌握游标属性的使用。2掌握各种程序控制结构的运用。实验环境（硬/软件要求）：安装Oracle 9i实验内容：1模仿例1，把其中的EMP表改成P表运行一遍，记录显示结果。2编程显示J表的前三条记录。3编程统计每一个供应商的供应零件总量，要求显示每一个供应商的名称以及供应总量。实验主要步骤：1实验前按实验内容编制程序。2调试程序，上机运行。评分标准：实验报告占70%，上机态度占30%实验报告形式(纸质/电子)：电子27 实验七 PL/SQL编程（四）实验目的：1掌握运用过程、函数的方法。2掌握程序包的编程方法。实验环

13、境（硬/软件要求）：安装Oracle 9i实验内容：1针对SPJ表编一程序包，要求包含以下三个过程实现以下功能：（1）插入一条新记录（传入各字段参数）（2）删除一条记录（传入SNO、PNO、JNO）（3）输出某供应商所提供的所有的零件号、工程号及数量（传入SNO）实验主要步骤：1实验前书面编制程序包。2调试程序，上机运行直至程序包无语法错误。3调用程序包中的过程，验证其正确性并记录结果。评分标准：实验报告占70%，上机态度占30%实验报告形式(纸质/电子)：电子28 实验八 触发器设计实验目的： 1掌握ORACLE中各种触发器的设计方法。实验环境（硬/软件要求）：安装Oracle 9i实验内容

14、：1实现教材P162中例18 的BEFORE行级触发器的定义，并检测触发器是否执行。2模仿教材P162例19，定义AFTER行级触发器，当供应情况表SPJ的供应数量Qty发生变化后就自动在数量变化表Qty_log中增加一条相应记录。3在S表上创建一个触发器，要求插入的记录中SNO的内容均为大写字符。实验主要步骤：1按照实验内容设计触发器。2调试程序，上机运行并加以验证。评分标准：实验报告占70%，上机态度占30%实验报告形式(纸质/电子)：电子第三部分 实验补充教材3.1 实验一 Oracle中的基本知识一 大型数据库与单用户数据库的区别1 安全性（1） 用户权限管理（2） C/S模式，服务器

15、统一处理客户端的请求（3） 没有DBF文件，一个表可以分跨在多个物理文件上，数据存放格式不公开2 并发性：用进程或线程同时处理多个用户的请求，自动进行并发控制，封锁的粒度可以到行3 分布式处理：多台服务器同时对外服务，提高处理速度及并发度4 大容量5 数据词典：定义了表的逻辑模式和物理模式，用户可以在这里找到了解数据库内容所需的所有对象二 SQL*PlusSQL*Plus是一个开发者和管理员用来与Oracle数据库进行交互的客户工具。它可以让用户发布SQL语句，编译和执行PL/SQL代码，从与Oracle相连接的命令行控制台上管理本地服务器或者网络上任何位置的服务器上的数据库。三 建立用户1

16、如何建立用户？SQLconnect wzp/wzpmyoracle；SQLcreate user jxxxidentified by jxxx;SQLgrant connect,resource to jxxx;2 如何改变用户参数？（1） 修改密码SQLconnect jxxx/jxxxmyoracle;SQLalter user jxxx identified by jyyy;（2） 锁定帐号以及解除帐号锁定SQLconnect wzp/wzpmyoracle;SQLalter user jxxx account lock; (锁定帐号)SQL alter user jxxx accoun

17、t unlock; (解除帐号锁定)（3） 如何删除用户SQLconnect wzp/wzpmyoracle;SQLdrop user jxxx cascade;四 建表1 Oracle中的基本数据类型（1） char(n)：定长字符串（2） varchar2(n)：变长字符串（3） number(m)：整数（4） number(m,n)：浮点数或实数（5） date：日期2 改表alter table s add /modify 3 显示表结构SQLdescribe s;4 删除表drop table s;五 记录的输入及修改输入SQLinsert into s values();2修改SQ

18、Lupdate s set 六 简单查询select * from s;七 提交和回滚commit/rollback3.2 实验二 Oracle中的表一 Oracle中的表Oracle已经发布了新类型的表，来适应各种类型的数据存储、数据访问以及性能要求。对于所有类型的表，Oracle都允许开发人员和管理人员规定各种表特性。我们应该理解各种表类型，以及用户为什么要选择其中一种类型，而不是其他的类型。在用户应用中，可能常规关系表就可以完成工作，然而其他类型的表可能会让工作更快速，使用更少的磁盘空间，并占用更少的处理器资源。常用的表类型有堆表、外部表、索引组织表和临时表，另外还有其他一些表类型。二

19、堆表（heap table）：是最基本的表，采用随机存储的方式，在将行写入数据块的时候不会考虑其他行的存储位置，而是将数据写入第一个具有足够自由空间的段，当更新和删除行的时候，就会为新的插入提供可用空间。三 外部表（rxternal tables,Oracle 9i中才有）：在数据库以外的文件系统上存储的只读表。通过使用外部表，就无须将数据复制到数据库中，并且强制更新，我们可以让数据保留在普通文件中，并且允许数据库对其进行实地读取。在这种方法中，外部应用可以采用它认为合适的方法更新数据，而且也不用调用SQL*Loader执行数据载入操作。四 索引组织表（IOT表）可以存储索引这样的内容，以辅助

20、查询性能。索引组织表会以牺牲插入和更新性能为代价提供极好的查询性能。对于总是要通过特定索引访问的表，使用索引组织表来代替堆组织表可以提高性能。建立方法：create table s (sno sname status city primary ) organization index /五 临时表：只在事务处理或会话进行期间存在数据的表。数据会在事务处理或者会话开始以后插入临时表，当事务处理或者会话完成之后就会删除。Oracle中，临时表只需建立一次，建议在需要临时存储数据的应用中使用临时表。例子：SQLcreate global temporary table s1_tab on commi

21、t preserve rows *规定了一个专用于会话的临时表 as select * from s; SQLselect count(* ) From s1_tab; ?SQLcreate global temporary table s2_tab on commit delete rows *规定了一个专用于事务的临时表 as select * from swhere 1=0; SQLinsert into s2_tabselect * from s; SQLselect count(* ) from s2_tab; ? SQLcommit; SQLselect count(* ) fro

22、m s1_tab; ? SQLselect count(* ) from s2_tab; ? SQLdisconnect SQLconnect wzp/wzpmyoracle; SQLselect count(* ) from s1_tab; ? SQLselect count(* ) from s2_tab; ?六 其它表类型1 分区表：可以将非常大的表分割成较小的片段分区存放2 簇表：物理上存储在一起的两个或多个表（如SQL中连接的表总是会一起受到查询），以减少磁盘读取量。3 散列簇表：类似于簇表，只是存取行的方法不同。七表空间当使用Oracle管理应用数据的时候，表的特性将会决定怎样建立

23、表，怎样在磁盘上存储表，以及当表生成和可以使用之后，应用最终执行方式。我们可以使用TableSpace子句来规定表的存储位置。该子句可选，若建表时不写该子句则把新建表放在默认表空间中，初始为system。但最好改为users。 判定默认表空间 select default_tablespace from user_users; 改变默认表空间（1）初建时(用DBA帐号登录)Create user j001 identified by j001 default tablespace users;（2）初建时未设，后来改(须用DBA帐号登录才可以改)Alter user j001 default

24、tablespace users;（3）将表移动到新的表空间Alter table s move tablespace users;八数据词典每一个数据库都有一个数据词典，它是用户的整个Oracle数据库的编目。当建立用户、表、约束和其他数据库对象的时候，Oracle都会自动维护一个在数据库中存储的项编目。用户可以通过以下几个视图得到相关的一些信息： User_tables 视图：展示当前用户所拥有的所有表的信息。SQL select table_name,tablespace_name from user_tables order by table_name; DBA_tables视图：数据

25、库管理员帐号可以使用它查看数据库中所有用户的表数据。SQLselect owner,table_name,tablespace_name from dba_tables where owner in(WZP,J101) order by owner,tablespace_name,table_name; ALL_tables视图：可以查看有关用户所拥有的表的信息以及用户已经被授予特权的表的信息。SQLselect owner,table_name from all_tables order by owner,table_name;3.3 实验三 ORACLE中的查询一复杂查询注意点a) 表别名

26、及列别名的设置b) 连接(Oracle8i中无法实现连接，只能用笛卡儿积代替)i. 自然连接ii. 内连接iii. 外连接二 其他语句a) 插入多行insert into s(sno) select distinct sno from spj;b) 生成一张新表create table 女生as select * from student where sex=F;c) 复制表结构create table 女生as select * from student where ;d) 删除表内容delete from s where 3.4 实验四 PL/SQL编程(一)一 脚本的编写及执行 在记事本

27、中编写并以*.sql 保存 运行：路径*.sql;二 PL/SQL的来源及实质PL/SQL是Oracle的过程化编程语言，用户可以使用它编写用来在数据库中执行的定制程序以及过程代码。PL/SQL程序能够存储在数据库中，用于用户应用程序和日常操作。这可以让用户在编程语言中实现与用户数据密切相关的业务逻辑。三 PL/SQL的块结构 PL/SQL代码使用了程序块（block），利用模块化方式进行构建。每个程序块都是一组逻辑上的变量、可执行代码以及错误控制代码。例：SQLset serveroutput onSQLdeclare L_number number:=1; Begin L_number:=

28、1+1; Dbms_output.put_line(1+1|to_char(L_number)|!); Exception When others then Dbms_output.put_line(We encountered an exception!); End; / 块嵌套：程序块可以在可执行部分和异常处理部分包含另外的程序块。例：declarel_text varchar2(20);begin l_text:=First Block; Dbms_output.put_line(l_text); declarel_more_text varchar2(20);begin l_more_

29、text:=Second Block; Dbms_output.put_line(l_more_text); End;End;/四 声明 变量和常量的声明i. 变量的声明declare l_number_variable number;begin l_number_variable:=50;end;以上方式等价于：declare l_number_variable number:=50;begin null;end;ii. 常量的声明declare l_number_constant constant number:=50;begin null;end; 变量的作用域：在嵌套程序块中，父块本身

30、和任何嵌套块都是在父块中定义的标识符的作用域，而在子块声明中定义的标识符只有在子块本身中才处于它的作用域 使用%TYPE和%ROWTYPE %TYPE：声明类似字段的单独的变量，如declare l_status s.status%type;定义了象s表的status那样的字段变量。 %ROWTYPE：声明表示表、视图或游标的完整行的记录变量,如declare l_s s%rowtype; 定义了象表s那样的记录类型。 好处： （1）用户不需在声明的时候知道数据类型（2）如果用户正在引用的变量的数据类型发生变化，用户的%TYPE或者%ROWTYPE也会在运行时进行改变，而用户不必重写用户的变量

31、声明。五 PL/SQL集合在大多数编程语言中，提供这样或者那样的方式来声明对象集合都很有必要。PL/SQL中也是如此，它具有可以用于这一目的的大量集合类型。它们是：l 记录：在称为记录的单独集合中可以存储一对多的标量属性l PL/SQL表：是可以在用户的PL/SQL代码中使用的“表”，只存在用户应用运行期间，非常类似于其他语言中的数组，但不能够存储在数据库表中。l VARRAY：能够在表列中存储的集合，通常用于存储小集合的固定大小的集合。l NESTED TABLE：另一种可以在表列中存储的集合，大小可变，适用于用户不知道集合大小，或者用户知道它要包含大量数据的时候。e) 记录例：SQLset

32、 serverout onSQLdeclare Type location_record_type is record( Streent_address varchar2(40), Postal_code varchar2(12), City varchar2(30), State_province varchar2(25), Country_id char(2) not null :=US ); l_my_loc location_record_type; begin l_my_loc.street_address:=1 Oracle Way; l_my_loc.postal_code:=2

33、0190; l_my_loc.city:=Reston; l_my_loc.state_province:=VA; dbms_output.put_line(MY LOCATION IS:); dbms_output.put_line(l_my_loc.street_address); dbms_output.put_line(l_my_loc.city|,|l_my_loc.state_province); dbms_output.put_line( |l_my_loc.postal_code); dbms_output.put_line(l_my_loc.country_id); end;

34、 /3.5 实验五 PL/SQL编程（二）一 PL/SQL表：有时也称为索引表，是用户可以在PL/SQL例程中使用，能够模仿数组的非永久表。用户可以定义一个PL/SQL表类型，然后声明这种类型的变量。接下来，用户就可以将记录增加到用户的PL/SQL表中，并且采用与引用数组元素大体相同的方式引用它们。1定义以及引用例1：declare type my_text_table_type is table of varchar2(200) index by binary_integer; l_text_table my_text_table_type;begin l_text_table(1):=So

35、me varchar2 value; l_text_table(3):=Another varchar2 value; dbms_output.put_line(We have |l_text_table.count| varchar2s); dbms_output.put_line(-); dbms_output.put_line(vc2(1)=|l_text_table(1); dbms_output.put_line(vc2(2)=|l_text_table(3);end;/注意：索引算子是变量名称后面的括号中的数值。它会通知PL/SQL解析器，我们正在谈论由这个值唯一标识的记录，它必须

36、是合法的Binary_integer，但不一定是连续整数，这一点不同于数组下标。2PL/SQL中的记录，具体要用到以下几个方法：i. l_text_tables.delete(i)：删除索引算子I所标识的表项。ii. l_text_tables.delete：删除所有表项。iii. l_text_tables:=l_empty_table：把Null表赋给l_text_table，等价于.delete例2：declare type my_text_table_type is table of varchar2(200) index by binary_integer; l_text_table

37、 my_text_table_type; l_empty_table my_text_table_type;begin l_text_table(10):=A value; l_text_table(20):=Another value; l_text_table(30):=Yet another value; dbms_output.put_line(We start with |l_text_table.count| varchar2s); dbms_output.put_line(-); l_text_table.delete(20); dbms_output.put_line(Afte

38、r using the DELETE operator on the second record,); dbms_output.put_line(we have |l_text_table.count| varchar2s); dbms_output.put_line(-); l_text_table.delete; dbms_output.put_line(After using the DELETE operator,); dbms_output.put_line(we have |l_text_table.count| varchar2s); dbms_output.put_line(-); l_text_table(15):=Some text; l_text_table(25):=Some more text; dbms_output.put_line(After some assignments,); dbms_output.put_line(we end up with |l_text_table.count| varchar2s);