Debian Codegraph @

mysql-test/suite/compat/oracle/r/exception.result

raw
1 SET sql_mode=ORACLE;
2 #
3 # sql_mode=ORACLE: Predefined exceptions: TOO_MANY_ROWS, NO_DATA_FOUND, DUP_VAL_ON_INDEX
4 #
5 #
6 # Testing NO_DATA_FOUND and TOO_MANY_ROWS
7 #
8 CREATE TABLE t1 (a INT);
9 INSERT INTO t1 VALUES (10),(20);
10 CREATE PROCEDURE p1(lim INT, res OUT VARCHAR)
11 AS
12 a INT;
13 BEGIN
14 SELECT a INTO a FROM t1 LIMIT lim;
15 EXCEPTION
16 WHEN TOO_MANY_ROWS THEN res:='--- too_many_rows cought ---';
17 WHEN NO_DATA_FOUND THEN res:='--- no_data_found cought ---';
18 END;
19 $$
20 SET @res='';
21 CALL p1(0, @res);
22 SELECT @res;
23 @res
24 --- no_data_found cought ---
25 CALL p1(2, @res);
26 SELECT @res;
27 @res
28 --- too_many_rows cought ---
29 DROP PROCEDURE p1;
30 DROP TABLE t1;
31 #
32 # Testing DUP_VAL_ON_INDEX
33 #
34 CREATE TABLE t1 (a INT PRIMARY KEY);
35 CREATE PROCEDURE p1(res OUT VARCHAR)
36 AS
37 BEGIN
38 INSERT INTO t1 VALUES (10);
39 INSERT INTO t1 VALUES (10);
40 EXCEPTION
41 WHEN DUP_VAL_ON_INDEX THEN res:='--- dup_val_on_index cought ---';
42 END;
43 $$
44 SET @res='';
45 CALL p1(@res);
46 SELECT @res;
47 @res
48 --- dup_val_on_index cought ---
49 SELECT * FROM t1;
50 a
51 10
52 DROP PROCEDURE p1;
53 DROP TABLE t1;
54 #
55 # MDEV-10840 sql_mode=ORACLE: RAISE statement for predefined exceptions
56 #
57 #
58 # RAISE outside of an SP context
59 #
60 RAISE NO_DATA_FOUND;
61 ERROR 42000: Undefined CONDITION: NO_DATA_FOUND
62 RAISE INVALID_CURSOR;
63 ERROR 42000: Undefined CONDITION: INVALID_CURSOR
64 RAISE DUP_VAL_ON_INDEX;
65 ERROR 42000: Undefined CONDITION: DUP_VAL_ON_INDEX
66 RAISE TOO_MANY_ROWS;
67 ERROR 42000: Undefined CONDITION: TOO_MANY_ROWS
68 RAISE;
69 ERROR 0K000: RESIGNAL when handler not active
70 #
71 # RAISE for an undefinite exception
72 #
73 CREATE PROCEDURE p1
74 AS
75 BEGIN
76 RAISE xxx;
77 END;
78 $$
79 ERROR 42000: Undefined CONDITION: xxx
80 #
81 # RAISE for predefined exceptions
82 #
83 CREATE PROCEDURE p1
84 AS
85 BEGIN
86 RAISE no_data_found;
87 END;
88 $$
89 CALL p1();
90 Warnings:
91 Warning 1329 No data - zero rows fetched, selected, or processed
92 DROP PROCEDURE p1;
93 CREATE PROCEDURE p1
94 AS
95 BEGIN
96 RAISE invalid_cursor;
97 END;
98 $$
99 CALL p1();
100 ERROR 24000: Cursor is not open
101 DROP PROCEDURE p1;
102 CREATE PROCEDURE p1
103 AS
104 BEGIN
105 RAISE dup_val_on_index;
106 END;
107 $$
108 CALL p1();
109 ERROR 23000: Duplicate entry '%-.192sT' for key %d
110 DROP PROCEDURE p1;
111 CREATE PROCEDURE p1
112 AS
113 BEGIN
114 raise too_many_rows;
115 END;
116 $$
117 CALL p1();
118 ERROR 42000: Result consisted of more than one row
119 DROP PROCEDURE p1;
120 #
121 # RAISE with no exception name (resignal)
122 #
123 CREATE PROCEDURE p1()
124 AS
125 BEGIN
126 RAISE;
127 END;
128 $$
129 CALL p1();
130 ERROR 0K000: RESIGNAL when handler not active
131 DROP PROCEDURE p1;
132 CREATE TABLE t1 (a INT);
133 INSERT INTO t1 VALUES (10),(20);
134 CREATE PROCEDURE p1(lim INT)
135 AS
136 a INT;
137 BEGIN
138 SELECT a INTO a FROM t1 LIMIT lim;
139 EXCEPTION
140 WHEN TOO_MANY_ROWS THEN RAISE;
141 WHEN NO_DATA_FOUND THEN RAISE;
142 END;
143 $$
144 CALL p1(0);
145 Warnings:
146 Warning 1329 No data - zero rows fetched, selected, or processed
147 CALL p1(2);
148 ERROR 42000: Result consisted of more than one row
149 DROP PROCEDURE p1;
150 DROP TABLE t1;
151 CREATE TABLE t1 (a INT);
152 INSERT INTO t1 VALUES (10),(20);
153 CREATE PROCEDURE p1(lim INT)
154 AS
155 a INT;
156 BEGIN
157 SELECT a INTO a FROM t1 LIMIT lim;
158 EXCEPTION
159 WHEN OTHERS THEN RAISE;
160 END;
161 $$
162 CALL p1(0);
163 Warnings:
164 Warning 1329 No data - zero rows fetched, selected, or processed
165 CALL p1(2);
166 ERROR 42000: Result consisted of more than one row
167 DROP PROCEDURE p1;
168 DROP TABLE t1;
169 CREATE TABLE t1 (a INT);
170 INSERT INTO t1 VALUES (10),(20);
171 CREATE PROCEDURE p1()
172 AS
173 a INT;
174 CURSOR c IS SELECT a FROM t1;
175 BEGIN
176 FETCH c INTO a;
177 EXCEPTION
178 WHEN INVALID_CURSOR THEN RAISE;
179 END;
180 $$
181 CALL p1();
182 ERROR 24000: Cursor is not open
183 DROP PROCEDURE p1;
184 DROP TABLE t1;
185 CREATE TABLE t1 (a INT);
186 INSERT INTO t1 VALUES (10),(20);
187 CREATE PROCEDURE p1()
188 AS
189 a INT;
190 CURSOR c IS SELECT a FROM t1;
191 BEGIN
192 FETCH c INTO a;
193 EXCEPTION
194 WHEN OTHERS THEN RAISE;
195 END;
196 $$
197 CALL p1();
198 ERROR 24000: Cursor is not open
199 DROP PROCEDURE p1;
200 DROP TABLE t1;
201 #
202 # Testing that warning-alike errors are caught by OTHERS
203 #
204 CREATE TABLE t1 (a INT);
205 CREATE FUNCTION f1 RETURN VARCHAR
206 AS
207 a INT:=10;
208 BEGIN
209 SELECT a INTO a FROM t1;
210 RETURN 'OK';
211 EXCEPTION
212 WHEN OTHERS THEN RETURN 'Exception';
213 END;
214 $$
215 SELECT f1() FROM DUAL;
216 f1()
217 Exception
218 DROP FUNCTION f1;
219 DROP TABLE t1;
220 #
221 # End of MDEV-10840 sql_mode=ORACLE: RAISE statement for predefined exceptions
222 #
223 #
224 # MDEV-10587 sql_mode=ORACLE: User defined exceptions
225 #
226 #
227 # Checking that duplicate WHEN clause is not allowed
228 #
229 CREATE FUNCTION f1() RETURN VARCHAR
230 AS
231 e EXCEPTION;
232 BEGIN
233 RETURN 'Got no exceptions';
234 EXCEPTION
235 WHEN e THEN RETURN 'Got exception e';
236 WHEN e THEN RETURN 'Got exception e';
237 END;
238 $$
239 ERROR 42000: Duplicate handler declared in the same block
240 #
241 # Checking that raised user exceptions are further caught by name
242 #
243 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
244 AS
245 e EXCEPTION;
246 f EXCEPTION;
247 BEGIN
248 IF c = 'e' THEN RAISE e; END IF;
249 IF c = 'f' THEN RAISE f; END IF;
250 RETURN 'Got no exceptions';
251 EXCEPTION
252 WHEN e THEN RETURN 'Got exception e';
253 END;
254 $$
255 SELECT f1('');
256 f1('')
257 Got no exceptions
258 SELECT f1('e');
259 f1('e')
260 Got exception e
261 SELECT f1('f');
262 ERROR 45000: Unhandled user-defined exception condition
263 DROP FUNCTION f1;
264 #
265 # Checking that raised user exceptions are further caught by OTHERS
266 #
267 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
268 AS
269 e EXCEPTION;
270 f EXCEPTION;
271 BEGIN
272 IF c = 'e' THEN RAISE e; END IF;
273 IF c = 'f' THEN RAISE f; END IF;
274 RETURN 'Got no exceptions';
275 EXCEPTION
276 WHEN OTHERS THEN RETURN 'Got some exception';
277 END;
278 $$
279 SELECT f1('');
280 f1('')
281 Got no exceptions
282 SELECT f1('e');
283 f1('e')
284 Got some exception
285 SELECT f1('f');
286 f1('f')
287 Got some exception
288 DROP FUNCTION f1;
289 #
290 # Checking that 'WHEN e .. WHEN f' does not produce ER_SP_DUP_HANDLER
291 #
292 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
293 AS
294 e EXCEPTION;
295 f EXCEPTION;
296 a VARCHAR(64):='';
297 BEGIN
298 BEGIN
299 IF c = 'e' THEN RAISE e; END IF;
300 IF c = 'f' THEN RAISE f; END IF;
301 EXCEPTION
302 WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE e; END;
303 WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE f; END;
304 END;
305 RETURN 'Got no exceptions';
306 EXCEPTION
307 WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;';
308 END;
309 $$
310 SELECT f1('');
311 f1('')
312 Got no exceptions
313 SELECT f1('e');
314 f1('e')
315 Got EXCEPTION1/e; Got EXCEPTION2/OTHERS;
316 SELECT f1('f');
317 f1('f')
318 Got EXCEPTION1/f; Got EXCEPTION2/OTHERS;
319 DROP FUNCTION f1;
320 #
321 # Checking that resignaled user exceptions are further caught by name
322 #
323 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
324 AS
325 e EXCEPTION;
326 f EXCEPTION;
327 a VARCHAR(64):='';
328 BEGIN
329 BEGIN
330 IF c = 'e' THEN RAISE e; END IF;
331 IF c = 'f' THEN RAISE f; END IF;
332 EXCEPTION
333 WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE; END;
334 WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE; END;
335 END;
336 RETURN 'Got no exceptions';
337 EXCEPTION
338 WHEN e THEN RETURN a || 'Got EXCEPTION2/e;';
339 END;
340 $$
341 SELECT f1('');
342 f1('')
343 Got no exceptions
344 SELECT f1('e');
345 f1('e')
346 Got EXCEPTION1/e; Got EXCEPTION2/e;
347 SELECT f1('f');
348 ERROR 45000: Unhandled user-defined exception condition
349 DROP FUNCTION f1;
350 #
351 # Checking that resignaled user exceptions are further caught by OTHERS
352 #
353 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
354 AS
355 e EXCEPTION;
356 f EXCEPTION;
357 a VARCHAR(64):='';
358 BEGIN
359 BEGIN
360 IF c = 'e' THEN RAISE e; END IF;
361 IF c = 'f' THEN RAISE f; END IF;
362 EXCEPTION
363 WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE; END;
364 WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE; END;
365 END;
366 RETURN 'Got no exceptions';
367 EXCEPTION
368 WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;';
369 END;
370 $$
371 SELECT f1('');
372 f1('')
373 Got no exceptions
374 SELECT f1('e');
375 f1('e')
376 Got EXCEPTION1/e; Got EXCEPTION2/OTHERS;
377 SELECT f1('f');
378 f1('f')
379 Got EXCEPTION1/f; Got EXCEPTION2/OTHERS;
380 DROP FUNCTION f1;
381 #
382 # End of MDEV-10587 sql_mode=ORACLE: User defined exceptions
383 #
384 #
385 # MDEV-12088 sql_mode=ORACLE: Do not require BEGIN..END in multi-statement exception handlers in THEN clause
386 #
387 CREATE TABLE t1 (a INT PRIMARY KEY);
388 INSERT INTO t1 VALUES (10),(20),(30);
389 CREATE PROCEDURE p1(a INT) AS
390 BEGIN
391 INSERT INTO t1 (a) VALUES (a);
392 EXCEPTION
393 WHEN DUP_VAL_ON_INDEX THEN
394 a:= a+1;
395 INSERT INTO t1 VALUES (a);
396 WHEN OTHERS THEN
397 NULL;
398 NULL;
399 END;
400 $$
401 CALL p1(30);
402 SELECT * FROM t1;
403 a
404 10
405 20
406 30
407 31
408 DROP PROCEDURE p1;
409 DROP TABLE t1;