Debian Codegraph @

mysql-test/suite/engines/funcs/t/sp_alter.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3;
3 DROP PROCEDURE IF EXISTS sp1;
4 --enable_warnings
5 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
6 CREATE TABLE t2 (i INTEGER NULL);
7 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
8 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
9 INSERT INTO t2 VALUES(1),(2),(3);
10 DELIMITER //;
11 CREATE PROCEDURE sp1()
12 BEGIN
13 DECLARE done INT DEFAULT 0;
14 DECLARE a CHAR(16);
15 DECLARE b,c INT;
16 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
17 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
18 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
19 OPEN cur1;
20 OPEN cur2;
21 REPEAT
22 FETCH cur1 INTO a, b;
23 FETCH cur2 INTO c;
24 IF NOT done THEN
25 IF b < c THEN
26 INSERT INTO t3 VALUES (a,b);
27 ELSE
28 INSERT INTO t3 VALUES (a,c);
29 END IF;
30 END IF;
31 UNTIL done END REPEAT;
32 CLOSE cur1;
33 CLOSE cur2;
34 END//
35 DELIMITER ;//
36 ALTER PROCEDURE sp1 #DET# ;
37 CALL sp1();
38 SELECT * FROM t1 ORDER BY id;
39 SELECT * FROM t2 ORDER BY i;
40 SELECT * FROM t3 ORDER BY id;
41 DROP PROCEDURE sp1;
42 DROP TABLE t1,t2,t3;
43 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
44 CREATE TABLE t2 (i INTEGER NULL);
45 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
46 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
47 INSERT INTO t2 VALUES(1),(2),(3);
48 DELIMITER //;
49 CREATE PROCEDURE sp1()
50 BEGIN
51 DECLARE done INT DEFAULT 0;
52 DECLARE a CHAR(16);
53 DECLARE b,c INT;
54 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
55 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
56 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
57 OPEN cur1;
58 OPEN cur2;
59 REPEAT
60 FETCH cur1 INTO a, b;
61 FETCH cur2 INTO c;
62 IF NOT done THEN
63 IF b < c THEN
64 INSERT INTO t3 VALUES (a,b);
65 ELSE
66 INSERT INTO t3 VALUES (a,c);
67 END IF;
68 END IF;
69 UNTIL done END REPEAT;
70 CLOSE cur1;
71 CLOSE cur2;
72 END//
73 DELIMITER ;//
74 ALTER PROCEDURE sp1 #DET# CONTAINS SQL ;
75 CALL sp1();
76 SELECT * FROM t1 ORDER BY id;
77 SELECT * FROM t2 ORDER BY i;
78 SELECT * FROM t3 ORDER BY id;
79 DROP PROCEDURE sp1;
80 DROP TABLE t1,t2,t3;
81 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
82 CREATE TABLE t2 (i INTEGER NULL);
83 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
84 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
85 INSERT INTO t2 VALUES(1),(2),(3);
86 DELIMITER //;
87 CREATE PROCEDURE sp1()
88 BEGIN
89 DECLARE done INT DEFAULT 0;
90 DECLARE a CHAR(16);
91 DECLARE b,c INT;
92 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
93 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
94 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
95 OPEN cur1;
96 OPEN cur2;
97 REPEAT
98 FETCH cur1 INTO a, b;
99 FETCH cur2 INTO c;
100 IF NOT done THEN
101 IF b < c THEN
102 INSERT INTO t3 VALUES (a,b);
103 ELSE
104 INSERT INTO t3 VALUES (a,c);
105 END IF;
106 END IF;
107 UNTIL done END REPEAT;
108 CLOSE cur1;
109 CLOSE cur2;
110 END//
111 DELIMITER ;//
112 ALTER PROCEDURE sp1 #DET# NO SQL ;
113 CALL sp1();
114 SELECT * FROM t1 ORDER BY id;
115 SELECT * FROM t2 ORDER BY i;
116 SELECT * FROM t3 ORDER BY id;
117 DROP PROCEDURE sp1;
118 DROP TABLE t1,t2,t3;
119 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
120 CREATE TABLE t2 (i INTEGER NULL);
121 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
122 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
123 INSERT INTO t2 VALUES(1),(2),(3);
124 DELIMITER //;
125 CREATE PROCEDURE sp1()
126 BEGIN
127 DECLARE done INT DEFAULT 0;
128 DECLARE a CHAR(16);
129 DECLARE b,c INT;
130 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
131 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
132 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
133 OPEN cur1;
134 OPEN cur2;
135 REPEAT
136 FETCH cur1 INTO a, b;
137 FETCH cur2 INTO c;
138 IF NOT done THEN
139 IF b < c THEN
140 INSERT INTO t3 VALUES (a,b);
141 ELSE
142 INSERT INTO t3 VALUES (a,c);
143 END IF;
144 END IF;
145 UNTIL done END REPEAT;
146 CLOSE cur1;
147 CLOSE cur2;
148 END//
149 DELIMITER ;//
150 ALTER PROCEDURE sp1 #DET# READS SQL DATA ;
151 CALL sp1();
152 SELECT * FROM t1 ORDER BY id;
153 SELECT * FROM t2 ORDER BY i;
154 SELECT * FROM t3 ORDER BY id;
155 DROP PROCEDURE sp1;
156 DROP TABLE t1,t2,t3;
157 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
158 CREATE TABLE t2 (i INTEGER NULL);
159 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
160 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
161 INSERT INTO t2 VALUES(1),(2),(3);
162 DELIMITER //;
163 CREATE PROCEDURE sp1()
164 BEGIN
165 DECLARE done INT DEFAULT 0;
166 DECLARE a CHAR(16);
167 DECLARE b,c INT;
168 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
169 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
170 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
171 OPEN cur1;
172 OPEN cur2;
173 REPEAT
174 FETCH cur1 INTO a, b;
175 FETCH cur2 INTO c;
176 IF NOT done THEN
177 IF b < c THEN
178 INSERT INTO t3 VALUES (a,b);
179 ELSE
180 INSERT INTO t3 VALUES (a,c);
181 END IF;
182 END IF;
183 UNTIL done END REPEAT;
184 CLOSE cur1;
185 CLOSE cur2;
186 END//
187 DELIMITER ;//
188 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA ;
189 CALL sp1();
190 SELECT * FROM t1 ORDER BY id;
191 SELECT * FROM t2 ORDER BY i;
192 SELECT * FROM t3 ORDER BY id;
193 DROP PROCEDURE sp1;
194 DROP TABLE t1,t2,t3;
195 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
196 CREATE TABLE t2 (i INTEGER NULL);
197 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
198 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
199 INSERT INTO t2 VALUES(1),(2),(3);
200 DELIMITER //;
201 CREATE PROCEDURE sp1()
202 BEGIN
203 DECLARE done INT DEFAULT 0;
204 DECLARE a CHAR(16);
205 DECLARE b,c INT;
206 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
207 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
208 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
209 OPEN cur1;
210 OPEN cur2;
211 REPEAT
212 FETCH cur1 INTO a, b;
213 FETCH cur2 INTO c;
214 IF NOT done THEN
215 IF b < c THEN
216 INSERT INTO t3 VALUES (a,b);
217 ELSE
218 INSERT INTO t3 VALUES (a,c);
219 END IF;
220 END IF;
221 UNTIL done END REPEAT;
222 CLOSE cur1;
223 CLOSE cur2;
224 END//
225 DELIMITER ;//
226 ALTER PROCEDURE sp1 #DET# COMMENT 'comment' ;
227 CALL sp1();
228 SELECT * FROM t1 ORDER BY id;
229 SELECT * FROM t2 ORDER BY i;
230 SELECT * FROM t3 ORDER BY id;
231 DROP PROCEDURE sp1;
232 DROP TABLE t1,t2,t3;
233 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
234 CREATE TABLE t2 (i INTEGER NULL);
235 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
236 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
237 INSERT INTO t2 VALUES(1),(2),(3);
238 DELIMITER //;
239 CREATE PROCEDURE sp1()
240 BEGIN
241 DECLARE done INT DEFAULT 0;
242 DECLARE a CHAR(16);
243 DECLARE b,c INT;
244 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
245 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
246 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
247 OPEN cur1;
248 OPEN cur2;
249 REPEAT
250 FETCH cur1 INTO a, b;
251 FETCH cur2 INTO c;
252 IF NOT done THEN
253 IF b < c THEN
254 INSERT INTO t3 VALUES (a,b);
255 ELSE
256 INSERT INTO t3 VALUES (a,c);
257 END IF;
258 END IF;
259 UNTIL done END REPEAT;
260 CLOSE cur1;
261 CLOSE cur2;
262 END//
263 DELIMITER ;//
264 ALTER PROCEDURE sp1 #DET# CONTAINS SQL COMMENT 'comment' ;
265 CALL sp1();
266 SELECT * FROM t1 ORDER BY id;
267 SELECT * FROM t2 ORDER BY i;
268 SELECT * FROM t3 ORDER BY id;
269 DROP PROCEDURE sp1;
270 DROP TABLE t1,t2,t3;
271 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
272 CREATE TABLE t2 (i INTEGER NULL);
273 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
274 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
275 INSERT INTO t2 VALUES(1),(2),(3);
276 DELIMITER //;
277 CREATE PROCEDURE sp1()
278 BEGIN
279 DECLARE done INT DEFAULT 0;
280 DECLARE a CHAR(16);
281 DECLARE b,c INT;
282 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
283 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
284 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
285 OPEN cur1;
286 OPEN cur2;
287 REPEAT
288 FETCH cur1 INTO a, b;
289 FETCH cur2 INTO c;
290 IF NOT done THEN
291 IF b < c THEN
292 INSERT INTO t3 VALUES (a,b);
293 ELSE
294 INSERT INTO t3 VALUES (a,c);
295 END IF;
296 END IF;
297 UNTIL done END REPEAT;
298 CLOSE cur1;
299 CLOSE cur2;
300 END//
301 DELIMITER ;//
302 ALTER PROCEDURE sp1 #DET# NO SQL COMMENT 'comment' ;
303 CALL sp1();
304 SELECT * FROM t1 ORDER BY id;
305 SELECT * FROM t2 ORDER BY i;
306 SELECT * FROM t3 ORDER BY id;
307 DROP PROCEDURE sp1;
308 DROP TABLE t1,t2,t3;
309 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
310 CREATE TABLE t2 (i INTEGER NULL);
311 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
312 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
313 INSERT INTO t2 VALUES(1),(2),(3);
314 DELIMITER //;
315 CREATE PROCEDURE sp1()
316 BEGIN
317 DECLARE done INT DEFAULT 0;
318 DECLARE a CHAR(16);
319 DECLARE b,c INT;
320 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
321 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
322 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
323 OPEN cur1;
324 OPEN cur2;
325 REPEAT
326 FETCH cur1 INTO a, b;
327 FETCH cur2 INTO c;
328 IF NOT done THEN
329 IF b < c THEN
330 INSERT INTO t3 VALUES (a,b);
331 ELSE
332 INSERT INTO t3 VALUES (a,c);
333 END IF;
334 END IF;
335 UNTIL done END REPEAT;
336 CLOSE cur1;
337 CLOSE cur2;
338 END//
339 DELIMITER ;//
340 ALTER PROCEDURE sp1 #DET# READS SQL DATA COMMENT 'comment' ;
341 CALL sp1();
342 SELECT * FROM t1 ORDER BY id;
343 SELECT * FROM t2 ORDER BY i;
344 SELECT * FROM t3 ORDER BY id;
345 DROP PROCEDURE sp1;
346 DROP TABLE t1,t2,t3;
347 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
348 CREATE TABLE t2 (i INTEGER NULL);
349 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
350 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
351 INSERT INTO t2 VALUES(1),(2),(3);
352 DELIMITER //;
353 CREATE PROCEDURE sp1()
354 BEGIN
355 DECLARE done INT DEFAULT 0;
356 DECLARE a CHAR(16);
357 DECLARE b,c INT;
358 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
359 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
360 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
361 OPEN cur1;
362 OPEN cur2;
363 REPEAT
364 FETCH cur1 INTO a, b;
365 FETCH cur2 INTO c;
366 IF NOT done THEN
367 IF b < c THEN
368 INSERT INTO t3 VALUES (a,b);
369 ELSE
370 INSERT INTO t3 VALUES (a,c);
371 END IF;
372 END IF;
373 UNTIL done END REPEAT;
374 CLOSE cur1;
375 CLOSE cur2;
376 END//
377 DELIMITER ;//
378 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA COMMENT 'comment' ;
379 CALL sp1();
380 SELECT * FROM t1 ORDER BY id;
381 SELECT * FROM t2 ORDER BY i;
382 SELECT * FROM t3 ORDER BY id;
383 DROP PROCEDURE sp1;
384 DROP TABLE t1,t2,t3;
385 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
386 CREATE TABLE t2 (i INTEGER NULL);
387 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
388 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
389 INSERT INTO t2 VALUES(1),(2),(3);
390 DELIMITER //;
391 CREATE PROCEDURE sp1()
392 BEGIN
393 DECLARE done INT DEFAULT 0;
394 DECLARE a CHAR(16);
395 DECLARE b,c INT;
396 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
397 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
398 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
399 OPEN cur1;
400 OPEN cur2;
401 REPEAT
402 FETCH cur1 INTO a, b;
403 FETCH cur2 INTO c;
404 IF NOT done THEN
405 IF b < c THEN
406 INSERT INTO t3 VALUES (a,b);
407 ELSE
408 INSERT INTO t3 VALUES (a,c);
409 END IF;
410 END IF;
411 UNTIL done END REPEAT;
412 CLOSE cur1;
413 CLOSE cur2;
414 END//
415 DELIMITER ;//
416 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# ;
417 CALL sp1();
418 SELECT * FROM t1 ORDER BY id;
419 SELECT * FROM t2 ORDER BY i;
420 SELECT * FROM t3 ORDER BY id;
421 DROP PROCEDURE sp1;
422 DROP TABLE t1,t2,t3;
423 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
424 CREATE TABLE t2 (i INTEGER NULL);
425 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
426 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
427 INSERT INTO t2 VALUES(1),(2),(3);
428 DELIMITER //;
429 CREATE PROCEDURE sp1()
430 BEGIN
431 DECLARE done INT DEFAULT 0;
432 DECLARE a CHAR(16);
433 DECLARE b,c INT;
434 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
435 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
436 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
437 OPEN cur1;
438 OPEN cur2;
439 REPEAT
440 FETCH cur1 INTO a, b;
441 FETCH cur2 INTO c;
442 IF NOT done THEN
443 IF b < c THEN
444 INSERT INTO t3 VALUES (a,b);
445 ELSE
446 INSERT INTO t3 VALUES (a,c);
447 END IF;
448 END IF;
449 UNTIL done END REPEAT;
450 CLOSE cur1;
451 CLOSE cur2;
452 END//
453 DELIMITER ;//
454 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL ;
455 CALL sp1();
456 SELECT * FROM t1 ORDER BY id;
457 SELECT * FROM t2 ORDER BY i;
458 SELECT * FROM t3 ORDER BY id;
459 DROP PROCEDURE sp1;
460 DROP TABLE t1,t2,t3;
461 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
462 CREATE TABLE t2 (i INTEGER NULL);
463 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
464 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
465 INSERT INTO t2 VALUES(1),(2),(3);
466 DELIMITER //;
467 CREATE PROCEDURE sp1()
468 BEGIN
469 DECLARE done INT DEFAULT 0;
470 DECLARE a CHAR(16);
471 DECLARE b,c INT;
472 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
473 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
474 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
475 OPEN cur1;
476 OPEN cur2;
477 REPEAT
478 FETCH cur1 INTO a, b;
479 FETCH cur2 INTO c;
480 IF NOT done THEN
481 IF b < c THEN
482 INSERT INTO t3 VALUES (a,b);
483 ELSE
484 INSERT INTO t3 VALUES (a,c);
485 END IF;
486 END IF;
487 UNTIL done END REPEAT;
488 CLOSE cur1;
489 CLOSE cur2;
490 END//
491 DELIMITER ;//
492 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL ;
493 CALL sp1();
494 SELECT * FROM t1 ORDER BY id;
495 SELECT * FROM t2 ORDER BY i;
496 SELECT * FROM t3 ORDER BY id;
497 DROP PROCEDURE sp1;
498 DROP TABLE t1,t2,t3;
499 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
500 CREATE TABLE t2 (i INTEGER NULL);
501 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
502 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
503 INSERT INTO t2 VALUES(1),(2),(3);
504 DELIMITER //;
505 CREATE PROCEDURE sp1()
506 BEGIN
507 DECLARE done INT DEFAULT 0;
508 DECLARE a CHAR(16);
509 DECLARE b,c INT;
510 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
511 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
512 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
513 OPEN cur1;
514 OPEN cur2;
515 REPEAT
516 FETCH cur1 INTO a, b;
517 FETCH cur2 INTO c;
518 IF NOT done THEN
519 IF b < c THEN
520 INSERT INTO t3 VALUES (a,b);
521 ELSE
522 INSERT INTO t3 VALUES (a,c);
523 END IF;
524 END IF;
525 UNTIL done END REPEAT;
526 CLOSE cur1;
527 CLOSE cur2;
528 END//
529 DELIMITER ;//
530 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA ;
531 CALL sp1();
532 SELECT * FROM t1 ORDER BY id;
533 SELECT * FROM t2 ORDER BY i;
534 SELECT * FROM t3 ORDER BY id;
535 DROP PROCEDURE sp1;
536 DROP TABLE t1,t2,t3;
537 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
538 CREATE TABLE t2 (i INTEGER NULL);
539 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
540 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
541 INSERT INTO t2 VALUES(1),(2),(3);
542 DELIMITER //;
543 CREATE PROCEDURE sp1()
544 BEGIN
545 DECLARE done INT DEFAULT 0;
546 DECLARE a CHAR(16);
547 DECLARE b,c INT;
548 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
549 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
550 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
551 OPEN cur1;
552 OPEN cur2;
553 REPEAT
554 FETCH cur1 INTO a, b;
555 FETCH cur2 INTO c;
556 IF NOT done THEN
557 IF b < c THEN
558 INSERT INTO t3 VALUES (a,b);
559 ELSE
560 INSERT INTO t3 VALUES (a,c);
561 END IF;
562 END IF;
563 UNTIL done END REPEAT;
564 CLOSE cur1;
565 CLOSE cur2;
566 END//
567 DELIMITER ;//
568 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA ;
569 CALL sp1();
570 SELECT * FROM t1 ORDER BY id;
571 SELECT * FROM t2 ORDER BY i;
572 SELECT * FROM t3 ORDER BY id;
573 DROP PROCEDURE sp1;
574 DROP TABLE t1,t2,t3;
575 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
576 CREATE TABLE t2 (i INTEGER NULL);
577 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
578 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
579 INSERT INTO t2 VALUES(1),(2),(3);
580 DELIMITER //;
581 CREATE PROCEDURE sp1()
582 BEGIN
583 DECLARE done INT DEFAULT 0;
584 DECLARE a CHAR(16);
585 DECLARE b,c INT;
586 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
587 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
588 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
589 OPEN cur1;
590 OPEN cur2;
591 REPEAT
592 FETCH cur1 INTO a, b;
593 FETCH cur2 INTO c;
594 IF NOT done THEN
595 IF b < c THEN
596 INSERT INTO t3 VALUES (a,b);
597 ELSE
598 INSERT INTO t3 VALUES (a,c);
599 END IF;
600 END IF;
601 UNTIL done END REPEAT;
602 CLOSE cur1;
603 CLOSE cur2;
604 END//
605 DELIMITER ;//
606 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# COMMENT 'comment' ;
607 CALL sp1();
608 SELECT * FROM t1 ORDER BY id;
609 SELECT * FROM t2 ORDER BY i;
610 SELECT * FROM t3 ORDER BY id;
611 DROP PROCEDURE sp1;
612 DROP TABLE t1,t2,t3;
613 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
614 CREATE TABLE t2 (i INTEGER NULL);
615 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
616 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
617 INSERT INTO t2 VALUES(1),(2),(3);
618 DELIMITER //;
619 CREATE PROCEDURE sp1()
620 BEGIN
621 DECLARE done INT DEFAULT 0;
622 DECLARE a CHAR(16);
623 DECLARE b,c INT;
624 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
625 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
626 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
627 OPEN cur1;
628 OPEN cur2;
629 REPEAT
630 FETCH cur1 INTO a, b;
631 FETCH cur2 INTO c;
632 IF NOT done THEN
633 IF b < c THEN
634 INSERT INTO t3 VALUES (a,b);
635 ELSE
636 INSERT INTO t3 VALUES (a,c);
637 END IF;
638 END IF;
639 UNTIL done END REPEAT;
640 CLOSE cur1;
641 CLOSE cur2;
642 END//
643 DELIMITER ;//
644 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL COMMENT 'comment' ;
645 CALL sp1();
646 SELECT * FROM t1 ORDER BY id;
647 SELECT * FROM t2 ORDER BY i;
648 SELECT * FROM t3 ORDER BY id;
649 DROP PROCEDURE sp1;
650 DROP TABLE t1,t2,t3;
651 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
652 CREATE TABLE t2 (i INTEGER NULL);
653 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
654 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
655 INSERT INTO t2 VALUES(1),(2),(3);
656 DELIMITER //;
657 CREATE PROCEDURE sp1()
658 BEGIN
659 DECLARE done INT DEFAULT 0;
660 DECLARE a CHAR(16);
661 DECLARE b,c INT;
662 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
663 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
664 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
665 OPEN cur1;
666 OPEN cur2;
667 REPEAT
668 FETCH cur1 INTO a, b;
669 FETCH cur2 INTO c;
670 IF NOT done THEN
671 IF b < c THEN
672 INSERT INTO t3 VALUES (a,b);
673 ELSE
674 INSERT INTO t3 VALUES (a,c);
675 END IF;
676 END IF;
677 UNTIL done END REPEAT;
678 CLOSE cur1;
679 CLOSE cur2;
680 END//
681 DELIMITER ;//
682 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL COMMENT 'comment' ;
683 CALL sp1();
684 SELECT * FROM t1 ORDER BY id;
685 SELECT * FROM t2 ORDER BY i;
686 SELECT * FROM t3 ORDER BY id;
687 DROP PROCEDURE sp1;
688 DROP TABLE t1,t2,t3;
689 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
690 CREATE TABLE t2 (i INTEGER NULL);
691 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
692 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
693 INSERT INTO t2 VALUES(1),(2),(3);
694 DELIMITER //;
695 CREATE PROCEDURE sp1()
696 BEGIN
697 DECLARE done INT DEFAULT 0;
698 DECLARE a CHAR(16);
699 DECLARE b,c INT;
700 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
701 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
702 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
703 OPEN cur1;
704 OPEN cur2;
705 REPEAT
706 FETCH cur1 INTO a, b;
707 FETCH cur2 INTO c;
708 IF NOT done THEN
709 IF b < c THEN
710 INSERT INTO t3 VALUES (a,b);
711 ELSE
712 INSERT INTO t3 VALUES (a,c);
713 END IF;
714 END IF;
715 UNTIL done END REPEAT;
716 CLOSE cur1;
717 CLOSE cur2;
718 END//
719 DELIMITER ;//
720 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA COMMENT 'comment' ;
721 CALL sp1();
722 SELECT * FROM t1 ORDER BY id;
723 SELECT * FROM t2 ORDER BY i;
724 SELECT * FROM t3 ORDER BY id;
725 DROP PROCEDURE sp1;
726 DROP TABLE t1,t2,t3;
727 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
728 CREATE TABLE t2 (i INTEGER NULL);
729 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
730 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
731 INSERT INTO t2 VALUES(1),(2),(3);
732 DELIMITER //;
733 CREATE PROCEDURE sp1()
734 BEGIN
735 DECLARE done INT DEFAULT 0;
736 DECLARE a CHAR(16);
737 DECLARE b,c INT;
738 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
739 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
740 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
741 OPEN cur1;
742 OPEN cur2;
743 REPEAT
744 FETCH cur1 INTO a, b;
745 FETCH cur2 INTO c;
746 IF NOT done THEN
747 IF b < c THEN
748 INSERT INTO t3 VALUES (a,b);
749 ELSE
750 INSERT INTO t3 VALUES (a,c);
751 END IF;
752 END IF;
753 UNTIL done END REPEAT;
754 CLOSE cur1;
755 CLOSE cur2;
756 END//
757 DELIMITER ;//
758 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA COMMENT 'comment' ;
759 CALL sp1();
760 SELECT * FROM t1 ORDER BY id;
761 SELECT * FROM t2 ORDER BY i;
762 SELECT * FROM t3 ORDER BY id;
763 DROP PROCEDURE sp1;
764 DROP TABLE t1,t2,t3;
765 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
766 CREATE TABLE t2 (i INTEGER NULL);
767 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
768 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
769 INSERT INTO t2 VALUES(1),(2),(3);
770 DELIMITER //;
771 CREATE PROCEDURE sp1()
772 BEGIN
773 DECLARE done INT DEFAULT 0;
774 DECLARE a CHAR(16);
775 DECLARE b,c INT;
776 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
777 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
778 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
779 OPEN cur1;
780 OPEN cur2;
781 REPEAT
782 FETCH cur1 INTO a, b;
783 FETCH cur2 INTO c;
784 IF NOT done THEN
785 IF b < c THEN
786 INSERT INTO t3 VALUES (a,b);
787 ELSE
788 INSERT INTO t3 VALUES (a,c);
789 END IF;
790 END IF;
791 UNTIL done END REPEAT;
792 CLOSE cur1;
793 CLOSE cur2;
794 END//
795 DELIMITER ;//
796 ALTER PROCEDURE sp1 #DET# SQL SECURITY INVOKER ;
797 CALL sp1();
798 SELECT * FROM t1 ORDER BY id;
799 SELECT * FROM t2 ORDER BY i;
800 SELECT * FROM t3 ORDER BY id;
801 DROP PROCEDURE sp1;
802 DROP TABLE t1,t2,t3;
803 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
804 CREATE TABLE t2 (i INTEGER NULL);
805 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
806 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
807 INSERT INTO t2 VALUES(1),(2),(3);
808 DELIMITER //;
809 CREATE PROCEDURE sp1()
810 BEGIN
811 DECLARE done INT DEFAULT 0;
812 DECLARE a CHAR(16);
813 DECLARE b,c INT;
814 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
815 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
816 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
817 OPEN cur1;
818 OPEN cur2;
819 REPEAT
820 FETCH cur1 INTO a, b;
821 FETCH cur2 INTO c;
822 IF NOT done THEN
823 IF b < c THEN
824 INSERT INTO t3 VALUES (a,b);
825 ELSE
826 INSERT INTO t3 VALUES (a,c);
827 END IF;
828 END IF;
829 UNTIL done END REPEAT;
830 CLOSE cur1;
831 CLOSE cur2;
832 END//
833 DELIMITER ;//
834 ALTER PROCEDURE sp1 #DET# CONTAINS SQL SQL SECURITY INVOKER ;
835 CALL sp1();
836 SELECT * FROM t1 ORDER BY id;
837 SELECT * FROM t2 ORDER BY i;
838 SELECT * FROM t3 ORDER BY id;
839 DROP PROCEDURE sp1;
840 DROP TABLE t1,t2,t3;
841 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
842 CREATE TABLE t2 (i INTEGER NULL);
843 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
844 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
845 INSERT INTO t2 VALUES(1),(2),(3);
846 DELIMITER //;
847 CREATE PROCEDURE sp1()
848 BEGIN
849 DECLARE done INT DEFAULT 0;
850 DECLARE a CHAR(16);
851 DECLARE b,c INT;
852 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
853 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
854 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
855 OPEN cur1;
856 OPEN cur2;
857 REPEAT
858 FETCH cur1 INTO a, b;
859 FETCH cur2 INTO c;
860 IF NOT done THEN
861 IF b < c THEN
862 INSERT INTO t3 VALUES (a,b);
863 ELSE
864 INSERT INTO t3 VALUES (a,c);
865 END IF;
866 END IF;
867 UNTIL done END REPEAT;
868 CLOSE cur1;
869 CLOSE cur2;
870 END//
871 DELIMITER ;//
872 ALTER PROCEDURE sp1 #DET# NO SQL SQL SECURITY INVOKER ;
873 CALL sp1();
874 SELECT * FROM t1 ORDER BY id;
875 SELECT * FROM t2 ORDER BY i;
876 SELECT * FROM t3 ORDER BY id;
877 DROP PROCEDURE sp1;
878 DROP TABLE t1,t2,t3;
879 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
880 CREATE TABLE t2 (i INTEGER NULL);
881 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
882 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
883 INSERT INTO t2 VALUES(1),(2),(3);
884 DELIMITER //;
885 CREATE PROCEDURE sp1()
886 BEGIN
887 DECLARE done INT DEFAULT 0;
888 DECLARE a CHAR(16);
889 DECLARE b,c INT;
890 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
891 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
892 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
893 OPEN cur1;
894 OPEN cur2;
895 REPEAT
896 FETCH cur1 INTO a, b;
897 FETCH cur2 INTO c;
898 IF NOT done THEN
899 IF b < c THEN
900 INSERT INTO t3 VALUES (a,b);
901 ELSE
902 INSERT INTO t3 VALUES (a,c);
903 END IF;
904 END IF;
905 UNTIL done END REPEAT;
906 CLOSE cur1;
907 CLOSE cur2;
908 END//
909 DELIMITER ;//
910 ALTER PROCEDURE sp1 #DET# READS SQL DATA SQL SECURITY INVOKER ;
911 CALL sp1();
912 SELECT * FROM t1 ORDER BY id;
913 SELECT * FROM t2 ORDER BY i;
914 SELECT * FROM t3 ORDER BY id;
915 DROP PROCEDURE sp1;
916 DROP TABLE t1,t2,t3;
917 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
918 CREATE TABLE t2 (i INTEGER NULL);
919 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
920 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
921 INSERT INTO t2 VALUES(1),(2),(3);
922 DELIMITER //;
923 CREATE PROCEDURE sp1()
924 BEGIN
925 DECLARE done INT DEFAULT 0;
926 DECLARE a CHAR(16);
927 DECLARE b,c INT;
928 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
929 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
930 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
931 OPEN cur1;
932 OPEN cur2;
933 REPEAT
934 FETCH cur1 INTO a, b;
935 FETCH cur2 INTO c;
936 IF NOT done THEN
937 IF b < c THEN
938 INSERT INTO t3 VALUES (a,b);
939 ELSE
940 INSERT INTO t3 VALUES (a,c);
941 END IF;
942 END IF;
943 UNTIL done END REPEAT;
944 CLOSE cur1;
945 CLOSE cur2;
946 END//
947 DELIMITER ;//
948 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA SQL SECURITY INVOKER ;
949 CALL sp1();
950 SELECT * FROM t1 ORDER BY id;
951 SELECT * FROM t2 ORDER BY i;
952 SELECT * FROM t3 ORDER BY id;
953 DROP PROCEDURE sp1;
954 DROP TABLE t1,t2,t3;
955 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
956 CREATE TABLE t2 (i INTEGER NULL);
957 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
958 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
959 INSERT INTO t2 VALUES(1),(2),(3);
960 DELIMITER //;
961 CREATE PROCEDURE sp1()
962 BEGIN
963 DECLARE done INT DEFAULT 0;
964 DECLARE a CHAR(16);
965 DECLARE b,c INT;
966 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
967 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
968 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
969 OPEN cur1;
970 OPEN cur2;
971 REPEAT
972 FETCH cur1 INTO a, b;
973 FETCH cur2 INTO c;
974 IF NOT done THEN
975 IF b < c THEN
976 INSERT INTO t3 VALUES (a,b);
977 ELSE
978 INSERT INTO t3 VALUES (a,c);
979 END IF;
980 END IF;
981 UNTIL done END REPEAT;
982 CLOSE cur1;
983 CLOSE cur2;
984 END//
985 DELIMITER ;//
986 ALTER PROCEDURE sp1 #DET# SQL SECURITY INVOKER COMMENT 'comment' ;
987 CALL sp1();
988 SELECT * FROM t1 ORDER BY id;
989 SELECT * FROM t2 ORDER BY i;
990 SELECT * FROM t3 ORDER BY id;
991 DROP PROCEDURE sp1;
992 DROP TABLE t1,t2,t3;
993 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
994 CREATE TABLE t2 (i INTEGER NULL);
995 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
996 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
997 INSERT INTO t2 VALUES(1),(2),(3);
998 DELIMITER //;
999 CREATE PROCEDURE sp1()
1000 BEGIN
1001 DECLARE done INT DEFAULT 0;
1002 DECLARE a CHAR(16);
1003 DECLARE b,c INT;
1004 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1005 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1006 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1007 OPEN cur1;
1008 OPEN cur2;
1009 REPEAT
1010 FETCH cur1 INTO a, b;
1011 FETCH cur2 INTO c;
1012 IF NOT done THEN
1013 IF b < c THEN
1014 INSERT INTO t3 VALUES (a,b);
1015 ELSE
1016 INSERT INTO t3 VALUES (a,c);
1017 END IF;
1018 END IF;
1019 UNTIL done END REPEAT;
1020 CLOSE cur1;
1021 CLOSE cur2;
1022 END//
1023 DELIMITER ;//
1024 ALTER PROCEDURE sp1 #DET# CONTAINS SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1025 CALL sp1();
1026 SELECT * FROM t1 ORDER BY id;
1027 SELECT * FROM t2 ORDER BY i;
1028 SELECT * FROM t3 ORDER BY id;
1029 DROP PROCEDURE sp1;
1030 DROP TABLE t1,t2,t3;
1031 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1032 CREATE TABLE t2 (i INTEGER NULL);
1033 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1034 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1035 INSERT INTO t2 VALUES(1),(2),(3);
1036 DELIMITER //;
1037 CREATE PROCEDURE sp1()
1038 BEGIN
1039 DECLARE done INT DEFAULT 0;
1040 DECLARE a CHAR(16);
1041 DECLARE b,c INT;
1042 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1043 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1044 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1045 OPEN cur1;
1046 OPEN cur2;
1047 REPEAT
1048 FETCH cur1 INTO a, b;
1049 FETCH cur2 INTO c;
1050 IF NOT done THEN
1051 IF b < c THEN
1052 INSERT INTO t3 VALUES (a,b);
1053 ELSE
1054 INSERT INTO t3 VALUES (a,c);
1055 END IF;
1056 END IF;
1057 UNTIL done END REPEAT;
1058 CLOSE cur1;
1059 CLOSE cur2;
1060 END//
1061 DELIMITER ;//
1062 ALTER PROCEDURE sp1 #DET# NO SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1063 CALL sp1();
1064 SELECT * FROM t1 ORDER BY id;
1065 SELECT * FROM t2 ORDER BY i;
1066 SELECT * FROM t3 ORDER BY id;
1067 DROP PROCEDURE sp1;
1068 DROP TABLE t1,t2,t3;
1069 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1070 CREATE TABLE t2 (i INTEGER NULL);
1071 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1072 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1073 INSERT INTO t2 VALUES(1),(2),(3);
1074 DELIMITER //;
1075 CREATE PROCEDURE sp1()
1076 BEGIN
1077 DECLARE done INT DEFAULT 0;
1078 DECLARE a CHAR(16);
1079 DECLARE b,c INT;
1080 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1081 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1082 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1083 OPEN cur1;
1084 OPEN cur2;
1085 REPEAT
1086 FETCH cur1 INTO a, b;
1087 FETCH cur2 INTO c;
1088 IF NOT done THEN
1089 IF b < c THEN
1090 INSERT INTO t3 VALUES (a,b);
1091 ELSE
1092 INSERT INTO t3 VALUES (a,c);
1093 END IF;
1094 END IF;
1095 UNTIL done END REPEAT;
1096 CLOSE cur1;
1097 CLOSE cur2;
1098 END//
1099 DELIMITER ;//
1100 ALTER PROCEDURE sp1 #DET# READS SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1101 CALL sp1();
1102 SELECT * FROM t1 ORDER BY id;
1103 SELECT * FROM t2 ORDER BY i;
1104 SELECT * FROM t3 ORDER BY id;
1105 DROP PROCEDURE sp1;
1106 DROP TABLE t1,t2,t3;
1107 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1108 CREATE TABLE t2 (i INTEGER NULL);
1109 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1110 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1111 INSERT INTO t2 VALUES(1),(2),(3);
1112 DELIMITER //;
1113 CREATE PROCEDURE sp1()
1114 BEGIN
1115 DECLARE done INT DEFAULT 0;
1116 DECLARE a CHAR(16);
1117 DECLARE b,c INT;
1118 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1119 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1120 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1121 OPEN cur1;
1122 OPEN cur2;
1123 REPEAT
1124 FETCH cur1 INTO a, b;
1125 FETCH cur2 INTO c;
1126 IF NOT done THEN
1127 IF b < c THEN
1128 INSERT INTO t3 VALUES (a,b);
1129 ELSE
1130 INSERT INTO t3 VALUES (a,c);
1131 END IF;
1132 END IF;
1133 UNTIL done END REPEAT;
1134 CLOSE cur1;
1135 CLOSE cur2;
1136 END//
1137 DELIMITER ;//
1138 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1139 CALL sp1();
1140 SELECT * FROM t1 ORDER BY id;
1141 SELECT * FROM t2 ORDER BY i;
1142 SELECT * FROM t3 ORDER BY id;
1143 DROP PROCEDURE sp1;
1144 DROP TABLE t1,t2,t3;
1145 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1146 CREATE TABLE t2 (i INTEGER NULL);
1147 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1148 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1149 INSERT INTO t2 VALUES(1),(2),(3);
1150 DELIMITER //;
1151 CREATE PROCEDURE sp1()
1152 BEGIN
1153 DECLARE done INT DEFAULT 0;
1154 DECLARE a CHAR(16);
1155 DECLARE b,c INT;
1156 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1157 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1158 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1159 OPEN cur1;
1160 OPEN cur2;
1161 REPEAT
1162 FETCH cur1 INTO a, b;
1163 FETCH cur2 INTO c;
1164 IF NOT done THEN
1165 IF b < c THEN
1166 INSERT INTO t3 VALUES (a,b);
1167 ELSE
1168 INSERT INTO t3 VALUES (a,c);
1169 END IF;
1170 END IF;
1171 UNTIL done END REPEAT;
1172 CLOSE cur1;
1173 CLOSE cur2;
1174 END//
1175 DELIMITER ;//
1176 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# SQL SECURITY INVOKER ;
1177 CALL sp1();
1178 SELECT * FROM t1 ORDER BY id;
1179 SELECT * FROM t2 ORDER BY i;
1180 SELECT * FROM t3 ORDER BY id;
1181 DROP PROCEDURE sp1;
1182 DROP TABLE t1,t2,t3;
1183 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1184 CREATE TABLE t2 (i INTEGER NULL);
1185 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1186 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1187 INSERT INTO t2 VALUES(1),(2),(3);
1188 DELIMITER //;
1189 CREATE PROCEDURE sp1()
1190 BEGIN
1191 DECLARE done INT DEFAULT 0;
1192 DECLARE a CHAR(16);
1193 DECLARE b,c INT;
1194 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1195 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1196 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1197 OPEN cur1;
1198 OPEN cur2;
1199 REPEAT
1200 FETCH cur1 INTO a, b;
1201 FETCH cur2 INTO c;
1202 IF NOT done THEN
1203 IF b < c THEN
1204 INSERT INTO t3 VALUES (a,b);
1205 ELSE
1206 INSERT INTO t3 VALUES (a,c);
1207 END IF;
1208 END IF;
1209 UNTIL done END REPEAT;
1210 CLOSE cur1;
1211 CLOSE cur2;
1212 END//
1213 DELIMITER ;//
1214 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY INVOKER ;
1215 CALL sp1();
1216 SELECT * FROM t1 ORDER BY id;
1217 SELECT * FROM t2 ORDER BY i;
1218 SELECT * FROM t3 ORDER BY id;
1219 DROP PROCEDURE sp1;
1220 DROP TABLE t1,t2,t3;
1221 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1222 CREATE TABLE t2 (i INTEGER NULL);
1223 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1224 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1225 INSERT INTO t2 VALUES(1),(2),(3);
1226 DELIMITER //;
1227 CREATE PROCEDURE sp1()
1228 BEGIN
1229 DECLARE done INT DEFAULT 0;
1230 DECLARE a CHAR(16);
1231 DECLARE b,c INT;
1232 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1233 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1234 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1235 OPEN cur1;
1236 OPEN cur2;
1237 REPEAT
1238 FETCH cur1 INTO a, b;
1239 FETCH cur2 INTO c;
1240 IF NOT done THEN
1241 IF b < c THEN
1242 INSERT INTO t3 VALUES (a,b);
1243 ELSE
1244 INSERT INTO t3 VALUES (a,c);
1245 END IF;
1246 END IF;
1247 UNTIL done END REPEAT;
1248 CLOSE cur1;
1249 CLOSE cur2;
1250 END//
1251 DELIMITER ;//
1252 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL SQL SECURITY INVOKER ;
1253 CALL sp1();
1254 SELECT * FROM t1 ORDER BY id;
1255 SELECT * FROM t2 ORDER BY i;
1256 SELECT * FROM t3 ORDER BY id;
1257 DROP PROCEDURE sp1;
1258 DROP TABLE t1,t2,t3;
1259 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1260 CREATE TABLE t2 (i INTEGER NULL);
1261 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1262 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1263 INSERT INTO t2 VALUES(1),(2),(3);
1264 DELIMITER //;
1265 CREATE PROCEDURE sp1()
1266 BEGIN
1267 DECLARE done INT DEFAULT 0;
1268 DECLARE a CHAR(16);
1269 DECLARE b,c INT;
1270 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1271 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1272 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1273 OPEN cur1;
1274 OPEN cur2;
1275 REPEAT
1276 FETCH cur1 INTO a, b;
1277 FETCH cur2 INTO c;
1278 IF NOT done THEN
1279 IF b < c THEN
1280 INSERT INTO t3 VALUES (a,b);
1281 ELSE
1282 INSERT INTO t3 VALUES (a,c);
1283 END IF;
1284 END IF;
1285 UNTIL done END REPEAT;
1286 CLOSE cur1;
1287 CLOSE cur2;
1288 END//
1289 DELIMITER ;//
1290 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY INVOKER ;
1291 CALL sp1();
1292 SELECT * FROM t1 ORDER BY id;
1293 SELECT * FROM t2 ORDER BY i;
1294 SELECT * FROM t3 ORDER BY id;
1295 DROP PROCEDURE sp1;
1296 DROP TABLE t1,t2,t3;
1297 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1298 CREATE TABLE t2 (i INTEGER NULL);
1299 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1300 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1301 INSERT INTO t2 VALUES(1),(2),(3);
1302 DELIMITER //;
1303 CREATE PROCEDURE sp1()
1304 BEGIN
1305 DECLARE done INT DEFAULT 0;
1306 DECLARE a CHAR(16);
1307 DECLARE b,c INT;
1308 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1309 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1310 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1311 OPEN cur1;
1312 OPEN cur2;
1313 REPEAT
1314 FETCH cur1 INTO a, b;
1315 FETCH cur2 INTO c;
1316 IF NOT done THEN
1317 IF b < c THEN
1318 INSERT INTO t3 VALUES (a,b);
1319 ELSE
1320 INSERT INTO t3 VALUES (a,c);
1321 END IF;
1322 END IF;
1323 UNTIL done END REPEAT;
1324 CLOSE cur1;
1325 CLOSE cur2;
1326 END//
1327 DELIMITER ;//
1328 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY INVOKER ;
1329 CALL sp1();
1330 SELECT * FROM t1 ORDER BY id;
1331 SELECT * FROM t2 ORDER BY i;
1332 SELECT * FROM t3 ORDER BY id;
1333 DROP PROCEDURE sp1;
1334 DROP TABLE t1,t2,t3;
1335 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1336 CREATE TABLE t2 (i INTEGER NULL);
1337 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1338 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1339 INSERT INTO t2 VALUES(1),(2),(3);
1340 DELIMITER //;
1341 CREATE PROCEDURE sp1()
1342 BEGIN
1343 DECLARE done INT DEFAULT 0;
1344 DECLARE a CHAR(16);
1345 DECLARE b,c INT;
1346 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1347 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1348 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1349 OPEN cur1;
1350 OPEN cur2;
1351 REPEAT
1352 FETCH cur1 INTO a, b;
1353 FETCH cur2 INTO c;
1354 IF NOT done THEN
1355 IF b < c THEN
1356 INSERT INTO t3 VALUES (a,b);
1357 ELSE
1358 INSERT INTO t3 VALUES (a,c);
1359 END IF;
1360 END IF;
1361 UNTIL done END REPEAT;
1362 CLOSE cur1;
1363 CLOSE cur2;
1364 END//
1365 DELIMITER ;//
1366 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# SQL SECURITY INVOKER COMMENT 'comment' ;
1367 CALL sp1();
1368 SELECT * FROM t1 ORDER BY id;
1369 SELECT * FROM t2 ORDER BY i;
1370 SELECT * FROM t3 ORDER BY id;
1371 DROP PROCEDURE sp1;
1372 DROP TABLE t1,t2,t3;
1373 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1374 CREATE TABLE t2 (i INTEGER NULL);
1375 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1376 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1377 INSERT INTO t2 VALUES(1),(2),(3);
1378 DELIMITER //;
1379 CREATE PROCEDURE sp1()
1380 BEGIN
1381 DECLARE done INT DEFAULT 0;
1382 DECLARE a CHAR(16);
1383 DECLARE b,c INT;
1384 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1385 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1386 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1387 OPEN cur1;
1388 OPEN cur2;
1389 REPEAT
1390 FETCH cur1 INTO a, b;
1391 FETCH cur2 INTO c;
1392 IF NOT done THEN
1393 IF b < c THEN
1394 INSERT INTO t3 VALUES (a,b);
1395 ELSE
1396 INSERT INTO t3 VALUES (a,c);
1397 END IF;
1398 END IF;
1399 UNTIL done END REPEAT;
1400 CLOSE cur1;
1401 CLOSE cur2;
1402 END//
1403 DELIMITER ;//
1404 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1405 CALL sp1();
1406 SELECT * FROM t1 ORDER BY id;
1407 SELECT * FROM t2 ORDER BY i;
1408 SELECT * FROM t3 ORDER BY id;
1409 DROP PROCEDURE sp1;
1410 DROP TABLE t1,t2,t3;
1411 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1412 CREATE TABLE t2 (i INTEGER NULL);
1413 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1414 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1415 INSERT INTO t2 VALUES(1),(2),(3);
1416 DELIMITER //;
1417 CREATE PROCEDURE sp1()
1418 BEGIN
1419 DECLARE done INT DEFAULT 0;
1420 DECLARE a CHAR(16);
1421 DECLARE b,c INT;
1422 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1423 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1424 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1425 OPEN cur1;
1426 OPEN cur2;
1427 REPEAT
1428 FETCH cur1 INTO a, b;
1429 FETCH cur2 INTO c;
1430 IF NOT done THEN
1431 IF b < c THEN
1432 INSERT INTO t3 VALUES (a,b);
1433 ELSE
1434 INSERT INTO t3 VALUES (a,c);
1435 END IF;
1436 END IF;
1437 UNTIL done END REPEAT;
1438 CLOSE cur1;
1439 CLOSE cur2;
1440 END//
1441 DELIMITER ;//
1442 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1443 CALL sp1();
1444 SELECT * FROM t1 ORDER BY id;
1445 SELECT * FROM t2 ORDER BY i;
1446 SELECT * FROM t3 ORDER BY id;
1447 DROP PROCEDURE sp1;
1448 DROP TABLE t1,t2,t3;
1449 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1450 CREATE TABLE t2 (i INTEGER NULL);
1451 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1452 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1453 INSERT INTO t2 VALUES(1),(2),(3);
1454 DELIMITER //;
1455 CREATE PROCEDURE sp1()
1456 BEGIN
1457 DECLARE done INT DEFAULT 0;
1458 DECLARE a CHAR(16);
1459 DECLARE b,c INT;
1460 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1461 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1462 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1463 OPEN cur1;
1464 OPEN cur2;
1465 REPEAT
1466 FETCH cur1 INTO a, b;
1467 FETCH cur2 INTO c;
1468 IF NOT done THEN
1469 IF b < c THEN
1470 INSERT INTO t3 VALUES (a,b);
1471 ELSE
1472 INSERT INTO t3 VALUES (a,c);
1473 END IF;
1474 END IF;
1475 UNTIL done END REPEAT;
1476 CLOSE cur1;
1477 CLOSE cur2;
1478 END//
1479 DELIMITER ;//
1480 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1481 CALL sp1();
1482 SELECT * FROM t1 ORDER BY id;
1483 SELECT * FROM t2 ORDER BY i;
1484 SELECT * FROM t3 ORDER BY id;
1485 DROP PROCEDURE sp1;
1486 DROP TABLE t1,t2,t3;
1487 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1488 CREATE TABLE t2 (i INTEGER NULL);
1489 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1490 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1491 INSERT INTO t2 VALUES(1),(2),(3);
1492 DELIMITER //;
1493 CREATE PROCEDURE sp1()
1494 BEGIN
1495 DECLARE done INT DEFAULT 0;
1496 DECLARE a CHAR(16);
1497 DECLARE b,c INT;
1498 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1499 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1500 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1501 OPEN cur1;
1502 OPEN cur2;
1503 REPEAT
1504 FETCH cur1 INTO a, b;
1505 FETCH cur2 INTO c;
1506 IF NOT done THEN
1507 IF b < c THEN
1508 INSERT INTO t3 VALUES (a,b);
1509 ELSE
1510 INSERT INTO t3 VALUES (a,c);
1511 END IF;
1512 END IF;
1513 UNTIL done END REPEAT;
1514 CLOSE cur1;
1515 CLOSE cur2;
1516 END//
1517 DELIMITER ;//
1518 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1519 CALL sp1();
1520 SELECT * FROM t1 ORDER BY id;
1521 SELECT * FROM t2 ORDER BY i;
1522 SELECT * FROM t3 ORDER BY id;
1523 DROP PROCEDURE sp1;
1524 DROP TABLE t1,t2,t3;
1525 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1526 CREATE TABLE t2 (i INTEGER NULL);
1527 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1528 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1529 INSERT INTO t2 VALUES(1),(2),(3);
1530 DELIMITER //;
1531 CREATE PROCEDURE sp1()
1532 BEGIN
1533 DECLARE done INT DEFAULT 0;
1534 DECLARE a CHAR(16);
1535 DECLARE b,c INT;
1536 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1537 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1538 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1539 OPEN cur1;
1540 OPEN cur2;
1541 REPEAT
1542 FETCH cur1 INTO a, b;
1543 FETCH cur2 INTO c;
1544 IF NOT done THEN
1545 IF b < c THEN
1546 INSERT INTO t3 VALUES (a,b);
1547 ELSE
1548 INSERT INTO t3 VALUES (a,c);
1549 END IF;
1550 END IF;
1551 UNTIL done END REPEAT;
1552 CLOSE cur1;
1553 CLOSE cur2;
1554 END//
1555 DELIMITER ;//
1556 ALTER PROCEDURE sp1 #DET# SQL SECURITY DEFINER ;
1557 CALL sp1();
1558 SELECT * FROM t1 ORDER BY id;
1559 SELECT * FROM t2 ORDER BY i;
1560 SELECT * FROM t3 ORDER BY id;
1561 DROP PROCEDURE sp1;
1562 DROP TABLE t1,t2,t3;
1563 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1564 CREATE TABLE t2 (i INTEGER NULL);
1565 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1566 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1567 INSERT INTO t2 VALUES(1),(2),(3);
1568 DELIMITER //;
1569 CREATE PROCEDURE sp1()
1570 BEGIN
1571 DECLARE done INT DEFAULT 0;
1572 DECLARE a CHAR(16);
1573 DECLARE b,c INT;
1574 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1575 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1576 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1577 OPEN cur1;
1578 OPEN cur2;
1579 REPEAT
1580 FETCH cur1 INTO a, b;
1581 FETCH cur2 INTO c;
1582 IF NOT done THEN
1583 IF b < c THEN
1584 INSERT INTO t3 VALUES (a,b);
1585 ELSE
1586 INSERT INTO t3 VALUES (a,c);
1587 END IF;
1588 END IF;
1589 UNTIL done END REPEAT;
1590 CLOSE cur1;
1591 CLOSE cur2;
1592 END//
1593 DELIMITER ;//
1594 ALTER PROCEDURE sp1 #DET# CONTAINS SQL SQL SECURITY DEFINER ;
1595 CALL sp1();
1596 SELECT * FROM t1 ORDER BY id;
1597 SELECT * FROM t2 ORDER BY i;
1598 SELECT * FROM t3 ORDER BY id;
1599 DROP PROCEDURE sp1;
1600 DROP TABLE t1,t2,t3;
1601 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1602 CREATE TABLE t2 (i INTEGER NULL);
1603 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1604 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1605 INSERT INTO t2 VALUES(1),(2),(3);
1606 DELIMITER //;
1607 CREATE PROCEDURE sp1()
1608 BEGIN
1609 DECLARE done INT DEFAULT 0;
1610 DECLARE a CHAR(16);
1611 DECLARE b,c INT;
1612 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1613 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1614 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1615 OPEN cur1;
1616 OPEN cur2;
1617 REPEAT
1618 FETCH cur1 INTO a, b;
1619 FETCH cur2 INTO c;
1620 IF NOT done THEN
1621 IF b < c THEN
1622 INSERT INTO t3 VALUES (a,b);
1623 ELSE
1624 INSERT INTO t3 VALUES (a,c);
1625 END IF;
1626 END IF;
1627 UNTIL done END REPEAT;
1628 CLOSE cur1;
1629 CLOSE cur2;
1630 END//
1631 DELIMITER ;//
1632 ALTER PROCEDURE sp1 #DET# NO SQL SQL SECURITY DEFINER ;
1633 CALL sp1();
1634 SELECT * FROM t1 ORDER BY id;
1635 SELECT * FROM t2 ORDER BY i;
1636 SELECT * FROM t3 ORDER BY id;
1637 DROP PROCEDURE sp1;
1638 DROP TABLE t1,t2,t3;
1639 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1640 CREATE TABLE t2 (i INTEGER NULL);
1641 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1642 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1643 INSERT INTO t2 VALUES(1),(2),(3);
1644 DELIMITER //;
1645 CREATE PROCEDURE sp1()
1646 BEGIN
1647 DECLARE done INT DEFAULT 0;
1648 DECLARE a CHAR(16);
1649 DECLARE b,c INT;
1650 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1651 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1652 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1653 OPEN cur1;
1654 OPEN cur2;
1655 REPEAT
1656 FETCH cur1 INTO a, b;
1657 FETCH cur2 INTO c;
1658 IF NOT done THEN
1659 IF b < c THEN
1660 INSERT INTO t3 VALUES (a,b);
1661 ELSE
1662 INSERT INTO t3 VALUES (a,c);
1663 END IF;
1664 END IF;
1665 UNTIL done END REPEAT;
1666 CLOSE cur1;
1667 CLOSE cur2;
1668 END//
1669 DELIMITER ;//
1670 ALTER PROCEDURE sp1 #DET# READS SQL DATA SQL SECURITY DEFINER ;
1671 CALL sp1();
1672 SELECT * FROM t1 ORDER BY id;
1673 SELECT * FROM t2 ORDER BY i;
1674 SELECT * FROM t3 ORDER BY id;
1675 DROP PROCEDURE sp1;
1676 DROP TABLE t1,t2,t3;
1677 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1678 CREATE TABLE t2 (i INTEGER NULL);
1679 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1680 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1681 INSERT INTO t2 VALUES(1),(2),(3);
1682 DELIMITER //;
1683 CREATE PROCEDURE sp1()
1684 BEGIN
1685 DECLARE done INT DEFAULT 0;
1686 DECLARE a CHAR(16);
1687 DECLARE b,c INT;
1688 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1689 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1690 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1691 OPEN cur1;
1692 OPEN cur2;
1693 REPEAT
1694 FETCH cur1 INTO a, b;
1695 FETCH cur2 INTO c;
1696 IF NOT done THEN
1697 IF b < c THEN
1698 INSERT INTO t3 VALUES (a,b);
1699 ELSE
1700 INSERT INTO t3 VALUES (a,c);
1701 END IF;
1702 END IF;
1703 UNTIL done END REPEAT;
1704 CLOSE cur1;
1705 CLOSE cur2;
1706 END//
1707 DELIMITER ;//
1708 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA SQL SECURITY DEFINER ;
1709 CALL sp1();
1710 SELECT * FROM t1 ORDER BY id;
1711 SELECT * FROM t2 ORDER BY i;
1712 SELECT * FROM t3 ORDER BY id;
1713 DROP PROCEDURE sp1;
1714 DROP TABLE t1,t2,t3;
1715 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1716 CREATE TABLE t2 (i INTEGER NULL);
1717 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1718 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1719 INSERT INTO t2 VALUES(1),(2),(3);
1720 DELIMITER //;
1721 CREATE PROCEDURE sp1()
1722 BEGIN
1723 DECLARE done INT DEFAULT 0;
1724 DECLARE a CHAR(16);
1725 DECLARE b,c INT;
1726 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1727 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1728 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1729 OPEN cur1;
1730 OPEN cur2;
1731 REPEAT
1732 FETCH cur1 INTO a, b;
1733 FETCH cur2 INTO c;
1734 IF NOT done THEN
1735 IF b < c THEN
1736 INSERT INTO t3 VALUES (a,b);
1737 ELSE
1738 INSERT INTO t3 VALUES (a,c);
1739 END IF;
1740 END IF;
1741 UNTIL done END REPEAT;
1742 CLOSE cur1;
1743 CLOSE cur2;
1744 END//
1745 DELIMITER ;//
1746 ALTER PROCEDURE sp1 #DET# SQL SECURITY DEFINER COMMENT 'comment' ;
1747 CALL sp1();
1748 SELECT * FROM t1 ORDER BY id;
1749 SELECT * FROM t2 ORDER BY i;
1750 SELECT * FROM t3 ORDER BY id;
1751 DROP PROCEDURE sp1;
1752 DROP TABLE t1,t2,t3;
1753 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1754 CREATE TABLE t2 (i INTEGER NULL);
1755 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1756 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1757 INSERT INTO t2 VALUES(1),(2),(3);
1758 DELIMITER //;
1759 CREATE PROCEDURE sp1()
1760 BEGIN
1761 DECLARE done INT DEFAULT 0;
1762 DECLARE a CHAR(16);
1763 DECLARE b,c INT;
1764 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1765 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1766 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1767 OPEN cur1;
1768 OPEN cur2;
1769 REPEAT
1770 FETCH cur1 INTO a, b;
1771 FETCH cur2 INTO c;
1772 IF NOT done THEN
1773 IF b < c THEN
1774 INSERT INTO t3 VALUES (a,b);
1775 ELSE
1776 INSERT INTO t3 VALUES (a,c);
1777 END IF;
1778 END IF;
1779 UNTIL done END REPEAT;
1780 CLOSE cur1;
1781 CLOSE cur2;
1782 END//
1783 DELIMITER ;//
1784 ALTER PROCEDURE sp1 #DET# CONTAINS SQL SQL SECURITY DEFINER COMMENT 'comment' ;
1785 CALL sp1();
1786 SELECT * FROM t1 ORDER BY id;
1787 SELECT * FROM t2 ORDER BY i;
1788 SELECT * FROM t3 ORDER BY id;
1789 DROP PROCEDURE sp1;
1790 DROP TABLE t1,t2,t3;
1791 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1792 CREATE TABLE t2 (i INTEGER NULL);
1793 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1794 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1795 INSERT INTO t2 VALUES(1),(2),(3);
1796 DELIMITER //;
1797 CREATE PROCEDURE sp1()
1798 BEGIN
1799 DECLARE done INT DEFAULT 0;
1800 DECLARE a CHAR(16);
1801 DECLARE b,c INT;
1802 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1803 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1804 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1805 OPEN cur1;
1806 OPEN cur2;
1807 REPEAT
1808 FETCH cur1 INTO a, b;
1809 FETCH cur2 INTO c;
1810 IF NOT done THEN
1811 IF b < c THEN
1812 INSERT INTO t3 VALUES (a,b);
1813 ELSE
1814 INSERT INTO t3 VALUES (a,c);
1815 END IF;
1816 END IF;
1817 UNTIL done END REPEAT;
1818 CLOSE cur1;
1819 CLOSE cur2;
1820 END//
1821 DELIMITER ;//
1822 ALTER PROCEDURE sp1 #DET# NO SQL SQL SECURITY DEFINER COMMENT 'comment' ;
1823 CALL sp1();
1824 SELECT * FROM t1 ORDER BY id;
1825 SELECT * FROM t2 ORDER BY i;
1826 SELECT * FROM t3 ORDER BY id;
1827 DROP PROCEDURE sp1;
1828 DROP TABLE t1,t2,t3;
1829 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1830 CREATE TABLE t2 (i INTEGER NULL);
1831 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1832 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1833 INSERT INTO t2 VALUES(1),(2),(3);
1834 DELIMITER //;
1835 CREATE PROCEDURE sp1()
1836 BEGIN
1837 DECLARE done INT DEFAULT 0;
1838 DECLARE a CHAR(16);
1839 DECLARE b,c INT;
1840 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1841 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1842 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1843 OPEN cur1;
1844 OPEN cur2;
1845 REPEAT
1846 FETCH cur1 INTO a, b;
1847 FETCH cur2 INTO c;
1848 IF NOT done THEN
1849 IF b < c THEN
1850 INSERT INTO t3 VALUES (a,b);
1851 ELSE
1852 INSERT INTO t3 VALUES (a,c);
1853 END IF;
1854 END IF;
1855 UNTIL done END REPEAT;
1856 CLOSE cur1;
1857 CLOSE cur2;
1858 END//
1859 DELIMITER ;//
1860 ALTER PROCEDURE sp1 #DET# READS SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
1861 CALL sp1();
1862 SELECT * FROM t1 ORDER BY id;
1863 SELECT * FROM t2 ORDER BY i;
1864 SELECT * FROM t3 ORDER BY id;
1865 DROP PROCEDURE sp1;
1866 DROP TABLE t1,t2,t3;
1867 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1868 CREATE TABLE t2 (i INTEGER NULL);
1869 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1870 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1871 INSERT INTO t2 VALUES(1),(2),(3);
1872 DELIMITER //;
1873 CREATE PROCEDURE sp1()
1874 BEGIN
1875 DECLARE done INT DEFAULT 0;
1876 DECLARE a CHAR(16);
1877 DECLARE b,c INT;
1878 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1879 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1880 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1881 OPEN cur1;
1882 OPEN cur2;
1883 REPEAT
1884 FETCH cur1 INTO a, b;
1885 FETCH cur2 INTO c;
1886 IF NOT done THEN
1887 IF b < c THEN
1888 INSERT INTO t3 VALUES (a,b);
1889 ELSE
1890 INSERT INTO t3 VALUES (a,c);
1891 END IF;
1892 END IF;
1893 UNTIL done END REPEAT;
1894 CLOSE cur1;
1895 CLOSE cur2;
1896 END//
1897 DELIMITER ;//
1898 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
1899 CALL sp1();
1900 SELECT * FROM t1 ORDER BY id;
1901 SELECT * FROM t2 ORDER BY i;
1902 SELECT * FROM t3 ORDER BY id;
1903 DROP PROCEDURE sp1;
1904 DROP TABLE t1,t2,t3;
1905 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1906 CREATE TABLE t2 (i INTEGER NULL);
1907 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1908 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1909 INSERT INTO t2 VALUES(1),(2),(3);
1910 DELIMITER //;
1911 CREATE PROCEDURE sp1()
1912 BEGIN
1913 DECLARE done INT DEFAULT 0;
1914 DECLARE a CHAR(16);
1915 DECLARE b,c INT;
1916 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1917 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1918 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1919 OPEN cur1;
1920 OPEN cur2;
1921 REPEAT
1922 FETCH cur1 INTO a, b;
1923 FETCH cur2 INTO c;
1924 IF NOT done THEN
1925 IF b < c THEN
1926 INSERT INTO t3 VALUES (a,b);
1927 ELSE
1928 INSERT INTO t3 VALUES (a,c);
1929 END IF;
1930 END IF;
1931 UNTIL done END REPEAT;
1932 CLOSE cur1;
1933 CLOSE cur2;
1934 END//
1935 DELIMITER ;//
1936 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# SQL SECURITY DEFINER ;
1937 CALL sp1();
1938 SELECT * FROM t1 ORDER BY id;
1939 SELECT * FROM t2 ORDER BY i;
1940 SELECT * FROM t3 ORDER BY id;
1941 DROP PROCEDURE sp1;
1942 DROP TABLE t1,t2,t3;
1943 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1944 CREATE TABLE t2 (i INTEGER NULL);
1945 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1946 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1947 INSERT INTO t2 VALUES(1),(2),(3);
1948 DELIMITER //;
1949 CREATE PROCEDURE sp1()
1950 BEGIN
1951 DECLARE done INT DEFAULT 0;
1952 DECLARE a CHAR(16);
1953 DECLARE b,c INT;
1954 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1955 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1956 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1957 OPEN cur1;
1958 OPEN cur2;
1959 REPEAT
1960 FETCH cur1 INTO a, b;
1961 FETCH cur2 INTO c;
1962 IF NOT done THEN
1963 IF b < c THEN
1964 INSERT INTO t3 VALUES (a,b);
1965 ELSE
1966 INSERT INTO t3 VALUES (a,c);
1967 END IF;
1968 END IF;
1969 UNTIL done END REPEAT;
1970 CLOSE cur1;
1971 CLOSE cur2;
1972 END//
1973 DELIMITER ;//
1974 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY DEFINER ;
1975 CALL sp1();
1976 SELECT * FROM t1 ORDER BY id;
1977 SELECT * FROM t2 ORDER BY i;
1978 SELECT * FROM t3 ORDER BY id;
1979 DROP PROCEDURE sp1;
1980 DROP TABLE t1,t2,t3;
1981 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1982 CREATE TABLE t2 (i INTEGER NULL);
1983 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1984 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1985 INSERT INTO t2 VALUES(1),(2),(3);
1986 DELIMITER //;
1987 CREATE PROCEDURE sp1()
1988 BEGIN
1989 DECLARE done INT DEFAULT 0;
1990 DECLARE a CHAR(16);
1991 DECLARE b,c INT;
1992 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1993 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1994 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1995 OPEN cur1;
1996 OPEN cur2;
1997 REPEAT
1998 FETCH cur1 INTO a, b;
1999 FETCH cur2 INTO c;
2000 IF NOT done THEN
2001 IF b < c THEN
2002 INSERT INTO t3 VALUES (a,b);
2003 ELSE
2004 INSERT INTO t3 VALUES (a,c);
2005 END IF;
2006 END IF;
2007 UNTIL done END REPEAT;
2008 CLOSE cur1;
2009 CLOSE cur2;
2010 END//
2011 DELIMITER ;//
2012 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL SQL SECURITY DEFINER ;
2013 CALL sp1();
2014 SELECT * FROM t1 ORDER BY id;
2015 SELECT * FROM t2 ORDER BY i;
2016 SELECT * FROM t3 ORDER BY id;
2017 DROP PROCEDURE sp1;
2018 DROP TABLE t1,t2,t3;
2019 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2020 CREATE TABLE t2 (i INTEGER NULL);
2021 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2022 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2023 INSERT INTO t2 VALUES(1),(2),(3);
2024 DELIMITER //;
2025 CREATE PROCEDURE sp1()
2026 BEGIN
2027 DECLARE done INT DEFAULT 0;
2028 DECLARE a CHAR(16);
2029 DECLARE b,c INT;
2030 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2031 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2032 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2033 OPEN cur1;
2034 OPEN cur2;
2035 REPEAT
2036 FETCH cur1 INTO a, b;
2037 FETCH cur2 INTO c;
2038 IF NOT done THEN
2039 IF b < c THEN
2040 INSERT INTO t3 VALUES (a,b);
2041 ELSE
2042 INSERT INTO t3 VALUES (a,c);
2043 END IF;
2044 END IF;
2045 UNTIL done END REPEAT;
2046 CLOSE cur1;
2047 CLOSE cur2;
2048 END//
2049 DELIMITER ;//
2050 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY DEFINER ;
2051 CALL sp1();
2052 SELECT * FROM t1 ORDER BY id;
2053 SELECT * FROM t2 ORDER BY i;
2054 SELECT * FROM t3 ORDER BY id;
2055 DROP PROCEDURE sp1;
2056 DROP TABLE t1,t2,t3;
2057 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2058 CREATE TABLE t2 (i INTEGER NULL);
2059 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2060 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2061 INSERT INTO t2 VALUES(1),(2),(3);
2062 DELIMITER //;
2063 CREATE PROCEDURE sp1()
2064 BEGIN
2065 DECLARE done INT DEFAULT 0;
2066 DECLARE a CHAR(16);
2067 DECLARE b,c INT;
2068 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2069 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2070 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2071 OPEN cur1;
2072 OPEN cur2;
2073 REPEAT
2074 FETCH cur1 INTO a, b;
2075 FETCH cur2 INTO c;
2076 IF NOT done THEN
2077 IF b < c THEN
2078 INSERT INTO t3 VALUES (a,b);
2079 ELSE
2080 INSERT INTO t3 VALUES (a,c);
2081 END IF;
2082 END IF;
2083 UNTIL done END REPEAT;
2084 CLOSE cur1;
2085 CLOSE cur2;
2086 END//
2087 DELIMITER ;//
2088 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY DEFINER ;
2089 CALL sp1();
2090 SELECT * FROM t1 ORDER BY id;
2091 SELECT * FROM t2 ORDER BY i;
2092 SELECT * FROM t3 ORDER BY id;
2093 DROP PROCEDURE sp1;
2094 DROP TABLE t1,t2,t3;
2095 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2096 CREATE TABLE t2 (i INTEGER NULL);
2097 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2098 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2099 INSERT INTO t2 VALUES(1),(2),(3);
2100 DELIMITER //;
2101 CREATE PROCEDURE sp1()
2102 BEGIN
2103 DECLARE done INT DEFAULT 0;
2104 DECLARE a CHAR(16);
2105 DECLARE b,c INT;
2106 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2107 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2108 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2109 OPEN cur1;
2110 OPEN cur2;
2111 REPEAT
2112 FETCH cur1 INTO a, b;
2113 FETCH cur2 INTO c;
2114 IF NOT done THEN
2115 IF b < c THEN
2116 INSERT INTO t3 VALUES (a,b);
2117 ELSE
2118 INSERT INTO t3 VALUES (a,c);
2119 END IF;
2120 END IF;
2121 UNTIL done END REPEAT;
2122 CLOSE cur1;
2123 CLOSE cur2;
2124 END//
2125 DELIMITER ;//
2126 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# SQL SECURITY DEFINER COMMENT 'comment' ;
2127 CALL sp1();
2128 SELECT * FROM t1 ORDER BY id;
2129 SELECT * FROM t2 ORDER BY i;
2130 SELECT * FROM t3 ORDER BY id;
2131 DROP PROCEDURE sp1;
2132 DROP TABLE t1,t2,t3;
2133 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2134 CREATE TABLE t2 (i INTEGER NULL);
2135 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2136 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2137 INSERT INTO t2 VALUES(1),(2),(3);
2138 DELIMITER //;
2139 CREATE PROCEDURE sp1()
2140 BEGIN
2141 DECLARE done INT DEFAULT 0;
2142 DECLARE a CHAR(16);
2143 DECLARE b,c INT;
2144 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2145 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2146 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2147 OPEN cur1;
2148 OPEN cur2;
2149 REPEAT
2150 FETCH cur1 INTO a, b;
2151 FETCH cur2 INTO c;
2152 IF NOT done THEN
2153 IF b < c THEN
2154 INSERT INTO t3 VALUES (a,b);
2155 ELSE
2156 INSERT INTO t3 VALUES (a,c);
2157 END IF;
2158 END IF;
2159 UNTIL done END REPEAT;
2160 CLOSE cur1;
2161 CLOSE cur2;
2162 END//
2163 DELIMITER ;//
2164 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2165 CALL sp1();
2166 SELECT * FROM t1 ORDER BY id;
2167 SELECT * FROM t2 ORDER BY i;
2168 SELECT * FROM t3 ORDER BY id;
2169 DROP PROCEDURE sp1;
2170 DROP TABLE t1,t2,t3;
2171 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2172 CREATE TABLE t2 (i INTEGER NULL);
2173 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2174 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2175 INSERT INTO t2 VALUES(1),(2),(3);
2176 DELIMITER //;
2177 CREATE PROCEDURE sp1()
2178 BEGIN
2179 DECLARE done INT DEFAULT 0;
2180 DECLARE a CHAR(16);
2181 DECLARE b,c INT;
2182 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2183 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2184 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2185 OPEN cur1;
2186 OPEN cur2;
2187 REPEAT
2188 FETCH cur1 INTO a, b;
2189 FETCH cur2 INTO c;
2190 IF NOT done THEN
2191 IF b < c THEN
2192 INSERT INTO t3 VALUES (a,b);
2193 ELSE
2194 INSERT INTO t3 VALUES (a,c);
2195 END IF;
2196 END IF;
2197 UNTIL done END REPEAT;
2198 CLOSE cur1;
2199 CLOSE cur2;
2200 END//
2201 DELIMITER ;//
2202 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2203 CALL sp1();
2204 SELECT * FROM t1 ORDER BY id;
2205 SELECT * FROM t2 ORDER BY i;
2206 SELECT * FROM t3 ORDER BY id;
2207 DROP PROCEDURE sp1;
2208 DROP TABLE t1,t2,t3;
2209 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2210 CREATE TABLE t2 (i INTEGER NULL);
2211 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2212 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2213 INSERT INTO t2 VALUES(1),(2),(3);
2214 DELIMITER //;
2215 CREATE PROCEDURE sp1()
2216 BEGIN
2217 DECLARE done INT DEFAULT 0;
2218 DECLARE a CHAR(16);
2219 DECLARE b,c INT;
2220 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2221 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2222 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2223 OPEN cur1;
2224 OPEN cur2;
2225 REPEAT
2226 FETCH cur1 INTO a, b;
2227 FETCH cur2 INTO c;
2228 IF NOT done THEN
2229 IF b < c THEN
2230 INSERT INTO t3 VALUES (a,b);
2231 ELSE
2232 INSERT INTO t3 VALUES (a,c);
2233 END IF;
2234 END IF;
2235 UNTIL done END REPEAT;
2236 CLOSE cur1;
2237 CLOSE cur2;
2238 END//
2239 DELIMITER ;//
2240 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
2241 CALL sp1();
2242 SELECT * FROM t1 ORDER BY id;
2243 SELECT * FROM t2 ORDER BY i;
2244 SELECT * FROM t3 ORDER BY id;
2245 DROP PROCEDURE sp1;
2246 DROP TABLE t1,t2,t3;
2247 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2248 CREATE TABLE t2 (i INTEGER NULL);
2249 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2250 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2251 INSERT INTO t2 VALUES(1),(2),(3);
2252 DELIMITER //;
2253 CREATE PROCEDURE sp1()
2254 BEGIN
2255 DECLARE done INT DEFAULT 0;
2256 DECLARE a CHAR(16);
2257 DECLARE b,c INT;
2258 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2259 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2260 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2261 OPEN cur1;
2262 OPEN cur2;
2263 REPEAT
2264 FETCH cur1 INTO a, b;
2265 FETCH cur2 INTO c;
2266 IF NOT done THEN
2267 IF b < c THEN
2268 INSERT INTO t3 VALUES (a,b);
2269 ELSE
2270 INSERT INTO t3 VALUES (a,c);
2271 END IF;
2272 END IF;
2273 UNTIL done END REPEAT;
2274 CLOSE cur1;
2275 CLOSE cur2;
2276 END//
2277 DELIMITER ;//
2278 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
2279 CALL sp1();
2280 SELECT * FROM t1 ORDER BY id;
2281 SELECT * FROM t2 ORDER BY i;
2282 SELECT * FROM t3 ORDER BY id;
2283 DROP PROCEDURE sp1;
2284 DROP TABLE t1,t2,t3;
2285