数据库编程

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!