Need help understanding Cursor for loop

Question

I am trying to write a code for every stock value that is $75 or more add a "*" in the STK_FLAG column. my error repots are: Error report -

ORA-06550: line 15, column 21: PLS-00201: identifier 'STK_FLG' must be declared ORA-06550: line 15, column 5: PL/SQL: SQL Statement ignored ORA-06550: line 23, column 7: PL/SQL: ORA-00904: "STK_FLG": invalid identifier ORA-06550: line 17, column 5: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Blockquote

SET SERVEROUTPUT ON

DECLARE

CURSOR CURR
   IS
     SELECT STK_FLAG
     FROM MM_MOVIE
     WHERE MOVIE_VALUE * MOVIE_QTY >= 75
     FOR UPDATE;
BEGIN

OPEN CURR;
   
  LOOP

    FETCH CURR INTO STK_FLG;

    UPDATE

      MM_MOVIE

    SET

      STK_FLG= '*'

    WHERE

      CURRENT OF CURR;

    EXIT

  WHEN CURR%notfound;

  END LOOP;

  Commit;

   CLOSE CURR;
END;
/

Answer 1

You didn't declare a cursor variable (so there's nowhere you can put values returned by the cursor). Don't name it as column name; use a prefix, such as v_ or l_ or whichever you want.

Furthermore, in UPDATE you referenced a non-existent column. Cursor suggests its name is stk_flag, not stk_flg

Therefore, code that might be OK is

DECLARE
   CURSOR curr IS
      SELECT stk_flag
        FROM mm_movie
       WHERE movie_value * movie_qty >= 75
      FOR UPDATE;

   l_stk_flag  mm_movie.stk_flag%TYPE;  --> this
BEGIN
   OPEN curr;

   LOOP
      FETCH curr INTO l_stk_flag;

      EXIT WHEN curr%NOTFOUND;

      UPDATE mm_movie
         SET stk_flag = '*'             --> this
       WHERE CURRENT OF curr;
   END LOOP;

   COMMIT;

   CLOSE curr;
END;
/

Answer 2

Why use a pl/sql anonymous block? Even if there is an "external" requirement for the functionality wrapped into pl/sql why use a cursor and loop? Using code that might be OK (from @Littlefoot) you retrieve a single column meeting your condition, iterate the resulting record set fetching that column but otherwise do nothing with it, and update a single row on every iteration of the loop with a literal value. SQL is designed for processing entire sets of rows at a time. Your processing can be done in a single update statement. Assuming there is an external requirement for a pl/sql block your code reduces to:

BEGIN
   UPDATE mm_movie
      SET stk_flag = '*'   
    WHERE movie_value * movie_qty >= 75;
    
   COMMIT;
END;   

Take away: When working with SQL stop thinking in terms of iterating (loops). Instead think of the commonality (set) of all objects to be processed. A different way of looking at problems and their corresponding solutions to be sure. Getting used to thinking that way will take some time, but in the long run your SQL and procedures will greatly improve because of it. Both in performance and clarity.