How should I calculate sum of all values in a field using cursor in mysql?

Question

Select sum(balance) as total_asset from account_details; I should replace this query using cursor of stored procedure in MySQL

CREATE PROCEDURE `GetTotalBankAsset`()
BEGIN
    DECLARE c_balance INT DEFAULT 0;
    DECLARE sum INT DEFAULT 0;
    DECLARE sumCurs CURSOR FOR Select balance from account_details;
    OPEN sumCurs;
    getSum: LOOP
        FETCH NEXT FROM sumCurs INTO c_balance;
            SET sum=c_balance+sum;
        LEAVE getSum;     
    END LOOP;
    SELECT sum;
    CLOSE sumCurs;
END 

• This is the procedure I wrote. But here I only get value from single row. How can I process multiple rows?

Answer 1

You can use a handler to leave the loop like this:

BEGIN
DECLARE done INT DEFAULT FALSE;
    DECLARE c_balance INT DEFAULT 0;
    DECLARE sum INT DEFAULT 0;
    DECLARE sumCurs CURSOR FOR Select balance from account_details;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN sumCurs;
    getSum: LOOP
        FETCH NEXT FROM sumCurs INTO c_balance;
        IF done THEN
      LEAVE getSum;
        END IF; 
        SET sum=c_balance+sum;
    END LOOP;
    
    CLOSE sumCurs;
  
    SELECT sum(sum);
END