It’s always been a small annoyance that the MySQL / MariaDB flavour of SQL wouldn’t allow you to use if else logic or loops outside of a stored procedure or trigger. There were ways around this but it’s not as nice if you’re coming from TSQL. This is rectified in MariaDB from 10.1.1.
One thing that is worth noting, which perhaps the manual doesn’t make totally clear, is that you should use the DELIMITER statement in your SQL code. This is just the same as when writing stored procedures. You get syntax error if you don’t do this.
DELIMITER | BEGIN NOT ATOMIC /* SQL CODE HERE */ END; | DELIMITER ;
Here are a few examples;
UPDATE: Please note that the source code plugin strips the pipe characters from the code after you click “View Code”
DELIMITER | BEGIN NOT ATOMIC DECLARE i INTEGER; SET i = 1; WHILE i < 10 DO SELECT i; SELECT SLEEP(i); SET i = i + 1; END WHILE; END; | DELIMITER ;
DELIMITER | BEGIN NOT ATOMIC IF 1 = 1 THEN SELECT 'Hello, World!'; END IF; END; | DELIMITER ;
DELIMITER | BEGIN NOT ATOMIC CASE(@@port) WHEN 3306 THEN SELECT 'Running on the default port.'; ELSE SELECT 'You are not running on the default port.'; END CASE; END; | DELIMITER ;
ITERATE LOOP WITH IF ELSE
DELIMITER | BEGIN NOT ATOMIC DECLARE i INTEGER DEFAULT 0; my_loop: LOOP SET i = i + 1; SELECT i * i; IF i < 10 THEN ITERATE my_loop; ELSE LEAVE my_loop; END IF; END LOOP my_loop; END; | DELIMITER ;