Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-10581

sql_mode=ORACLE: Explicit cursor FOR LOOP

    XMLWordPrintable

    Details

    • Sprint:
      10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

      Description

      Add support for explicit cursor FOR loops when running in sql_mode=ORACLE:

      FOR rec IN cur
      LOOP
        -- statements
      END LOOP;
      

      The benefits of this syntax is:

      • No declaration of the FOR variable rec is needed. The cursor FOR statement implicitly declares a record variable (i.e. a ROW type variable}} that has the same structure with the data returned by the cursor.
      • No needs to do OPEN or CLOSE for the cursor. It's automatically opened before the loop, and is closed after the loop.
      • No needs to do CLOSE from inside the FOR loop if one uses a GOTO statement from inside the loop. The cursor is closed automatically.
      • No needs to do an explicit FETCH. The next row is automatically fetched in the beginning of every iteration.
      • No needs to do an explicit EXIT to leave the loop when FETCH returned no data.

      Example:

      DECLARE
        CURSOR c1 IS
          SELECT last_name, job_id FROM employees
          WHERE job_id LIKE '%CLERK%' AND manager_id > 120
          ORDER BY last_name;
      BEGIN
        FOR item IN c1
        LOOP
          DBMS_OUTPUT.PUT_LINE
            ('Name = ' || item.last_name || ', Job = ' || item.job_id);
        END LOOP;
      END;
      /
      

      The fields of the implicitly declared record variable must be updatable:

      DECLARE
        CURSOR cur IS
          SELECT 'Black' AS last_name, 10 AS job_id FROM DUAL UNION
          SELECT 'White' AS last_name, 20 AS job_id FROM DUAL;
      BEGIN
        FOR rec IN cur
        LOOP
          rec.job_id:= rec.job_id + 100;
          DBMS_OUTPUT.PUT_LINE ('Name=' || rec.last_name || ', Job=' || rec.job_id);
        END LOOP;
      END;
      /
      

      Internally, the explicit cursor FOR LOOP will be transparently translated to:

      • OPEN
      • Declaration of a %ROWTYPE variable
      • Simple LOOP with EXIT
      • CLOSE

      So the block in the previous example will be effectively equal to:

      DECLARE
        CURSOR cur IS
          SELECT 'Black' AS last_name, 10 AS job_id FROM DUAL UNION
          SELECT 'White' AS last_name, 20 AS job_id FROM DUAL;
      BEGIN
        OPEN cur;
        DECLARE
          rec cur%ROWTYPE;
        BEGIN
          LOOP
            FETCH cur INTO rec;
            EXIT WHEN cur%NOTFOUND;
            rec.job_id:= rec.job_id + 100;
            DBMS_OUTPUT.PUT_LINE ('Name=' || rec.last_name || ', Job=' || rec.job_id);
          END LOOP;
          CLOSE cur;
        END;
      END;
      /
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: