华南理工 数据库 实验三 代码

华南理工 数据库 实验三 代码

实验要求：
1． 采用实验一的建库脚本和数据插入脚本创建Student数据库。
2．在数据库中创建以下存储过程：

1. Add_Student (SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT)
   要求：根据输入参数，插入一条学生记录。
2. Upd_Grade (SNO, CNO, GRADE)
   要求：根据输入参数，修改某学生选课的成绩。
3. Disp_Student (SNO，SUM_CREDIT output，AVG_GRADE output)
   要求：根据SNO参数显示该学生的有关信息，包括：
   a)学号，姓名，性别，年龄，身高，系别，所有选修的课程及成绩；
   b)显示输出参数SUM_CREDIT(表示选修课程的总学分)及AVG_GRADE (表示3学分以上的课程的平均成绩)。
4. CAL_GPA (SNO，GPA output)
   要求：根据SNO参数, 输出并显示该学生的GPA值。计算方法如下：
   GRADE(G) GRADEPOINT(GP)
   G>=85 4
   85>G>=75 3
   75>G>=60 2
   60>G 1
   GPA= (∑GP*CREDIT)/ ∑CREDIT)

代码：

create table SC
(SNO varchar(100),
CNO varchar(100),
GRADE int
);

create table Students(
SNO varchar(100),
SNAME varchar(100),
SEX varchar(10),
BIRTHDAY DATE,
HEIGHT decimal,
DEPT varchar(100)
);

create table Courses(
CNO varchar(100),
CNAME varchar(100),
LHOUR varchar(100),
CREDIT float,
SEMESTER varchar(10)
);

create table credits
(sno varchar(100),
sumcredit int,nopass int);

create or replace trigger upd_credit
after insert
on sc
for each row
declare sss number:=0;
begin
select count(sno) into sss from credits where credits.sno=:new.sno;
if(sss=0)
then
insert into credits values(:new.sno,0,0);
end if;
if(60<=:new.grade)
then
update credits set sumcredit=sumcredit+(select credit from courses where courses.cno=:new.cno) where credits.sno=:new.sno;
else
update credits set nopass=nopass+1 where credits.sno=:new.sno;
end if;
end;
/

1)Add_Student
create or replace procedure Add_Student(aSNO varchar,aSNAME varchar,aSEX varchar,aBIRTHDAY date,aHEIGHT decimal,aDEPT varchar)
as
begin
insert into Students(SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT) values(aSNO,aSNAME,aSEX,aBIRTHDAY,aHEIGHT,aDEPT);
commit;
end;
/
set serveroutput on;
exec procedure Add_Student(‘201730621231’,‘林影响’,‘女’,TO_DATE(‘1988-5-6’,‘YYYY-MM-DD’),1.70,‘计算机系’);

2)Upd_Grade
create or replace procedure Upd_Grade(updSNO varchar,updCNO varchar,updGRADE INT)
as
begin
update SC
set SNO=updSNO,
CNO=updCNO,
GRADE=updGRADE
where sno=updSNO and CNO=updCNO;
commit;
end;
/

3)Disp_Student
create or replace procedure Disp_Student(aaSNO varchar)
is
sum_credit int;
avg_grade int;
cursor pl_cursor is
select students.sno,students.sname,students.sex,students.birthday,students.height,students.dept,sc.cno,sc.grade
from students
inner join sc on sc.sno=students.sno
inner join Courses on SC.CNO=Courses.CNO
where students.SNO=aaSNO;
c_row pl_cursor%rowtype;
begin
open pl_cursor;
loop
fetch pl_cursor
into c_row;
exit when pl_cursor%notfound;
dbms_output.put_line(c_row.sno||’ ‘||c_row.sname||’ ‘||c_row.sex||’ ‘||to_char(c_row.birthday)||’ ‘||c_row.height||’ ‘||c_row.dept||’ ‘||c_row.cno||’ '||c_row.grade);
end loop;
close pl_cursor;
select sumcredit into sum_credit from credits
where credits.sno=aaSNO;
select avg(sc.grade) into avg_grade from students
inner join SC on SC.SNO=students.SNO
where students.SNO=aaSNO and SC.GRADE>3
group by students.SNO;
dbms_output.put_line(‘sum_credit:’||sum_credit);
dbms_output.put_line(‘avg_grade:’||avg_grade);
end;
/

4)CAL_GPA
create or replace procedure CAL_GPA(aaSNO varchar)
is
GPA number(3,2);
SUM_CREDIT int;
AVG_GRADE number(3,2);
begin
select SumCredit into SUM_CREDIT from Credits where SNO=aaSNO;
select avg(case
when SC.GRADE>=85 THEN 4
WHEN SC.GRADE<85 AND SC.GRADE>=75 THEN 3
WHEN SC.GRADE<75 AND SC.GRADE>=60 THEN 2
WHEN SC.GRADE<60 THEN 1 END) into AVG_GRADE from Students s
inner join SC on SC.SNO=s.SNO
where s.SNO=aaSNO and SC.GRADE>3
group by s.SNO;
GPA:=AVG_GRADE;
dbms_output.put_line(‘gpa:’||GPA);
end;
/