DELIMITER //
 
CREATE PROCEDURE CLEAR_LIST(
 in in_list_id int
)
BEGIN
 
    DECLARE done INT DEFAULT FALSE;
    DECLARE deleted INT DEFAULT 0;
    DECLARE rowId INT;
    DECLARE dataId INT;
  
    DECLARE cur CURSOR FOR SELECT ID, TBL_DATASET_ID FROM TBL_ROW WHERE LIST_ID = in_list_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
         
    OPEN cur;
          
   read_loop: LOOP
        FETCH cur INTO rowId, dataId;
        IF done THEN
            LEAVE read_loop;
        END IF;
          
        SET deleted = deleted + 1;
  
        DELETE from TBL_REGION where TOP = rowId;
      
        DELETE from TBL_REGION where BOTTOM = rowId;
 
        DELETE from TBL_ROW where ID = rowId;
 
        DELETE from TBL_ENTRY where TBL_DATASET_ID = dataId;
 
        DELETE from TBL_DATASET where ID = pclId;
 
        COMMIT;
    END LOOP;
  
    CLOSE cur;
      
   SELECT concat(deleted, ' rows deleted');
      
END; //
  
DELIMITER ;