PL/SQL Cursor 2 – Update value in the same table

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;

/


 

You can leave a response, or trackback from your own site.

Leave a Reply