This MDEV is a part of MDEV-12518.
Under terms of this task we'll add Oracle-style FOR loop (both for integer ranges and cursors) into sql_mode=DEFAULT.
FOR loops will be a non-standard MariaDB extension.

We won't implement the SQL-standard cursor FOR loop yet, because it would need to open the cursor at parse time to know column names of the cursor.

Proposed syntax:

• Integer range FOR loop:

  [begin_label:]

  FOR var_name IN [ REVERSE ] lower_bound .. upper_bound

  DO statement_list

  END FOR [ end_label ]

• Explicit cursor FOR loop

  [begin_label:]

  FOR record_name IN cursor_name [ ( cursor_actual_parameter_list)]

  DO statement_list

  END FOR [ end_label ]

• Implicit cursor FOR loop

  [begin_label:]

  FOR record_name IN ( select_statement )

  DO statement_list

  END FOR [ end_label ]

The proposed syntax is a compilation of the SQL standard syntax and Oracle's syntax for FOR loop:

• It generally uses Oracle style syntax
• however, like in the SQL standard, it uses FOR..DO..END FOR (instead or Oracle's FOR..LOOP..END LOOP)

Note, for cursor FOR loops, there is no a need to do OPEN, FETCH and CLOSE. These commands are done automatically. However, it will still be possible to fetch extra records inside the explicit cursor FOR loop body using explicit FETCH commands.

In sql_mode=DEFAULT, the automatic implicit FETCH which happens on FOR iterations and an explicit FETCH inside the loop body will work differently:

• The automatic FETCH will never generate errors. On NOT FOUND, it will automatically close the cursor and leave the loop.
• An explicit FETCH command inside the FOR loop will work like normal FETCH commands: i.e. it will generate an error on NOT FOUND, even when used with the FOR cursor.

This script uses an extra FETCH command inside the loop body:

DELIMITER $$

BEGIN NOT ATOMIC

  DECLARE x INT;

  DECLARE cur CURSOR FOR SELECT 1 AS x;

  FOR rec IN cur

  DO

    FETCH cur INTO x; -- Notice it uses "cur" (the loop cursor)

  END FOR;

END;

$$

DELIMITER ;

It will return:

ERROR 02000: No data - zero rows fetched, selected, or processed

because the explicit FETCH command returns no rows: the first record is fetched automatically in the beginning of the FOR iteration, and there are no more records.

FETCH commands inside the FOR body will be normally handled by "NOT FOUND" handlers.
The following script uses a FETCH command with the FOR loop cursor, in combination with a CONTINUE HANDLER FOR NOT FOUND.

DELIMITER $$

BEGIN NOT ATOMIC

  DECLARE done INT DEFAULT 0;

  DECLARE cur CURSOR FOR SELECT 1 AS x, 'y1' AS y UNION

                         SELECT 2,'y2' UNION

                         SELECT 3,'y3';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  forrec:

  FOR rec IN cur

  DO

    SELECT CONCAT(rec.x, ' ', rec.y) AS 'Implicit FETCH';

    FETCH cur INTO rec;

    IF done THEN

      SELECT 'NO DATA' AS `Explicit FETCH`;

      LEAVE forrec;

    ELSE

      SELECT CONCAT(rec.x, ' ', rec.y) AS 'Explicit FETCH';

    END IF;

  END FOR;

END;

$$

DELIMITER ;

It will return without errors, with the following output:

Implicit FETCH

1 y1

Explicit FETCH

2 y2

Implicit FETCH

3 y3

Explicit FETCH

NO DATA

Notice:

• some records are fetched using the automatic FETCH on FOR iterations
• some records are fetched using the explicit FETCH command inside the loop body
• the last explicit FETCH fails on NOT FOUND, which is caught by the CONTINUE handler, and the error is suppressed.