CREATE DEFINER=`java_user`@`%` PROCEDURE `populate_change_rate`()
BEGIN
   
	declare myid    int;
	declare myid_prev    int;
	
    declare myclose double;
	declare myclose_prev double;
	
	declare cnt int;
	
	-- this flag will be set to true ;
   DECLARE exit_loop BOOLEAN; 
    
	-- Declare the cursor
   DECLARE my_cursor CURSOR FOR
       select id, close
       from stock.stock_hist order by id desc;
	   
	   -- set exit_loop flag to true if there are no more rows
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
   
   set cnt = 0;
   
   -- open the cursor
   OPEN my_cursor;

   -- start looping
   myloop: 
   LOOP
   
	   -- check if the exit_loop flag has been set by mysql, 
		 -- close the cursor and exit the loop if it has.
		 IF exit_loop THEN
			 CLOSE my_cursor;
			 LEAVE myloop;
			 
		 END IF;
		 
		 set cnt = cnt + 1;
		 
		 -- read the name from next row into the variables 
		FETCH  my_cursor INTO myid, myclose;
		 
          if cnt = 1 then
		  
			set myid_prev = myid;
			set myclose_prev = myclose;
		  
		  else
		  
		  set cnt = 0;
		  
		  UPDATE STOCK.STOCK_HIST
			SET CHANGE_RATE = concat( cast( ( MYCLOSE_PREV - MYCLOSE ) * 100 / MYCLOSE as decimal(12,2) ), '%' ) WHERE ID = MYID_PREV;
			
		  end if;
     
	 END LOOP myloop;
     

   
    END