oracle实验报告四川师范大学.doc

《oracle实验报告四川师范大学.doc》由会员分享，可在线阅读，更多相关《oracle实验报告四川师范大学.doc（34页珍藏版）》请在三一办公上搜索。

1、四川师范大学计算机学院实 验 报 告 册院系名称： 计算机科学学院 课程名称： Oracle 实验学期 2014 年至 2015 年 第 一 学期专业班级： 网络工程3班 姓名： 学号： 指导教师： 俞晓 实验最终成绩： 实验一 了解ORACLE环境，使用ORACLE数据库实用工具 1.目的要求： 了解ORACLE数据库的各个常用工具软件 2.实验内容： 在ORACEL数据库下使用SQL*PLUS ，SQL*PLUS Worksheet，PL/SQL Developer工具，企业管理器等实用工具与Oracle交互。并在企业管理器中观察ORACLE的底层存储原理。在PL/SQL Develope

2、r中书写简单的SQL语言。 3.主要仪器设备及软件 1）PC 2）ORACLE数据库 PL/SQL Developer工具的运用:实验二 熟悉SQL语言1.目的要求 在SQL*PLUS或PL/SQL Developer工具中编写SQL语句 2.实验内容 ORACLE 数据库中定义用户，给用户赋权限，创建，修改和删除表格，视图等数据库对象，并向表格中插入，修改和删除数据。体会SQL语言中ORACLE的“方言”。 对自己建立的表做查询：包括单表查询，多表查询，嵌套查询，分组查询，相关查询 掌握SQL语句的书写方法熟练使用SQL语句实现建表，修改表，删除表，向表中插入，删除，修改，查询等操作。1.

3、创建用户 create user LWQ identified by 123;2. 给用户赋权限 -连接权限： grant connect to LWQ; -登录数据库： connect LWQ/123; -建表权： grant create table to LWQ; -若想将权限赋予所有用户，可以使用Public角色。如： grant select on sc to public;收回权限：Revoke create table from LWQ; 3. 创建表Create Table Student(sno char(10) primary key ,sname varchar(20)

4、not null,sage smallint,ssex char(2),sdept varchar(20); Create Table Course (cno char(10), primary key (cno) ,cname varchar(20) , cpno char(10),credit smallint ); Create Table SC (sno char(10),cno char(10),grade smallint,primary key (sno, cno) ); 4. 修改表-向已经存在的表中添加属性： alter table student add avg_grade

5、 number;-删除一列：alter table student drop cloumn avg_grade;-修改属性及相应数据： alter table student modify sage varchar(30);5. 删除表格drop table student;6. DML数据库的修改: 向表中插入，删除，修改，查询等操作-向表中插入数据：insert into student values(001,张四,20,男,CS);insert into student values(002,刘五,19,女,IS);-删除数据：Delete from student where sno

6、= 001;-修改数据：update student set age = age + 1 where sno = 002;update sc set grade = 90 where sno = 001 and cno = 1001;-查询：select * from sc; select cno from course where cname=数据库; select * from student where sname like 李%;-为结果集中的某个属性改名：select pno as 产品号,pname 名字,place 产地 from p;-列出jkx系中的女生的学号、姓名、身高，并

7、按身高进行排列（降序）： select sno,sname,heigh from student where sdept=jkx and ssex=女 order by heigh desc;-查询成绩小于60的10位同学的学号，课程号，成绩: select * from (select * from sc where grade60 order by grade desc) where rownum=10;7. 建视图-建立学生平均成绩视图:create view avg_grade(sno,avgs) as select sno,avg(grade） from sc group by sn

8、o-找出平均成绩小于89的学生select * from sc where avg(grade)=all(select sage from student);-2、求每一个学生的最高分和最低分。select sc.sno,sname,MAX(grade) Maxgrade,MIN(grade) Mingrade from sc,student where sc.sno=student.sno group by sc.sno,sname;-3、查询CS系所有男同学考C05课程的成绩，列出这些学生的学号，姓名，成绩，并按成绩降序排列。select student.sno,sname,grade f

9、rom sc,student where sc.sno=student.sno and sdept=CS and ssex=男 and cno=C05 order by grade desc;-4、检索选修了“数据库”课程的学生的姓名（可用子查询IN或Exists）select sname from student where exists (select * from sc where sno=student.sno and cno=( select cno from course where cname=数据库);select sname from student,sc,course wh

10、ere student.sno=sc.sno and o=o and cname=数据库-(方法二)select sname from student where sno in (select sno from sc where cno = ( select cno from course where cname=数据库);-(方法三)-5、检索选修了课程号为C01或C02课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。select sname,cname,grade from student,sc,course where student.sno=sc.sno and o=o and

11、 o in (C01,C02) and grade = 70;-6、检索所有学生的姓名、所选课程的课程名和成绩以及课程号，并且按成绩的降序和课程号的升序进行排列（使用外连接将没有选课的同学列出来）。select sname,cname,o,grade from sc,student,course where sc.sno=student.sno and o=o order by grade desc, o asc;select student.sname,ame,sc.grade,o from ( student left join sc on (student.sno=sc.sno) lef

12、t join course on (o=o) order by sc.grade desc,o asc;-7. 列出没有选课的学生姓名select sname from student where not exists (select * from sc where sno=student.sno);-8. 列出平均分最高的学生所在系的所有学生的姓名select sname from student where sdept=( select sdept from student where sno=( select sno from sc group by sno having AVG(gra

13、de)=all(select AVG(grade) from sc group by sno);select * from student where sdept=CM;-(检查)-9.查询CS系C05课程的成绩比C05课程的平均分高的学生学号select student.sno,cno,grade from student,sc where student.sno=sc.sno and grade(select AVG(grade) from sc where cno=C05 group by cno) and sdept=CS and cno=C05;select student.sno,

14、cno,grade from sc,student where sc.sno=student.sno and cno=C05 and sdept=CS;-(测试)-10.查询既选修了C01又选修了C02的学生select sname from student where not exists (select * from course where cno in (C01,C02) and not exists (select * from sc where cno=o and sno=student.sno);-11.统计及格的课程数在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名

15、次名单来。select sno,avg(grade) Agrade from sc where sno in ( select sno from sc where grade=60 group by sno having count(cno)4) group by sno order by avg(grade) desc;-12.检索所有CS系学生都选修了的课程（列出课程号） select cno from course wherenot exists (select * from student where sno in (select sno from student where sdep

16、t=CS) and not exists (select * from sc where sc.sno=student.sno and o=o);select sno,cno from sc where sno in (select sno from student where sdept =CS)-(检查)-13.查询年龄高于其所在系的平均年龄的学生姓名select sname,sdept from student x where sage( select Avg(sage) from student y where y.sdept=x.sdept);-14.查询每位同学的选课中成绩最高的课

17、程对应的学号，姓名，课程名，成绩select student.sno,Student.sname,ame,x.grade from Student,sc x,course where o=o and student.sno=x.sno and grade=( select max(grade) MAXgrade from sc y where y.sno=x.sno);-15.为MA系学生选修必修课C05 insert into sc(sno,cno) select sno,C05 from student where sdept = MA and not exists (select * f

18、rom sc where sno=student.sno and cno=C05);-16.将CS系,C01课程学生的成绩加10分update sc set grade=grade+10 where sno in (select sno from student where sdept=CS) and cno=C01;select * from sc where sno in (select sno from student where sdept=CS) and cno=C01-(检查)-17.将每位同学的最低分加10分（选）update sc set grade=grade+10 wher

19、e GRADE=(select min(grade) from sc x where sno=sc.sno)select sno,MIN(grade) from sc group by sno-(检查用)-18.将”数据库”的选课记录全部删除delete from sc where cno=(select cno from course where cname=数据库);实验三 实现简单的PL/SQL程序一.目的要求 编写简单的PL/SQL程序，熟悉PL/SQL编程环境 二.实验内容 在SQL*PLUS或PL/SQL Developer工具中编写PL/SQL的简单程序，熟悉PL/SQL的编程环

20、境和代码结构。实现与Oracle数据库交互，并捕获和处理常见系统异常和用户自定义异常。 1.熟悉PL/SQL结构PL/SQL块语句是由Declare或Begin开始，以End结束，在PL/SQL块中不能直接使用DDL。2.循环控制语句的运用（素数）/for循环declare i integer; j int; k int:=0;-标志Kbegin for i in 2.100 loop k:=0; for j in 2.i/2 loop if mod(i,j)=0 then k:=1;-将不是素数的标志K设为1 exit; end if; end loop; if k =0 then dbms

21、_output.put_line(i); end if; end loop;end;-while循环 declare i integer; j int; -标志K K初始为0 当k值为1的时候 ，不是素数；值为0，是素数 k int := 0; begin for i in 2 . 100 loop k := 0; j := 2; while j = i / 2 loop -进入while循环 求i除J的模，如果等于0则把K设为1，否则K=0 if mod(i, j) = 0 then k := 1; exit; end if; j := j + 1;-判断之后继续进入循环 end loop;

22、 - 判断K的值，如果为0，则输出I的值 if k = 0 then dbms_output.put_line(i); end if; end loop; end;捕获异常（预定义异常处理）declare x number;begin x :=y;exception when value_error then dbms_output.put_line(value error); end;3.自定义异常declare vsno varchar2(20);sno_code exception;begin vsno := x; if vsno not in(A,B,C)then raise sno_

23、code; end if;exception when sno_code then dbms_output.put_line(vsno error);end;实验四 在PL/SQL中使用游标一.目的要求 在PL/SQL中使用无参数的游标和带参数的游标处理结果集 二.实验内容 在PL/SQL程序中使用游标来处理结果集，分别使用fetch, while和For循环来遍历查询结果集中的每一条记录，并对这些记录进行判断和处理，将处理的结果格式化打印出来。使用带参数的游标来传递查询条件，使程序更加灵活实用。 实现下面功能1.种不同的循环来遍历游标查询结果集Declare -用loop循环遍历sc表 cu

24、rsor c1 is select sno,cno,grade from sc order by sno; v_sc sc%rowtype;begin dbms_output.put_line(rpad(学号,7)|rpad(课程号,6)|成绩); dbms_output.put_line(rpad(=,25,=); open c1; loop fetch c1 into v_sc; exit when c1%notfound; dbms_output.put_line(v_sc.sno|v_o|v_sc.grade); end loop; dbms_output.put_line(row c

25、ount:|c1%rowcount);close c1; end;declare -用while循环遍历sc表cursor v_sc is select * from sc; sc_rec sc%rowtype;begin open v_sc; dbms_output.put_line(学号：|rpad( ,7)|课程号：|rpad( ,6)|成绩：); fetch v_sc into sc_rec; while v_sc%found loop dbms_output.put_line(sc_rec.sno|sc_o|sc_rec.grade); fetch v_sc into sc_rec;

26、 end loop; close v_sc; end;declare cursor c1 is select sno,cno,grade from sc; -格式化输出sc表 v_sc sc%rowtype; v_sno varchar(9);begin dbms_output.put_line(rpad(学号,7)|rpad(课程号,11)|成绩); dbms_output.put_line(rpad(=,25,=); v_sno:=xxx; for i in c1 loop-用for循环遍历sc表 if v_sno i.sno then dbms_output.put_line(rpad(

27、-,25,-); dbms_output.put_line(i.sno|o|i.grade); v_sno:=i.sno; else dbms_output.put_line(rpad( ,9)|o|i.grade); end if; end loop;end;2. 使用游标实现：将任意一门（每门）课程成绩高于课程平均分的学生所选的 所有课程的姓名，课程名，成绩格式化输出。declare cursor stu_grade is select student.sno,sname,cname,grade,o from student,sc sc1,course where student.sno=

28、sc1.sno and o=o and student.sno in(select sno from sc where grade(select avg(grade) from sc where o=o ) order by student.sno ; cs stu_grade%rowtype; v_sno varchar2(20):=null; cavg int:=0;begin open stu_grade; dbms_output.put_line(rpad(*,20,*)|信息查询|rpad(*,20,*); loop fetch stu_grade into cs; exit whe

29、n stu_grade%notfound; if v_sno!=cs.sno then dbms_output.put_line(rpad(=,50,=); dbms_output.put_line(rpad( ,30, )|学号:|rpad(cs.sno,10); dbms_output.put_line(rpad( ,30, )|姓名:|rpad(cs.sname,20); dbms_output.put_line(rpad( ,30, )|rpad(-,10,-); dbms_output.put_line(rpad( ,3, )|课程名:|rpad( ,10, )|成绩:|rpad(

30、,10, )|课程平均成绩:); dbms_output.put_line(rpad( ,50, ); v_sno:=cs.sno; else select avg(grade)into cavg from sc where o=o; dbms_output.put_line(rpad( ,3)|rpad(ame,10)|rpad( ,10, )|cs.grade|rpad( ,20, )|cavg); dbms_output.put_line(rpad( ,50, ); -dbms_output.put_line(rpad( ,40, )|cs.grade|rpad( ,30, )|cavg

31、); -dbms_output.put_line(cavg); -dbms_output.put_line(rpad( ,50, ); end if; end loop; dbms_output.put_line(rpad(=,50,=); exception when others then NULL; close stu_grade; end;实验五 实现过程，包，函数的编写一.目的要求 使用PL/SQL语言编写过程，包和函数 二.实验内容 创建存储过程，包和函数，并能通过参数将结果传递出去。在存储过程中使用游标处理结果集。在PL/SQL块调试编写的包，函数和存储过程。注意包的作用，比较在

32、包体中定义的过程和函数与独立的过程和函数有什么区别。 使用Student, SC, Course三张表，作存储过程和函数，完成下面的功能： 1.过程和函数作一存储过程和函数，完成下面的功能：输入姓名，课程名，成绩，该过程完成对SC表的插入或修改操作，若插入成功，返回成功信息，若该选课信息已经存在，则修改其成绩为输入的成绩，若遇系统错误，返回错误信息。-建立存储过程：create or replace procedure keke(psname student.sname%type,pcname ame%type,pgrade number)ispsno student.sno%type;pcn

33、o o%type;vc number;vs number;begin if(pgrade not between 0 and 100) then dbms_output.put_line(成绩超出范围，请重新输入！); return; end if; begin select sno into psno from student where sname=psname; exception when no_data_found then vs:= 0; end; begin select cno into pcno from course where cname=pcname; exceptio

34、n when no_data_found then vc:=0; end; if vs=0 and vc=0 then dbms_output.put_line(学生：|psname|和|课程：|pcname|都不存在! 请重新输入：); return; elsif vs=0 then dbms_output.put_line(学生：|psname| 不存在! 请重新输入： ); return; elsif vc=0 then dbms_output.put_line(课程：|pcname| 不存在! 请重新输入：); return; end if; update sc set grade=p

35、grade where sno=psno and cno=pcno; if sql%found then commit; dbms_output.put_line(修改成功！); else insert into sc values(psno,pcno,pgrade); commit; dbms_output.put_line(插入成功); end if;end;-测试1.当学生与课程都不存在：1. 当学生不存在：2. 当课程不存在：3. 插入数据：4. 更新数据-创建函数create or replace function P2(Psname char, Pcname char, Pgrad

36、e number) return varchar is v_return varchar(200); Psno varchar2(10); Pcno varchar2(20); vc number;-计数器,当课程不存在时，置为0 vs number;-计数器，当学生不存在时，置为0begin-在student表中查询输入的学生学号，如果不存在，则把vs置为0 begin select sno into Psno from student where sname = Psname; exception when no_data_found then vs := 0; end;-在course表

37、中查询输入的课程的课程号，如果不存在，则把vc置为0 begin select cno into Pcno from course where cname = Pcname; exception when no_data_found then vc := 0; end;-只有当vc与vs都不为0的情况，才可以插入数据 if vc = 0 and vs = 0 then v_return:=警告！不存在|Psname|这个学生和|Pcname|这门课程，不能修改!; return v_return; elsif vc = 0 then v_return:=警告！不存在|Pcname|这门课程，不

38、能修改!; return v_return; elsif vs=0 then v_return:=警告！不存在|Psname|这个学生，不能修改!; return v_return; else update sc set grade = Pgrade where sno = Psno and cno = Pcno; if sql%notfound then insert into sc values (Psno, Pcno, Pgrade); end if; v_return:=恭喜！更新数据成功！|Psno|Pcname|Pgrade; commit; return v_return; en

39、d if; end; end;*/2. 包练习 定义一个包，使其中包括下面 功能：1 建立过程，当传入学号和选课门数，首先判断SC_Number表是否存在，若不存在则创建该表格（包括学号和选修门数两列），将传入值插入或修改到SC_Number表中（该生不存在则插入，若存在则修改其选课门数）（私有过程.2 建立过程（重载），当用户输入学号（或姓名），课程号，成绩，将该信息插入到SC表格中，若该课程已经满额，则提示相关信息；若该生已经选择了该课程，则修改该课程的成绩为输入成绩；若该生或该课程不存在，则提示相关错误。插入成功后调用上一个过程将学生选课情况修改.3 建立过程，当用户输入学号，将该生对应的选课信息(SC)，学生基本信息(Student)，SC_Number中关于该生的信息全部删除，若该生不存在，则给出相关提示.4 建立过程，实现删除SC_Number表格的功能.包头：create or replace package pk1is - procedure SCN(Psno student.sno%type,Pscnt number) ; procedure insertSC(P