Can someone help me on Cursor For Loops?!?!?!

[Jplaya2023]

declare
cursor inventory_cur is
select * from movie_table
for update;
begin
for stk_rec in inventory_cur loop
if stk_rec.stk_flag > 75 then
update movie_table
set stk_flag = whatever
where current of inventory_cur;
-- you can add in the else branch
end loop;
end;
/

Thanks alot but its still not compiling it correctly.

This is what i typed


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 SET STK_FLAG = 'NULL'
WHERE CURRENT OF STK_CURSOR;
END IF;
END LOOP;
END;
/

And these are the errors its telling me.



FOR UPDATE NOWAIT;
*

ERROR at line 4:
ORA-06550: line 11, column 10:
PL/SQL: ORA-00922: missing or invalid option
ORA-06550: line 11, column 6:
PL/SQL: SQL Statement ignored

[gandolf989]

This is a stupid assignment. 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.

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;
/

[Jplaya2023]

This is a stupid assignment. 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.

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;
/

I agree about the assignment, there are so many more efficient ways of doing this it's mind boggling. I think they want students to be familiar with different ways of setting up condition statements so they can be "real world" ready after graduating. To make things worse this is an online class, so i dont even get to sit and chat with the teacher unless its through email or any other type of communication medium like that.

I appreciate all of the help everyone has given me on this, its greatly appreciated.

I want the value to be null not the word null.

I ran the program it works, but nothing is being outputted to the stk_flag column

[gandolf989]

Use dbms_output.put_line to print out values and see what your program is doing.
Make sure that you put DBMS_OUTPUT.ENABLE(1000000); as the first line after the begin statement.

[Jplaya2023]

Use dbms_output.put_line to print out values and see what your program is doing.
Make sure that you put DBMS_OUTPUT.ENABLE(1000000); as the first line after the begin statement.

I'm using SQL+ and it doesnt like the commands you told me to to enter after the begin statement.

[gandolf989]

I'm using SQL+ and it doesnt like the commands you told me to to enter after the begin statement.

I'm not sure what you mean.

DECLARE
   CURSOR STK_CURSOR IS
      SELECT * FROM MM_MOVIE
         FOR UPDATE NOWAIT;
BEGIN
   DBMS_OUTPUT.ENABLE(1000000); 
   FOR STK_REC IN STK_CURSOR LOOP
      IF STK_REC.STK_FLAG > 75 THEN
         dbms_output.put_line ('STK_REC.STK_FLAG > 75 - ' || STK_REC.STK_FLAG);
         UPDATE MM_MOVIE
            SET STK_FLAG = '*'
          WHERE CURRENT OF STK_CURSOR;
         dbms_output.put_line ('Rows updated: ' || SQL%ROWCOUNT);
      ELSE 
         dbms_output.put_line ('STK_REC.STK_FLAG <= 75 - ' || STK_REC.STK_FLAG);
         UPDATE MM_MOVIE
            SET STK_FLAG = NULL
          WHERE CURRENT OF STK_CURSOR;
         dbms_output.put_line ('Rows updated: ' || SQL%ROWCOUNT);
      END IF;
   END LOOP;

   COMMIT;
END;
/

[Jplaya2023]

This is what's happening, I created mt column, and did my calculations so it shows the updated columns, this is the result



Then i ran the syntax you had gave me, and my column just shows a blank now on a select

[gandolf989]

Try this instead. You may want to use sqlplus if you have it.

set serveroutput on

DECLARE
   CURSOR STK_CURSOR IS
      SELECT movie_id, 
             movie_value*movie_qty as movies_in_stock 
        FROM MM_MOVIE;
BEGIN
   DBMS_OUTPUT.ENABLE(1000000); 
   FOR STK_REC IN STK_CURSOR 
   LOOP
      IF STK_REC.movies_in_stock > 75 THEN
         dbms_output.put_line ('STK_REC.movies_in_stock > 75 - ' 
                             || STK_REC.movies_in_stock);
         UPDATE MM_MOVIE
            SET STK_FLAG = '*'
          WHERE movie_id = STK_REC.movie_id;
         dbms_output.put_line ('Rows updated: ' || SQL%ROWCOUNT);
      ELSE 
         dbms_output.put_line ('STK_REC.movies_in_stock <= 75 - ' 
                             || STK_REC.movies_in_stock);
         UPDATE MM_MOVIE
            SET STK_FLAG = STK_REC.movies_in_stock
          WHERE movie_id = STK_REC.movie_id;
         dbms_output.put_line ('Rows updated: ' || SQL%ROWCOUNT);
      END IF;
   END LOOP;

   COMMIT;
END;
/

[Jplaya2023]

[gandolf989]

What kind of field is stk_flag? I would guess that it is a number field. In which case you can't set it to '*'.