mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
220 lines
3.5 KiB
Text
220 lines
3.5 KiB
Text
SET sql_mode=ORACLE;
|
|
#
|
|
# MDEV-10655 Anonymous blocks
|
|
#
|
|
# Testing BEGIN NOT ATOMIC with no declarations
|
|
BEGIN NOT ATOMIC
|
|
SELECT 1 AS a;
|
|
END
|
|
/
|
|
a
|
|
1
|
|
# Testing BEGIN NOT ATOMIC with declarations
|
|
# DECLARE starts a new block and thus must be followed by BEGIN .. END
|
|
BEGIN NOT ATOMIC
|
|
DECLARE
|
|
i INT DEFAULT 5;
|
|
x INT DEFAULT 10;
|
|
BEGIN
|
|
<<label>>
|
|
WHILE i > 3 LOOP
|
|
i:= i - 1;
|
|
SELECT i;
|
|
END LOOP label;
|
|
END;
|
|
END
|
|
/
|
|
i
|
|
4
|
|
i
|
|
3
|
|
# Anonymous blocks with no declarations and no exceptions
|
|
BEGIN
|
|
SELECT 1 AS a;
|
|
END
|
|
$$
|
|
a
|
|
1
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(20);
|
|
INSERT INTO t1 VALUES(30);
|
|
ROLLBACK;
|
|
END;
|
|
$$
|
|
SELECT * FROM t1;
|
|
a
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(20);
|
|
INSERT INTO t1 VALUES(30);
|
|
END;
|
|
$$
|
|
ROLLBACK;
|
|
SELECT * FROM t1;
|
|
a
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(20);
|
|
INSERT INTO t1 VALUES(30);
|
|
COMMIT;
|
|
END;
|
|
$$
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
20
|
|
30
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(20);
|
|
INSERT INTO t1 VALUES(30);
|
|
END;
|
|
$$
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
20
|
|
30
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(20);
|
|
INSERT INTO t1 VALUES(20);
|
|
END;
|
|
$$
|
|
ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
20
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
# Anonymous blocks with no declarations, with exceptions
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(20);
|
|
INSERT INTO t1 VALUES(20);
|
|
EXCEPTION
|
|
WHEN DUP_VAL_ON_INDEX THEN NULL;
|
|
END;
|
|
$$
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
20
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
# Anonymous blocks with declarations, with no exceptions
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
DECLARE
|
|
a20 INT:=20;
|
|
a30 INT:=30;
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(a20);
|
|
INSERT INTO t1 VALUES(a30);
|
|
ROLLBACK;
|
|
END;
|
|
$$
|
|
SELECT * FROM t1;
|
|
a
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
DECLARE
|
|
a20 INT:=20;
|
|
a30 INT:=30;
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(a20);
|
|
INSERT INTO t1 VALUES(a30);
|
|
END;
|
|
$$
|
|
ROLLBACK;
|
|
SELECT * FROM t1;
|
|
a
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
DECLARE
|
|
a20 INT:=20;
|
|
a30 INT:=30;
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(a20);
|
|
INSERT INTO t1 VALUES(a30);
|
|
COMMIT;
|
|
END;
|
|
$$
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
20
|
|
30
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
DECLARE
|
|
a20 INT:=20;
|
|
a30 INT:=30;
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(a20);
|
|
INSERT INTO t1 VALUES(a30);
|
|
END;
|
|
$$
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
20
|
|
30
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|
|
# Anonymous blocks with declarations, with exceptions
|
|
SET AUTOCOMMIT=OFF;
|
|
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10);
|
|
DECLARE
|
|
a20 INT:=20;
|
|
BEGIN
|
|
INSERT INTO t1 VALUES(a20);
|
|
INSERT INTO t1 VALUES(a20);
|
|
EXCEPTION
|
|
WHEN DUP_VAL_ON_INDEX THEN NULL;
|
|
END;
|
|
$$
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
20
|
|
DROP TABLE t1;
|
|
SET AUTOCOMMIT=DEFAULT;
|