Can someone help me on Cursor For Loops?!?!?! - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

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

  1. #11
    Join Date
    Mar 2006
    Posts
    11
    Quote Originally Posted by stecal
    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

  2. #12
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.

    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;
    /
    this space intentionally left blank

  3. #13
    Join Date
    Mar 2006
    Posts
    11
    Quote Originally Posted by 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.

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

    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
    Last edited by Jplaya2023; 03-17-2006 at 12:12 AM.

  4. #14
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.
    this space intentionally left blank

  5. #15
    Join Date
    Mar 2006
    Posts
    11
    Quote Originally Posted by 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.

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

  6. #16
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Quote Originally Posted by Jplaya2023
    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.

    Code:
    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;
    /
    this space intentionally left blank

  7. #17
    Join Date
    Mar 2006
    Posts
    11
    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



  8. #18
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Try this instead. You may want to use sqlplus if you have it.

    Code:
    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;
    /
    this space intentionally left blank

  9. #19
    Join Date
    Mar 2006
    Posts
    11

  10. #20
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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 '*'.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width