#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    54
    Rep Power
    11

    WHILE LOOP and stored procedure


    hi, i have a stored procedure, pls review it:

    DECLARE
    control NUMBER;
    cnt NUMBER;

    BEGIN
    control := 1;
    cnt := 0;

    WHILE control=1 LOOP
    BEGIN
    SELECT numb INTO tempNumb FROM temp;
    cnt := cnt + 1;

    IF tempNumb != 10 AND cnt <= 5 THEN
    DBMS_LOCK.sleep(60);
    ELSE
    control := 0;
    END IF;
    END;
    END LOOP;
    END;


    There is only one row in temp table with numb column, value=1.

    The loop will run 5 times if the numb column's value not equal to 10. Each time will delay 60 seconds.


    So, i run the stored procedure. When it runs 3rd loop and during delay, i change the temp table numb column to 10, and commit.


    However, the stored procedure still run until 5 times and then end the loop.

    My question is: Why the stored procedure cannt read the latest value from temp table, which is 10 by now?

    Thanks,
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    54
    Rep Power
    11
    Hi,

    Does anyone try the code above? Anyone know what and why it happen?

IMN logo majestic logo threadwatch logo seochat tools logo