This is a stupid assignment. :rolleyes: What about writing a
trigger that flags when someone is trying to cause a negative
inventory? What about creating a package that contains all of your
business logic? i.e. adding inventory, selling inventory, discounting
inventory based on what isn't selling. What about writing a logging
and error handling package? There are a lot of better projects that
you could be working on besides this.
--- end of rant ---
You forgot the update
in the second update
statement. You should have a commit in your code. Do you
want the word NULL or the value NULL. If you put it in quotes
you will get the word NULL rather than the value NULL. You are
also corrupting your data by setting it to '*'. You should have
an overstock flag that gets set and unset as inventory is added
and removed. This flag should probably get set through a trigger.
Code:
DECLARE
CURSOR STK_CURSOR IS
SELECT * FROM MM_MOVIE
FOR UPDATE NOWAIT;
BEGIN
FOR STK_REC IN STK_CURSOR LOOP
IF STK_REC.STK_FLAG > 75 THEN
UPDATE MM_MOVIE
SET STK_FLAG = '*'
WHERE CURRENT OF STK_CURSOR;
ELSE
UPDATE MM_MOVIE
SET STK_FLAG = NULL
WHERE CURRENT OF STK_CURSOR;
END IF;
END LOOP;
COMMIT;
END;
/