Stored Function (Routine)

MySQL

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 ;

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:

• https://dev.mysql.com/doc/refman/8.0/en/cursors.html
• http://www.mysqltutorial.org/mysql-stored-function/
• https://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine