oracle数据库实训报告

2010/2011学年第1学期Oracle实训报告课程名称 ： Oracle数据库实训 班 级 ： 软 件 111 班 姓 名： 浦 江 峰 学 号： 教学周数 ： 17 周 地 点 ： B10-509教室 指导教师 ： 龙浩 一 实训对象1、实训班级：软件111班2、计划实训周期：一个星期3、计划受训人数： 37人4二 实训目的1.知识目标通过综合实训进一步巩固、深化和扩展大家的Oracle 11g数据库管理和开发的基本知识和技能。（1）熟练掌握Oracle 11g数据库的操作；（2）熟练掌握Oracle 11g表的操作；（3）熟练掌握Oracle 11g视图的操作和应用；（4）掌握Oracle 11g索引的操作；（5）熟练掌握PL/SQL变成技术和Oracle 11g储存过程的操作和使用；（6）熟练掌握Oracle 11g触发器的操作和应用；（7）掌握Oracle 11g数据安全性操作；（8）熟练掌握Oracle 11g数据管理操作；（9）了解Oracle 11g数据库程序开发技术。2.能力目标培养学生运用所学的知识和技能解决Oracle 11g数据库管理和开发过程中所遇到的实际问题的能力、掌握基本的SQL脚本编写规范、养成良好的数据库操作习惯。（1）培养学生通过各种媒体搜集资料、阅读资料和利用资料的能力；（2）培养学生基本的数据库应用能力；（3）培养学生基本的编程逻辑思想；（4）培养学生通过各种媒体进行自主学习的能力。三 实训内容StudentMis教务管理系统是用来实现学生学籍的管理、学生成绩的管理、课程的管理、学生选课管理等功能的信息系统。该系统采用Oracle 11g为关系型数据库管理系统，该系统主要满足来自3方面的需求，这3个方面分别是学生、教务管理人员和系统管理员。学生：l 注册入学；l 选择每学期学习课程；l 查询每学期课程考试成绩；教务管理人员l 管理学生学籍信息异动；l 管理学生选课信息；l 管理每学期课程考试成绩；系统管理员l 管理系统用户；l 管理课程；l 管理部门；l 管理专业；l 管理班级；作为一个数据库管理员或数据库程序开发人员，需要从以下几个方面完成数据库的管理操作。（一） 数据库对象的管理1. 数据库数据库实例名称：Student.2. 表创建Student数据库中的所有表。添加样本数据到所创建的表中。（1） 学生信息表create table scott.Student-学生信息表(x_id char(12) NOT NULL PRIMARY KEY,x_name varchar2(30) not null UNIQUE,x_sex char(2) check (x_sex=男 or x_sex=女),x_cardid varchar2(18) not null,b_id char(50) not null REFERENCES scott.banji(b_id),jiguan varchar2(10) not null,xueji varchar2(10) not null,x_brith date not null,m_id char(6) not null);-插入三条记录insert into scott.STUDENT values(1,袁洁芳,女,湖南省,在籍,to_date(1982-05-18,yyyy-mm-dd),04);insert into scott.STUDENT values(2,王华宝,男,江苏省,在籍,to_date(1993-05-18,yyyy-mm-dd),03);insert into scott.STUDENT values(3,王浩,男,江苏省,在籍,to_date(1992-05-18,yyyy-mm-dd),02);commit;（2） 课程信息表create table scott.Class1-课程信息表(c_id char(6) NOT NULL PRIMARY KEY,c_name varchar2(30) UNIQUE not null,d_id char(5) not null REFERENCES scott.Discipline(d_id),xuefen char(2) not null,xueshi char(5) not null,c_style char(2) not null,bianhao char(5) not null);-插入三条记录insert into scott.Class1 values(03107,软件工程,0310,4,60,01,01);insert into scott.Class1 values(03106,软件工程1,0311,5,70,02,02)；insert into scott.Class1 values(03108,软件工程2,0312,6,80,03,03)；commit;（3） 专业信息表create table Discipline-专业信息表(d_id char(5) NOT NULL PRIMARY KEY,d_name varchar2(20) UNIQUE not null,d_fuzeren varchar(30) not null,phone varchar2(15) not null,xuezhi char(2) not null,s_id char(5) not null REFERENCES scott.Section(s_id),d_year char(10) not null);-插入三条记录insert into Scott.Discipline values(0310,软件技术,刘志成,3,03,2003); insert into Scott.Discipline values(0311,软件技术1,王浩,4,01,2004); insert into Scott.Discipline values(0312,软件技术2,王化宝,2,02,2005);（4）部门信息表create table scott.Section -部门表(s_id char(5) NOT NULL PRIMARY KEY,s_name varchar2(20) not null UNIQUE,s_fuzeren varchar2(30) not null,phone varchar2(15) not null);-插入三条记录insert into Scott.SECTION VALUES(03,信息工程系,彭勇,);insert into Scott.SECTION VALUES(01,信息系,王化宝,);insert into Scott.SECTION VALUES(02,信息管理系,王浩,);commit;（5） 班级信息表create table Banji-班级信息表(b_id char(50) NOT NULL PRIMARY KEY,b_name varchar2(15) UNIQUE not null,s_id char(5) not null REFERENCES scott.Section(s_id),d_id char(5) not null REFERENCES scott.Discipline(d_id);-插入三条记录insert into Scott.BANJI VALUES(,软件051,03,0310);insert into Scott.BANJI VALUES(,软件111,01,0311);insert into Scott.BANJI VALUES(,软件112,02,0312);commit;（6）学生成绩表create table XueSheng1-学生成绩表(x_id char(12) NOT NULL REFERENCES scott.Student(x_id),c_id char(6) NOT NULL REFERENCES scott.Class1(c_id) ,zhengkao char(5),bukao char(5),chongxiu char(5);-插入三条记录insert into scott.XUESHENG1 values(1,03107,87,0,0);insert into scott.XUESHENG1 values(2,03106,88,1,1);insert into scott.XUESHENG1 values(3,03108,89,2,2);（7）管理员信息表create table Admins-管理员信息表(a_id char(5) NOT NULL PRIMARY KEY,c_name varchar2(30) UNIQUE,mima varchar2(15),a_style varchar2(30),a_date date not null);-插入三条记录insert into Scott.ADMINS values(1,admin,123,系统管理员,to_date(2007-02-10,yyyy-mm-dd);insert into Scott.ADMINS values(2,A类用户,123,超级用户,to_date(2007-02-10,yyyy-mm-dd);insert into Scott.ADMINS values(3,B类用户,123,超级用户,to_date(2007-02-10,yyyy-mm-dd);commit;3. 视图（1） 创建指定部门的专业信息的视图vw_Majorcreate view scott.vw_MajorasSELECT d_name,d_fuzeren,Discipline.phone,s_name,s_fuzeren,Section.phone,d_year FROM scott.Discipline ,scott.Section WHERE scott.Discipline.s_id=scott.Section.s_idwith read only; (2)创建学生成绩的视图vw_Scorecreate view scott.vw_ScoreasSELECT scott.student.x_id,student.x_name,class1.c_id,class1.c_name,xuesheng1.zhengkao FROM SCOTT.studentJOIN SCOTT.xuesheng1 ON xuesheng1.x_id=student.x_id JOIN SCOTT.class1 ON xuesheng1.c_id=class1.c_idwith read only;4.索引-创建学生名称为关键字的唯一索引-create index scott.is_studenton scott.student(s_name);-创建课程名称为关键字的唯一索引-create UNIQUE INDEX scott.is_classon scott.class1(c_name);-创建专业名称为关键字的唯一索引-create UNIQUE INDEX scott.is_disciplineon scott.Discipline(d_name);-创建班级名称为关键字的唯一索引-create UNIQUE INDEX scott.is_banjion scott.banji(b_name);5.存储过程（1）-创建根据指定的学号查询学生所有课程成绩信息的存储过程up_MyScore，并执行该存储过程查询学号为“1”的学生的成绩信息CREATE OR REPLACEPROCEDURE UP_MYSCORE( st IN VARCHAR2,kid out xuesheng1.c_id%type,x_zhengkao out xuesheng1.zhengkao%type,x_bukao out xuesheng1.bukao%type,x_chongxiu out xuesheng1.chongxiu%type) ASBEGINselect c_id,zhengkao,bukao,chongxiu into kid,x_zhengkao,x_bukao,x_chongxiufrom scott.xuesheng1where x_id=st;END UP_MYSCORE;（2）-创建根据指定的管理员信息实现添加管理员的储存过程up_AddAdmin，并执行该储存过程，将管理员添加到管理员列表中create or replace procedure up_AddAdmin(aid in char,aname in varchar2,mima1 in varchar2,aleixing in varchar2,ayear in date)asbegininsert into scott.admins values(aid,aname,mima1,aleixing,ayear);end up_AddAdmin;-将一条管理员记录插入到管理员表中exec up_AddAdmin(8,A用户,888,超级用户,to_date(2007-10-10,yyyy-mm-dd);select * from scott.admins（3）-创建统计每门课程总成绩和平均成绩的存储过程，并将课程总成绩和平均成绩以输出参数形式输出-create or replaceprocedure up_xuesheng(kid in char)astotal number;total2 number;beginselect sum(zhengkao ) into total from scott.xuesheng1 where c_id=kid;select avg(zhengkao ) into total2 from scott.xuesheng1 where c_id=kid;dbms_output.put_line(total);dbms_output.put_line(total2);end;exec up_xuesheng(03107);6.触发器（1） -创建在删除“学生信息表”中的学生信息时，删除“学生成绩表”中该学生信息的触发器tr_DeleteStudent，并设置删除语句验证该触发器工作-create or replace trigger scott.tr_DeleteStudentafter deleteon scott.studentfor each rowbegindelete from scott.xuesheng1 where x_id=:old.x_id;dbms_output.put_line(成功删除学生信息及其成绩信息);end tr_DeleteStudent;-删除语句delete from scott.xuesheng1where x_id=3;commit;（2）-创建在修改部门信息表中的部门标号时，修改班级信息表和专业信息表的触发器tr_UpdateDeptNo，并设置修改语句验证该触发器的工作create or replacetrigger scott.tr_UpdateDeptNoafter updateon scott.section for each row begin update banjiset s_id=:new.s_idwhere s_id=:old.s_id;update disciplineset s_id=:new.s_idwhere s_id=:old.s_id;end tr_UpdateDeptNo;（二） 数据库安全策略（1）-创建用户MyLogin，并将该用户添加到CONNECT角色中create user MyLoginidentified by student;grant connect to MyLogin;（2） -查看用户MyLogin的基本信息-select * from all_userswhere username = MyLogin;（3） -创建数据库角色MyRole-create role MyRoleidentified by student;（4） -为角色MyRole添加系统权限-grant create session to MyRole;（5） -为角色MyRole添加Student数据库中表的相关权限-grant select on scott.STUDENT to MyRole;（查询权限）grant insert on scott.STUDENT to MyRole；（插入权限）grant update on scott.STUDENT to MyRole;（更新权限）grant delete on scott.STUDENT to MyRole;（删除权限）（6） -查看角色MyRole的基本信息-select * from session_roleswhere role = MyRole;（三） 数据查询（1） -查询学生信息表中的所有数据-select * from scott.STUDENT;(2)-查询部门编号“03”，专业负责人为“刘志成”的专业信息并显示汉字标题-select d_id 专业编号,d_name 专业名称,d_fuzeren 专业负责人,discipline.phone 联系电话,xuezhi 学制,d_year 开设年份,s_id 部门编号from scott.disciplinewhere d_fuzeren=刘志成and s_id=07;(3)-查询所有年龄在20岁以下的学生的名称、籍贯和年龄-select x_name 姓名,jiguan 籍贯,to_char(sysdate,yyyy)-to_char(x_brith,yyyy) nlfrom scott.STUDENTwhere to_char(sysdate,yyyy)-to_char(x_brith,yyyy)=(select to_char(sysdate,yyyy)-to_char(x_BRITH,yyyy)from scott.STUDENTwhere student.x_NAME=袁洁芳);（9）-查询每个班级男女学生的平均年龄，并将结果保存到“t_Age”表中-create table t_age(x_name varchar2(30),b_avg float);insert into t_age(x_name,b_avg)select x_name,avg(to_char(sysdate,yyyy)-to_char(x_BRITH,yyyy)from scott.STUDENT group by x_name;（10）-查询年龄到20岁以上以及班级编号为“”的学生信息（使用联合查询）-select x_id,x_name,x_sex,x_cardid,b_id,jiguan,xueji,to_char(sysdate,yyyy)-to_char(x_BRITH,yyyy) nl,m_idfrom scott.STUDENTwhere to_char(sysdate,yyyy)-to_char(x_BRITH,yyyy)=19unionselect x_id,x_name,x_sex,x_cardid,b_id,jiguan,xueji,to_char(sysdate,yyyy)-to_char(x_BRITH,yyyy) nl,m_idfrom scott.STUDENTwhere b_id=;（四） 数据管理（1）将Student数据库中的“学生信息表”的信息导出到stu.dmp文件中（2）删除Student数据库中的“学生