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');