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

sql_mode=ORACLE: Explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND

    XMLWordPrintable

    Details

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

      Description

      Understand explicit cursor attributes when running in sql_mode=ORACLE:

      • cursor_name%ISOPEN
        Return TRUE if cursor_name was already open with OPEN, or FALSE otherwise
      • cursor_name%ROWCOUNT
        Return 0 before the first FETCH, afterwards return the number of rows that were already fetched from cursor_name using FETCH.
      • cursor_name%FOUND
        Return NULL before the first FETCH. Return TRUE if the last FETCH returned a row, or FALSE otherwise.
      • cursor_name%NOTFOUND
        Return NULL before the first FETCH. Return TRUE if the last FETCH returned no rows, or FALSE if the last FETCH returned some rows.

      Due to a grammar conflict, this task will remove the modulo operator '%'. One should use MOD in Oracle.

      This task will also implement the predefined exception INVALID_CURSOR. If a cursor is not open, referencing it with %FOUND, %NOTFOUND, or %ROWCOUNT raises INVALID_CURSOR.

      If a cursor is closed and then opened again, %FOUND and %NOTFOUND are reset to NULL and %ROWCOUNT is reset to 0.

      Note, we'll also change behavior of the FETCH statement to raise no conditions (exceptions) if no rows were returned. Instead of catching conditions, in Oracle one should test %FOUND or %NOTFOUND attributes to know if FETCH returned a row.

      Example:

        BEGIN
          FETCH cur INTO v1,v2;
          EXIT WHEN cur%NOTFOUND;
          ...
        END; 
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: