XMLWordPrintable

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

          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.