CODEWITHSUNDEEP
sqloracle-database
Elio Rahy
Elio Rahy

Reputation: 29

PL/SQL cursor fetch and loop UPDATE returning last row result

I got 3 tables:

BOOK(bookid primary,nbloans)
COPIES(copyid primary,bookid REFERENCES BOOK bookid)
BORROW(bid,copyid REFERENCES COPIES COPYID)

I need to set nbloans value to all borrowed books so i did this procedure:

create or replace procedure procedure1
IS
cursor c1 is
SELECT BOOK.BOOKID AS Books,COUNT(*) AS countloans
FROM BORROW,COPIES,BOOK
WHERE BORROW.COPYID=COPIES.COPYID AND COPIES.BOOKID=BOOK.BOOKID
group by BOOK.BOOKID;
rec1 c1%ROWTYPE;
v_rows number;
BEGIN
OPEN c1;
LOOP
    FETCH c1 into rec1;
    EXIT WHEN c1%notfound;
    UPDATE BOOK SET NBLOANS=rec1.countloans;
    END LOOP;
IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('0 rows changed');
    ELSIF SQL%FOUND THEN
    v_rows:=SQL%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE(v_rows||' rows changed');
END IF;
END;

When I tried the SELECT query alone, it showed me every book how many loans and the result was:

BOOKS COUNTLOANS
137   5
 81   1
138   2

That result is correct because I checked the tables. But when I do it in a procedure with cursor and loop fetch, all nbloans were set to 2 (Noticed it was the last value) Why would that happen?

Upvotes: 0

Views: 558

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Your UPDATE statement is:

UPDATE BOOK SET NBLOANS=rec1.countloans;

This will set the value of NBLOANS to rec1.countloans on every row in the table.

Perhaps you meant

UPDATE BOOK
  SET NBLOANS = rec1.countloans
  WHERE BOOKID = rec1.Books;

Upvotes: 1

Related Questions