MariaDB Compound Statements Outside Stored Procedures

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”

WHILE LOOP

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 ;

IF STATEMENT

DELIMITER |

BEGIN NOT ATOMIC

	IF 1 = 1 THEN
		SELECT 'Hello, World!';
	END IF;

END; |

DELIMITER ;

CASE STATEMENT

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 ;