实验一
1.创建用户utest,密码为utest1。
在这里插入图片描述
2、为utest用户赋予connect权和dba权。
3、收回utest用户dba权限。
4、使用utest用户登录。（使用connect命令）。
实验二
1第一关：
①向Course表中插入一行，数据为：
insert into Course(cno,cname,ccredit,snumber)
values(‘C50’,‘Oracle’,4,100);
②使用Insert语句为CS系学生学生选择必修课C50。
insert into SC(sno,cno)
select sno,‘C50’ from Student
where sdept=‘CS’ and not exists(select* from SC where sno=Student.sno and cno=‘C50’);
select * from sc where sno in (select sno from student where sdept=‘CS’)
③将Oracle课程的先行课修改为“DB”课程的课程号（使用子查询完成）
update Course set cpno=(select cno from Course where cname=‘DB’)
where cname=‘Oracle’;
Select * from course
④将CS系“DB”课程成绩为空的学生选课信息删除。
delete from SC where sno in(select sno from Student where sdept=‘CS’) and cno in(select cno from Course where cname=‘DB’) and grade is null;
select * from sc where sno in (select sno from student where sdept=‘CS’) and cno in (select cno from course where cname=‘DB’)
2 第二关
①查询CS系年龄大于20岁的学生，列出学号，姓名，性别。
select sno,sname,ssex from Student
where sdept=‘CS’ and sage>10;
②查询选了’DB’课程的学生的学号。
select sno from SC where cno in(select cno from Course where cname=‘DB’);
③查询CS系没有选’DB’课程的学生的学号，姓名，系。
select sno,sname,sdept
from Student
where not exists(select* from SC where sno=Student.sno
and cno in(select cno from Course where cname=‘DB’))
and sdept=‘CS’;
④查询男（‘m’）同学选择了’DB’课程，但是没有选’Oracle’课程的学生，列出学号，姓名。(课程名要区分大小写)
select sno,sname from Student
where ssex=‘m’ and sno in(select sno from SC where cno in(select cno from Course where cname=‘DB’)) and sno not in(select sno from SC where cno in(select cno from Course where cname=‘Oracle’));
3第三关
①查询每个学生的平均分，列出学生的学号，平均分（列名为savg）,并按平均分降序排列。
select sno,avg(grade) as savg
from SC
group by sno
order by savg desc;
②查询选课人数大于等于3人的课程，列出课程号，课程名，选课人数（列名为scnt）,并按课程号升序排列。
select SC.cno,cname,count(sno) as scnt
from Course,SC
where Course.cno=SC.cno
group by SC.cno,cname
having count(sno)>=3
order by SC.cno;
③查询选课人数最多的课程。列出课程号，课程名。
select Course.cno,cname
from Course,SC
where Course.cno=SC.cno
group by Course.cno,cname
having count(sno)>=all(select count(sno) from SC group by SC.cno);
④查询CS系选课人数最多的课程。列出课程号，课程名，CS系的选课人数（列名为CScnt）。
select SC.cno,cname,count(Student.sno)as CScnt
from Course,SC,Student
where SC.sno=Student.sno and SC.cno=Course.cno and sdept=‘CS’
group by SC.cno,cname
having count(Student.sno)>=all(select count(Student.sno)from SC,Course,Student where SC.sno=Student.sno and SC.cno=Course.cno and sdept=‘CS’ group by SC.cno,cname);
实验三.
第一关
1
declare
vsname varchar(20);
vsage smallint;
begin
select sname,sage into vsname,vsage from Student
where sno=‘005’;
dbms_output.put_line(‘005号同学的姓名：’||vsname);
dbms_output.put_line(‘005号同学的年龄：’||vsage);
end;
2
declare
vsno char(10);
vsname varchar(20);
vsage smallint;
vssex char(2);
begin
select sno,sname,sage,ssex into vsno,vsname,vsage,vssex from Student
where sage>=all(select sage from Student);
if vssex=‘m’ then
dbms_output.put_line(vsno||’，’||vsname||’，’||vsage||’，’||‘帅哥’);
else
dbms_output.put_line(vsno||’，’||vsname||’，’||vsage||’，’||‘美女’);
end if;
end;
第二关
备注：由于oracle版本问题，测试窗口一直执行，没有结果，所以用sql窗口进行试验(执行了22分钟都没有结果)
1将‘004’号同学的年龄改为18岁，系别改为‘CS’，为CS系年龄最小的同学选‘C01’号课程。
将‘004’号同学的年龄改为18岁，系别改为‘CS’
update Student set sage=18,sdept=‘CS’ where sno=‘004’;
为CS系年龄最小的同学选‘C01’号课程。
insert into SC(sno,cno)
select sno,‘C01’ from Student
where sdept=‘CS’ and sage<=all
(select sage from Student where sdept=‘CS’)
and not exists
(select*from SC where sno=Student.sno and cno=‘C01’);
2为‘002’号同学选‘C02’课程。将选课人数最多的课程的最大选课人数（snumber）改为105。
为‘002’号同学选‘C02’课程。
insert into SC(sno,cno) values(‘022’,‘C22’);
将选课人数最多的课程的最大选课人数（snumber）改为105。
update Course set snumber=105
where cno in(select cno from SC where Course.cno=SC.cno
group by SC.cno having count(sno)>=all
(select count(sno) from SC group by SC.cno));
实验四
1第一关：
①查询‘111’号同学的姓名，使用异常判断，若没有这个同学，则添加一条学号为‘111’的学生信息，姓名为‘张三’，其他属性为空，并输出：‘111’号同学已被成功添加。 若存在该同学，则输出（冒号为中文状态下的冒号）： 111号同学的姓名：查询出的姓名 若不存在该同学，则输出： 111号同学已被成功添加。
先查询student 没有学号为111的学生
第一次运行
第二次运行
程序代码：
DECLARE
vsno char(10);
vsname varchar(20);
BEGIN
vsno := ‘111’;
select sname into vsname from Student where sno=vsno;
dbms_output.put_line(‘111号同学的姓名：’||vsname);
exception when NO_DATA_FOUND then
insert into Student(sno,sname) values (‘111’,‘张三’);
dbms_output.put_line(’‘111’号同学已被成功添加。’);
commit;
END;
②打印‘C50’号课程的当前选课人数和最大选课人数（Snumber）。若出现异常，则判断C50号课程是否存在，若不存在，则添加C50课程（‘C50’,‘NewCourse’,null,3,100），再让001号同学和003号同学选修C50号课程，成绩分别为80分和90分，最后打印C50号课程的当前选课人数和最大选课人数。打印格式如下： 若C50存在，则打印格式如下（其中的逗号和冒号是中文状态下的）： （查询出的C50的课程名）课程当前选课人数：（查出来的人数），最大选课人数：（查出来的最大人数） 如：DB课程当前选课人数：80，最大选课人数：100 若C50不存在，则打印格式（其中的逗号和冒号是中文状态下的）： 添加NewCourse课程成功，课程当前选课人数：（查出来的人数），最大选课人数：（查出来的最大人数） 如：添加NewCourse课程成功，课程当前选课人数：10，最大选课人数：100（注：添加课程为c52）
首先查看course表，没有c52课程
第一次运行代码
第二次访问代码
declare
vsnumber2 course.snumber%type;
vsum int;
vcname varchar2(20);
begin
select cname,snumber into vcname,vsnumber2 from Course where cno=‘C52’;
select count() into vsum from SC where cno=‘C52’;
dbms_output.put_line(vcname||‘课程当前选课人数：’||vsum||’，最大选课人数：’||vsnumber2);
exception when NO_DATA_FOUND THEN
insert into Course values (‘C52’,‘NewCourse’,null,3,100);
insert into SC values (‘077’,‘C52’,80);
update Course set snumber=snumber+1 where cno=‘C52’;
insert into SC values (‘088’,‘C52’,90);
update Course set snumber=snumber+1 where cno=‘C52’;
select count() into vsum from SC where cno=‘C52’ ;
select snumber into vsnumber2 from course where cno=‘C51’;
dbms_output.put_line(‘添加NewCourse课程成功，课程当前选课人数：’||vsum||’，最大选课人数：’||vsnumber2);
commit;
end;
第二关.
为English系年龄最大的同学选C52课程，并打印选课结果和该同学的选课数，若该生已经选择了C52课程，则打印该生的平均分。使用嵌套块中的异常判断，若没有English系，则将Student表中的E系修改为English系，并打印：已成功将E系修改为English系，然后退出程序。若没有C52课程，请添加C52课程（‘C52’,‘NC’,null,2,100）并打印：C52课程添加成功，然后退出程序。
首先查看student表 无English 有E系
第一次执行代码
第二次执行代码
第三次执行代码
declare
vsno char(10);
vsname varchar(20);
vcno char(10);
vcname varchar(20);
vavg int;
vsum int;
n int;
begin
–判别English系是否存在
begin
select sno into vsno from Student where sdept=‘English’and sage=(select max(sage)from Student where sdept=‘English’);
exception when NO_DATA_FOUND then
update Student set sdept=‘English’ where sdept=‘E’;
dbms_output.put_line(‘已成功将E系修改为English系’);
end;
–判别‘C52’课程是否存在
begin
select cno into vcno from Course where cno=‘C52’;
exception when NO_DATA_FOUND then
insert into Course values(‘C52’,‘NC’,null,2,100);
dbms_output.put_line(‘C52课程添加成功’);
end;
–为English系的年龄最大的同学选C52课程
begin
select sno,sname into vsno,vsname from Student where sdept=‘English’ and sage=(select max(sage)from Student where sdept=‘English’);
select count() into n from SC where sno=vsno and cno=vcno;
if(n>0)then
select avg(grade) into vavg from SC where sno=vsno;
dbms_output.put_line(vsname||‘同学的平均成绩为：’||vavg);
else
insert into SC(sno,cno) select sno,‘C52’ from Student where sno=vsno;
select cname into vcname from Course where cno=‘C52’;
select count()into vsum from SC where sno=vsno;
dbms_output.put_line(‘已为’||vsname||‘同学选择了课程’||vcname||’，该同学的选课门数为：’||vsum);
end if;
end;
commit;
end;
实验五
第一关
①
注：将题目条件改为选大数据的 English 系学生
declare
cursor c1 is
select Student.sno,sname,grade
from Student,SC
where SC.sno=Student.sno and sdept=‘English’
and cno in(select cno from Course where cname=‘大数据’) group by Student.sno,sname,grade order by Student.sno;
begin
dbms_output.put_line(rpad(‘学号’,10,’ ‘)||rpad(‘姓名’,10,’ ‘)||rpad(‘成绩’,10,’ ‘));
dbms_output.put_line(’-------------------------’);
for i in c1 loop
dbms_output.put_line(rpad(i.sno,10,’ ‘)||rpad(i.sname,10,’ ‘)||rpad(i.grade,10,’ '));
end loop;
end;
②
declare
lname Student.sname%type;
cursor c2 is
select Student.sno,sname,sdept,cname,grade
from Student,Course,SC
where Student.sno=SC.sno and SC.cno=Course.cno
and sdept=‘English’
group by Student.sno,sname,sdept,cname,grade
order by Student.sno,cname;
begin
dbms_output.put_line(rpad(‘学号’,10,’ ‘)||rpad(‘姓名’,10,’ ‘)||rpad(‘系别’,10,’ ‘)||rpad(‘课程名’,10,’ ‘)||rpad(‘成绩’,10,’ ‘));
dbms_output.put_line(’=============================================’);
for i in c2 loop
if c2%rowcount>1 then
if(i.sname!=lname) then
dbms_output.put_line(’---------------------------------------------’);
end if;
end if;
dbms_output.put_line(rpad(i.sno,10,’ ‘)||rpad(i.sname,10,’ ‘)||rpad(i.sdept,10,’ ‘)||rpad(i.cname,10,’ ‘)||rpad(i.grade,10,’ '));
lname:=i.sname;
end loop;
end;
第二关
①
declare
rc sys_refcursor;
lastsno Student.sno%type;
cnt int;
vsno Student.sno%type;
vcname Course.cname%type;
vgrade SC.grade%type;
vccredit Course.ccredit%type;
begin
open rc for
select Student.sno,cname,grade,ccredit
from Student,Course,SC
where Student.sno=SC.sno and Course.cno=SC.cno and ssex=‘m’
group by Student.sno,cname,grade,ccredit order by Student.sno,cname;
dbms_output.put_line(rpad(‘学号’,10,’ ‘)||rpad(‘课程名’,10,’ ‘)||rpad(‘成绩’,10,’ ‘)||rpad(‘所得学分’,10,’ ‘));
dbms_output.put_line(rpad(’=’,40,’=’));
loop
fetch rc into vsno,vcname,vgrade,vccredit;
exit when rc%notfound;
if (rc%rowcount>1) then
if(vsno!=lastsno) then
dbms_output.put_line(rpad(’-’,35,’-’));
select count(lastsno) into cnt from SC where sno=lastsno;
dbms_output.put_line(trim(lastsno)||‘同学的选课数：’||cnt||‘门’);
dbms_output.put_line(rpad(’=’,40,’=’));
end if;
end if;
if (vgrade<60) then
dbms_output.put_line(rpad(vsno,10,’ ‘)||rpad(vcname,10,’ ‘)||rpad(vgrade,10,’ ‘)||rpad(‘0’,10,’ ‘));
else
dbms_output.put_line(rpad(vsno,10,’ ‘)||rpad(vcname,10,’ ‘)||rpad(vgrade,10,’ ‘)||rpad(vccredit,10,’ ‘));
end if;
lastsno:=vsno;
end loop;
dbms_output.put_line(rpad(’-’,35,’-’));
select count(lastsno) into cnt from SC where sno=lastsno;
dbms_output.put_line(trim(lastsno)||‘同学的选课数：’||cnt||‘门’);
close rc;
end;
实验六
第一关
1、
程序代码：
create or replace function Fs1(vcno Course.cno%type)
return Course.cpno%type
is
vcpno Course.cpno%type;
vpcname Course.cname%type;
vcname Course.cname%type;
begin
select cname into vcname from Course where cno=vcno;
begin
select cpno into vcpno from Course where cno=vcno;
select cname into vpcname from Course where cno=vcpno;
return (vcname||‘课程的先行课为：’||vpcname);
exception when no_data_found then
return (vcname||‘课程没有先行课。’);
end;
exception when no_data_found then
return (vcno||‘课程不存在。’);
end;
测试代码
begin
dbms_output.put_line(Fs1(‘001’));
dbms_output.put_line(Fs1(‘C01’));
dbms_output.put_line(Fs1(‘C52’));
end;
2、
程序代码
create or replace function Fs2(vsno SC.sno%type)
return number
is
vvsno SC.sno%type;
cnt number;
begin
select sno into vvsno from Student where sno=vsno;
begin
select count(*) into cnt from SC where sno=vvsno;
return cnt;
end;
exception when no_data_found then
return -1;
end;
测试代码
begin
if(Fs2(‘002’)=-1) then
dbms_output.put_line(‘002号同学不存在。’);
else
dbms_output.put_line(‘002号同学的选课门数为：’||Fs2(‘002’));
end if;
if(Fs2(‘111’)=-1)then
dbms_output.put_line(‘111号同学不存在。’);
else
dbms_output.put_line(‘111号同学的选课门数为：’||Fs2(‘111’));
end if;
if(Fs2(‘123’)=-1)then
dbms_output.put_line(‘123号同学不存在。’);
else
dbms_output.put_line(‘123号同学的选课门数为：’||Fs2(‘123’));
end if;
end;
第二关
1
程序代码
create or replace procedure Ps1(vcname varchar)
is
vcno SC.cno%type;
rc sys_refcursor;
v_s SC%rowtype;
begin
select cno into vcno from Course where cname=vcname;
dbms_output.put_line(rpad(‘学号’,10,’ ‘)||rpad(‘课程号’,10,’ ‘)||rpad(‘成绩’,10,’ ‘));
dbms_output.put_line(rpad(’=’,25,’=’));
open rc for select sno,cno,grade from SC where cno=vcno group by sno,cno,grade order by sno;
loop
fetch rc into v_s;
exit when rc%notfound;
if(v_s.grade is null) then
dbms_output.put_line(rpad(v_s.sno,10,’ ‘)||rpad(v_s.cno,10,’ ‘)||rpad(-1,10,’ ‘));
else
dbms_output.put_line(rpad(v_s.sno,10,’ ‘)||rpad(v_s.cno,10,’ ‘)||rpad(v_s.grade,10,’ ‘));
end if;
end loop;
dbms_output.put_line(rpad(’=’,25,’=’));
close rc;
exception when no_data_found then
dbms_output.put_line(vcname||‘课程不存在。’);
dbms_output.put_line(rpad(’=’,25,’=’));
end;
测试代码
begin
Ps1(‘数据库’);
dbms_output.put_line(‘Oracle’||rpad(’=’,25,’=’));
Ps1(‘Oracle’);
end;
第三关
1
程序代码
create or replace procedure Ps2(vsno Student.sno%type,outputs out varchar)
is
vsname Student.sname%type;
cnt int;
vsdept Student.sdept%type;
vcno Course.cno%type;
vgrade SC.grade%type;
begin
select sname into vsname from Student where sno=vsno;
begin
select cno into vcno from Course where cname=‘大数据’;
select count(*) into cnt from SC where sno=vsno and cno=vcno;
if(cnt>0) then
select sdept into vsdept from Student where sno=vsno;
select grade into vgrade from SC where sno=vsno and cno=vcno;
outputs:=vsname||‘同学的系别为：’||vsdept||’，大数据的成绩为：’||vgrade;
end if;
if(cnt=0) then
select sdept into vsdept from Student where sno=vsno;
if(vsdept=‘English’) then
insert into SC(sno,cno) values(vsno,vcno);
commit;
outputs:=‘已成功为’||vsname||‘同学选择了大数据课程。’;
else
outputs:=vsname||‘同学没有选择大数据课程。’;
end if;
end if;
end;
exception when no_data_found then
outputs:=vsno||‘号同学不存在。’;
end;
测试代码
declare
outputs varchar(1000);
begin
Ps2(‘001s’,outputs);
dbms_output.put_line(outputs);
Ps2(‘003’,outputs);
dbms_output.put_line(outputs);
Ps2(‘004’,outputs);
dbms_output.put_line(outputs);
Ps2(‘040’,outputs);
dbms_output.put_line(outputs);
end;
第四关
程序代码
create or replace procedure Ps3(vsname Student.sname%type,vcname Course.cname%type,vgrade SC.grade%type,outputs out varchar)
is
psno Student.sno%type;
pcno Course.cno%type;
cnt int;
begin
select sno into psno from Student where sname=vsname;
begin
select cno into pcno from Course where cname=vcname;
begin
if(vgrade>100 or vgrade<0) then
update SC set grade=null where sno=psno and cno=pcno;
commit;
outputs:=‘成绩必须在0到100分之间。’;
else
select count(*) into cnt from SC where sno=psno and cno=pcno;
if(cnt=0) then
insert into SC values(psno,pcno,vgrade);
commit;
outputs:=‘已经成功为’||trim(psno)||‘号同学选择了’||vcname||‘课程，成绩为’||vgrade;
end if;
if(cnt>0) then
if(vgrade is null) then
outputs:=‘已将’||trim(psno)||‘号同学’||vcname||‘课程的成绩改为未考试’;
else
update SC set grade=vgrade where sno=psno and cno=pcno;
commit;
outputs:=‘已将’||trim(psno)||‘号同学’||vcname||‘课程的成绩改为’||vgrade;
end if;
end if;
end if;
end;
exception when no_data_found then
outputs:=vcname||‘课程不存在。’;
end;
exception when no_data_found then
outputs:=vsname||‘同学不存在。’;
end;
测试代码
declare
outputs varchar(1000);
begin
Ps3(‘zr’,‘大数据’,86,outputs);
dbms_output.put_line(outputs);
Ps3(‘zr’,‘大数据’,null,outputs);
dbms_output.put_line(outputs);
Ps3(‘zrs’,‘大数据’,86,outputs);
dbms_output.put_line(outputs);
Ps3(‘zr’,‘大数据s’,86,outputs);
dbms_output.put_line(outputs);
Ps3(‘zr’,‘大数据’,120,outputs);
dbms_output.put_line(outputs);
end;
实验七
第一关
程序：
create or replace package pks1 is
vsno student.sno%type;
cursor c1 is
select student.sno,sname,cname,grade
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and sc.sno=vsno
order by grade desc;
procedure ps1(vsdept student.sdept%type);
function fs1(vsno student.sno%type)
return int;
end pks1;
create or replace package body pks1
is
procedure ps1(vsdept student.sdept%type)
is
cnt int;
cnt1 int;
begin
select count() into cnt from student where sdept=vsdept;
if(cnt=0) then
vsno:=’-1’;
else
begin
select count() into cnt1 from sc where sno in (select sno from student where sdept=vsdept);
if(cnt1=0) then
vsno:=‘2’;
else
select sc.sno into vsno
from student,sc
where student.sno = sc.sno and sdept=vsdept
group by sc.sno
having avg(grade)>=all(select avg(grade) from student,sc
where student.sno = sc.sno and sdept=vsdept group by sc.sno);
end if;
end;
end if;
end ps1;
function fs1(vsno student.sno%type)
return int
is
sumccredit int;
begin
select sum(ccredit) into sumccredit
from course,sc
where course.cno=sc.cno and sno=vsno and grade>=60;
return sumccredit;
end fs1;
end pks1;
测试：
查看English系平均分最高的同学：002
代码：
declare
psdept1 Student.sdept%type:=‘CS’;
psdept2 Student.sdept%type:=‘English’;
psdept3 Student.sdept%type:=‘软件体系’;
begin
pks1.ps1(psdept1);
if (pks1.vsno=’-1’) then
dbms_output.put_line(psdept1||‘系不存在。’);
elsif(pks1.vsno=’-2’) then
dbms_output.put_line(psdept1||‘系的同学没有选课记录。’);
else
dbms_output.put_line(rpad(‘学号’,10,’ ‘)||rpad(‘姓名’,10,’ ‘)||rpad(‘课程名’,10,’ ‘)||rpad(‘成绩’,10,’ ‘));
dbms_output.put_line(rpad(’=’,35,’=’));
for i in pks1.c1 loop
dbms_output.put_line(rpad(i.sno,10,’ ‘)||rpad(i.sname,10,’ ‘)||rpad(i.cname,10,’ ‘)||rpad(i.grade,10,’ ‘));
end loop;
dbms_output.put_line(rpad(’=’,35,’=’));
dbms_output.put_line(trim(pks1.vsno)||‘号同学获得的总学分为：’||pks1.fs1(pks1.vsno));
end if;
pks1.ps1(psdept2);
if (pks1.vsno=’-1’) then
dbms_output.put_line(psdept2||‘系不存在。’);
elsif(pks1.vsno=’-2’) then
dbms_output.put_line(psdept2||‘系的同学没有选课记录。’);
else
dbms_output.put_line(rpad(‘学号’,10,’ ‘)||rpad(‘姓名’,10,’ ‘)||rpad(‘课程名’,10,’ ‘)||rpad(‘成绩’,10,’ ‘));
dbms_output.put_line(rpad(’=’,35,’=’));
for i in pks1.c1 loop
dbms_output.put_line(rpad(i.sno,10,’ ‘)||rpad(i.sname,10,’ ‘)||rpad(i.cname,10,’ ‘)||rpad(i.grade,10,’ ‘));
end loop;
dbms_output.put_line(rpad(’=’,35,’=’));
dbms_output.put_line(trim(pks1.vsno)||‘号同学获得的总学分为：’||pks1.fs1(pks1.vsno));
end if;
pks1.ps1(psdept3);
if (pks1.vsno=’-1’) then
dbms_output.put_line(psdept3||‘系不存在。’);
elsif(pks1.vsno=’-2’) then
dbms_output.put_line(psdept3||‘系的同学没有选课记录。’);
else
dbms_output.put_line(rpad(‘学号’,10,’ ‘)||rpad(‘姓名’,10,’ ‘)||rpad(‘课程名’,10,’ ‘)||rpad(‘成绩’,10,’ ‘));
dbms_output.put_line(rpad(’=’,35,’=’));
for i in pks1.c1 loop
dbms_output.put_line(rpad(i.sno,10,’ ‘)||rpad(i.sname,10,’ ‘)||rpad(i.cname,10,’ ‘)||rpad(i.grade,10,’ ‘));
end loop;
dbms_output.put_line(rpad(’=’,35,’=’));
dbms_output.put_line(trim(pks1.vsno)||‘号同学获得的总学分为：’||pks1.fs1(pks1.vsno));
end if;
end;
第二关
程序部分截图
程序代码
create or replace package body pks2
is
procedure pSCnumber(vsno varchar)
is
num number(20);
begin
execute immediate
‘create table SC_Number(sno char(10),Scnt number(20))’;
commit;
select count(*) into num from sc where sno = vsno;
execute immediate
‘insert into SC_Number values(:1,:2)’
using vsno,num;
commit;
exception when others then
execute immediate
‘update SC_Number set scnt = scnt + 1 where sno =:1’
using vsno;
if SQL%NOTFOUND then
execute immediate
‘insert into SC_Number values(:1,:2)’
using vsno,1;
end if;
commit;
end pSCnumber;
procedure ps1(vsname student.sname%type,vcname course.cname%type,vgrade sc.grade%type,results out varchar)
is
sgrade number(20);
scno varchar(20);
ssno varchar(20);
ssnumber number(20);
num number(20) := 1;
num1 number(20) := 1;
num2 number (20);
begin
begin
select cno,snumber into scno,ssnumber from course where cname=vcname;
exception when no_data_found then
num:=0;
end;
begin
select sno into ssno from student where sname=vsname;
exception when no_data_found then
num1:=0;
end;
if num = 0 and num1 = 0 then
results := vsname||‘同学不存在。’||vcname||‘课程不存在。’;
elsif num = 0 and num1 != 0 then
results := vsname||‘同学不存在。’;
elsif num = 1 and num1 = 0 then
results := vcname||‘课程不存在。’;
else
begin
select grade into sgrade from sc where sno = ssno and cno = scno;
update sc set grade = vgrade where sno = ssno and cno = scno;
commit;
if vgrade is null then
results := ‘已将’||trim(ssno)||‘号同学的’||trim(scno)||‘号课程成绩从’||sgrade||‘修改为未考试’;
else
results := ‘已将’||trim(ssno)||‘号同学的’||trim(scno)||‘号课程成绩从’||sgrade||‘修改为’||vgrade;
end if;
exception when no_data_found then
select count() into num2 from sc where cno = scno;
if num2 >= ssnumber then
results := trim(scno)||‘号课程已经满额了，当前选课人数’||num2||’，最大选课人数’||ssnumber;
else
insert into sc values(ssno,scno,vgrade);
commit;
pSCnumber(ssno);
if vgrade is null then
results := trim(ssno)||‘号同学，选修了’||trim(scno)||‘号课程，成绩为未考试’;
else
results := trim(ssno)||‘号同学，选修了’||trim(scno)||‘号课程，成绩为’||vgrade;
end if;
end if;
end;
end if;
end ps1;
function fs1(vsno student.sno%type)
return varchar
is
results varchar(500);
num number(20);
begin
select count() into num from student where sno = rpad(vsno,10);
if num = 0 then
results := vsno||‘号同学不存在。’;
return results;
else
delete from sc where sno = rpad(vsno,10);
execute immediate
‘delete from SC_Number where sno = :1’
using rpad(vsno,10);
delete from student where sno = rpad(vsno,10);
commit;
results := vsno||‘号同学已被删除。’;
return results;
end if;
end fs1;
procedure ps2(results out varchar)
is
begin
execute immediate
‘drop table SC_Number’;
results := ‘SC_Number表被成功删除。’;
exception when others then
results := ‘SC_Number表不存在。’;
end ps2;
end pks2;
测试部分截图：
实验八
第一关
第一题
截图：
代码：
create or replace trigger tr_student_update
before update on student
for each row
begin
if(:old.sage>=20) then
raise_application_error(’-20000’,‘不能修改20岁以上的同学系别。’);
end if;
end;
测试：
第二题
截图：
代码：
create or replace trigger tr_course_insert
before insert on course
for each row
begin
if(:new.cno like ‘C%’ and :new.cno is not null) then
raise_application_error(-20000,‘课程号以C开头的课程，先行课不能为空。’);
end if;
end;
测试
第三题
截图：
代码：
create or replace trigger tr_sc_delete
before delete on sc
for each row
declare
tcno sc.cno%type;
begin
select cno into tcno from course where cname=‘DB’;
if(:old.cno=tcno) then
raise_application_error(-20000,‘不能删除数据库课程的选课信息。’);
end if;
end;
测试：
第四题
截图：
代码：
create or replace trigger tr_SC_Update
after update on sc
for each row
declare
tsdept student.sdept%type;
begin
select sdept into tsdept from student where sno = :new.sno;
if(tsdept=‘CS’) then
if(:new.grade is not null and :new.grade<:old.grade) then
raise_application_error(-20000,‘CS系学生的成绩只能增加不能减少。’);
end if;
if(:new.grade is null) then
raise_application_error(-20000,‘CS系学生的成绩不能修改为空。’);
end if;
end if;
end;
测试：
第二关
第一题
截图：
代码：
create or replace trigger tr_student_delete
before delete on student
for each row
begin
if deleting then
delete from sc where sno = :old.sno;
end if;
end;
测试：
第二题
第一步 增加冗余列
截图：
代码：
create or replace trigger tr_sc_IDU
after insert or delete or update on sc
for each row
begin
if inserting then
update student set scnt = scnt+1 where sno = :new.sno;
end if;
if deleting then
update student set scnt = scnt-1 where sno = :old.sno;
end if;
if updating then
update student set scnt = scnt+1 where sno = :new.sno;
update student set scnt = scnt-1 where sno = :old.sno;
end if;
end;
测试：
初始数据
初始数据
第三关
添加冗余列
截图：
代码：
create or replace trigger tr_student_sumc_IDU
before insert or delete or update of sno,cno,grade on sc
for each row
declare
ssumC number(10);
begin
if inserting then
if :new.grade is not null and :new.grade >= 60 then
select sumC into ssumC from student where sno = :new.sno;
if ssumC is null then
update student set sumC = (select ccredit from course where cno = :new.cno) where sno = :new.sno;
else
update student set sumC = sumC + (select ccredit from course where cno = :new.cno) where sno = :new.sno;
end if;
end if;
end if;
if deleting then
if :old.grade is not null and :old.grade >= 60 then
update student set sumC = sumC - (select ccredit from course where cno = :old.cno) where sno = :old.sno;
end if;
end if;
if updating then
if :new.sno = :old.sno then
if :new.grade is not null and :new.grade >= 60 then
select sumC into ssumC from student where sno = :new.sno;
if ssumC is null then
update student set sumC = (select ccredit from course where cno = :new.cno) where sno = :new.sno;
else
update student set sumC = sumC + (select ccredit from course where cno = :new.cno) where sno = :new.sno;
end if;
if :old.grade is not null and :old.grade >= 60 then
update student set sumC = sumC - (select ccredit from course where cno = :old.cno) where sno = :old.sno;
end if;
end if;
else
if :new.grade is not null and :new.grade >= 60 then
select sumC into ssumC from student where sno = :new.sno;
if ssumC is null then
update student set sumC = (select ccredit from course where cno = :new.cno) where sno = :new.sno;
else
update student set sumC = sumC + (select ccredit from course where cno = :new.cno) where sno = :new.sno;
end if;
end if;
if :old.grade is not null and :old.grade >= 60 then
update student set sumC = sumC - (select ccredit from course where cno = :old.cno) where sno = :old.sno;
end if;
end if;
end if;
end;
测试：
关于插入的测试
（1）插入成绩为空的行，然后观察sumc的变化。
（2）插入成绩不及格的行，然后观察sumc的变化。
（3）插入成绩及格的行，然后观察sumc的变化。
关于删除的测试
（4）删除成绩为空的行，然后观察sumc的变化。
（5）删除成绩不及格的行，然后观察sumc的变化。
（6）删除成绩及格的行，然后观察sumc的变化。
（7）删除某一个同学的所有选课记录，然后观察sumc的变化。
关于修改的测试
（9）将一行的成绩从空修改为不及格，然后观察sumc的变化。
（10）将一行的成绩从空修改为及格，然后观察sumc的变化。
（11）将一行的成绩从及格修改为不及格，然后观察sumc的变化。
（12）将某一个同学的学号修改为另一个学号（例如，将001改为020，其中020是未选课的同学），然后观察sumc的变化。
第四关
初始数据
截图 ：
代码：
create or replace trigger tr_SC_IDU
after insert or delete or update on sc
for each row
declare
tsumG int;
tsumG2 int;
begin
if inserting then
–插入成绩不为空的行
if(:new.grade is not null) then
select sumG into tsumG from Student where sno=:new.sno;
–没有选课的同学
if(tsumG is null) then
update Student set sumG=:new.grade where sno=:new.sno;
else
–总成绩不为空的同学选课
update Student set sumG=sumG+:new.grade where sno=:new.sno;
end if;
end if;
end if;
if deleting then
if(:old.grade is not null) then
update Student set sumG=sumG-(:old.grade) where sno=:old.sno;
select sumG into tsumG from Student where sno=:old.sno;
if(tsumG=0) then
update Student set sumG=null where sno=:old.sno;
end if;
end if;
end if;
if updating then
if(:new.sno=:old.sno) then
if(:old.grade is null and :new.grade is not null) then
select sumG into tsumG from Student where sno=:new.sno;
if(tsumG is null) then
update Student set sumG=:new.grade where sno=:new.sno;
else
update Student set sumG=sumG+(:new.grade) where sno=:new.sno;
end if;
end if;
if(:old.grade is not null and :new.grade is not null) then
update Student set sumG=sumG-(:old.grade)+(:new.grade) where sno=:old.sno;
end if;
if(:old.grade is not null and :new.grade is null) then
update Student set sumG=sumG-(:old.grade) where sno=:old.sno;
select sumG into tsumG from Student where sno=:old.sno;
if(tsumG=0) then
update Student set sumG=null where sno=:old.sno;
end if;
end if;
else
select sumG into tsumG from Student where sno=:new.sno;
if(:old.grade is not null) then
if(tsumG is null) then
update Student set sumG=(:old.grade) where sno=:new.sno;
else
update Student set sumG=sumG+(:old.grade) where sno=:new.sno;
end if;
update Student set sumG=sumG-(:old.grade) where sno=:old.sno;
select sumG into tsumG2 from Student where sno=:old.sno;
if(tsumG2=0) then
update Student set sumG=null where sno=:old.sno;
end if;
end if;
end if;
end if;
end;
测试：
关于插入的测试
（1）为某位没有选课的同学选课，插入成绩为空的行，然后观察sumG的变化。
（2）为某位没有选课的同学选课，插入成绩不为空的行，然后观察sumG的变化。
（3）为总成绩部位空的同学选课，插入成绩不为空的行，然后观察sumG的变化。
关于删除的测试
（1）删除成绩为空的行，然后观察sumG的变化。
（2）删除成绩不为的行，然后观察sumG的变化。
（3）删除某一个同学的所有选课记录，然后观察sumG的变化。
（4）关于修改的测试
（1）将一行的成绩从空修改为一份分数，然后观察sumG的变化。
（2）将一行的成绩从有分数修改空，然后观察sumG的变化。
（3）将某一个同学的学号修改为另一个学号（例如，将001改为020，其中020是未选课的同学），然后观察sumG的变化。