A student table has column names as given below :-
STUDENT_NO INTEGER(3)
STUDENT_NAME VARCHAR2(20)
TOTAL_MARKS NUMBER(6,2)
REMARKS VARCHAR2(25)
And another table, Marks, contains the following columns :-
STUDENT_NO INTEGER(3)
MARK1 NUMBER(5,2)
MARK2 NUMBER(5,2)
MARK3 NUMBER(5,2)
Read one record at a time and find the total marks of each student and update the total marks in Student table. If total_marks is greater than or equal to 150, update the remarks with ‘Passed’, oth erwise raise an exception and update remarks with ‘Failed’.
Answer
declare
marks_row student1_marks_a5518%rowtype;
cursor cur1 is select * from student1_marks_a5518;
total number(4);
begin
open cur1;
loop
fetch cur1 into marks_row;
exit when cur1%notfound;
total:=marks_row.mark1+marks_row.mark2+marks_row.mark3;
update student1_a5518 set total_marks=total where student_no=marks_row.student_no;
if(total>=150) then
update student1_a5518 set remarks='Passed' where student_no=marks_row.student_no;
else
update student1_a5518 set remarks='Failed' where student_no=marks_row.student_no;
end if;
end loop;
close cur1;
end;
/