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

sql_mode=ORACLE: SQL%ROWCOUNT

Details

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

    Description

      Understand SQL%ROWCOUNT when running in sql_mode=ORACLE

      Example:

      UPDATE t1 SET a=10;
      		 
      cnt:= cnt + SQL%ROWCOUNT;

      Oracle's SQL%ROWCOUNT looks very similar to MariaDB function ROW_COUNT(), with the following differences:

      • When a query like this: 

        SELECT a INTO spvar FROM t1;

        finds more than one rows, SQL%ROWCOUNT returns 1 rather than -1. We'll implement SQL%ROWCOUNT in MariaDB in the same way.

      • When no DELETE, INSERT, UPDATE or SELECT .. INTO.. queries happened during this session, SQL%ROWCOUNT returns NULL. Note, as this is a very minor issue, for simplicity of the implementation we'll return 1 instead.

      Example - no rows involved

      SET SERVEROUT ON;
      		 
      BEGIN
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      END;
      		 
      /


      SQL%ROWCOUNT IS

      Example - UPDATE

      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      BEGIN
      		 
        UPDATE t1 SET a=30;
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      END;
      		 
      /


      SQL%ROWCOUNT IS 0


      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (10);
      		 
      INSERT INTO t1 VALUES (20);
      		 
      BEGIN
      		 
        UPDATE t1 SET a=30;
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      END;
      		 
      /


      SQL%ROWCOUNT IS 2

      Example - DELETE

      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      DROP PROCEDURE p1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
      BEGIN
      		 
        DELETE FROM t1;
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      END;
      		 
      /
      		 
      CALL p1();


      SQL%ROWCOUNT IS 0


      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (10);
      		 
      INSERT INTO t1 VALUES (20);
      		 
      BEGIN
      		 
        DELETE FROM t1;
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      END;
      		 
      /


      SQL%ROWCOUNT IS 2

      Example - SELECT..INTO var FROM.. - one row found

      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (10);
      		 
      INSERT INTO t1 VALUES (20);
      		 
      DECLARE
      		 
        va INT;
      		 
      BEGIN
      		 
        SELECT a INTO va FROM t1 WHERE ROWNUM<2;
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      END;
      		 
      /


      SQL%ROWCOUNT IS 1

      Example - SELECT..INTO var FROM.. - no rows found

      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      DROP PROCEDURE p1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
        va INT;
      		 
      BEGIN
      		 
        SELECT a INTO va FROM t1;
      		 
        DBMS_OUTPUT.put_line('Still here');
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '|| COALESCE(SQL%ROWCOUNT,''));
      		 
      END;
      		 
      /
      		 
      CALL p1();

      Note, no output!

      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      DROP PROCEDURE p1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
        va INT;
      		 
      BEGIN
      		 
        SELECT a INTO va FROM t1;
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      EXCEPTION
      		 
        WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT||' (EXCEPTION)');
      		 
      END;
      		 
      /
      		 
      CALL p1();


      SQL%ROWCOUNT IS 0 (EXCEPTION)

      Example - SELECT..INTO var FROM.. - multiple rows found

      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      DROP PROCEDURE p1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (10);
      		 
      INSERT INTO t1 VALUES (20);
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
        va INT:=1;
      		 
      BEGIN
      		 
        SELECT a INTO va FROM t1;
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      EXCEPTION
      		 
        WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT || ' (EXCEPTION) va='||va);
      		 
      END;
      		 
      /
      		 
      CALL p1();


      SQL%ROWCOUNT IS 1 (EXCEPTION) va=1

      Example - INSERT INTO t2 SELECT .. FROM t1

      SET SERVEROUT ON;
      		 
      DROP TABLE t1;
      		 
      DROP TABLE t2;
      		 
      CREATE TABLE t1 (a INT);
      		 
      CREATE TABLE t2 (a INT);
      		 
      INSERT INTO t1 VALUES (10);
      		 
      INSERT INTO t1 VALUES (20);
      		 
      BEGIN
      		 
        INSERT INTO t2 SELECT * FROM t1;
      		 
        DBMS_OUTPUT.put_line('SQL%ROWCOUNT IS '||SQL%ROWCOUNT);
      		 
      END;
      		 
      /


      SQL%ROWCOUNT IS 2

      Attachments

        Issue Links

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34557 sql_mode=ORACLE: SQL%NOTFOUND

          • Major - Major loss of function.
          • Open

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.