Control Flow, Cursors and Error Handling

CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(20) NOT NULL, balance DECIMAL(10,2) NOT NULL) ENGINE = InnoDB;
CREATE TABLE transfer_log (id INT AUTO_INCREMENT PRIMARY KEY, from_id INT, to_id INT, amount DECIMAL(10,2), status VARCHAR(20), logged_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE = InnoDB;
INSERT INTO accounts VALUES (1, 'Asha', 500), (2, 'Ben', 200), (3, 'Chitra', 50);

DELIMITER //

CREATE FUNCTION fee_for(amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC NO SQL
BEGIN
    RETURN CASE
        WHEN amount < 100  THEN 0
        WHEN amount < 1000 THEN 5
        ELSE ROUND(amount * 0.01, 2)
    END;
END//

DELIMITER ;

SELECT fee_for(50) AS small, fee_for(500) AS medium, fee_for(5000) AS large;

+-------+--------+-------+
| small | medium | large |
+-------+--------+-------+
|  0.00 |   5.00 | 50.00 |
+-------+--------+-------+
1 row in set

DELIMITER //

CREATE PROCEDURE fill_numbers(IN how_many INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DROP TEMPORARY TABLE IF EXISTS nums;
    CREATE TEMPORARY TABLE nums (n INT);
    loop_label: LOOP
        IF i > how_many THEN
            LEAVE loop_label;
        END IF;
        INSERT INTO nums VALUES (i * i);
        SET i = i + 1;
    END LOOP;
    SELECT n AS square FROM nums ORDER BY n;
END//

DELIMITER ;

CALL fill_numbers(5);

+--------+
| square |
+--------+
|      1 |
|      4 |
|      9 |
|     16 |
|     25 |
+--------+
5 rows in set

DELIMITER //

CREATE PROCEDURE safe_transfer(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE current_balance DECIMAL(10,2);

    IF amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be positive';
    END IF;

    START TRANSACTION;
    SELECT balance INTO current_balance FROM accounts WHERE id = from_id FOR UPDATE;

    IF current_balance IS NULL THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown source account';
    ELSEIF current_balance < amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    INSERT INTO transfer_log (from_id, to_id, amount, status) VALUES (from_id, to_id, amount, 'done');
    COMMIT;
END//

DELIMITER ;

CALL safe_transfer(1, 2, 100);
CALL safe_transfer(3, 1, 500);
CALL safe_transfer(1, 2, -5);
SELECT id, owner, balance FROM accounts ORDER BY id;

ERROR 1644 (45000): Insufficient funds

ERROR 1644 (45000): Amount must be positive

+----+--------+---------+
| id | owner  | balance |
+----+--------+---------+
|  1 | Asha   |  400.00 |
|  2 | Ben    |  300.00 |
|  3 | Chitra |   50.00 |
+----+--------+---------+
3 rows in set

DELIMITER //

CREATE PROCEDURE transfer_with_log(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2), OUT result VARCHAR(40))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;
        ROLLBACK;
        INSERT INTO transfer_log (from_id, to_id, amount, status) VALUES (from_id, to_id, amount, CONCAT('failed: ', @err_no));
        SET result = CONCAT('error ', @err_no);
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    IF (SELECT balance FROM accounts WHERE id = from_id) < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative balance';
    END IF;
    COMMIT;
    SET result = 'ok';
END//

DELIMITER ;

CALL transfer_with_log(2, 1, 40, @r1);
CALL transfer_with_log(3, 1, 9999, @r2);
SELECT @r1 AS first_call, @r2 AS second_call;
SELECT id, owner, balance FROM accounts ORDER BY id;
SELECT from_id, to_id, amount, status FROM transfer_log ORDER BY id;

+------------+-------------+
| first_call | second_call |
+------------+-------------+
| ok         | error 1644  |
+------------+-------------+
1 row in set

+----+--------+---------+
| id | owner  | balance |
+----+--------+---------+
|  1 | Asha   |  440.00 |
|  2 | Ben    |  260.00 |
|  3 | Chitra |   50.00 |
+----+--------+---------+
3 rows in set

+---------+-------+---------+--------------+
| from_id | to_id | amount  | status       |
+---------+-------+---------+--------------+
|       1 |     2 |  100.00 | done         |
|       3 |     1 | 9999.00 | failed: 1644 |
+---------+-------+---------+--------------+
2 rows in set

DELIMITER //

CREATE PROCEDURE total_of_small_accounts(IN limit_amount DECIMAL(10,2), OUT total DECIMAL(12,2))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE b DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT balance FROM accounts WHERE balance < limit_amount;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET total = 0;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO b;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        SET total = total + b;
    END LOOP;
    CLOSE cur;
END//

DELIMITER ;

CALL total_of_small_accounts(300, @small_total);
SELECT @small_total AS total_of_accounts_below_300;
SELECT SUM(balance) AS same_result_in_one_query FROM accounts WHERE balance < 300;

+-----------------------------+
| total_of_accounts_below_300 |
+-----------------------------+
|                      310.00 |
+-----------------------------+
1 row in set

+--------------------------+
| same_result_in_one_query |
+--------------------------+
|                   310.00 |
+--------------------------+
1 row in set