oracle数据库实训报告.doc

2010/2011学年第1学期oracle实训报告课程名称 ： oracle数据库实训 班 级 ： 软 件 111 班 姓 名： 浦 江 峰 学 号： 1132304112 教学周数 ： 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(200503100101,袁洁芳,女2005031001,湖南省,在籍,to_date(1982-05-18,yyyy-mm-dd),04);insert into scott.student values(200503100102,王华宝,男2005031002,江苏省,在籍,to_date(1993-05-18,yyyy-mm-dd),03);insert into scott.student values(200503100103,王浩,男2005031003,江苏省,在籍,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,软件技术,刘志成,8208290,3,03,2003); insert into scott.discipline values(0311,软件技术1,王浩,8208291,4,01,2004); insert into scott.discipline values(0312,软件技术2,王化宝,8208292,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,信息工程系,彭勇,2783857);insert into scott.section values(01,信息系,王化宝,2783856);insert into scott.section values(02,信息管理系,王浩,2783855);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(2005031001,软件051,03,0310);insert into scott.banji values(2005031002,软件111,01,0311);insert into scott.banji values(2005031003,软件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(200503100101,03107,87,0,0);insert into scott.xuesheng1 values(200503100102,03106,88,1,1);insert into scott.xuesheng1 values(200503100103,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，并执行该存储过程查询学号为“200503100101”的学生的成绩信息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=200503100103;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岁以上以及班级编号为“2005031001”的学生信息（使用联合查询）-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=2003031001;（四） 数据管理（1）将student数据库中的“学生信息表”的信息导出到stu.dmp