diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle/t/sp-anonymous.test')
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/sp-anonymous.test | 244 |
1 files changed, 244 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/t/sp-anonymous.test b/mysql-test/suite/compat/oracle/t/sp-anonymous.test new file mode 100644 index 00000000..ac61e8ac --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-anonymous.test @@ -0,0 +1,244 @@ +--source include/have_innodb.inc + +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-10655 Anonymous blocks +--echo # + +--echo # Testing BEGIN NOT ATOMIC with no declarations +DELIMITER /; +BEGIN NOT ATOMIC + SELECT 1 AS a; +END +/ +DELIMITER ;/ + +--echo # Testing BEGIN NOT ATOMIC with declarations +--echo # DECLARE starts a new block and thus must be followed by BEGIN .. END +DELIMITER /; +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 +/ +DELIMITER ;/ + + +--echo # Anonymous blocks with no declarations and no exceptions + +DELIMITER $$; +BEGIN + SELECT 1 AS a; +END +$$ +DELIMITER ;$$ + + +SET AUTOCOMMIT=OFF; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (10); +DELIMITER $$; +BEGIN + INSERT INTO t1 VALUES(20); + INSERT INTO t1 VALUES(30); + ROLLBACK; +END; +$$ +DELIMITER ;$$ +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +SET AUTOCOMMIT=OFF; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (10); +DELIMITER $$; +BEGIN + INSERT INTO t1 VALUES(20); + INSERT INTO t1 VALUES(30); +END; +$$ +DELIMITER ;$$ +ROLLBACK; +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +SET AUTOCOMMIT=OFF; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (10); +DELIMITER $$; +BEGIN + INSERT INTO t1 VALUES(20); + INSERT INTO t1 VALUES(30); + COMMIT; +END; +$$ +DELIMITER ;$$ +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +SET AUTOCOMMIT=OFF; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (10); +DELIMITER $$; +BEGIN + INSERT INTO t1 VALUES(20); + INSERT INTO t1 VALUES(30); +END; +$$ +DELIMITER ;$$ +COMMIT; +SELECT * FROM t1; +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); +DELIMITER $$; +--error ER_DUP_ENTRY +BEGIN + INSERT INTO t1 VALUES(20); + INSERT INTO t1 VALUES(20); +END; +$$ +DELIMITER ;$$ +COMMIT; +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +--echo # 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); +DELIMITER $$; +BEGIN + INSERT INTO t1 VALUES(20); + INSERT INTO t1 VALUES(20); +EXCEPTION + WHEN DUP_VAL_ON_INDEX THEN NULL; +END; +$$ +DELIMITER ;$$ +COMMIT; +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +--echo # Anonymous blocks with declarations, with no exceptions + +SET AUTOCOMMIT=OFF; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (10); +DELIMITER $$; +DECLARE + a20 INT:=20; + a30 INT:=30; +BEGIN + INSERT INTO t1 VALUES(a20); + INSERT INTO t1 VALUES(a30); + ROLLBACK; +END; +$$ +DELIMITER ;$$ +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +SET AUTOCOMMIT=OFF; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (10); +DELIMITER $$; +DECLARE + a20 INT:=20; + a30 INT:=30; +BEGIN + INSERT INTO t1 VALUES(a20); + INSERT INTO t1 VALUES(a30); +END; +$$ +DELIMITER ;$$ +ROLLBACK; +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +SET AUTOCOMMIT=OFF; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (10); +DELIMITER $$; +DECLARE + a20 INT:=20; + a30 INT:=30; +BEGIN + INSERT INTO t1 VALUES(a20); + INSERT INTO t1 VALUES(a30); + COMMIT; +END; +$$ +DELIMITER ;$$ +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +SET AUTOCOMMIT=OFF; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (10); +DELIMITER $$; +DECLARE + a20 INT:=20; + a30 INT:=30; +BEGIN + INSERT INTO t1 VALUES(a20); + INSERT INTO t1 VALUES(a30); +END; +$$ +DELIMITER ;$$ +COMMIT; +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; + + +--echo # 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); +DELIMITER $$; +DECLARE + a20 INT:=20; +BEGIN + INSERT INTO t1 VALUES(a20); + INSERT INTO t1 VALUES(a20); +EXCEPTION + WHEN DUP_VAL_ON_INDEX THEN NULL; +END; +$$ +DELIMITER ;$$ +COMMIT; +SELECT * FROM t1; +DROP TABLE t1; +SET AUTOCOMMIT=DEFAULT; |