数据库编程
1.基于student、course、teacher、sc和tc表完成以下存储过程和触发器的编写。
(1)编写PL/SQL程序,计算每个学生的学分成绩,学分成绩计算公式为:
根据学分成绩输出该学生的专业奖学金等级,输出函数为dbms_output.put_line()。专业奖学金评定方法为:
- 学分成绩等于或高于90为一等专业奖学金;
- 学分成绩等于或高于85并且低于90为二等专业奖学金;
- 学分成绩等于或高于80并且低于85为三等专业奖学金;
- 低于80分未获得专业奖学金。
正确版本:
declarev_sno sc.sno%type;v_grade sc.grade%type;v_credit course.credit%type;v_sum_credit course.credit%type:=0;v_credit_grade sc.grade%type:=0;v_credit_grade2 number(5,2):=0;v_credit_grade3 sc.grade%type:=0;--对每个学生的遍历cursor c1 isselect sno,sum(credit)from sc,coursewhere sc.cno=course.cnogroup by sno;
--对某个学生的学分成绩求解cursor c2(psno sc.sno%type) isselect sno,grade,creditfrom sc,coursewhere sc.cno=course.cno and sno=psno;
--开始
beginopen c1;loopfetch c1 into v_sno,v_sum_credit;exit when c1%notfound;open c2(psno=>v_sno);loopfetch c2 into v_sno,v_grade,v_credit;exit when c2%notfound;v_credit_grade:=v_credit_grade+v_grade*v_credit;--对每个科目学分和成绩相乘的累积end loop;close c2;v_credit_grade3:= v_credit_grade;--输出总的 学分和成绩相乘的累积和(分子)v_credit_grade2:=v_credit_grade/v_sum_credit; --计算最终的学分成绩if(v_credit_grade2>=90) thendbms_output.put_line(v_sno||'获得一等奖学金');elsif(v_credit_grade2>=85) thendbms_output.put_line(v_sno||'获得二等奖学金');elsif(v_credit_grade2>=80) thendbms_output.put_line(v_sno||'获得三等奖学金');elsedbms_output.put_line(v_sno||'未获得奖学金');end if;v_credit_grade:=0; //注意这一步!!!end loop;close c1;
end;
执行结果(为方便观看,没有输出未获得奖学金的同学)
Fail 留存
//实习过程中出错的版本
//相关出错问题在代码后提出declarev_sno sc.sno%type;v_grade sc.grade%type;v_credit course.credit%type;v_sum_credit course.credit%type:=0;v_credit_grade sc.grade%type:=0;v_credit_grade2 number(4,2);v_credit_grade3 sc.grade%type:=0;--对每个学生的遍历cursor c1 isselect sno,sum(credit)from sc,coursewhere sc.cno=course.cnogroup by sno;
--对某个学生的学分成绩求解cursor c2(psno sc.sno%type) isselect sno,grade,creditfrom sc,coursewhere sc.cno=course.cno and sno=psno;
--开始
beginopen c1;loopfetch c1 into v_sno,v_sum_credit;exit when c1%notfound;open c2(psno=>v_sno);loopfetch c2 into v_sno,v_grade,v_credit;exit when c2%notfound;v_credit_grade:=v_credit_grade+v_grade*v_credit;--对每个科目学分和成绩相乘的累积-- if(v_sno='105573')then --对'105573'的测试-- dbms_output.put_line(v_sno||','||v_grade||','||v_credit||','||v_credit_grade);-- end if;end loop;close c2;v_credit_grade3:= v_credit_grade;--输出总的 学分和成绩相乘的累积和(分子)v_credit_grade:=v_credit_grade/v_sum_credit; --计算最终的学分成绩v_credit_grade2:=v_credit_grade; --(最终学分成绩)保留两位小数 防止溢出--dbms_output.put_line(v_sno||','||v_credit_grade2);if(v_credit_grade2>=90) thendbms_output.put_line(v_sno||'获得一等奖学金');elsif(v_credit_grade2>=85) thendbms_output.put_line(v_sno||'获得二等奖学金');elsif(v_credit_grade2>=80) thendbms_output.put_line(v_sno||'获得三等奖学金');--dbms_output.put_line(v_sno||','||v_credit_grade3);--dbms_output.put_line(v_sno||','||v_credit_grade2);dbms_output.put_line(v_sno||','||v_sum_credit);--所有科目的总的学分(分母)--else--dbms_output.put_line(v_sno||'未获得奖学金');end if;end loop;close c1;
end;
问题:
【因为代码输出仅仅只是显示了几个学生获得了三等奖学金,我觉得有错误,所有我针对学生'105573'进行检验,输出结果如下:】
【用以下的语句来输出该生所有的成绩和学分信息】
select sno,sc.cno,credit,gradefrom sc,coursewhere sc.cno=course.cno and sno='105573';
【然后对原代码进行输出检验】
其中测试语句dbms_output.put_line(v_sno||','||v_sum_credit);输出的该学生的总学分为38分正确,没有问题;
【然后对每次累加的学生成绩和学分乘积进行输出检验】
if(v_sno='105573')then --对'105573'的测试dbms_output.put_line(v_sno||', '||v_grade||', '||v_credit||', '||v_credit_grade);end if;
通过以上语句对每次进行输出,但是第一个98*3.5本应该是343,但是输出显示错误,之后的每一次在第一次的基础上累计错误;于是我觉得可能是原来course表里的credit是varchar2类型的问题,然后把它更改成number(4,2),但是依旧没有变化,仍然是图4;修改course里面的credit的代码如下:
//因为修改表格的列名要求该列内容为空,所以以下代码为修改非空列名/*修改原字段名credit为credit_tmp*/alter table course rename column credit to credit_tmp;/*增加一个和原字段名同名的字段credit*/alter table course add credit number(4,2);/*将原字段credit_tmp数据更新到增加的字段credit*/update course set credit=trim(credit_tmp);/*更新完,删除原字段credit_tmp*/alter table course drop column credit_tmp;
注意:
①对于用于计算的变量要赋初始值,否则系统会给定任意值,影响计算结果;
②一般游标在使用的时候不会使用参数列表,但是在求解学分成绩的过程中使用游标时使用了参数,所以要熟悉参数的含义和合理使用;
③在变量计算过程中,变量的类型需要保持一致,否则容易出现错误;
④在程序输出出错时,可以通过输出函数对计算的流程检错;
(2)创建存储过程,根据调用时提供的课程号查询选修该课程的学生学号、姓名和课程成绩,将查询结果在过程体中输出,输出函数为dbms_output.put_line()。
语句内容:
create or replace procedure p1(v_cno in sc.cno%type) is
--声明变量
v_sno student.sno%type;
v_sname student.sname%type;
v_grade sc.grade%type;
--声明游标
cursor c1 is
select student.sno,sname,grade
from student,sc
where student.sno=sc.sno and sc.cno=v_cno;
--查询
begin--打开游标open c1;--循环loop--取出游标内容 fetch c1 into v_sno,v_sname,v_grade;--循环退出条件exit when c1%notfound;--对每次取出的内容输出dbms_output.put_line(v_sno||','||v_sname||','||v_grade);end loop;--循环结束 close c1; --关闭游标
end p1;--Sql窗口调用存储过程
beginp1('2091109');
end;
验证语句:
select count(student.sno)
from sc,student
where student.sno = sc.sno and cno='2091109';
(3)创建存储过程,根据调用时提供的学生姓名,查询该学生所有课程的平均成绩,平均成绩的计算结果保留2位小数,将查询结果用输出参数传递给主程序,在SQL窗口中调用存储过程。
语句内容:
create or replace procedure p2(v_sname in student.sname%type, v_avgrade out sc.grade%type) is
beginselect avg(grade)into v_avgradefrom student,scwhere student.sname=v_sname and student.sno=sc.sno;
end p2;
--sql调用存储过程
declare
v_avgrade number(5,2);
beginp2('刘涛',v_avgrade);dbms_output.put_line(v_avgrade);
end;
验证语句:
select avg(grade)
from student,sc
where sname='刘涛' and student.sno=sc.sno;
(4)① 在SC关系中增加新属性列GPA,用来记录课程成绩的绩点。
alter table sc add gpa number(5,2);
② 当在SC表中插入元组和修改grade列的值时,用触发器实现自动记录和修改该门课程成绩的绩点值,课程成绩转换为绩点分值规则如下表:
③ 在SC表中录入和修改数据对触发器进行验证。
insert into sc(sno,cno,grade) values('105617','3094217',100)
update sc set grade=78 where sno='105617' and cno='3094217'
--先添加gpa属性列
alter table sc add gpa number(5,2);
--插入一行元组
insert into sc(sno,cno,grade) values('105617','3094217',100);
--验证insert
select *
from sc
where sno='105617' and cno='3094217';--更新元组数据
update sc set grade=78 where sno='105617' and cno='3094217';
--验证update
select *
from sc
where sno='105617' and cno='3094217';
2.基于S、P、J和SPJ表完成以下存储过程和触发器的编写。
(1)创建存储过程,实现将一行元组插入到s表中,要插入的数据由输入参数传入到过程体。
create or replace procedure p3(v_sno in s.sno%type,v_sname in s.sname%type, v_city in s.city%type,v_sphone in s.sphone%type) is
begininsert into s values(v_sno,v_sname,v_city,v_sphone);commit;
end p3;
--sql窗口调用存储过程
beginp3('s7','茶话弄','西安','021-6666666');
end;
验证语句:
select *
from s;
(2)创建存储过程,实现将某个供应商供应给某个工程的某个零件增加指定的供应量,供应商号、零件号、工程号以及要增加的供应量在调用存储过程时传递给过程体。
create or replace procedure p4(v_sno spj.sno%type,v_pno spj.pno%type,v_jno spj.jno%type,v_qty spj.qty%type) is
beginupdate spj set qty=qty+v_qtywhere spj.sno=v_sno and spj.pno=v_pno and spj.jno=v_jno;
end p4;
--调用语句
beginp4('s1','p2','j2','100');
end;
验证语句:
select *
from spj
where sno='s1' and pno='p2' and jno='j2';
(3)创建触发器实现如下功能:
当对spj表的供应量qty属性值进行修改时,若供应量的变化范围超过10%,自动将修改的数据情况记录到另一个表spj_tra(sno,pno,jno,b_qty,a_qty)中,其中sno、pno、jno是被修改的元组的供应商号、零件号和工程号,b_qty是修改前的供应量,a_qty是修改后的供应量。
要求:先创建spj_tra表,再创建触发器,最后修改spj表中qty的值对触发器进行验证。
--建表语句
create table spj_tra
(sno varchar2(10),
pno varchar2(10),
jno varchar2(10),
b_qty number,
a_qty number);
--创建触发器语句
create or replace trigger t2after update on spjfor each row
declare-- local variables herex number;
beginx := abs(:new.qty-:old.qty)/:old.qty;if x >0.1 theninsert into spj_tra(sno,pno,jno,b_qty,a_qty)values(:new.sno,:new.pno,:new.jno,:old.qty,:new.qty);end if;
end t2;
--触发器激活
update spj
set qty=300 --原来是200
where sno='s1' and pno='p2' and jno='j2';
commit;
--验证
select *
from spj_tra
where sno='s1' and pno='p2' and jno='j2';
(4)当对spj表的供应量qty属性值进行修改时,若供应量的变化范围超过10%(注意:变化有可能是增加,也有可能是减少),自动将修改的数据情况记录到另一个表spj_tra(sno,pno,jno,before_qty,after_qty,update_time)中。
Spj_tra表各属性列含义为:sno、pno、jno是被修改的元组的供应商号、零件号和工程项目号,before_qty是修改前的供应量,after_qty是修改后的供应量,update_time是修改时间,此列值通过使用sysdate函数获取系统当前日期插入。
要求:先创建spj_tra表,再创建触发器,最后修改spj表中qty的值对触发器进行验证。
语句内容:
--建表语句
create table spj_tra
(sno varchar2(10),
pno varchar2(10),
jno varchar2(10),
before_qty number,
after_qty number,
update_time date);
--创建触发器语句
create or replace trigger t2after update on spjfor each row
declare-- local variables herex number;--存储变化的范围
beginx := abs(:new.qty-:old.qty)/:old.qty;if x >0.1 theninsert into spj_tra(sno,pno,jno,before_qty,after_qty,update_time)values(:new.sno,:new.pno,:new.jno,:old.qty,:new.qty,sysdate());end if;
end t2;
--触发器激活
update spj
set qty=300 --原来是200
where sno='s1' and pno='p2' and jno='j2';
commit;
--验证
select *
from spj_tra
where sno='s1' and pno='p2' and jno='j2';
//好好好!最后一场酣畅淋漓的实验~End!