大型数据库oracle实验报告.docVIP

实 验 报 告课程名称： 大型数据库系统 实验项目： 数据库实验 实验地点： 逸夫楼502 专业班级： 软件12xx 学号： 201200xxxx 学生姓名： xx 指导教师： 宋成明 2015年 4月 11日实验1创建数据库和表及表数据的插入、修改和删除1、 实验目的和要求1.学会使用SQL语句手工出创建数据库，创建表；2.学会使用PL/SQL语句对数据库表进行插入、修改和删除数据的操作；3.学会使用SQL DEVELOPER对数据库表进行插入、修改和删除数据的操作；4.了解数据更新操作时要注意数据完整性；5.了解PL/SQL语句对表数据操作的灵活控制功能。2、 实验内容和原理 分别使用SQL Developer和PL/SQL语句，在实验二建立的数据库YGGL或实验一随安装Oracle 11g时建立的数据库中所建立的三个表Employees, Departments和Salary中分别插入多行数据记录，然后修改和删除一些记录。使用PL/SQL进行有限制的修改和删除。表的内容如教材p311页所示。使用PL/SQL命令操作数据。内容如教材p312页所要求，给出相应语句段及结果截图。3、 主要仪器设备系统运行的软硬件环境 1.HP 6470b笔记本 win7 32位系统 2.Oracle 11g大型数据库及其相应的配套软件4、 实验结果与分析1.在scott用户中创建表（1）Employees(员工信息表)表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(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(000001,王林,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,王向荣,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,刘明,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,钟敏,to_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(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(19781118,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,财务部,null);insert into Departments values(2,人力资源部,null);insert into Departments values(3,经理办公室,null);insert into Departments values(4,研发部,null);insert into Departments values(5,市场部,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,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);insert 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.在表中删除数据例如:删除表Salary中一行记录delete from Salary where EmployeeID = 000001;查看删除后的数据五、讨论、心得 通过本次实验，对oracle的PL/SQL数据库建立语句、建表语句以及数据操作语句DML有了更加熟练的掌握和操作。实验2数据库的查询、视图、索引及完整性1、 实验目的和要求1.掌握SELECT语句的基本语法；2.掌握子查询的表示方法；掌握连接查询的表示方法；3.掌握数据汇总的方法；掌握SELECT语句的GROUP BY子句的作用和使用方法；4.掌握SELECT语句的ORDER BY子句的作用和使用方法；5.掌握视图的使用方法。 6.掌握索引的使用方法。 7.掌握数据完整性的概念及分类。8.掌握各种数据完整性的实现方法。2、 实验内容和原理1、SELECT语句的基本使用 按照教材p313-p314的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。2、子查询的使用 按照教材p314-p315的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。3、连接查询的使用 按照教材p315的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。4、数据汇总 按照教材p315-p316的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。5、GROUP BY 和ORDER BY子句的使用 按照教材p316-p317的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。6、使用视图 按照教材p317的内容和步骤完成实验内容。给出相应语句段和结果的对应截图。 教材中的思考与练习部分可略去。3、 主要仪器设备系统运行的软硬件环境 1.HP 6470b笔记本 win7 32位系统 2.Oracle 11g大型数据库及其相应的配套软件四、实验结果与分析1、select语句的基本使用(1)查询Employees表select * from Employees;(2)查询每个雇员的的地址和电话select Address,PhoneNumber from Employees;(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 DepartmentID from Employees where Name like 王%;(7)找出所有收入在2000元到3000元之间的雇员号码select EmployeeID from Salary where InCome between 2000 and 3000;2、子查询使用查找在财务部工作的雇员情况select * from Employees where DepartmentID = (select DepartmentID from Departments where DepartmentName = 财务部);3、连接子查询的使用查询每个雇员的情况以及薪水情况select *from Employees e join Salary s on(e.EmployeeID = s.EmployeeID);4、数据汇总求财务部雇员的平均收入select avg(InCome) 财务部平均工资 from Salarywhere EmployeeID in( select EmployeeID from Employees where DepartmentID = ( select DepartmentID from Departments where DepartmentName = 财务部 );5、group by和order by子句的使用(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 as select EmployeeID,Name,Birthday,Sex,DepartmentID 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 9 );（2）实现实体完整性为Departments表的DepartmentName列建立唯一性索引alter table Departmentsadd(constraint UN_Departments unique(DepartmentName);（3）实现参照完整性为Employees表中的DepartmentID列建立外键alter table Employees add(constraint FK_Employees FOREIGN KEY(DepartmentID) references Departments(DepartmentID) );5、 讨论、心得 通过本次实验，对数据库的基本操作更加熟练，对其完整性约束的机理也有了更深入的理解。实验3 PL/SQL编程及存储过程和触发器的使用1、 实验目的和要求1. 进一步巩固第二章到第四章所学内容；2. 掌握变量的分类极其使用；3. 掌握各种运算符的使用；4. 掌握各种控制语句的使用；5. 掌握系统函数及用户自定义函数的使用；6. 掌握存储过程的使用；7.掌握触发器的使用；二、实验内容和原理1、条件结构、循环结构及选择和跳转语句的使用按照教材p319的要求在SQL*Plus窗口输入代码。数据库可用YGGL或实验一中创建的数据库。给出相应语句段和结果的对应截图。2、 自定义函数的使用按照教材p319-p320的要求在SQL*Plus窗口输入代码。数据库可用YGGL或实验一中创建的数据库。给出相应语句段和结果的对应截图。3、创建触发器 按照教材p321-p322的要求创建触发器。数据库可用YGGL或实验一中创建的数据库。给出相应语句段和结果的对应截图。 4、创建存储过程 按照教材p322-p323的要求创建存储过程。数据库可用YGGL或实验一中创建的数据库。给出相应语句段和结果的对应截图。 5、调用存储过程 按照教材p323的要求调用存储过程。数据库可用YGGL或实验一中创建的数据库。给出相应语句段和结果的对应截图。3、 主要仪器设备系统运行的软硬件环境 1.HP 6470b笔记本 win7 32位系统 2.Oracle 11g大型数据库及其相应的配套软件4、 实验结果与分析1、 自定义函数的使用（1）定义一个函数实现如下功能。对于一个给定的DepartmentID值，查询该值在departments表中是否存在，若存在返回0，否则返回-1。 create or replace function check_id(department in char) return numberas num number; x number;begin select count(*)into x from departments where DepartmentID=departmentid; if x0 then num:=0; else num:=-1; end if; return(num);end;（2） 写一段PL/SQL脚本程序调用上述函数。当向employees表插入一条记录时，首先调用函数CHECK_ID检索该记录的departmentid值在表departments的departmentid字段中是否存在对应的值，若存在，则该记录插入employees表。declare num number;begin num:=check_id(2); if num=0 then insert into employees values(990210,张文,to_date(19820324,YYYYMMDD),0,镇江路,210009,3497534,2); end if;end;“张文”记录插入成功，如下：2、 触发器的使用（1）创建触发器向Employees表中插入或修改一条记录时，通过触发器检查记录的值在Departments表是否存在，若不存在，则取消插入或修改操作。CREATE OR REPLACE TRIGGER EmployeesInsAFTER INSERT OR UPDATE ON Employees FOR EACH ROWDECLARE num number;BEGIN SELECT COUNT(*) INTO numFROM Departments WHERE DepartmentID=:NEW.DepartmentID;IF num=0 THEN DBMS_OUTPUT.PUT_LINE(职员所在部门不存在);END IF;END;(2)修改Departments表的DepartmentID字段值时，该字段在Employees表中的对应值也做相应的修改。CREATE OR REPLACE TRIGGER DepartmentsUpdate AFTER UPDATE ON Departments FOR EACH ROWBEGIN IF UPDATING THEN UPDATE Employees SET DepartmentID=:NEW.DepartmentID WHERE DepartmentID=:OLD.DepartmentID; END IF;END;（3）删除Departments表中一条记录的同时，也删除该记录DepartmentID字段值在Employees表中对应的记录CREATE OR REPLACE TRIGGER DepartmentsDelete AFTER DELETE ON Departments FOR EACH ROWBEGIN DELETE FROM Employees WHERE 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)ASBEGIN INSERT INTO Employees VALUES(employeeid,name,birthday,sex,address,zip,phonenumber,departmentI