Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
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
|