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.
- Integer range FOR loop:
- Explicit cursor FOR loop
- Implicit cursor FOR loop
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:
It will return:
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.
It will return without errors, with the following output:
- 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.