以下是我编写的一个触发器和一个函数,在编译时候都没有出错误,用触发器调用函数,其中在触发器里的insert 和 update 不能成功调用函数 ,但delete部分可以正常运行,所以触发器应该没有问题的.
现在怀疑是"GetPrize(vn_sno)"这部分出了问题,但不知道到如何改进,请各位高手帮帮忙吧!小弟急着要用.
--------------------------------触发器-----------------------------------------
Create OR Replace Trigger Tr_Ins_Upd_Del
After Insert OR Update OR Delete On CKLang.Grade
For Each Row
DECLARE
vn_sno Char(6);
BEGIN
Case
When Inserting Then
vn_sno:=:New.Sno;
Insert Into CKLang.Scholarship Values (vn_sno,GetPrize(vn_sno));
When Updating Then
vn_sno:=:New.Sno;
Update CKLang.Scholarship Set Sno=vn_sno Where Trim(Sno)=:Old.Sno;
Update CKLang.Scholarship Set Sprize=GetPrize(vn_sno) Where Trim(Sno)=vn_sno;
When Deleting Then
Delete From CKLang.Scholarship Where Sno=:Old.Sno;
End Case;
EXCEPTION
When OTHERS Then
Null;
END;
---------------------------------------------函数-------------------------------------------------
Create OR Replace Function GetPrize(vg_sno in char)
Return Varchar2
Is
v_prize Varchar2(8);
v_avg Number(5,2);
v_sum Number(5,2);
v_count Number;
v_grade CKLang.Grade.Grade%type;--临时存储一个学生成绩
Cursor c_grade is Select Grade From CKLang.Grade Where Trim(Sno)=vg_sno; --游标c_grade存放指定的学生成绩
BEGIN
v_avg:=0;
v_sum:=0;
Open c_grade;
Fetch c_grade Into v_grade;
While c_grade%found
Loop
v_sum:=v_sum+v_grade;
Fetch c_grade Into v_grade;
End Loop;
Close c_grade;
Select Count(Grade) Into v_count From CKLang.Grade Where Trim(Sno)=vg_sno; --统计出该学生的考试科目数
v_avg:=v_sum/v_count; --计算学生平均成绩
If v_avg>=95 Then --判断获奖层次
v_prize:='特等奖';
Elsif v_avg>=90 Then
v_prize:='一等奖';
Elsif v_avg>=85 Then
v_prize:='二等奖';
Elsif v_avg>=80 Then
v_prize:='三等奖';
Else
v_prize:='NULL';
End If;
Return v_prize;
EXCEPTION
When NO_DATA_FOUND Then
Rollback;
v_prize:='Error';
Return v_prize;
When ZERO_DIVIDE Then
Rollback;
v_prize:='Error';
Return v_prize;
When OTHERS Then
Null;
END;