Debian Codegraph @

mysql-test/suite/compat/oracle/r/sp-anonymous.result

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