Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14415

Add Oracle-style FOR loop to sql_mode=DEFAULT

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: