I need to check whether std_id is present same as in students table if this matches with std_id in std_grace_marks then I need to add that grace_marks of that std_id with marks column in students table.

I have created a procedure and created two cursors to fetch records in loop and I wrote if condition to check whether std_id matches if it is then I am adding marks with grace_marks but if condition is not working here... so please can anyone tell where I am going wrong

My code:

create or replace procedure std_info
IS
    CURSOR stdcur IS SELECT std_id,std_name, marks,mark_status FROM students;
    CURSOR gracer IS SELECT std_id,grace_marks from student_grace_marks;
    myvar  stdcur%ROWTYPE;
    mycur gracer%ROWTYPE;

BEGIN
    OPEN stdcur;
    OPEN gracer;

    LOOP
        FETCH stdcur INTO myvar;
        FETCH gracer INTO mycur;
        EXIT WHEN stdcur%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE( myvar.std_id || '  '|| myvar.std_name||' '||myvar.marks );

        if(myvar.std_id=mycur.std_id) then
            update students set marks=myvar.marks mycur.grace_marks;
        end if;

    END LOOP;

    CLOSE stdcur;
    CLOSE gracer;
END;

CodePudding user response：

I'm not sure this will ever work. You're fetching a row of each resultset in a row variable, assuming that row 1 for the first resultset will match the std_id of row 1 for the 2nd resultset. There are 2 things wrong with this assumption

1. There is no ORDER BY clause in the statements, so std_id for the firs row of cursor stdcur could be 10 while std_id for the first row of cursor gracer could be 99. Oracle does not guarantee the order of the a resultset unless an ORDER BY clause is included in the statement.
2. If table student_grace_marks has more than 1 row, or no rows for a std_id, it will start failing from that row onwards since the count will no longer match.

One solution is to use explicit cursor for loops:

DECLARE
BEGIN
  FOR r_student IN (SELECT * FROM students) LOOP
    FOR r_student_gm IN (SELECT * FROM student_grace_marks WHERE std_id = r_student.std_id) LOOP
      UPDATE students SET marks = marks   r_student_gm.grace_marks WHERE std_id = r_student.std_id;
    END LOOP;
  END LOOP;
END;
/

.. or .. if you want to use explicit cursors:

In this case the CURSOR gracer will have a where clause to only select the relevant row(s) for that particular student.

Note that I fixed some errors as well

DECLARE
    --declare variable to be used in the where clause of the select for cursor gracer.
    l_std_id students.std_id%TYPE;
    CURSOR stdcur IS SELECT std_id,std_name, marks FROM students;
    CURSOR gracer IS SELECT std_id,grace_marks from student_grace_marks WHERE std_id = l_std_id;
    myvar  stdcur%ROWTYPE;
    mycur gracer%ROWTYPE;
BEGIN
   OPEN stdcur;
   LOOP
        FETCH stdcur INTO myvar;
        EXIT WHEN stdcur%NOTFOUND;
        l_std_id :=  myvar.std_id;
        OPEN gracer;
        LOOP
           FETCH gracer INTO mycur;
           EXIT WHEN gracer%NOTFOUND;
           -- use marks, not myvar.marks. If there is >1 record in student_grace_marks it will only add the last value.
           -- add the where clause or every update will update all rows and every grace_marks will be added for every student in the table.
           UPDATE students set marks= marks mycur.grace_marks WHERE std_id = l_std_id;
        END LOOP;
        CLOSE gracer;   
   END LOOP;
   CLOSE stdcur;
END;
/