Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-anonymous.test

raw
1 --source include/have_innodb.inc
2
3 SET sql_mode=ORACLE;
4
5 --echo #
6 --echo # MDEV-10655 Anonymous blocks
7 --echo #
8
9 --echo # Testing BEGIN NOT ATOMIC with no declarations
10 DELIMITER /;
11 BEGIN NOT ATOMIC
12 SELECT 1 AS a;
13 END
14 /
15 DELIMITER ;/
16
17 --echo # Testing BEGIN NOT ATOMIC with declarations
18 --echo # DECLARE starts a new block and thus must be followed by BEGIN .. END
19 DELIMITER /;
20 BEGIN NOT ATOMIC
21 DECLARE
22 i INT DEFAULT 5;
23 x INT DEFAULT 10;
24 BEGIN
25 <<label>>
26 WHILE i > 3 LOOP
27 i:= i - 1;
28 SELECT i;
29 END LOOP label;
30 END;
31 END
32 /
33 DELIMITER ;/
34
35
36 --echo # Anonymous blocks with no declarations and no exceptions
37
38 DELIMITER $$;
39 BEGIN
40 SELECT 1 AS a;
41 END
42 $$
43 DELIMITER ;$$
44
45
46 SET AUTOCOMMIT=OFF;
47 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
48 INSERT INTO t1 VALUES (10);
49 DELIMITER $$;
50 BEGIN
51 INSERT INTO t1 VALUES(20);
52 INSERT INTO t1 VALUES(30);
53 ROLLBACK;
54 END;
55 $$
56 DELIMITER ;$$
57 SELECT * FROM t1;
58 DROP TABLE t1;
59 SET AUTOCOMMIT=DEFAULT;
60
61
62 SET AUTOCOMMIT=OFF;
63 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
64 INSERT INTO t1 VALUES (10);
65 DELIMITER $$;
66 BEGIN
67 INSERT INTO t1 VALUES(20);
68 INSERT INTO t1 VALUES(30);
69 END;
70 $$
71 DELIMITER ;$$
72 ROLLBACK;
73 SELECT * FROM t1;
74 DROP TABLE t1;
75 SET AUTOCOMMIT=DEFAULT;
76
77
78 SET AUTOCOMMIT=OFF;
79 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
80 INSERT INTO t1 VALUES (10);
81 DELIMITER $$;
82 BEGIN
83 INSERT INTO t1 VALUES(20);
84 INSERT INTO t1 VALUES(30);
85 COMMIT;
86 END;
87 $$
88 DELIMITER ;$$
89 SELECT * FROM t1;
90 DROP TABLE t1;
91 SET AUTOCOMMIT=DEFAULT;
92
93
94 SET AUTOCOMMIT=OFF;
95 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
96 INSERT INTO t1 VALUES (10);
97 DELIMITER $$;
98 BEGIN
99 INSERT INTO t1 VALUES(20);
100 INSERT INTO t1 VALUES(30);
101 END;
102 $$
103 DELIMITER ;$$
104 COMMIT;
105 SELECT * FROM t1;
106 DROP TABLE t1;
107 SET AUTOCOMMIT=DEFAULT;
108
109
110 SET AUTOCOMMIT=OFF;
111 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
112 INSERT INTO t1 VALUES (10);
113 DELIMITER $$;
114 --error ER_DUP_ENTRY
115 BEGIN
116 INSERT INTO t1 VALUES(20);
117 INSERT INTO t1 VALUES(20);
118 END;
119 $$
120 DELIMITER ;$$
121 COMMIT;
122 SELECT * FROM t1;
123 DROP TABLE t1;
124 SET AUTOCOMMIT=DEFAULT;
125
126
127 --echo # Anonymous blocks with no declarations, with exceptions
128
129 SET AUTOCOMMIT=OFF;
130 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
131 INSERT INTO t1 VALUES (10);
132 DELIMITER $$;
133 BEGIN
134 INSERT INTO t1 VALUES(20);
135 INSERT INTO t1 VALUES(20);
136 EXCEPTION
137 WHEN DUP_VAL_ON_INDEX THEN NULL;
138 END;
139 $$
140 DELIMITER ;$$
141 COMMIT;
142 SELECT * FROM t1;
143 DROP TABLE t1;
144 SET AUTOCOMMIT=DEFAULT;
145
146
147 --echo # Anonymous blocks with declarations, with no exceptions
148
149 SET AUTOCOMMIT=OFF;
150 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
151 INSERT INTO t1 VALUES (10);
152 DELIMITER $$;
153 DECLARE
154 a20 INT:=20;
155 a30 INT:=30;
156 BEGIN
157 INSERT INTO t1 VALUES(a20);
158 INSERT INTO t1 VALUES(a30);
159 ROLLBACK;
160 END;
161 $$
162 DELIMITER ;$$
163 SELECT * FROM t1;
164 DROP TABLE t1;
165 SET AUTOCOMMIT=DEFAULT;
166
167
168 SET AUTOCOMMIT=OFF;
169 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
170 INSERT INTO t1 VALUES (10);
171 DELIMITER $$;
172 DECLARE
173 a20 INT:=20;
174 a30 INT:=30;
175 BEGIN
176 INSERT INTO t1 VALUES(a20);
177 INSERT INTO t1 VALUES(a30);
178 END;
179 $$
180 DELIMITER ;$$
181 ROLLBACK;
182 SELECT * FROM t1;
183 DROP TABLE t1;
184 SET AUTOCOMMIT=DEFAULT;
185
186
187 SET AUTOCOMMIT=OFF;
188 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
189 INSERT INTO t1 VALUES (10);
190 DELIMITER $$;
191 DECLARE
192 a20 INT:=20;
193 a30 INT:=30;
194 BEGIN
195 INSERT INTO t1 VALUES(a20);
196 INSERT INTO t1 VALUES(a30);
197 COMMIT;
198 END;
199 $$
200 DELIMITER ;$$
201 SELECT * FROM t1;
202 DROP TABLE t1;
203 SET AUTOCOMMIT=DEFAULT;
204
205
206 SET AUTOCOMMIT=OFF;
207 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
208 INSERT INTO t1 VALUES (10);
209 DELIMITER $$;
210 DECLARE
211 a20 INT:=20;
212 a30 INT:=30;
213 BEGIN
214 INSERT INTO t1 VALUES(a20);
215 INSERT INTO t1 VALUES(a30);
216 END;
217 $$
218 DELIMITER ;$$
219 COMMIT;
220 SELECT * FROM t1;
221 DROP TABLE t1;
222 SET AUTOCOMMIT=DEFAULT;
223
224
225 --echo # Anonymous blocks with declarations, with exceptions
226
227 SET AUTOCOMMIT=OFF;
228 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
229 INSERT INTO t1 VALUES (10);
230 DELIMITER $$;
231 DECLARE
232 a20 INT:=20;
233 BEGIN
234 INSERT INTO t1 VALUES(a20);
235 INSERT INTO t1 VALUES(a20);
236 EXCEPTION
237 WHEN DUP_VAL_ON_INDEX THEN NULL;
238 END;
239 $$
240 DELIMITER ;$$
241 COMMIT;
242 SELECT * FROM t1;
243 DROP TABLE t1;
244 SET AUTOCOMMIT=DEFAULT;