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

四川师范大学计算机学院实 验 报 告 册院系名称： 计算机科学学院 课程名称： Oracle 实验学期 2014 年至 2015 年 第 一 学期专业班级： 网络工程3班 姓名： 学号： 指导教师： 俞晓 实验最终成绩： 实验一 了解ORACLE环境，使用ORACLE数据库实用工具 1.目的要求： 了解ORACLE数据库的各个常用工具软件 2.实验内容： 在ORACEL数据库下使用SQL*PLUS ，SQL*PLUS Worksheet，PL/SQL Developer工具，企业管理器等实用工具与Oracle交互。并在企业管理器中观察ORACLE的底层存储原理。在PL/SQL Developer中书写简单的SQL语言。 3.主要仪器设备及软件 1）PC 2）ORACLE数据库 PL/SQL Developer工具的运用:实验二 熟悉SQL语言1.目的要求 在SQL*PLUS或PL/SQL Developer工具中编写SQL语句 2.实验内容 ORACLE 数据库中定义用户，给用户赋权限，创建，修改和删除表格，视图等数据库对象，并向表格中插入，修改和删除数据。体会SQL语言中ORACLE的“方言”。 对自己建立的表做查询：包括单表查询，多表查询，嵌套查询，分组查询，相关查询 掌握SQL语句的书写方法熟练使用SQL语句实现建表，修改表，删除表，向表中插入，删除，修改，查询等操作。1. 创建用户 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) 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 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 = 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系中的女生的学号、姓名、身高，并按身高进行排列（降序）： select sno,sname,heigh from student where sdept='jkx' and ssex='女' order by heigh desc;-查询成绩小于60的10位同学的学号，课程号，成绩: select * from (select * from sc where grade<60 order by grade desc) where rownum<=10;7. 建视图-建立学生平均成绩视图:create view avg_grade(sno,avgs) as select sno,avg(grade） from sc group by sno-找出平均成绩小于89的学生select * from sc where avg(grade)<89; 8. SQL语句练习 -1、检索所有学生中年龄最大的学生的姓名及年龄。select sname,sage from student where sage>=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 from 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 where 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 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) left 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(grade)>=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,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.统计及格的课程数在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名次名单来。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 sdept='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.查询每位同学的选课中成绩最高的课程对应的学号，姓名，课程名，成绩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 * from 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 where 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的编程环境和代码结构。实现与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_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; - 判断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_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表 cursor 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 count:'|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; 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('-',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=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 when 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(' ',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); -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块调试编写的包，函数和存储过程。注意包的作用，比较在包体中定义的过程和函数与独立的过程和函数有什么区别。 使用Student, SC, Course三张表，作存储过程和函数，完成下面的功能： 1.过程和函数作一存储过程和函数，完成下面的功能：输入姓名，课程名，成绩，该过程完成对SC表的插入或修改操作，若插入成功，返回成功信息，若该选课信息已经存在，则修改其成绩为输入的成绩，若遇系统错误，返回错误信息。-建立存储过程：create or replace procedure keke(psname student.sname%type,pcname ame%type,pgrade number)ispsno student.sno%type;pcno 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; exception 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=pgrade 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, Pgrade 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表中查询输入的课程的课程号，如果不存在，则把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|'这门课程，不能修改!' 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; end 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