1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
DELIMITER $$ CREATE FUNCTION member_amount(p_member_id INT) RETURNS INT DETERMINISTIC BEGIN DECLARE total INT default 0; DECLARE done INT DEFAULT FALSE; DECLARE a INT; DECLARE b INT; DECLARE cur CURSOR FOR SELECT id, sum(amount) as amount FROM `vw_orders` WHERE `member_id` = p_member_id AND `status` = 3 group by id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO a, b; IF done THEN LEAVE read_loop; END IF; SET total = total + b; END LOOP; CLOSE cur; RETURN total; END $$ DELIMITER ; |
Reference: