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

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?

CodePudding user response：

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