目录
1.查询SOFTWARE(CS)系的学生的基本信息。对齐打印输出。
-- Created on 2020/11/16
declare
cursor c is select * from student where sdept = 'software';
begin
dbms_output.put_line(rpad('=',60,'='));
dbms_output.put_line(' ');
dbms_output.put_line(lpad('SOFTWARE(对应原题的SC)系学生基本信息表',45));
dbms_output.put_line(rpad('-',60,'-'));
dbms_output.put_line(rpad(' ',5)||rpad('学号',9)||rpad('姓名',8)||rpad('性别',10)||rpad('年龄',10)||'所在系');
for i in c loop
dbms_output.put_line(rpad(' ',5)||rpad(i.sno,10)||rpad(i.sname,10)||rpad(i.ssex,10)||rpad(i.sage,10)||i.sdept);
end loop;
dbms_output.put_line(rpad('=',60,'='));
end;
- 输出样例:
2.查询某学生的选课信息,打印课程号,成绩。并在最后一行打印(学生姓名)同学的平均分为:平均分。
-- Created on 2020/11/16
declare
cursor c is select cno,grade from sc where sno='18110401';
sn student.sname%type;
avgr sc.grade%type;
begin
select sname into sn from student where sno = '18110401';
select avg(grade) into avgr from sc where sno='18110401';
dbms_output.put_line(rpad('=',40,'='));
dbms_output.put_line(' ');
dbms_output.put_line(sn||'同学的选课信息如下:');
dbms_output.put_line(rpad('-',40,'-'));
dbms_output.put_line(rpad(' ',9)||rpad('课程号',9)||rpad('成绩',8));
for i in c loop
dbms_output.put_line(rpad(' ',10)||rpad(i.cno,10)||i.grade);
end loop;
dbms_output.put_line(rpad('-',40,'-'));
dbms_output.put_line(lpad(sn,15)||'同学的平均分为:'||avgr);
dbms_output.put_line(rpad('=',40,'='));
end;
输出样例:
3.查询某学生可以选的课程信息(该生没有选,同时课程也没有选满),打印课程号,课程名,学分,最大选课人数,当前选课人数。并在最后一行打印该生目前获得的学分。
-- Created on 2020/11/16
declare
sn student.sname%type:='chen';
sumg course.ccredit%type;
cursor c is select course.cno,cname,ccredit,snumber,count(*) nnumber
from student,course,sc a
where student.sno=a.sno and course.cno=a.cno and
a.cno not in (select cno from sc where sno in (select sno from student where sname=sn)) and
snumber>(select count(*) nnumber from sc b where a.cno=b.cno group by cno)
group by course.cno,cname,ccredit,snumber
order by course.cno;
x c%rowtype;
begin
--select sname into sn from student where sno = '18110416';
select sum(ccredit) into sumg from sc,course where sc.cno = course.cno and sno in (select sno from student where sname=sn);
declare
t int(1);
begin
select 1 into t from student where (sname=sn);
exception when NO_DATA_FOUND then
dbms_output.put_line('该学生不存在');
dbms_output.put_line(rpad('=',35,'='));
return;
end;
open c;
fetch c into x;
if c %notfound then
declare
xk course.snumber%type;
maxk course.snumber%type;
begin
select count(*) into xk from sc where sno in (select sno from student where sname=sn);
select count(*) into maxk from course;
if maxk=xk then
dbms_output.put_line('没有可选的课程信息! 该生已选满所有课程。');
dbms_output.put_line(rpad('=',50,'='));
else
dbms_output.put_line('没有可选的课程信息! 该生的未选课程均已达到最大选课人数。');
dbms_output.put_line(rpad('=',65,'='));
end if;
end;
return;
end if;
close c;
dbms_output.put_line(rpad('=',65,'='));
dbms_output.put_line(' ');
dbms_output.put_line(sn||'同学可以选的课程信息如下:');
dbms_output.put_line(rpad('-',65,'-'));
dbms_output.put_line(rpad(' ',8)||rpad('课程号',8)||rpad('课程名',10)||rpad('学分',5)||rpad('最大选课人数',14)||'当前选课人数');
for i in c loop
dbms_output.put_line(rpad(' ',9)||rpad(i.cno,9)||rpad(i.cname,11)||rpad(i.ccredit,10)||rpad(i.snumber,16)||i.nnumber);
end loop;
dbms_output.put_line(rpad('-',65,'-'));
dbms_output.put_line(lpad(sn,25)||'同学目前已获得的学分为:'||sumg);
dbms_output.put_line(rpad('=',65,'='));
end;
- 输出样例:
-
正常查询出某同学的可选课程:
-
查询不到该学生信息:
-
由于该生选满了课,而导致没有选课信息
-
由于未选课程全都选满而无法选课
4.使用游标实现:将某一门课程成绩高于课程平均分的学生所选的所有课程的姓名,课程名,成绩格式化输出。
-- Created on 2020/11/17
/*程序说明:
1.针对输出进行了重新设计,增加了课程平均分比较,单科排名(rownum有使用漏洞时,选择了运用rank()函数),挂科提醒,成绩评价等输出内容。
2.将某一个门高于和每门都高于的SQL写在一个程序里,运行时,可注释掉另外一个,有利于程序的可读性;
3.查询每门课程都高于平均分的学生所选所有课程SQL语句运用了in和exist两种方法。
*/
declare
--/*
-- 某一门课大于
cursor c is select sname,cname,grade
from sc,student,course
where student.sno=sc.sno and course.cno=sc.cno and
sc.sno in (select sno from sc a
where grade>(select avg(grade) average from sc b where a.cno=b.cno group by cno)); --至少有一门课大于平均成绩
--*/
/*
-- 每门课都大于
-- in写法
cursor c is select sname,cname,grade
from sc,student,course
where student.sno=sc.sno and course.cno=sc.cno and
sc.sno not in (select sno from sc a
where grade<=(select avg(grade) average from sc b where a.cno=b.cno group by cno)) --至少有一门课小于等于平均成绩
*/
-- 每门课都大于
-- exsits写法
/*
cursor c is select sname,cname,grade
from sc x,student,course
where student.sno=x.sno and course.cno=x.cno and
not exists (select * from sc a where x.sno=a.sno and grade<=(select avg(grade) average from sc b where a.cno=b.cno group by cno))
order by student.sno;
*/
sn student.sname%type:='000';
cn course.cname%type;
k decimal(4,1);--sc.grade%type;
snu student.sno%type;
nub int(10);
zumg course.ccredit%type;--总学分
sumg course.ccredit%type;--获得学分
avgr decimal(4,1);
p_avgr decimal(4,1);
--pm int(8);
sd student.sdept%type;
znub int(10);
nkc int(10);--所选课程数
nopass int(10);
cnt int(10):=0;--高于平均分计数
cursor f_ail is select cname into nopass from sc,course where sc.cno = course.cno and sno in (select sno from student where sname=sn) and grade<60;
begin
--dbms_output.put_line('某一门课程成绩高于课程平均分的学生所选的所有课程信息:');
dbms_output.put_line(' ');
dbms_output.put_line(lpad('每门课程成绩高于课程平均分的学生所选的所有课程信息表',66));
for i in c loop
if i.sname!=sn and sn!='000' then
dbms_output.put_line(lpad(' ',12)||rpad('-',68,'-'));
select count(*) into nkc from sc where sno in (select sno from student where sname=sn) group by sno;
--dbms_output.put_line(lpad('选课门数:',30)||nkc);
select sum(ccredit) into zumg from sc,course where sc.cno = course.cno and sno in (select sno from student where sname=sn);
select sum(ccredit) into sumg from sc,course where sc.cno = course.cno and sno in (select sno from student where sname=sn) and grade>=60;
--dbms_output.put_line(lpad('已获得的学分:',30)||sumg);
--select avg(grade) into avgr from sc where sno in (select sno from student where sname=sn);
--dbms_output.put_line(lpad('平均分:',30)||avgr);
select sum(grade*ccredit)/sum(ccredit) into p_avgr from sc,course where sc.cno=course.cno and sno in (select sno from student where sname=sn);
--dbms_output.put_line(lpad('加权平均分:',28)||p_avgr);
select sdept into sd from student where sname=sn;
select distinct(totalrank) into znub from
(select rank() over(order by sum(grade*ccredit)/sum(ccredit) desc) totalrank,sum(grade*ccredit)/sum(ccredit) x_avgr
from sc,course where sc.cno=course.cno and sno in (select sno from student where sdept=sd) group by sno)
where x_avgr=(select sum(grade*ccredit)/sum(ccredit) from sc,course where sc.cno=course.cno and sno in (select sno from student where sname=sn));
dbms_output.put_line(' ');
dbms_output.put_line('数据性评价:'||nkc||'门选课,其中'||cnt||'门高于课程平均分,'||(nkc-cnt)||'门低于课程平均分;');
dbms_output.put_line(rpad(' ',12)||'共获得'||sumg||'学分,加权平均分为'||p_avgr||'分,在所在系('||sd||'系)中排名第'||znub||'名。');
cnt:=0;
dbms_output.put_line(' ');
if sumg=zumg then
dbms_output.put_line('总体评价:该生总体成绩优秀,无挂科。请继续保持努力。');
else
select count(*) into nopass from sc,course where sc.cno = course.cno and sno in (select sno from student where sname=sn) and grade<60;
dbms_output.put_line('总体评价:该生总体成绩良好,有'||nopass||'门挂科。挂科科目为:');
for j in f_ail loop
dbms_output.put_line(lpad(j.cname,58));
end loop;
dbms_output.put_line(lpad('请及时参与补考或重修!',75));
end if;
dbms_output.put_line(' ');
end if;
if i.sname!=sn then
sn:=i.sname;
select sno into snu from student where sname=sn;
dbms_output.put_line(rpad('=',85,'='));
dbms_output.put_line(' ');
dbms_output.put_line(rpad('学生姓名:',10)||i.sname);
dbms_output.put_line(rpad('学号:',6)||snu);
dbms_output.put_line(rpad('-',20,'-'));
dbms_output.put_line(' ');
dbms_output.put_line(lpad(' ',15)||rpad('课程名',11)||rpad('成绩',7)||rpad('课程平均分',12)||rpad('与平均分(±)',15)||'单科排名');
dbms_output.put_line(lpad(' ',12)||rpad('-',68,'-'));
--else
end if;
cn:=i.cname;
select avg(grade) into k from sc where cno in (select cno from course where cname=cn) group by cno;
-- 用rownum发现如果有相同分的情况下不能解决
--select rownum into nub from (select * from sc where cno=(select cno from course where cname=cn) order by grade desc) xh where xh.grade=i.grade;
-- 用rank()函数
select distinct(rank) into nub from
(select rank() over(order by grade desc) rank ,grade from (select * from sc where cno=(select cno from course where cname=cn)))
where grade=i.grade;
if (i.grade-k)>0 then
dbms_output.put_line(lpad(' ',16)||rpad(i.cname,9)||rpad('|',3)||rpad(i.grade,5)||rpad('|',5)||rpad(k,8)||rpad('|',5)||'+'||rpad(i.grade-k,10)||rpad('|',5)||nub);
else
dbms_output.put_line(lpad(' ',16)||rpad(i.cname,9)||rpad('|',3)||rpad(i.grade,5)||rpad('|',5)||rpad(k,8)||rpad('|',5)||'-'||rpad(abs(i.grade-k),10)||rpad('|',5)||nub);
end if;
if i.grade-k>0 then
cnt:=cnt+1;
end if;
--end if;
end loop;
dbms_output.put_line(lpad(' ',12)||rpad('-',68,'-'));
select count(*) into nkc from sc where sno in (select sno from student where sname=sn) group by sno;
--dbms_output.put_line(lpad('选课门数:',30)||nkc);
select sum(ccredit) into zumg from sc,course where sc.cno = course.cno and sno in (select sno from student where sname=sn);
select sum(ccredit) into sumg from sc,course where sc.cno = course.cno and sno in (select sno from student where sname=sn) and grade>=60;
--dbms_output.put_line(lpad('已获得的学分:',30)||sumg);
--select avg(grade) into avgr from sc where sno in (select sno from student where sname=sn);
--dbms_output.put_line(lpad('平均分:',30)||avgr);
select sum(grade*ccredit)/sum(ccredit) into p_avgr from sc,course where sc.cno=course.cno and sno in (select sno from student where sname=sn);
--dbms_output.put_line(lpad('加权平均分:',28)||p_avgr);
select sdept into sd from student where sname=sn;
select distinct(totalrank) into znub from
(select rank() over(order by sum(grade*ccredit)/sum(ccredit) desc) totalrank,sum(grade*ccredit)/sum(ccredit) x_avgr
from sc,course where sc.cno=course.cno and sno in (select sno from student where sdept=sd) group by sno)
where x_avgr=(select sum(grade*ccredit)/sum(ccredit) from sc,course where sc.cno=course.cno and sno in (select sno from student where sname=sn));
dbms_output.put_line(' ');
dbms_output.put_line('数据性评价:'||nkc||'门选课,其中'||cnt||'门高于课程平均分,'||(nkc-cnt)||'门低于课程平均分;');
dbms_output.put_line(rpad(' ',12)||'共获得'||sumg||'学分,加权平均分为'||p_avgr||'分,在所在系('||sd||'系)中排名第'||znub||'名。');
cnt:=0;
dbms_output.put_line(' ');
if sumg=zumg then
dbms_output.put_line('总体评价:该生总体成绩优秀,无挂科。请继续保持努力。');
else
select count(*) into nopass from sc,course where sc.cno = course.cno and sno in (select sno from student where sname=sn) and grade<60;
dbms_output.put_line('总体评价:该生总体成绩良好,有'||nopass||'门挂科。挂科科目为:');
for j in f_ail loop
dbms_output.put_line(lpad(j.cname,58));
end loop;
dbms_output.put_line(lpad('请及时参与补考或重修!',75));
end if;
dbms_output.put_line(' ');
dbms_output.put_line(' ');
dbms_output.put_line(rpad('=',95,'='));
end;
输出样例:
- 无挂科
2.挂科提醒
5.使用游标实现:将每门课程成绩高于课程平均分的学生所选的所有课程的姓名,课程名,成绩格式化输出。
-- Created on 2020/11/16
-- 代码同某一门时相同,只是注释块不同,此处不再赘述
输出样例: