title: Cursor in mysql stored procedure executes only 1 iteration
body: <p>I have been struck in this stored procedure for 2 days now.
  Here's the code</p>
  
  <pre><code>BEGIN
  
  DECLARE in_township_id INT DEFAULT 2;
  DECLARE in_billing_month INT DEFAULT 1;
  DECLARE in_billing_month_string VARCHAR(15) DEFAULT 'January';
  DECLARE in_billing_year INT DEFAULT 2016;
  DECLARE in_account_id INT DEFAULT 1;
  DECLARE out_status INT DEFAULT 0;
  DECLARE in_invoice_date DATETIME DEFAULT CURRENT_DATE();
  
  DECLARE _no_flats INT;
  DECLARE _current_month_data_exists INT;
  
  DECLARE _flat_id INT;
  DECLARE _current_billing_month, _current_billing_year INT;
  DECLARE _next_billing_month, _next_billing_year INT;
  DECLARE _current_balance, _new_balance FLOAT DEFAULT 0;
  DECLARE _maintenance, _power_backup, _service_tax, _penalty FLOAT DEFAULT 0;
  DECLARE _maintenance_price, _power_backup_price, _service_tax_price, _balance_price, _penalty_price FLOAT DEFAULT 0;
  DECLARE _consumed_units INT;
  DECLARE _penal_interest_remark, _maintenance_bill_remark, _power_backup_remark, _service_tax_remark, _maintenance_account_remark  VARCHAR(100);
  DECLARE _total_penalty, _total_maintenance, _total_power_backup, _total_service_tax FLOAT DEFAULT 0;
  
  DECLARE _all_flats_cursor CURSOR FOR SELECT id FROM flat_resident_v WHERE township_id=2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _no_flats=1;
  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
      ROLLBACK;
      SET out_status=2;
      SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
  END;
  
  START TRANSACTION;
  
  SET _current_billing_month=in_billing_month;
  SET _current_billing_year=in_billing_year;
  SET _next_billing_month=in_billing_month;
  SET _next_billing_year=in_billing_year;
  IF in_billing_month=1 THEN
      SET _current_billing_month=12;
      SET _current_billing_year=in_billing_year-1;
  END IF;
  IF in_billing_month=12 THEN
      SET _next_billing_month=1;
      SET _next_billing_year=in_billing_year+1;
  ELSE
      SET _next_billing_month = in_billing_month+1;
  END IF;
  SELECT CONCAT('On account of billing month ', in_billing_month_string) INTO _maintenance_account_remark;
  SELECT penalty, rate_maintenance, rate_power_backup, service_tax_rate 
      INTO _penalty, _maintenance, _power_backup, _service_tax FROM account_master WHERE id=in_account_id;
  SELECT CONCAT('Charged @ ', _penalty*1200, '%') INTO _penal_interest_remark;
  SELECT CONCAT('Charged @ Rs. ', _maintenance, '/sq. feet') INTO _maintenance_bill_remark;
  SELECT CONCAT('Charged @ Rs. ', _power_backup, '/unit') INTO _power_backup_remark;
  SELECT CONCAT('Charged @ ', _service_tax_price*100, '%') INTO _service_tax_remark;
  SET out_status = 0;
  INSERT INTO temp(value1, value2) VALUES('SUCCESS', 'Initialization Completed');
  
  OPEN _all_flats_cursor;
  SET _no_flats=0;
  ALL_FLATS_LOOP: LOOP
      FETCH _all_flats_cursor INTO _flat_id;
      IF _no_flats=1 THEN
          INSERT INTO temp(value1, value2) VALUES('LOOP BREAK', _no_flats);
          LEAVE ALL_FLATS_LOOP;
      END IF;
      IF _no_flats=0 THEN
          INSERT INTO temp(value1, value2) VALUES('INSIDE LOOP', _no_flats);
          INSERT INTO temp(value1, value2) VALUES('FLAT_ID', _flat_id);
          SELECT count(FLATS.id) INTO _current_month_data_exists FROM account_flat_ledger as FLATS 
              WHERE FLATS.flat_id=_flat_id AND FLATS.debit_month=in_billing_month 
                  AND FLATS.debit_year=in_billing_year FOR UPDATE;
          IF _current_month_data_exists=0 THEN
              SET out_status = 1;
              INSERT INTO temp(value1, value2) VALUES('_current_month_data_exists', 'FAILED: Data not exists for billing year');
              LEAVE ALL_FLATS_LOOP;
          END IF;
  
          SELECT consumed_units INTO _consumed_units FROM account_power_backup_usage 
              WHERE flat_id=_flat_id AND month=in_billing_month AND year=in_billing_year;
  
          INSERT INTO temp(value1, value2) VALUES('SUCCESS', 'Power Ok'); 
          SELECT balance INTO _current_balance FROM account_flat_ledger as FLATS 
              WHERE FLATS.flat_id=_flat_id AND FLATS.debit_month=_current_billing_month AND FLATS.debit_year=_current_billing_year FOR UPDATE;
          IF _current_balance&gt;0 THEN
              SET _penalty_price = _penalty*_current_balance;
          END IF;
          SET _maintenance_price = _maintenance*1000;
          SET _power_backup_price = _power_backup*_consumed_units;
          SET _service_tax_price = _service_tax * (_maintenance+_power_backup);
          SET _new_balance = _current_balance + _penalty + _maintenance + _power_backup + _service_tax;
  
          INSERT INTO temp(value1, value2) VALUES('SUCCESS', 'All Values Set');
  
          UPDATE account_flat_ledger SET penal_interest=_penalty_price, maintenance_bill=_maintenance_price, service_tax=_service_tax_price,
              power_backup=_power_backup_price, opening_balance=_current_balance,received_amount=0, balance=_new_balance,
              debit_date=CURDATE(), penal_interest_remark=_penal_interest_remark, maintenance_bill_remark=_maintenance_bill_remark,
              power_backup_remark=_power_backup_remark, service_tax_remark=_service_tax_remark 
              WHERE flat_id=_flat_id AND debit_month=in_billing_month AND debit_year=in_billing_year;
  
          INSERT INTO account_flat_ledger(flat_id, debit_month, debit_year) 
              VALUES(_flat_id, _next_billing_month, _next_billing_year);
  
          INSERT INTO temp(value1, value2) VALUES('SUCCESS', 'Insertion of new Flats OK');
  
          SET _total_maintenance = _total_maintenance + (_maintenance_price 
              + _power_backup_price + _service_tax_price + _penalty_price);
          SET _total_penalty = _total_penalty + _penalty_price;
          SET _total_power_backup = _total_power_backup + _power_backup_price;
          SET _total_service_tax = _total_service_tax + _service_tax_price;
  
          INSERT INTO temp(value1, value2) VALUES('SUCCESS', 'Flat over');
      END IF;
  END LOOP ;
  CLOSE _all_flats_cursor;
  
  IF out_status != 0 THEN 
      ROLLBACK;
  END IF;
  
  SELECT @out_status;
  END IF;
  
  
  COMMIT;
  END
  </code></pre>
  
  <p>Previously I have done this whole thing using Java code (using ResultSets and PreparedStatements). But someone told me to use stored procedure for this purpose to make things handy and efficient.</p>
  
  <p><strong>Problem:</strong> The loop i.e. Cursor executes only ones, but there are 3 records present in the table.
  I used logging into temp table to trace the error but couldn't find.</p>