BEGIN
declare newprodid int;
declare stock int ;
declare total int;
declare warehouse_stock_num int;
declare prod_stock_num int;
declare cursor_test CURSOR FOR SELECT product_id,stock_quantity_bh FROM ProductPage.product_stock where stock_quantity_bh>0;
select count(product_id) into prod_stock_num from ProductPage.product_stock where stock_quantity_bh>0;
set total=0;
OPEN cursor_test;
repeat
FETCH cursor_test INTO newprodid,stock;
select count(product_id) into warehouse_stock_num from prodstockdb.product_warehouse_stock
where product_id =newprodid and warehouse_id=2;
if (warehouse_stock_num >0) then
update prodstockdb.product_warehouse_stock set stock_quantity=stock, last_changed_date=now() where product_id =newprodid and warehouse_id=2;
else
insert into prodstockdb.product_warehouse_stock(product_id,warehouse_id,stock_quantity,last_changed_date) values(newprodid,2, stock,now());
end if;
set total=total+1;
until total=prod_stock_num
end repeat;
select total;
CLOSE cursor_test;
END