科 技 学 院
课程设计(综合实验)报告
( 2011 -- 2012 年度第 2 学期)
名 称： Oracle11g数据库应用 题 目： 学生选课数据库的设计与管理 院 系： 信息工程系 班 级： 软件09K1 学 号： 091909020125 学生姓名： 王 鑫 指导教师： 黄建才 成 绩：
日 期： 2012 年 6 月 1 日
一、实验目的与要求
1. 熟悉ORACLE的环境。
2. 用SQL PLUS创建用户，并赋予其管理员权限。 3. 登陆创建的用户。
4. 设计教师表、课程表、学生表，及其建立各个表之间的约束。 5. 用数据定义语言建立三个表及其约束。
6. 运用SQL，对上述三个表插入一定数量的记录（至少每个表要求插入10条记录）。
7. 用SQL语句查询至少教两门课的教师信息；用临时变量查询某个学生信息。
8. 用 PL/SQL，显示所有教师的信息。
9. 将上面的所有的插入操作和查询操作按照不同的内容放到不同的文件中，当到某个操作时，在SQL PLUS下运行该文件即可。 10. 分别设计函数和过程均实现如下功能：返回某门课学生的平均成
绩（该课名称由参数传递）。
11. 设计过程显示某位教师所教的学生（该教师名通过参数进行传
递）。
二、实验内容
1. 熟悉ORACLE的环境，并登陆创建用户
connect system/manager; /*连接数据库，机房的用这个*/
1) 创建表空间
create tablespace WANGXIN
datafile 'D:\\WANGXIN.dbf' size 20M
autoextend on next 5M maxsize 100M;
2) 创建WANGXIN空间下的用户，并赋予管理员权限 create user wangxin
identified by wangxin25 default tablespace WANGXIN quota 20M on users; grant resource,connect, dba to wangxin; commit; /*提交*/ 3) 登陆创建用户
connect wangxin/wangxin25;
1 / 14
2. 在表空间WANGXIN中根据其约束，创建学生表、教师表、课程表及辅表学生-成绩表 1) 创建教师表
create table teacher(
tno number(4) primary key, tname varchar2(10) not null,
tage int check(tage between 24 and 65), tsex varchar2(2) check(tsex in('男','女')), tdept varchar2(20) not null ); 2) 创建课程表
create table course(
cno number(6) primary key, cname varchar2(30) not null,
tno number(4) references teacher(tno) );
3) 创建学生表
create table student(
sno varchar2(12) primary key, sname varchar2(16) not null,
sage int check(sage between 15 and 36), ssex varchar2(2) check(ssex in('男','女')), sdept varchar2(20) not null );
4) 创建学生-成绩表 create table sc(
sno varchar2(12) references student(sno), cno number(6) references course(cno),
grade number(3) check(grade between 0 and 100), tno number(4) references teacher(tno), primary key(sno,cno) );
commit; /*提交*/ 1) 向教师表插入数据
/*依次为：教师编号，姓名，所在系，年龄，性别*/
insert into teacher values(1000,'李霸天',30,'男','体育系'); insert into teacher values(1001,'袁天罡',41,'男','天文系'); insert into teacher values(1002,'诸葛无敌',34,'男','信息系'); insert into teacher values(1003,'孙芳',29,'女','体育系'); insert into teacher values(1004,'高强',36,'男','经管系');
insert into teacher values(1005,'西门吹雪',40,'男','文学系'); insert into teacher values(1006,'欧阳无情',40,'女','数学系'); insert into teacher values(1007,'李飘渺',28,'男','信息系'); insert into teacher values(1009,'欧阳菲菲',37,'女','艺术系'); insert into teacher values(1008,'石雨晴',39,'女','信息系'); insert into teacher values(1012,'周星星',38,'男','影艺系'); insert into teacher values(1011,'百事通',50,'男','文学系');
3. 用SQL向以上表中插入数据
2 / 14
insert into teacher values(1010,'唐三奘',54,'男','文学系');
2) 向课程表插入数据
/*依次为：课程号，课程名，授课教师编号*/
insert into course values(10000,'C程序设计',1002); insert into course values(10004,'JAVA程序设计',1007); insert into course values(13012,'武术',1000); insert into course values(10213,'网络安全',1008); insert into course values(11221,'文学鉴赏',1005); insert into course values(14201,'佛学史',1010); insert into course values(15258,'演员的素养',1012); insert into course values(15465,'音乐鉴赏',1010); insert into course values(10281,'数据结构',1008); insert into course values(18259,'电力负荷预测',1004); insert into course values(13251,'羽毛球',1003); insert into course values(10251,'数据库',1002);
insert into course values(10151,'计算机发展史',1008); insert into course values(17771,'天象',1001); insert into course values(13551,'现代舞蹈',1009); insert into course values(15011,'野史简介',1011); insert into course values(13333,'军事训练',1001);
3 / 14
insert into course values(15133,'高等数学',1006);
insert into course values(15333,'概率论与数理统计统计',1006);
3) 向学生表插入数据
/*依次为：学号，姓名，所在系，年龄，性别*/
insert into student values('091909020125','王鑫',24,'男','信息系'); insert into student values('091909010109','李丽',21,'女','信息系'); insert into student values('091909040118','朱烨',22,'男','经管系'); insert into student values('091909050213','黄依依',22,'女','文学系'); insert into student values('091909070105','袁綝',23,'男','天文系'); insert into student values('091909040217','欧阳峰',20,'男','信息系'); insert into student values('081909090311','何青',24,'女','影艺系'); insert into student values('091909090211','李湘',24,'女','影艺系'); insert into student values('091909020231','曲洋',22,'男','信息系'); insert into student values('081909060225','高力',24,'男','体育系'); insert into student values('101909050209','赵茜',19,'女','文学系'); insert into student values('091909080125','梁熙',22,'女','艺术系'); insert into student values('091909080225','王鑫',23,'男','艺术系'); insert into student values('091909030202','高立',25,'男','数学系');
4 / 14
insert into student values('091909030112','朱鹏',24,'男','数学系');
4) 向学生-成绩表插入数据
/*依次为：学号，课程号，成绩，教师号*/
insert into sc values('091909030112',15133,45,1006); insert into sc values('091909030202',15133,55,1006); insert into sc values('091909020125',15333,88,1006); insert into sc values('091909020125',15133,75,1006); insert into sc values('091909080225',13551,85,1009); insert into sc values('091909080125',13551,95,1009); insert into sc values('091909080125',13251,90,1003); insert into sc values('091909020125',10000,85,1002); insert into sc values('091909020125',10004,75,1007); insert into sc values('091909040217',15133,95,1006); insert into sc values('091909040118',18259,100,1004); insert into sc values('091909070105',17771,99,1001); insert into sc values('091909070105',15133,100,1006); insert into sc values('091909070105',15333,100,1006); insert into sc values('091909090211',15258,95,1012);
5 / 14
insert into sc values('081909060225',13012,89,1000); insert into sc values('081909060225',13333,85,1001); insert into sc values('091909020125',13333,85,1001); insert into sc values('091909010109',13333,85,1001); insert into sc values('091909030112',10151,60,1008); insert into sc values('091909030112',10251,35,1002); insert into sc values('091909050213',15133,75,1006); insert into sc values('101909050209',13251,90,1003); insert into sc values('091909050213',13251,92,1003); insert into sc values('091909050213',13551,94,1009); insert into sc values('091909050213',11221,86,1005); insert into sc values('091909020125',11221,87,1005); insert into sc values('091909020125',15011,92,1011); insert into sc values('091909080125',15011,94,1011);
6 / 14
4. 使用SQL语句查询以下操作
1) 至少教两门课的教师信息查询
set linesize 200;/*格式控制*/
select tname ,cname,tdept,tage ,tsex,teacher.tno from teacher,course
where teacher.tno=course.tno and teacher.tno in(
select tno from course group by tno having count(*)>=2);
7 / 14
2) 用临时变量查询某个学生信息
select distinct* from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and student.sno=&sno;
/*输入'091909020125'或091909020125均可*/
8 / 14
5. 用 PL/SQL，显示所有教师的信息
declare
cursor t_cursor
is select *from teacher order by tno ;; begin
for wx in t_cursor loop
dbms_output.put_line('教师编号:'||wx.tno||', 姓名:'||wx.tname|| ', 性别:'||wx.tsex||', 年龄:'||wx.tage||', 系别:'||wx.tdept); end loop; end; /
6. SQL*PLUS 脚本命令
9 / 14
7. 设计函数和过程，查询学生平均成绩(总成绩，可选)
1) 设计过程
create or replace procedure mypro as
cursor mycur is
select cname,avg(grade) from sc,course where sc.cno=course.cno group by cname;
type template is record( tempname varchar2(30), tempavg number(5)); wx template; begin
open mycur; loop
fetch mycur into wx;
exit when mycur%notfound;
dbms_output.put_line('课程名：'||wx.tempname||',平均分数
10 / 14
为:'||to_char
(wx.tempavg,'FM999.00')); end loop; close mycur; end; /
如果课程名由参数传递则，只需修改select cname,avg(grade) from sc,course where sc.cno=course.cno and cname=&input_cname
调用过程语句
set serveroutput on; exec mypro;
实验结果：
2) 设计函数查询
create or replace function get_avg (ccname varchar2) return number is
11 / 14
o_avg number; begin
select avg(grade) into o_avg from sc,course where sc.cno=course.cno and cname=ccname; return o_avg; end get_avg; /
调用函数语句
select get_avg(‘课程名字’) from dual;
Waring：课程名务必和录入的数据一致
函数查询结果：
8. 设计过程显示某位教师所教的学生（该教师名通过参数进行传
递）
create or replace procedure mypro as
cursor mycur is
select tname,cname,sname from sc,course,teacher,student where sc.cno=course.cno
and teacher.tno=sc.tno and sc.sno=student.sno and teacher.tname=&t_name order by tname;
12 / 14
type template is record( temptname varchar2(12), tempcname varchar2(20), tempsname varchar2(12)); wx template; begin
open mycur; loop
fetch mycur into wx;
exit when mycur%notfound;
dbms_output.put_line('教师名：'||wx.temptname||' 学生名:'||wx.tempsname||' 课程名:'||
wx.tempcname); end loop; close mycur; end; /
13 / 14
四、实验总结
本次上机，大部分是在WIN XP +Oracle8i，也有一部分是WIN7 Ultimate 64bits+Oracle11g下做的，所以有些试验运行结果截图界面稍有差异。本次试验前认真完成练习课本上的基本操作，然后才开始本次试验内容。
Oracle 以SQL为基础，和其他语言一样，但是也有着独特的优势，自定义函数过程等比较实用。
SQL语言比较简单，但是能处理很多实际的问题，Oracle在其基础上加入了很多，使其更加适应现代需求。试验中，用基本SQL操作是比较简单的，但是当用到PL/SQL时遇到一些小问题，经过查阅资料已经解决。过程与函数的编写有些难度，经过本次试验学到了怎么运用简单的函数过程去解决问题，收获还是不小的，但是还相差较远，在以后的学习中一定努力的去学习并完善自己这方面的知识。
14 / 14
因篇幅问题不能全部显示，请点此查看更多更全内容