[#MDEV-10583] sql_mode=ORACLE: SQL%ROWCOUNT

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

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

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

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

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)

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

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

PL/SQL parser (MDEV-10142) [MDEV-10583] sql_mode=ORACLE: SQL%ROWCOUNT Created: 2016-08-17 Updated: 2020-08-27 Resolved: 2016-09-22
Status:	Closed
Project:	MariaDB Server
Component/s:	Parser, Stored routines
Affects Version/s:	None
Fix Version/s:	10.3.0