title: Dynamic Views in Mysql Stored Procedure does not update
body: <p>I have a table containing 100,000+ records from which i have some calculations to do based on a value which is stored in a specific column.</p>
  
  <p>For this i have written a stored procedure which takes 100 rows at a time from the base table. Since cursors don't support dynamic sql. What i did was i create a dynamic view and the cursor always selects data from that view.</p>
  
  <p>The code :</p>
  
  <pre><code>BEGIN
  -- VARIABLES TO RETURN
  DECLARE ORIG_COUNT INT ;
  DECLARE SERVED_COUNT INT;
  
  -- VARIABLED FOR LOOPING
  DECLARE no_more_rows BOOLEAN;
  DECLARE no_more_rows_sub BOOLEAN;
  DECLARE num_rows INT DEFAULT 0;
  
  SET @ORIG_COUNT =0;
  SET @SERVED_COUNT=0;
  
  SELECT COUNT(*) FROM table_one AS A WHERE A.year='2011' INTO @ORIG_COUNT ;
  
  DELETE FROM table_test;
  insert into table_test values ('Total Rows',@ORIG_COUNT,NULL);
  
  DROP TABLE IF EXISTS table_request;
  CREATE  TABLE table_request (
      BN_NUM VARCHAR(25) NOT NULL,
      DN_NUM varchar(10) NOT NULL,
      A_TOTAL FLOAT DEFAULT 0,
      B_TOTAL FLOAT DEFAULT 0,        
      C_TOTAL FLOAT DEFAULT 0,
      PRIMARY KEY (BN)
  );
  
  
  WHILE (@SERVED_COUNT&lt;@ORIG_COUNT)  DO
  
      DROP VIEW IF EXISTS pbs_history.temp_view; 
      SET @query = CONCAT('CREATE VIEW temp_view as SELECT A.BN,A.DN
          FROM table_one AS A
          WHERE A.year='2011'
          ORDER BY A.ID
          LIMIT ', @SERVED_COUNT,',100'); 
      PREPARE stmt from @query; 
      EXECUTE stmt; 
      DEALLOCATE PREPARE stmt; 
  
              /* ================= HERE ==================== */
      SELECT * FROM temp_view;
  
      BLOKA:BEGIN
          DECLARE BN VARCHAR(25);
          DECLARE DN VARCHAR(10);
          DECLARE num_rows INT(5) DEFAULT 0;          
          DECLARE bn_count INT(5) DEFAULT 0;
  
          DECLARE sel_recs CURSOR FOR SELECT * FROM temp_view;            
          DECLARE CONTINUE HANDLER FOR NOT FOUND  SET no_more_rows = TRUE;            
  
          insert into table_test values('Start Served',@SERVED_COUNT,@query); 
  
          OPEN sel_recs;
          SELECT FOUND_ROWS() INTO @num_rows;
          insert into table_test values('Found Rows in View',@num_rows,NULL);
  
          the_loop: LOOP
              FETCH  sel_recs INTO BN,DN;
              IF no_more_rows THEN
                  CLOSE sel_recs;
                  LEAVE the_loop;
              END IF;
              SET @bn_count=0;
  
              SELECT COUNT(*) FROM table_request WHERE BILL_NUMBER=BN INTO @bn_count;
  
              insert into table_test VALUES(BN,DN,@bn_count);
              IF(@bn_count=0) THEN
                  INSERT INTO table_request VALUES (BN,DN,0,0,0);
              END IF;
  
              BLOKB:BEGIN
                  DECLARE CAT VARCHAR(5);
                  DECLARE T1 FLOAT;
  
                  DECLARE data_fetch CURSOR FOR SELECT CAT_ID, TYPE_1 FROM transactions WHERE ID=BN;
  
                  DECLARE CONTINUE HANDLER FOR NOT FOUND  SET no_more_rows_sub = TRUE;
  
                  OPEN data_fetch;    
                  sub_loop: LOOP
  
                      FETCH  data_fetch INTO CAT,T1;
                      IF no_more_rows_sub THEN
                          CLOSE data_fetch;
                          LEAVE sub_loop;
                      END IF;
  
                      IF (CAT='P1') THEN
                          UPDATE table_request SET A_TOTAL = A_TOTAL+T1 WHERE BN_NUM=BN;
                      ELSEIF (CAT='P2') THEN
                          UPDATE table_request SET B_TOTAL = B_TOTAL+T1 WHERE BN_NUM=BN;
                      ELSEIF (CAT='P3') THEN
                          UPDATE table_request SET N_TOTAL = N_TOTAL+T1 WHERE BN_NUM=BN;
                      END IF;
                  END LOOP sub_loop;
              END BLOKB;
              SET @SERVED_COUNT = @SERVED_COUNT+1;
  
          END LOOP the_loop;
          insert into table_test values ('End the_loop',0,NULL);
      END BLOKA;
  END WHILE;
   END
  </code></pre>
  
  <p>I'm testing the code with a subset from the original table. And the subset has 461 records.</p>
  
  <p>And with this code there should be 5 iterations. And in the final iteration the view should contain only 61 records. But with this code each iteration has 100 records, and during each iteration it takes into consideration only the first 100 records.</p>
  
  <p>But if i run <code>SELECT * FROM temp_view</code> in mysql console after the procedure has run, it shows the last 61 records.</p>
  
  <p>Anybody can point out what i'm doing wrong here?</p>