大型数据库oracle实验报告

1、实验报告课程名称：大型数据库系统实验项目：数据库实验实验地点：逸夫楼 502专业班级：软件 12xx学号：201200xxxx学生姓名：xx指导教师：宋成明2015年4月 11日实验 1 创建数据库和表及表数据的插入、修改和删除一、实验目的和要求1.学会使用SQL 语句手工出创建数据库，创建表；2.学会使用PL/SQL 语句对数据库表进行插入、修改和删除数据的操作；3.学会使用SQL DEVELOPER对数据库表进行插入、修改和删除数据的操作；4.了解数据更新操作时要注意数据完整性；5.了解 PL/SQL 语句对表数据操作的灵活控制功能。二、实验内容和原理分别使用 SQL Developer

2、和 PL/SQL 语句，在实验二建立的数据库YGGL或实验一随安装Oracle 11g时建立的数据库中所建立的三个表Employees, Departments和 Salary中分别插入多行数据记录，然后修改和删除一些记录。使用PL/SQL 进行有限制的修改和删除。表的内容如教材p311 页所示。使用PL/SQL 命令操作数据。内容如教材p312 页所要求， 给出相应语句段及结果 截图。三、主要仪器设备系统运行的软硬件环境1.HP6470b 笔记本win7 32 位系统2.Oracle 11g大型数据库及其相应的配套软件四、实验结果与分析1.在 scott 用户中创建表（ 1） Employe

3、es(员工信息表 )表create table Employees(EmployeeID char(6) primary key,Name char(10) not null,Birthday date not null,Sex number(1) not null ，Address char(20),Zip char(6),PhoneNumber char(12),DepartmentID char(3) not null);查看创建后的 Employees 表（ 2） Departments(部门信息 )表create table Departments(DepartmentID char

4、(3) primary key,DepartmentName char(20) not null,Note varchar2(100);查看创建后的 Departments 表（ 3） Salary(员工薪水情况 )表create table Salary(EmployeeId char(6) primary key,InCome number(8,2) not null,OutCome number(8,2) not null);查看创建后的 Salary 表2.向表中插入数据（ 1）在 Employees表中插入数据insert into employees values('000

5、001','王林 ',to_date('19960123','YYYYMMDD'),1,' 中山路 32',210003,83355668,2); insert into employees values('010008','吴荣华 ',to_date('19760123','YYYYMMDD'),1,' 北京东路 ',210001,83321321,1); insert into employees values('020010

6、9;,'王向荣 ',to_date('19821209','YYYYMMDD'),1,' 四牌楼 ',210006,83792361,1); insert into employees values('020018','李丽 ',to_date('19600730','YYYYMMDD'),0,' 中山东路 ',210002,83413301,1); insert into employees values('102201','

7、刘明 ',to_date('19721018','YYYYMMDD'),1,' 虎踞路 ',210013,83606608,5); insert into employees values('102208','朱俊 ',to_date('19650928','YYYYMMDD'),1,' 牌楼巷 ',210004,84708817,5); insert into employees values('108991','钟敏 ',t

8、o_date('19790810','YYYYMMDD'),0,' 中山路 10',210003,83346722,3); insert into employees values('111006','张石兵 ',to_date('19741001','YYYYMMDD'),1,' 解放路 ',210010,84563418,5); insert into employees values('210678','林涛 ',to_date(

9、'19770402','YYYYMMDD'),1,' 中山北路 ',210008,83467336,3); insert into employees values('302566','李玉珉 ',to_date('19680920','YYYYMMDD'),1,' 热河路 ',210001,58765991,4); insert into employees values('308759','叶凡 ',to_date('197

10、81118','YYYYMMDD'),1,' 北京西路 ',210002,83308901,4); insert into employees values('504209','陈林琳 ',to_date('19690903','YYYYMMDD'),1,' 汉中路 ',210018,84468158,4);查看插入数据后的Employees表（ 2）在 Departments表中插入数据insert into Departments values('1',&

11、#39; 财务部 ','null');insert into Departments values('2',' 人力资源部 ','null');insert into Departments values('3',' 经理办公室 ','null');insert into Departments values('4',' 研发部 ','null');insert into Departments values('5

12、9;,' 市场部 ','null');查看插入数据后的Departments表（ 3）在 Salary 表中插入数据insert into Salary values('000001',2100.8,123.09);insert into Salary values('010008',1582.62,88.03);insert into Salary values('102201',2569.88,185.65);insert into Salary values('111006',1987.01,

13、79.58);insert into Salary values('504209',2066.15,108.0);insert into Salary values('302566',2980.7,210.2);insert into Salary values('108991',3259.98,281.52);insert into Salary values('020010',2860.0,198.0);insert into Salary values('020018',2347.68,180.0);inse

14、rt into Salary values('308759',2531.98,199.08);insert into Salary values('210678',2240.0,121.0);insert into Salary values('102208',1980.0,100.0);查看插入数据后的Salary 表3.在表中修改数据例如 :修改表 Salary 中某个记录的字段值update Salary set INCOME = 2890.5 where EmployeeID = '000001'查看修改后的数据4.在表中

15、删除数据例如 :删除表 Salary 中一行记录delete from Salary where EmployeeID ='000001'查看删除后的数据五、讨论、心得通过本次实验，对 oracle 的 PL/SQL 数据库建立语句、建表语句以及数据操作语句 DML 有了更加熟练的掌握和操作。实验 2 数据库的查询、视图、索引及完整性一、实验目的和要求1.掌握 SELECT 语句的基本语法；2.掌握子查询的表示方法；掌握连接查询的表示方法；3.掌握数据汇总的方法；掌握SELECT 语句的 GROUP BY 子句的作用和使用方法；4.掌握 SELECT 语句的 ORDER BY

16、子句的作用和使用方法；5.掌握视图的使用方法。6.掌握索引的使用方法。7.掌握数据完整性的概念及分类。8.掌握各种数据完整性的实现方法。二、实验内容和原理1、 SELECT语句的基本使用按照教材p313-p314 的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。2、子查询的使用按照教材p314-p315 的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。3、连接查询的使用按照教材p315 的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。4、数据汇总按照教材p315-p316 的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。5、 GROUP BY和 ORDE

17、R BY子句的使用按照教材p316-p317 的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。6、使用视图按照教材p317 的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。教材中的思考与练习部分可略去。三、主要仪器设备系统运行的软硬件环境1.HP6470b 笔记本win7 32 位系统2.Oracle 11g大型数据库及其相应的配套软件四、实验结果与分析1、select 语句的基本使用(1)查询 Employees表select * from Employees;(2)查询每个雇员的的地址和电话select Address,PhoneNumber from Employee

18、s;(3)查询 EmployeeID 为 000001 的雇员地址和电话select Address,PhoneNumber from Employees where EmployeeID ='000001'(4)查询 Employees表中所有女雇员的地址和电话select Address 地址 ,PhoneNumber 电话 from Employees where sex = 0;(5)计算每个雇员的实际收入select EmployeeID,InCome-OutCome 实际收入from Salary;(6)找出所有姓王的雇员的部门号select DepartmentI

19、D from Employees where Name like '王%'(7)找出所有收入在2000 元到 3000 元之间的雇员号码select EmployeeID from Salary where InCome between 2000 and 3000;2、子查询使用查找在财务部工作的雇员情况select * from Employeeswhere DepartmentID = (select DepartmentID from Departments where DepartmentName = 财'务部 ');3、连接子查询的使用查询每个雇员的情

20、况以及薪水情况select *from Employees e join Salary s on(e.EmployeeID = s.EmployeeID);4、数据汇总求财务部雇员的平均收入select avg(InCome) 财务部平均工资from Salarywhere EmployeeID in(select EmployeeID from Employeeswhere DepartmentID =(select DepartmentID from Departmentswhere DepartmentName = 财'务部 ');5、group by 和 order b

21、y 子句的使用(1) 求各部门的雇员数select count(DepartmentID) from Employees group by DepartmentID;(2) 将各雇员的情况按收入由低到高排序select * from Employees e join Salary s on(e.EmployeeID = s.EmployeeID)order by InCome asc;6、使用视图(1) 创建视图限制查看雇员的某些情况create or replace view cx_employees asselect EmployeeID,Name,Birthday,Sex,Departm

22、entID from Employees;查看创建好的视图(2) 使用视图查询财务部雇员信息select * from cx_employees;7、建立索引对数据库中 Employees表的 DepartmentID 列建立索引 create index PK_XS_BAK on Employees(DepartmentID);（ 1）实现域完整性为 Employees表的 PhoneNumber列建立 check 约束 .alter table Employeesadd(constraint CH_PHONE CHECK(PhoneNumber between'0' and

23、 '9' );（ 2）实现实体完整性为 Departments表的 DepartmentName列建立唯一性索引alter table Departmentsadd(constraint UN_Departments unique(DepartmentName);（ 3）实现参照完整性为 Employees表中的 DepartmentID 列建立外键alter table Employeesadd(constraint FK_Employees FOREIGN KEY(DepartmentID)references Departments(DepartmentID) );五、讨

24、论、心得通过本次实验， 对数据库的基本操作更加熟练， 对其完整性约束的机理也有了更深入的理解。实验 3 PL/SQL 编程及存储过程和触发器的使用一、实验目的和要求1.进一步巩固第二章到第四章所学内容；2.掌握变量的分类极其使用；3.掌握各种运算符的使用；4.掌握各种控制语句的使用；5.掌握系统函数及用户自定义函数的使用；6.掌握存储过程的使用；7.掌握触发器的使用；二、实验内容和原理1、条件结构、循环结构及选择和跳转语句的使用按照教材 p319 的要求在 SQL*Plus 窗口输入代码。数据库可用YGGL 或实验一中创建的数据库。给出相应语句段和结果的对应截图。2、自定义函数的使用YGGL

25、或实验一按照教材 p319-p320 的要求在 SQL*Plus 窗口输入代码。数据库可用中创建的数据库。给出相应语句段和结果的对应截图。3、创建触发器按照教材 p321-p322 的要求创建触发器。 数据库可用YGGL 或实验一中创建的数据库。给出相应语句段和结果的对应截图。4 、创建存储过程按照教材 p322-p323 的要求创建存储过程。 数据库可用 YGGL 或实验一中创建的数据库。给出相应语句段和结果的对应截图。5 、调用存储过程按照教材 p323 的要求调用存储过程。数据库可用YGGL 或实验一中创建的数据库。给出相应语句段和结果的对应截图。三、主要仪器设备系统运行的软硬件环境1.

26、HP6470b 笔记本win7 32 位系统2.Oracle 11g大型数据库及其相应的配套软件四、实验结果与分析1、自定义函数的使用（1）定义一个函数实现如下功能。对于一个给定的 DepartmentID 值，查询该值在 departments 表中是否存在，若存在返回 0，否则返回 -1。create or replace function check_id(department in char)return numberasnum number;x number;beginselect count(*)into x from departmentswhere DepartmentID=d

27、epartmentid; if x>0 thennum:=0;elsenum:=-1; end if; return(num);end;（2）写一段PL/SQL 脚本程序调用上述函数。当向employees 表插入一条记录时，首先调用函数 CHECK_ID检索该记录的departmentid 值在表 departments 的 departmentid 字段中是否存在对应的值，若存在，则该记录插入employees 表。declarenum number;beginnum:=check_id('2');if num=0 theninsert into employees

28、values('990210','张文 ',to_date('19820324','YYYYMMDD'),0,' 镇江路 ','210009','3497534',2);end if;end;“张文”记录插入成功，如下：2、触发器的使用（1）创建触发器向 Employees 表中插入或修改一条记录时，通过触发器检查记录的值在Departments 表是否存在，若不存在，则取消插入或修改操作。CREATE OR REPLACE TRIGGER EmployeesInsAFTER INS

29、ERT OR UPDATE ON Employees FOR EACH ROW DECLAREnum number;BEGINSELECT COUNT(*) INTO numFROM DepartmentsWHERE DepartmentID=:NEW.DepartmentID;IF num=0 THENDBMS_OUTPUT.PUT_LINE(' 职员所在部门不存在');END IF;END;(2) 修改 Departments 表的 DepartmentID字段值时，该字段在Employees 表中的对应值也做相应的修改。CREATE OR REPLACE TRIGGER

30、DepartmentsUpdateAFTER UPDATE ON Departments FOR EACH ROWBEGINIF UPDATING THENUPDATE EmployeesSET DepartmentID=:NEW.DepartmentIDWHERE DepartmentID=:OLD.DepartmentID;END IF;END;（ 3）删除 Departments 表中一条记录的同时， 也删除该记录 DepartmentID 字段值在 Employees表中对应的记录CREATE OR REPLACE TRIGGER DepartmentsDeleteAFTER DELE

31、TE ON Departments FOR EACH ROWBEGINDELETE FROM EmployeesWHERE DepartmentID=:OLD.DepartmentID;END;3、存储过程（ 1）添加职员记录的存储过 EmployeeADD 。CREATE OR REPLACE PROCEDURE EmployeeAdd(employeeid IN char,name IN char,birthday IN date,sex IN number,address IN char,zip IN char,phonenumber IN char,departmentID IN char)ASBEGININSERT INTO Employees V ALUES(employeeid,name,birthday,sex,address,zip,phonenumber,departmentID);