MariaDB/MySQL Stored Language Examples

MariaDB/MySQL Stored Language is called SQL/PSM.
There are 4 different types of Stored Language: Stored Procedures, Stored Functions, Triggers and Events.

Stored Procedures

Stored Procedure with a Cursor:

DELIMITER //
CREATE PROCEDURE cleanup(IN pData VARCHAR(48))
BEGIN
  DECLARE vId INTEGER;
  DECLARE vNotFound INTEGER;

  DECLARE cCleanUp CURSOR FOR
    SELECT id FROM test WHERE data = pData;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET vNotFound = 1;
  
  OPEN cCleanUp;
  lGetRecord: LOOP
  
    FETCH cCleanUp INTO vId;
    IF vNotFound = 1 THEN 
      LEAVE lGetRecord;
    END IF;
  
    DELETE FROM test WHERE id = vId;
  END LOOP lGetRecord;
  CLOSE cCleanUp;
END //
DELIMITER ;

CALL cleanup('Bla1Trx');

Stored Procedure with SUPER PRIVILEGE

Disabling binary log on a per session base requires the SUPER privilege:

SQL> SET SESSION sql_log_bin = off;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

If you do NOT want to GRANT the SUPER privilege to your application users you can GRANT the EXECUTE privilege to a PROCEDURE which will disable the binary log for your session:

SQL> use mysql
SQL> DELIMITER //
SQL> CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `sql_log_bin`(IN pValue VARCHAR(3))
DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER
BEGIN
  SET SESSION sql_log_bin = pValue;
END;
//
SQL> DELIMITER ;

SQL> GRANT EXECUTE ON PROCEDURE `mysql`.`sql_log_bin` TO 'app'@'%';

Then as user app:

SQL> SELECT  current_user();
+----------------+
| current_user() |
+----------------+
| app@%          |
+----------------+

SQL> SHOW SESSION VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+

SQL> CALL mysql.sql_log_bin('OFF');
SQL> SHOW SESSION VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+

SQL> INSERT INTO test.test VALUES (NULL, 'This row should not be replicated!', null);

Then on the master the row should be inserted and on the slave it should be missing...