Debian Codegraph @

mysql-test/suite/engines/funcs/r/sp_alter.result

raw
1 DROP TABLE IF EXISTS t1,t2,t3;
2 DROP PROCEDURE IF EXISTS sp1;
3 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
4 CREATE TABLE t2 (i INTEGER NULL);
5 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
6 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
7 INSERT INTO t2 VALUES(1),(2),(3);
8 CREATE PROCEDURE sp1()
9 BEGIN
10 DECLARE done INT DEFAULT 0;
11 DECLARE a CHAR(16);
12 DECLARE b,c INT;
13 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
14 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
15 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
16 OPEN cur1;
17 OPEN cur2;
18 REPEAT
19 FETCH cur1 INTO a, b;
20 FETCH cur2 INTO c;
21 IF NOT done THEN
22 IF b < c THEN
23 INSERT INTO t3 VALUES (a,b);
24 ELSE
25 INSERT INTO t3 VALUES (a,c);
26 END IF;
27 END IF;
28 UNTIL done END REPEAT;
29 CLOSE cur1;
30 CLOSE cur2;
31 END//
32 ALTER PROCEDURE sp1 #DET# ;
33 CALL sp1();
34 SELECT * FROM t1 ORDER BY id;
35 id data
36 1 1
37 2 1
38 3 4
39 4 5
40 SELECT * FROM t2 ORDER BY i;
41 i
42 1
43 2
44 3
45 SELECT * FROM t3 ORDER BY id;
46 id data
47 1 1
48 2 1
49 3 3
50 DROP PROCEDURE sp1;
51 DROP TABLE t1,t2,t3;
52 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
53 CREATE TABLE t2 (i INTEGER NULL);
54 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
55 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
56 INSERT INTO t2 VALUES(1),(2),(3);
57 CREATE PROCEDURE sp1()
58 BEGIN
59 DECLARE done INT DEFAULT 0;
60 DECLARE a CHAR(16);
61 DECLARE b,c INT;
62 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
63 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
64 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
65 OPEN cur1;
66 OPEN cur2;
67 REPEAT
68 FETCH cur1 INTO a, b;
69 FETCH cur2 INTO c;
70 IF NOT done THEN
71 IF b < c THEN
72 INSERT INTO t3 VALUES (a,b);
73 ELSE
74 INSERT INTO t3 VALUES (a,c);
75 END IF;
76 END IF;
77 UNTIL done END REPEAT;
78 CLOSE cur1;
79 CLOSE cur2;
80 END//
81 ALTER PROCEDURE sp1 #DET# CONTAINS SQL ;
82 CALL sp1();
83 SELECT * FROM t1 ORDER BY id;
84 id data
85 1 1
86 2 1
87 3 4
88 4 5
89 SELECT * FROM t2 ORDER BY i;
90 i
91 1
92 2
93 3
94 SELECT * FROM t3 ORDER BY id;
95 id data
96 1 1
97 2 1
98 3 3
99 DROP PROCEDURE sp1;
100 DROP TABLE t1,t2,t3;
101 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
102 CREATE TABLE t2 (i INTEGER NULL);
103 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
104 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
105 INSERT INTO t2 VALUES(1),(2),(3);
106 CREATE PROCEDURE sp1()
107 BEGIN
108 DECLARE done INT DEFAULT 0;
109 DECLARE a CHAR(16);
110 DECLARE b,c INT;
111 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
112 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
113 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
114 OPEN cur1;
115 OPEN cur2;
116 REPEAT
117 FETCH cur1 INTO a, b;
118 FETCH cur2 INTO c;
119 IF NOT done THEN
120 IF b < c THEN
121 INSERT INTO t3 VALUES (a,b);
122 ELSE
123 INSERT INTO t3 VALUES (a,c);
124 END IF;
125 END IF;
126 UNTIL done END REPEAT;
127 CLOSE cur1;
128 CLOSE cur2;
129 END//
130 ALTER PROCEDURE sp1 #DET# NO SQL ;
131 CALL sp1();
132 SELECT * FROM t1 ORDER BY id;
133 id data
134 1 1
135 2 1
136 3 4
137 4 5
138 SELECT * FROM t2 ORDER BY i;
139 i
140 1
141 2
142 3
143 SELECT * FROM t3 ORDER BY id;
144 id data
145 1 1
146 2 1
147 3 3
148 DROP PROCEDURE sp1;
149 DROP TABLE t1,t2,t3;
150 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
151 CREATE TABLE t2 (i INTEGER NULL);
152 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
153 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
154 INSERT INTO t2 VALUES(1),(2),(3);
155 CREATE PROCEDURE sp1()
156 BEGIN
157 DECLARE done INT DEFAULT 0;
158 DECLARE a CHAR(16);
159 DECLARE b,c INT;
160 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
161 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
162 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
163 OPEN cur1;
164 OPEN cur2;
165 REPEAT
166 FETCH cur1 INTO a, b;
167 FETCH cur2 INTO c;
168 IF NOT done THEN
169 IF b < c THEN
170 INSERT INTO t3 VALUES (a,b);
171 ELSE
172 INSERT INTO t3 VALUES (a,c);
173 END IF;
174 END IF;
175 UNTIL done END REPEAT;
176 CLOSE cur1;
177 CLOSE cur2;
178 END//
179 ALTER PROCEDURE sp1 #DET# READS SQL DATA ;
180 CALL sp1();
181 SELECT * FROM t1 ORDER BY id;
182 id data
183 1 1
184 2 1
185 3 4
186 4 5
187 SELECT * FROM t2 ORDER BY i;
188 i
189 1
190 2
191 3
192 SELECT * FROM t3 ORDER BY id;
193 id data
194 1 1
195 2 1
196 3 3
197 DROP PROCEDURE sp1;
198 DROP TABLE t1,t2,t3;
199 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
200 CREATE TABLE t2 (i INTEGER NULL);
201 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
202 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
203 INSERT INTO t2 VALUES(1),(2),(3);
204 CREATE PROCEDURE sp1()
205 BEGIN
206 DECLARE done INT DEFAULT 0;
207 DECLARE a CHAR(16);
208 DECLARE b,c INT;
209 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
210 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
211 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
212 OPEN cur1;
213 OPEN cur2;
214 REPEAT
215 FETCH cur1 INTO a, b;
216 FETCH cur2 INTO c;
217 IF NOT done THEN
218 IF b < c THEN
219 INSERT INTO t3 VALUES (a,b);
220 ELSE
221 INSERT INTO t3 VALUES (a,c);
222 END IF;
223 END IF;
224 UNTIL done END REPEAT;
225 CLOSE cur1;
226 CLOSE cur2;
227 END//
228 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA ;
229 CALL sp1();
230 SELECT * FROM t1 ORDER BY id;
231 id data
232 1 1
233 2 1
234 3 4
235 4 5
236 SELECT * FROM t2 ORDER BY i;
237 i
238 1
239 2
240 3
241 SELECT * FROM t3 ORDER BY id;
242 id data
243 1 1
244 2 1
245 3 3
246 DROP PROCEDURE sp1;
247 DROP TABLE t1,t2,t3;
248 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
249 CREATE TABLE t2 (i INTEGER NULL);
250 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
251 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
252 INSERT INTO t2 VALUES(1),(2),(3);
253 CREATE PROCEDURE sp1()
254 BEGIN
255 DECLARE done INT DEFAULT 0;
256 DECLARE a CHAR(16);
257 DECLARE b,c INT;
258 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
259 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
260 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
261 OPEN cur1;
262 OPEN cur2;
263 REPEAT
264 FETCH cur1 INTO a, b;
265 FETCH cur2 INTO c;
266 IF NOT done THEN
267 IF b < c THEN
268 INSERT INTO t3 VALUES (a,b);
269 ELSE
270 INSERT INTO t3 VALUES (a,c);
271 END IF;
272 END IF;
273 UNTIL done END REPEAT;
274 CLOSE cur1;
275 CLOSE cur2;
276 END//
277 ALTER PROCEDURE sp1 #DET# COMMENT 'comment' ;
278 CALL sp1();
279 SELECT * FROM t1 ORDER BY id;
280 id data
281 1 1
282 2 1
283 3 4
284 4 5
285 SELECT * FROM t2 ORDER BY i;
286 i
287 1
288 2
289 3
290 SELECT * FROM t3 ORDER BY id;
291 id data
292 1 1
293 2 1
294 3 3
295 DROP PROCEDURE sp1;
296 DROP TABLE t1,t2,t3;
297 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
298 CREATE TABLE t2 (i INTEGER NULL);
299 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
300 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
301 INSERT INTO t2 VALUES(1),(2),(3);
302 CREATE PROCEDURE sp1()
303 BEGIN
304 DECLARE done INT DEFAULT 0;
305 DECLARE a CHAR(16);
306 DECLARE b,c INT;
307 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
308 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
309 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
310 OPEN cur1;
311 OPEN cur2;
312 REPEAT
313 FETCH cur1 INTO a, b;
314 FETCH cur2 INTO c;
315 IF NOT done THEN
316 IF b < c THEN
317 INSERT INTO t3 VALUES (a,b);
318 ELSE
319 INSERT INTO t3 VALUES (a,c);
320 END IF;
321 END IF;
322 UNTIL done END REPEAT;
323 CLOSE cur1;
324 CLOSE cur2;
325 END//
326 ALTER PROCEDURE sp1 #DET# CONTAINS SQL COMMENT 'comment' ;
327 CALL sp1();
328 SELECT * FROM t1 ORDER BY id;
329 id data
330 1 1
331 2 1
332 3 4
333 4 5
334 SELECT * FROM t2 ORDER BY i;
335 i
336 1
337 2
338 3
339 SELECT * FROM t3 ORDER BY id;
340 id data
341 1 1
342 2 1
343 3 3
344 DROP PROCEDURE sp1;
345 DROP TABLE t1,t2,t3;
346 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
347 CREATE TABLE t2 (i INTEGER NULL);
348 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
349 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
350 INSERT INTO t2 VALUES(1),(2),(3);
351 CREATE PROCEDURE sp1()
352 BEGIN
353 DECLARE done INT DEFAULT 0;
354 DECLARE a CHAR(16);
355 DECLARE b,c INT;
356 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
357 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
358 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
359 OPEN cur1;
360 OPEN cur2;
361 REPEAT
362 FETCH cur1 INTO a, b;
363 FETCH cur2 INTO c;
364 IF NOT done THEN
365 IF b < c THEN
366 INSERT INTO t3 VALUES (a,b);
367 ELSE
368 INSERT INTO t3 VALUES (a,c);
369 END IF;
370 END IF;
371 UNTIL done END REPEAT;
372 CLOSE cur1;
373 CLOSE cur2;
374 END//
375 ALTER PROCEDURE sp1 #DET# NO SQL COMMENT 'comment' ;
376 CALL sp1();
377 SELECT * FROM t1 ORDER BY id;
378 id data
379 1 1
380 2 1
381 3 4
382 4 5
383 SELECT * FROM t2 ORDER BY i;
384 i
385 1
386 2
387 3
388 SELECT * FROM t3 ORDER BY id;
389 id data
390 1 1
391 2 1
392 3 3
393 DROP PROCEDURE sp1;
394 DROP TABLE t1,t2,t3;
395 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
396 CREATE TABLE t2 (i INTEGER NULL);
397 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
398 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
399 INSERT INTO t2 VALUES(1),(2),(3);
400 CREATE PROCEDURE sp1()
401 BEGIN
402 DECLARE done INT DEFAULT 0;
403 DECLARE a CHAR(16);
404 DECLARE b,c INT;
405 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
406 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
407 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
408 OPEN cur1;
409 OPEN cur2;
410 REPEAT
411 FETCH cur1 INTO a, b;
412 FETCH cur2 INTO c;
413 IF NOT done THEN
414 IF b < c THEN
415 INSERT INTO t3 VALUES (a,b);
416 ELSE
417 INSERT INTO t3 VALUES (a,c);
418 END IF;
419 END IF;
420 UNTIL done END REPEAT;
421 CLOSE cur1;
422 CLOSE cur2;
423 END//
424 ALTER PROCEDURE sp1 #DET# READS SQL DATA COMMENT 'comment' ;
425 CALL sp1();
426 SELECT * FROM t1 ORDER BY id;
427 id data
428 1 1
429 2 1
430 3 4
431 4 5
432 SELECT * FROM t2 ORDER BY i;
433 i
434 1
435 2
436 3
437 SELECT * FROM t3 ORDER BY id;
438 id data
439 1 1
440 2 1
441 3 3
442 DROP PROCEDURE sp1;
443 DROP TABLE t1,t2,t3;
444 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
445 CREATE TABLE t2 (i INTEGER NULL);
446 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
447 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
448 INSERT INTO t2 VALUES(1),(2),(3);
449 CREATE PROCEDURE sp1()
450 BEGIN
451 DECLARE done INT DEFAULT 0;
452 DECLARE a CHAR(16);
453 DECLARE b,c INT;
454 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
455 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
456 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
457 OPEN cur1;
458 OPEN cur2;
459 REPEAT
460 FETCH cur1 INTO a, b;
461 FETCH cur2 INTO c;
462 IF NOT done THEN
463 IF b < c THEN
464 INSERT INTO t3 VALUES (a,b);
465 ELSE
466 INSERT INTO t3 VALUES (a,c);
467 END IF;
468 END IF;
469 UNTIL done END REPEAT;
470 CLOSE cur1;
471 CLOSE cur2;
472 END//
473 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA COMMENT 'comment' ;
474 CALL sp1();
475 SELECT * FROM t1 ORDER BY id;
476 id data
477 1 1
478 2 1
479 3 4
480 4 5
481 SELECT * FROM t2 ORDER BY i;
482 i
483 1
484 2
485 3
486 SELECT * FROM t3 ORDER BY id;
487 id data
488 1 1
489 2 1
490 3 3
491 DROP PROCEDURE sp1;
492 DROP TABLE t1,t2,t3;
493 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
494 CREATE TABLE t2 (i INTEGER NULL);
495 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
496 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
497 INSERT INTO t2 VALUES(1),(2),(3);
498 CREATE PROCEDURE sp1()
499 BEGIN
500 DECLARE done INT DEFAULT 0;
501 DECLARE a CHAR(16);
502 DECLARE b,c INT;
503 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
504 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
505 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
506 OPEN cur1;
507 OPEN cur2;
508 REPEAT
509 FETCH cur1 INTO a, b;
510 FETCH cur2 INTO c;
511 IF NOT done THEN
512 IF b < c THEN
513 INSERT INTO t3 VALUES (a,b);
514 ELSE
515 INSERT INTO t3 VALUES (a,c);
516 END IF;
517 END IF;
518 UNTIL done END REPEAT;
519 CLOSE cur1;
520 CLOSE cur2;
521 END//
522 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# ;
523 CALL sp1();
524 SELECT * FROM t1 ORDER BY id;
525 id data
526 1 1
527 2 1
528 3 4
529 4 5
530 SELECT * FROM t2 ORDER BY i;
531 i
532 1
533 2
534 3
535 SELECT * FROM t3 ORDER BY id;
536 id data
537 1 1
538 2 1
539 3 3
540 DROP PROCEDURE sp1;
541 DROP TABLE t1,t2,t3;
542 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
543 CREATE TABLE t2 (i INTEGER NULL);
544 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
545 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
546 INSERT INTO t2 VALUES(1),(2),(3);
547 CREATE PROCEDURE sp1()
548 BEGIN
549 DECLARE done INT DEFAULT 0;
550 DECLARE a CHAR(16);
551 DECLARE b,c INT;
552 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
553 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
554 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
555 OPEN cur1;
556 OPEN cur2;
557 REPEAT
558 FETCH cur1 INTO a, b;
559 FETCH cur2 INTO c;
560 IF NOT done THEN
561 IF b < c THEN
562 INSERT INTO t3 VALUES (a,b);
563 ELSE
564 INSERT INTO t3 VALUES (a,c);
565 END IF;
566 END IF;
567 UNTIL done END REPEAT;
568 CLOSE cur1;
569 CLOSE cur2;
570 END//
571 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL ;
572 CALL sp1();
573 SELECT * FROM t1 ORDER BY id;
574 id data
575 1 1
576 2 1
577 3 4
578 4 5
579 SELECT * FROM t2 ORDER BY i;
580 i
581 1
582 2
583 3
584 SELECT * FROM t3 ORDER BY id;
585 id data
586 1 1
587 2 1
588 3 3
589 DROP PROCEDURE sp1;
590 DROP TABLE t1,t2,t3;
591 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
592 CREATE TABLE t2 (i INTEGER NULL);
593 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
594 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
595 INSERT INTO t2 VALUES(1),(2),(3);
596 CREATE PROCEDURE sp1()
597 BEGIN
598 DECLARE done INT DEFAULT 0;
599 DECLARE a CHAR(16);
600 DECLARE b,c INT;
601 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
602 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
603 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
604 OPEN cur1;
605 OPEN cur2;
606 REPEAT
607 FETCH cur1 INTO a, b;
608 FETCH cur2 INTO c;
609 IF NOT done THEN
610 IF b < c THEN
611 INSERT INTO t3 VALUES (a,b);
612 ELSE
613 INSERT INTO t3 VALUES (a,c);
614 END IF;
615 END IF;
616 UNTIL done END REPEAT;
617 CLOSE cur1;
618 CLOSE cur2;
619 END//
620 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL ;
621 CALL sp1();
622 SELECT * FROM t1 ORDER BY id;
623 id data
624 1 1
625 2 1
626 3 4
627 4 5
628 SELECT * FROM t2 ORDER BY i;
629 i
630 1
631 2
632 3
633 SELECT * FROM t3 ORDER BY id;
634 id data
635 1 1
636 2 1
637 3 3
638 DROP PROCEDURE sp1;
639 DROP TABLE t1,t2,t3;
640 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
641 CREATE TABLE t2 (i INTEGER NULL);
642 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
643 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
644 INSERT INTO t2 VALUES(1),(2),(3);
645 CREATE PROCEDURE sp1()
646 BEGIN
647 DECLARE done INT DEFAULT 0;
648 DECLARE a CHAR(16);
649 DECLARE b,c INT;
650 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
651 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
652 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
653 OPEN cur1;
654 OPEN cur2;
655 REPEAT
656 FETCH cur1 INTO a, b;
657 FETCH cur2 INTO c;
658 IF NOT done THEN
659 IF b < c THEN
660 INSERT INTO t3 VALUES (a,b);
661 ELSE
662 INSERT INTO t3 VALUES (a,c);
663 END IF;
664 END IF;
665 UNTIL done END REPEAT;
666 CLOSE cur1;
667 CLOSE cur2;
668 END//
669 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA ;
670 CALL sp1();
671 SELECT * FROM t1 ORDER BY id;
672 id data
673 1 1
674 2 1
675 3 4
676 4 5
677 SELECT * FROM t2 ORDER BY i;
678 i
679 1
680 2
681 3
682 SELECT * FROM t3 ORDER BY id;
683 id data
684 1 1
685 2 1
686 3 3
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 CREATE PROCEDURE sp1()
695 BEGIN
696 DECLARE done INT DEFAULT 0;
697 DECLARE a CHAR(16);
698 DECLARE b,c INT;
699 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
700 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
701 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
702 OPEN cur1;
703 OPEN cur2;
704 REPEAT
705 FETCH cur1 INTO a, b;
706 FETCH cur2 INTO c;
707 IF NOT done THEN
708 IF b < c THEN
709 INSERT INTO t3 VALUES (a,b);
710 ELSE
711 INSERT INTO t3 VALUES (a,c);
712 END IF;
713 END IF;
714 UNTIL done END REPEAT;
715 CLOSE cur1;
716 CLOSE cur2;
717 END//
718 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA ;
719 CALL sp1();
720 SELECT * FROM t1 ORDER BY id;
721 id data
722 1 1
723 2 1
724 3 4
725 4 5
726 SELECT * FROM t2 ORDER BY i;
727 i
728 1
729 2
730 3
731 SELECT * FROM t3 ORDER BY id;
732 id data
733 1 1
734 2 1
735 3 3
736 DROP PROCEDURE sp1;
737 DROP TABLE t1,t2,t3;
738 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
739 CREATE TABLE t2 (i INTEGER NULL);
740 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
741 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
742 INSERT INTO t2 VALUES(1),(2),(3);
743 CREATE PROCEDURE sp1()
744 BEGIN
745 DECLARE done INT DEFAULT 0;
746 DECLARE a CHAR(16);
747 DECLARE b,c INT;
748 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
749 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
750 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
751 OPEN cur1;
752 OPEN cur2;
753 REPEAT
754 FETCH cur1 INTO a, b;
755 FETCH cur2 INTO c;
756 IF NOT done THEN
757 IF b < c THEN
758 INSERT INTO t3 VALUES (a,b);
759 ELSE
760 INSERT INTO t3 VALUES (a,c);
761 END IF;
762 END IF;
763 UNTIL done END REPEAT;
764 CLOSE cur1;
765 CLOSE cur2;
766 END//
767 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# COMMENT 'comment' ;
768 CALL sp1();
769 SELECT * FROM t1 ORDER BY id;
770 id data
771 1 1
772 2 1
773 3 4
774 4 5
775 SELECT * FROM t2 ORDER BY i;
776 i
777 1
778 2
779 3
780 SELECT * FROM t3 ORDER BY id;
781 id data
782 1 1
783 2 1
784 3 3
785 DROP PROCEDURE sp1;
786 DROP TABLE t1,t2,t3;
787 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
788 CREATE TABLE t2 (i INTEGER NULL);
789 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
790 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
791 INSERT INTO t2 VALUES(1),(2),(3);
792 CREATE PROCEDURE sp1()
793 BEGIN
794 DECLARE done INT DEFAULT 0;
795 DECLARE a CHAR(16);
796 DECLARE b,c INT;
797 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
798 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
799 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
800 OPEN cur1;
801 OPEN cur2;
802 REPEAT
803 FETCH cur1 INTO a, b;
804 FETCH cur2 INTO c;
805 IF NOT done THEN
806 IF b < c THEN
807 INSERT INTO t3 VALUES (a,b);
808 ELSE
809 INSERT INTO t3 VALUES (a,c);
810 END IF;
811 END IF;
812 UNTIL done END REPEAT;
813 CLOSE cur1;
814 CLOSE cur2;
815 END//
816 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL COMMENT 'comment' ;
817 CALL sp1();
818 SELECT * FROM t1 ORDER BY id;
819 id data
820 1 1
821 2 1
822 3 4
823 4 5
824 SELECT * FROM t2 ORDER BY i;
825 i
826 1
827 2
828 3
829 SELECT * FROM t3 ORDER BY id;
830 id data
831 1 1
832 2 1
833 3 3
834 DROP PROCEDURE sp1;
835 DROP TABLE t1,t2,t3;
836 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
837 CREATE TABLE t2 (i INTEGER NULL);
838 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
839 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
840 INSERT INTO t2 VALUES(1),(2),(3);
841 CREATE PROCEDURE sp1()
842 BEGIN
843 DECLARE done INT DEFAULT 0;
844 DECLARE a CHAR(16);
845 DECLARE b,c INT;
846 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
847 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
848 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
849 OPEN cur1;
850 OPEN cur2;
851 REPEAT
852 FETCH cur1 INTO a, b;
853 FETCH cur2 INTO c;
854 IF NOT done THEN
855 IF b < c THEN
856 INSERT INTO t3 VALUES (a,b);
857 ELSE
858 INSERT INTO t3 VALUES (a,c);
859 END IF;
860 END IF;
861 UNTIL done END REPEAT;
862 CLOSE cur1;
863 CLOSE cur2;
864 END//
865 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL COMMENT 'comment' ;
866 CALL sp1();
867 SELECT * FROM t1 ORDER BY id;
868 id data
869 1 1
870 2 1
871 3 4
872 4 5
873 SELECT * FROM t2 ORDER BY i;
874 i
875 1
876 2
877 3
878 SELECT * FROM t3 ORDER BY id;
879 id data
880 1 1
881 2 1
882 3 3
883 DROP PROCEDURE sp1;
884 DROP TABLE t1,t2,t3;
885 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
886 CREATE TABLE t2 (i INTEGER NULL);
887 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
888 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
889 INSERT INTO t2 VALUES(1),(2),(3);
890 CREATE PROCEDURE sp1()
891 BEGIN
892 DECLARE done INT DEFAULT 0;
893 DECLARE a CHAR(16);
894 DECLARE b,c INT;
895 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
896 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
897 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
898 OPEN cur1;
899 OPEN cur2;
900 REPEAT
901 FETCH cur1 INTO a, b;
902 FETCH cur2 INTO c;
903 IF NOT done THEN
904 IF b < c THEN
905 INSERT INTO t3 VALUES (a,b);
906 ELSE
907 INSERT INTO t3 VALUES (a,c);
908 END IF;
909 END IF;
910 UNTIL done END REPEAT;
911 CLOSE cur1;
912 CLOSE cur2;
913 END//
914 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA COMMENT 'comment' ;
915 CALL sp1();
916 SELECT * FROM t1 ORDER BY id;
917 id data
918 1 1
919 2 1
920 3 4
921 4 5
922 SELECT * FROM t2 ORDER BY i;
923 i
924 1
925 2
926 3
927 SELECT * FROM t3 ORDER BY id;
928 id data
929 1 1
930 2 1
931 3 3
932 DROP PROCEDURE sp1;
933 DROP TABLE t1,t2,t3;
934 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
935 CREATE TABLE t2 (i INTEGER NULL);
936 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
937 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
938 INSERT INTO t2 VALUES(1),(2),(3);
939 CREATE PROCEDURE sp1()
940 BEGIN
941 DECLARE done INT DEFAULT 0;
942 DECLARE a CHAR(16);
943 DECLARE b,c INT;
944 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
945 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
946 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
947 OPEN cur1;
948 OPEN cur2;
949 REPEAT
950 FETCH cur1 INTO a, b;
951 FETCH cur2 INTO c;
952 IF NOT done THEN
953 IF b < c THEN
954 INSERT INTO t3 VALUES (a,b);
955 ELSE
956 INSERT INTO t3 VALUES (a,c);
957 END IF;
958 END IF;
959 UNTIL done END REPEAT;
960 CLOSE cur1;
961 CLOSE cur2;
962 END//
963 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA COMMENT 'comment' ;
964 CALL sp1();
965 SELECT * FROM t1 ORDER BY id;
966 id data
967 1 1
968 2 1
969 3 4
970 4 5
971 SELECT * FROM t2 ORDER BY i;
972 i
973 1
974 2
975 3
976 SELECT * FROM t3 ORDER BY id;
977 id data
978 1 1
979 2 1
980 3 3
981 DROP PROCEDURE sp1;
982 DROP TABLE t1,t2,t3;
983 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
984 CREATE TABLE t2 (i INTEGER NULL);
985 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
986 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
987 INSERT INTO t2 VALUES(1),(2),(3);
988 CREATE PROCEDURE sp1()
989 BEGIN
990 DECLARE done INT DEFAULT 0;
991 DECLARE a CHAR(16);
992 DECLARE b,c INT;
993 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
994 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
995 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
996 OPEN cur1;
997 OPEN cur2;
998 REPEAT
999 FETCH cur1 INTO a, b;
1000 FETCH cur2 INTO c;
1001 IF NOT done THEN
1002 IF b < c THEN
1003 INSERT INTO t3 VALUES (a,b);
1004 ELSE
1005 INSERT INTO t3 VALUES (a,c);
1006 END IF;
1007 END IF;
1008 UNTIL done END REPEAT;
1009 CLOSE cur1;
1010 CLOSE cur2;
1011 END//
1012 ALTER PROCEDURE sp1 #DET# SQL SECURITY INVOKER ;
1013 CALL sp1();
1014 SELECT * FROM t1 ORDER BY id;
1015 id data
1016 1 1
1017 2 1
1018 3 4
1019 4 5
1020 SELECT * FROM t2 ORDER BY i;
1021 i
1022 1
1023 2
1024 3
1025 SELECT * FROM t3 ORDER BY id;
1026 id data
1027 1 1
1028 2 1
1029 3 3
1030 DROP PROCEDURE sp1;
1031 DROP TABLE t1,t2,t3;
1032 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1033 CREATE TABLE t2 (i INTEGER NULL);
1034 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1035 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1036 INSERT INTO t2 VALUES(1),(2),(3);
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 ALTER PROCEDURE sp1 #DET# CONTAINS SQL SQL SECURITY INVOKER ;
1062 CALL sp1();
1063 SELECT * FROM t1 ORDER BY id;
1064 id data
1065 1 1
1066 2 1
1067 3 4
1068 4 5
1069 SELECT * FROM t2 ORDER BY i;
1070 i
1071 1
1072 2
1073 3
1074 SELECT * FROM t3 ORDER BY id;
1075 id data
1076 1 1
1077 2 1
1078 3 3
1079 DROP PROCEDURE sp1;
1080 DROP TABLE t1,t2,t3;
1081 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1082 CREATE TABLE t2 (i INTEGER NULL);
1083 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1084 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1085 INSERT INTO t2 VALUES(1),(2),(3);
1086 CREATE PROCEDURE sp1()
1087 BEGIN
1088 DECLARE done INT DEFAULT 0;
1089 DECLARE a CHAR(16);
1090 DECLARE b,c INT;
1091 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1092 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1093 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1094 OPEN cur1;
1095 OPEN cur2;
1096 REPEAT
1097 FETCH cur1 INTO a, b;
1098 FETCH cur2 INTO c;
1099 IF NOT done THEN
1100 IF b < c THEN
1101 INSERT INTO t3 VALUES (a,b);
1102 ELSE
1103 INSERT INTO t3 VALUES (a,c);
1104 END IF;
1105 END IF;
1106 UNTIL done END REPEAT;
1107 CLOSE cur1;
1108 CLOSE cur2;
1109 END//
1110 ALTER PROCEDURE sp1 #DET# NO SQL SQL SECURITY INVOKER ;
1111 CALL sp1();
1112 SELECT * FROM t1 ORDER BY id;
1113 id data
1114 1 1
1115 2 1
1116 3 4
1117 4 5
1118 SELECT * FROM t2 ORDER BY i;
1119 i
1120 1
1121 2
1122 3
1123 SELECT * FROM t3 ORDER BY id;
1124 id data
1125 1 1
1126 2 1
1127 3 3
1128 DROP PROCEDURE sp1;
1129 DROP TABLE t1,t2,t3;
1130 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1131 CREATE TABLE t2 (i INTEGER NULL);
1132 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1133 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1134 INSERT INTO t2 VALUES(1),(2),(3);
1135 CREATE PROCEDURE sp1()
1136 BEGIN
1137 DECLARE done INT DEFAULT 0;
1138 DECLARE a CHAR(16);
1139 DECLARE b,c INT;
1140 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1141 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1142 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1143 OPEN cur1;
1144 OPEN cur2;
1145 REPEAT
1146 FETCH cur1 INTO a, b;
1147 FETCH cur2 INTO c;
1148 IF NOT done THEN
1149 IF b < c THEN
1150 INSERT INTO t3 VALUES (a,b);
1151 ELSE
1152 INSERT INTO t3 VALUES (a,c);
1153 END IF;
1154 END IF;
1155 UNTIL done END REPEAT;
1156 CLOSE cur1;
1157 CLOSE cur2;
1158 END//
1159 ALTER PROCEDURE sp1 #DET# READS SQL DATA SQL SECURITY INVOKER ;
1160 CALL sp1();
1161 SELECT * FROM t1 ORDER BY id;
1162 id data
1163 1 1
1164 2 1
1165 3 4
1166 4 5
1167 SELECT * FROM t2 ORDER BY i;
1168 i
1169 1
1170 2
1171 3
1172 SELECT * FROM t3 ORDER BY id;
1173 id data
1174 1 1
1175 2 1
1176 3 3
1177 DROP PROCEDURE sp1;
1178 DROP TABLE t1,t2,t3;
1179 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1180 CREATE TABLE t2 (i INTEGER NULL);
1181 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1182 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1183 INSERT INTO t2 VALUES(1),(2),(3);
1184 CREATE PROCEDURE sp1()
1185 BEGIN
1186 DECLARE done INT DEFAULT 0;
1187 DECLARE a CHAR(16);
1188 DECLARE b,c INT;
1189 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1190 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1191 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1192 OPEN cur1;
1193 OPEN cur2;
1194 REPEAT
1195 FETCH cur1 INTO a, b;
1196 FETCH cur2 INTO c;
1197 IF NOT done THEN
1198 IF b < c THEN
1199 INSERT INTO t3 VALUES (a,b);
1200 ELSE
1201 INSERT INTO t3 VALUES (a,c);
1202 END IF;
1203 END IF;
1204 UNTIL done END REPEAT;
1205 CLOSE cur1;
1206 CLOSE cur2;
1207 END//
1208 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA SQL SECURITY INVOKER ;
1209 CALL sp1();
1210 SELECT * FROM t1 ORDER BY id;
1211 id data
1212 1 1
1213 2 1
1214 3 4
1215 4 5
1216 SELECT * FROM t2 ORDER BY i;
1217 i
1218 1
1219 2
1220 3
1221 SELECT * FROM t3 ORDER BY id;
1222 id data
1223 1 1
1224 2 1
1225 3 3
1226 DROP PROCEDURE sp1;
1227 DROP TABLE t1,t2,t3;
1228 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1229 CREATE TABLE t2 (i INTEGER NULL);
1230 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1231 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1232 INSERT INTO t2 VALUES(1),(2),(3);
1233 CREATE PROCEDURE sp1()
1234 BEGIN
1235 DECLARE done INT DEFAULT 0;
1236 DECLARE a CHAR(16);
1237 DECLARE b,c INT;
1238 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1239 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1240 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1241 OPEN cur1;
1242 OPEN cur2;
1243 REPEAT
1244 FETCH cur1 INTO a, b;
1245 FETCH cur2 INTO c;
1246 IF NOT done THEN
1247 IF b < c THEN
1248 INSERT INTO t3 VALUES (a,b);
1249 ELSE
1250 INSERT INTO t3 VALUES (a,c);
1251 END IF;
1252 END IF;
1253 UNTIL done END REPEAT;
1254 CLOSE cur1;
1255 CLOSE cur2;
1256 END//
1257 ALTER PROCEDURE sp1 #DET# SQL SECURITY INVOKER COMMENT 'comment' ;
1258 CALL sp1();
1259 SELECT * FROM t1 ORDER BY id;
1260 id data
1261 1 1
1262 2 1
1263 3 4
1264 4 5
1265 SELECT * FROM t2 ORDER BY i;
1266 i
1267 1
1268 2
1269 3
1270 SELECT * FROM t3 ORDER BY id;
1271 id data
1272 1 1
1273 2 1
1274 3 3
1275 DROP PROCEDURE sp1;
1276 DROP TABLE t1,t2,t3;
1277 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1278 CREATE TABLE t2 (i INTEGER NULL);
1279 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1280 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1281 INSERT INTO t2 VALUES(1),(2),(3);
1282 CREATE PROCEDURE sp1()
1283 BEGIN
1284 DECLARE done INT DEFAULT 0;
1285 DECLARE a CHAR(16);
1286 DECLARE b,c INT;
1287 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1288 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1289 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1290 OPEN cur1;
1291 OPEN cur2;
1292 REPEAT
1293 FETCH cur1 INTO a, b;
1294 FETCH cur2 INTO c;
1295 IF NOT done THEN
1296 IF b < c THEN
1297 INSERT INTO t3 VALUES (a,b);
1298 ELSE
1299 INSERT INTO t3 VALUES (a,c);
1300 END IF;
1301 END IF;
1302 UNTIL done END REPEAT;
1303 CLOSE cur1;
1304 CLOSE cur2;
1305 END//
1306 ALTER PROCEDURE sp1 #DET# CONTAINS SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1307 CALL sp1();
1308 SELECT * FROM t1 ORDER BY id;
1309 id data
1310 1 1
1311 2 1
1312 3 4
1313 4 5
1314 SELECT * FROM t2 ORDER BY i;
1315 i
1316 1
1317 2
1318 3
1319 SELECT * FROM t3 ORDER BY id;
1320 id data
1321 1 1
1322 2 1
1323 3 3
1324 DROP PROCEDURE sp1;
1325 DROP TABLE t1,t2,t3;
1326 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1327 CREATE TABLE t2 (i INTEGER NULL);
1328 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1329 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1330 INSERT INTO t2 VALUES(1),(2),(3);
1331 CREATE PROCEDURE sp1()
1332 BEGIN
1333 DECLARE done INT DEFAULT 0;
1334 DECLARE a CHAR(16);
1335 DECLARE b,c INT;
1336 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1337 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1338 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1339 OPEN cur1;
1340 OPEN cur2;
1341 REPEAT
1342 FETCH cur1 INTO a, b;
1343 FETCH cur2 INTO c;
1344 IF NOT done THEN
1345 IF b < c THEN
1346 INSERT INTO t3 VALUES (a,b);
1347 ELSE
1348 INSERT INTO t3 VALUES (a,c);
1349 END IF;
1350 END IF;
1351 UNTIL done END REPEAT;
1352 CLOSE cur1;
1353 CLOSE cur2;
1354 END//
1355 ALTER PROCEDURE sp1 #DET# NO SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1356 CALL sp1();
1357 SELECT * FROM t1 ORDER BY id;
1358 id data
1359 1 1
1360 2 1
1361 3 4
1362 4 5
1363 SELECT * FROM t2 ORDER BY i;
1364 i
1365 1
1366 2
1367 3
1368 SELECT * FROM t3 ORDER BY id;
1369 id data
1370 1 1
1371 2 1
1372 3 3
1373 DROP PROCEDURE sp1;
1374 DROP TABLE t1,t2,t3;
1375 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1376 CREATE TABLE t2 (i INTEGER NULL);
1377 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1378 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1379 INSERT INTO t2 VALUES(1),(2),(3);
1380 CREATE PROCEDURE sp1()
1381 BEGIN
1382 DECLARE done INT DEFAULT 0;
1383 DECLARE a CHAR(16);
1384 DECLARE b,c INT;
1385 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1386 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1387 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1388 OPEN cur1;
1389 OPEN cur2;
1390 REPEAT
1391 FETCH cur1 INTO a, b;
1392 FETCH cur2 INTO c;
1393 IF NOT done THEN
1394 IF b < c THEN
1395 INSERT INTO t3 VALUES (a,b);
1396 ELSE
1397 INSERT INTO t3 VALUES (a,c);
1398 END IF;
1399 END IF;
1400 UNTIL done END REPEAT;
1401 CLOSE cur1;
1402 CLOSE cur2;
1403 END//
1404 ALTER PROCEDURE sp1 #DET# READS SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1405 CALL sp1();
1406 SELECT * FROM t1 ORDER BY id;
1407 id data
1408 1 1
1409 2 1
1410 3 4
1411 4 5
1412 SELECT * FROM t2 ORDER BY i;
1413 i
1414 1
1415 2
1416 3
1417 SELECT * FROM t3 ORDER BY id;
1418 id data
1419 1 1
1420 2 1
1421 3 3
1422 DROP PROCEDURE sp1;
1423 DROP TABLE t1,t2,t3;
1424 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1425 CREATE TABLE t2 (i INTEGER NULL);
1426 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1427 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1428 INSERT INTO t2 VALUES(1),(2),(3);
1429 CREATE PROCEDURE sp1()
1430 BEGIN
1431 DECLARE done INT DEFAULT 0;
1432 DECLARE a CHAR(16);
1433 DECLARE b,c INT;
1434 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1435 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1436 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1437 OPEN cur1;
1438 OPEN cur2;
1439 REPEAT
1440 FETCH cur1 INTO a, b;
1441 FETCH cur2 INTO c;
1442 IF NOT done THEN
1443 IF b < c THEN
1444 INSERT INTO t3 VALUES (a,b);
1445 ELSE
1446 INSERT INTO t3 VALUES (a,c);
1447 END IF;
1448 END IF;
1449 UNTIL done END REPEAT;
1450 CLOSE cur1;
1451 CLOSE cur2;
1452 END//
1453 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1454 CALL sp1();
1455 SELECT * FROM t1 ORDER BY id;
1456 id data
1457 1 1
1458 2 1
1459 3 4
1460 4 5
1461 SELECT * FROM t2 ORDER BY i;
1462 i
1463 1
1464 2
1465 3
1466 SELECT * FROM t3 ORDER BY id;
1467 id data
1468 1 1
1469 2 1
1470 3 3
1471 DROP PROCEDURE sp1;
1472 DROP TABLE t1,t2,t3;
1473 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1474 CREATE TABLE t2 (i INTEGER NULL);
1475 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1476 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1477 INSERT INTO t2 VALUES(1),(2),(3);
1478 CREATE PROCEDURE sp1()
1479 BEGIN
1480 DECLARE done INT DEFAULT 0;
1481 DECLARE a CHAR(16);
1482 DECLARE b,c INT;
1483 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1484 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1485 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1486 OPEN cur1;
1487 OPEN cur2;
1488 REPEAT
1489 FETCH cur1 INTO a, b;
1490 FETCH cur2 INTO c;
1491 IF NOT done THEN
1492 IF b < c THEN
1493 INSERT INTO t3 VALUES (a,b);
1494 ELSE
1495 INSERT INTO t3 VALUES (a,c);
1496 END IF;
1497 END IF;
1498 UNTIL done END REPEAT;
1499 CLOSE cur1;
1500 CLOSE cur2;
1501 END//
1502 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# SQL SECURITY INVOKER ;
1503 CALL sp1();
1504 SELECT * FROM t1 ORDER BY id;
1505 id data
1506 1 1
1507 2 1
1508 3 4
1509 4 5
1510 SELECT * FROM t2 ORDER BY i;
1511 i
1512 1
1513 2
1514 3
1515 SELECT * FROM t3 ORDER BY id;
1516 id data
1517 1 1
1518 2 1
1519 3 3
1520 DROP PROCEDURE sp1;
1521 DROP TABLE t1,t2,t3;
1522 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1523 CREATE TABLE t2 (i INTEGER NULL);
1524 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1525 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1526 INSERT INTO t2 VALUES(1),(2),(3);
1527 CREATE PROCEDURE sp1()
1528 BEGIN
1529 DECLARE done INT DEFAULT 0;
1530 DECLARE a CHAR(16);
1531 DECLARE b,c INT;
1532 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1533 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1534 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1535 OPEN cur1;
1536 OPEN cur2;
1537 REPEAT
1538 FETCH cur1 INTO a, b;
1539 FETCH cur2 INTO c;
1540 IF NOT done THEN
1541 IF b < c THEN
1542 INSERT INTO t3 VALUES (a,b);
1543 ELSE
1544 INSERT INTO t3 VALUES (a,c);
1545 END IF;
1546 END IF;
1547 UNTIL done END REPEAT;
1548 CLOSE cur1;
1549 CLOSE cur2;
1550 END//
1551 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY INVOKER ;
1552 CALL sp1();
1553 SELECT * FROM t1 ORDER BY id;
1554 id data
1555 1 1
1556 2 1
1557 3 4
1558 4 5
1559 SELECT * FROM t2 ORDER BY i;
1560 i
1561 1
1562 2
1563 3
1564 SELECT * FROM t3 ORDER BY id;
1565 id data
1566 1 1
1567 2 1
1568 3 3
1569 DROP PROCEDURE sp1;
1570 DROP TABLE t1,t2,t3;
1571 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1572 CREATE TABLE t2 (i INTEGER NULL);
1573 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1574 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1575 INSERT INTO t2 VALUES(1),(2),(3);
1576 CREATE PROCEDURE sp1()
1577 BEGIN
1578 DECLARE done INT DEFAULT 0;
1579 DECLARE a CHAR(16);
1580 DECLARE b,c INT;
1581 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1582 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1583 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1584 OPEN cur1;
1585 OPEN cur2;
1586 REPEAT
1587 FETCH cur1 INTO a, b;
1588 FETCH cur2 INTO c;
1589 IF NOT done THEN
1590 IF b < c THEN
1591 INSERT INTO t3 VALUES (a,b);
1592 ELSE
1593 INSERT INTO t3 VALUES (a,c);
1594 END IF;
1595 END IF;
1596 UNTIL done END REPEAT;
1597 CLOSE cur1;
1598 CLOSE cur2;
1599 END//
1600 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL SQL SECURITY INVOKER ;
1601 CALL sp1();
1602 SELECT * FROM t1 ORDER BY id;
1603 id data
1604 1 1
1605 2 1
1606 3 4
1607 4 5
1608 SELECT * FROM t2 ORDER BY i;
1609 i
1610 1
1611 2
1612 3
1613 SELECT * FROM t3 ORDER BY id;
1614 id data
1615 1 1
1616 2 1
1617 3 3
1618 DROP PROCEDURE sp1;
1619 DROP TABLE t1,t2,t3;
1620 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1621 CREATE TABLE t2 (i INTEGER NULL);
1622 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1623 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1624 INSERT INTO t2 VALUES(1),(2),(3);
1625 CREATE PROCEDURE sp1()
1626 BEGIN
1627 DECLARE done INT DEFAULT 0;
1628 DECLARE a CHAR(16);
1629 DECLARE b,c INT;
1630 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1631 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1632 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1633 OPEN cur1;
1634 OPEN cur2;
1635 REPEAT
1636 FETCH cur1 INTO a, b;
1637 FETCH cur2 INTO c;
1638 IF NOT done THEN
1639 IF b < c THEN
1640 INSERT INTO t3 VALUES (a,b);
1641 ELSE
1642 INSERT INTO t3 VALUES (a,c);
1643 END IF;
1644 END IF;
1645 UNTIL done END REPEAT;
1646 CLOSE cur1;
1647 CLOSE cur2;
1648 END//
1649 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY INVOKER ;
1650 CALL sp1();
1651 SELECT * FROM t1 ORDER BY id;
1652 id data
1653 1 1
1654 2 1
1655 3 4
1656 4 5
1657 SELECT * FROM t2 ORDER BY i;
1658 i
1659 1
1660 2
1661 3
1662 SELECT * FROM t3 ORDER BY id;
1663 id data
1664 1 1
1665 2 1
1666 3 3
1667 DROP PROCEDURE sp1;
1668 DROP TABLE t1,t2,t3;
1669 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1670 CREATE TABLE t2 (i INTEGER NULL);
1671 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1672 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1673 INSERT INTO t2 VALUES(1),(2),(3);
1674 CREATE PROCEDURE sp1()
1675 BEGIN
1676 DECLARE done INT DEFAULT 0;
1677 DECLARE a CHAR(16);
1678 DECLARE b,c INT;
1679 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1680 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1681 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1682 OPEN cur1;
1683 OPEN cur2;
1684 REPEAT
1685 FETCH cur1 INTO a, b;
1686 FETCH cur2 INTO c;
1687 IF NOT done THEN
1688 IF b < c THEN
1689 INSERT INTO t3 VALUES (a,b);
1690 ELSE
1691 INSERT INTO t3 VALUES (a,c);
1692 END IF;
1693 END IF;
1694 UNTIL done END REPEAT;
1695 CLOSE cur1;
1696 CLOSE cur2;
1697 END//
1698 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY INVOKER ;
1699 CALL sp1();
1700 SELECT * FROM t1 ORDER BY id;
1701 id data
1702 1 1
1703 2 1
1704 3 4
1705 4 5
1706 SELECT * FROM t2 ORDER BY i;
1707 i
1708 1
1709 2
1710 3
1711 SELECT * FROM t3 ORDER BY id;
1712 id data
1713 1 1
1714 2 1
1715 3 3
1716 DROP PROCEDURE sp1;
1717 DROP TABLE t1,t2,t3;
1718 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1719 CREATE TABLE t2 (i INTEGER NULL);
1720 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1721 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1722 INSERT INTO t2 VALUES(1),(2),(3);
1723 CREATE PROCEDURE sp1()
1724 BEGIN
1725 DECLARE done INT DEFAULT 0;
1726 DECLARE a CHAR(16);
1727 DECLARE b,c INT;
1728 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1729 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1730 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1731 OPEN cur1;
1732 OPEN cur2;
1733 REPEAT
1734 FETCH cur1 INTO a, b;
1735 FETCH cur2 INTO c;
1736 IF NOT done THEN
1737 IF b < c THEN
1738 INSERT INTO t3 VALUES (a,b);
1739 ELSE
1740 INSERT INTO t3 VALUES (a,c);
1741 END IF;
1742 END IF;
1743 UNTIL done END REPEAT;
1744 CLOSE cur1;
1745 CLOSE cur2;
1746 END//
1747 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# SQL SECURITY INVOKER COMMENT 'comment' ;
1748 CALL sp1();
1749 SELECT * FROM t1 ORDER BY id;
1750 id data
1751 1 1
1752 2 1
1753 3 4
1754 4 5
1755 SELECT * FROM t2 ORDER BY i;
1756 i
1757 1
1758 2
1759 3
1760 SELECT * FROM t3 ORDER BY id;
1761 id data
1762 1 1
1763 2 1
1764 3 3
1765 DROP PROCEDURE sp1;
1766 DROP TABLE t1,t2,t3;
1767 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1768 CREATE TABLE t2 (i INTEGER NULL);
1769 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1770 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1771 INSERT INTO t2 VALUES(1),(2),(3);
1772 CREATE PROCEDURE sp1()
1773 BEGIN
1774 DECLARE done INT DEFAULT 0;
1775 DECLARE a CHAR(16);
1776 DECLARE b,c INT;
1777 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1778 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1779 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1780 OPEN cur1;
1781 OPEN cur2;
1782 REPEAT
1783 FETCH cur1 INTO a, b;
1784 FETCH cur2 INTO c;
1785 IF NOT done THEN
1786 IF b < c THEN
1787 INSERT INTO t3 VALUES (a,b);
1788 ELSE
1789 INSERT INTO t3 VALUES (a,c);
1790 END IF;
1791 END IF;
1792 UNTIL done END REPEAT;
1793 CLOSE cur1;
1794 CLOSE cur2;
1795 END//
1796 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1797 CALL sp1();
1798 SELECT * FROM t1 ORDER BY id;
1799 id data
1800 1 1
1801 2 1
1802 3 4
1803 4 5
1804 SELECT * FROM t2 ORDER BY i;
1805 i
1806 1
1807 2
1808 3
1809 SELECT * FROM t3 ORDER BY id;
1810 id data
1811 1 1
1812 2 1
1813 3 3
1814 DROP PROCEDURE sp1;
1815 DROP TABLE t1,t2,t3;
1816 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1817 CREATE TABLE t2 (i INTEGER NULL);
1818 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1819 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1820 INSERT INTO t2 VALUES(1),(2),(3);
1821 CREATE PROCEDURE sp1()
1822 BEGIN
1823 DECLARE done INT DEFAULT 0;
1824 DECLARE a CHAR(16);
1825 DECLARE b,c INT;
1826 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1827 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1828 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1829 OPEN cur1;
1830 OPEN cur2;
1831 REPEAT
1832 FETCH cur1 INTO a, b;
1833 FETCH cur2 INTO c;
1834 IF NOT done THEN
1835 IF b < c THEN
1836 INSERT INTO t3 VALUES (a,b);
1837 ELSE
1838 INSERT INTO t3 VALUES (a,c);
1839 END IF;
1840 END IF;
1841 UNTIL done END REPEAT;
1842 CLOSE cur1;
1843 CLOSE cur2;
1844 END//
1845 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1846 CALL sp1();
1847 SELECT * FROM t1 ORDER BY id;
1848 id data
1849 1 1
1850 2 1
1851 3 4
1852 4 5
1853 SELECT * FROM t2 ORDER BY i;
1854 i
1855 1
1856 2
1857 3
1858 SELECT * FROM t3 ORDER BY id;
1859 id data
1860 1 1
1861 2 1
1862 3 3
1863 DROP PROCEDURE sp1;
1864 DROP TABLE t1,t2,t3;
1865 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1866 CREATE TABLE t2 (i INTEGER NULL);
1867 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1868 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1869 INSERT INTO t2 VALUES(1),(2),(3);
1870 CREATE PROCEDURE sp1()
1871 BEGIN
1872 DECLARE done INT DEFAULT 0;
1873 DECLARE a CHAR(16);
1874 DECLARE b,c INT;
1875 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1876 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1877 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1878 OPEN cur1;
1879 OPEN cur2;
1880 REPEAT
1881 FETCH cur1 INTO a, b;
1882 FETCH cur2 INTO c;
1883 IF NOT done THEN
1884 IF b < c THEN
1885 INSERT INTO t3 VALUES (a,b);
1886 ELSE
1887 INSERT INTO t3 VALUES (a,c);
1888 END IF;
1889 END IF;
1890 UNTIL done END REPEAT;
1891 CLOSE cur1;
1892 CLOSE cur2;
1893 END//
1894 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1895 CALL sp1();
1896 SELECT * FROM t1 ORDER BY id;
1897 id data
1898 1 1
1899 2 1
1900 3 4
1901 4 5
1902 SELECT * FROM t2 ORDER BY i;
1903 i
1904 1
1905 2
1906 3
1907 SELECT * FROM t3 ORDER BY id;
1908 id data
1909 1 1
1910 2 1
1911 3 3
1912 DROP PROCEDURE sp1;
1913 DROP TABLE t1,t2,t3;
1914 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1915 CREATE TABLE t2 (i INTEGER NULL);
1916 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1917 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1918 INSERT INTO t2 VALUES(1),(2),(3);
1919 CREATE PROCEDURE sp1()
1920 BEGIN
1921 DECLARE done INT DEFAULT 0;
1922 DECLARE a CHAR(16);
1923 DECLARE b,c INT;
1924 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1925 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1926 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1927 OPEN cur1;
1928 OPEN cur2;
1929 REPEAT
1930 FETCH cur1 INTO a, b;
1931 FETCH cur2 INTO c;
1932 IF NOT done THEN
1933 IF b < c THEN
1934 INSERT INTO t3 VALUES (a,b);
1935 ELSE
1936 INSERT INTO t3 VALUES (a,c);
1937 END IF;
1938 END IF;
1939 UNTIL done END REPEAT;
1940 CLOSE cur1;
1941 CLOSE cur2;
1942 END//
1943 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1944 CALL sp1();
1945 SELECT * FROM t1 ORDER BY id;
1946 id data
1947 1 1
1948 2 1
1949 3 4
1950 4 5
1951 SELECT * FROM t2 ORDER BY i;
1952 i
1953 1
1954 2
1955 3
1956 SELECT * FROM t3 ORDER BY id;
1957 id data
1958 1 1
1959 2 1
1960 3 3
1961 DROP PROCEDURE sp1;
1962 DROP TABLE t1,t2,t3;
1963 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1964 CREATE TABLE t2 (i INTEGER NULL);
1965 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
1966 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1967 INSERT INTO t2 VALUES(1),(2),(3);
1968 CREATE PROCEDURE sp1()
1969 BEGIN
1970 DECLARE done INT DEFAULT 0;
1971 DECLARE a CHAR(16);
1972 DECLARE b,c INT;
1973 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1974 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1975 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1976 OPEN cur1;
1977 OPEN cur2;
1978 REPEAT
1979 FETCH cur1 INTO a, b;
1980 FETCH cur2 INTO c;
1981 IF NOT done THEN
1982 IF b < c THEN
1983 INSERT INTO t3 VALUES (a,b);
1984 ELSE
1985 INSERT INTO t3 VALUES (a,c);
1986 END IF;
1987 END IF;
1988 UNTIL done END REPEAT;
1989 CLOSE cur1;
1990 CLOSE cur2;
1991 END//
1992 ALTER PROCEDURE sp1 #DET# SQL SECURITY DEFINER ;
1993 CALL sp1();
1994 SELECT * FROM t1 ORDER BY id;
1995 id data
1996 1 1
1997 2 1
1998 3 4
1999 4 5
2000 SELECT * FROM t2 ORDER BY i;
2001 i
2002 1
2003 2
2004 3
2005 SELECT * FROM t3 ORDER BY id;
2006 id data
2007 1 1
2008 2 1
2009 3 3
2010 DROP PROCEDURE sp1;
2011 DROP TABLE t1,t2,t3;
2012 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2013 CREATE TABLE t2 (i INTEGER NULL);
2014 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2015 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2016 INSERT INTO t2 VALUES(1),(2),(3);
2017 CREATE PROCEDURE sp1()
2018 BEGIN
2019 DECLARE done INT DEFAULT 0;
2020 DECLARE a CHAR(16);
2021 DECLARE b,c INT;
2022 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2023 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2024 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2025 OPEN cur1;
2026 OPEN cur2;
2027 REPEAT
2028 FETCH cur1 INTO a, b;
2029 FETCH cur2 INTO c;
2030 IF NOT done THEN
2031 IF b < c THEN
2032 INSERT INTO t3 VALUES (a,b);
2033 ELSE
2034 INSERT INTO t3 VALUES (a,c);
2035 END IF;
2036 END IF;
2037 UNTIL done END REPEAT;
2038 CLOSE cur1;
2039 CLOSE cur2;
2040 END//
2041 ALTER PROCEDURE sp1 #DET# CONTAINS SQL SQL SECURITY DEFINER ;
2042 CALL sp1();
2043 SELECT * FROM t1 ORDER BY id;
2044 id data
2045 1 1
2046 2 1
2047 3 4
2048 4 5
2049 SELECT * FROM t2 ORDER BY i;
2050 i
2051 1
2052 2
2053 3
2054 SELECT * FROM t3 ORDER BY id;
2055 id data
2056 1 1
2057 2 1
2058 3 3
2059 DROP PROCEDURE sp1;
2060 DROP TABLE t1,t2,t3;
2061 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2062 CREATE TABLE t2 (i INTEGER NULL);
2063 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2064 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2065 INSERT INTO t2 VALUES(1),(2),(3);
2066 CREATE PROCEDURE sp1()
2067 BEGIN
2068 DECLARE done INT DEFAULT 0;
2069 DECLARE a CHAR(16);
2070 DECLARE b,c INT;
2071 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2072 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2073 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2074 OPEN cur1;
2075 OPEN cur2;
2076 REPEAT
2077 FETCH cur1 INTO a, b;
2078 FETCH cur2 INTO c;
2079 IF NOT done THEN
2080 IF b < c THEN
2081 INSERT INTO t3 VALUES (a,b);
2082 ELSE
2083 INSERT INTO t3 VALUES (a,c);
2084 END IF;
2085 END IF;
2086 UNTIL done END REPEAT;
2087 CLOSE cur1;
2088 CLOSE cur2;
2089 END//
2090 ALTER PROCEDURE sp1 #DET# NO SQL SQL SECURITY DEFINER ;
2091 CALL sp1();
2092 SELECT * FROM t1 ORDER BY id;
2093 id data
2094 1 1
2095 2 1
2096 3 4
2097 4 5
2098 SELECT * FROM t2 ORDER BY i;
2099 i
2100 1
2101 2
2102 3
2103 SELECT * FROM t3 ORDER BY id;
2104 id data
2105 1 1
2106 2 1
2107 3 3
2108 DROP PROCEDURE sp1;
2109 DROP TABLE t1,t2,t3;
2110 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2111 CREATE TABLE t2 (i INTEGER NULL);
2112 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2113 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2114 INSERT INTO t2 VALUES(1),(2),(3);
2115 CREATE PROCEDURE sp1()
2116 BEGIN
2117 DECLARE done INT DEFAULT 0;
2118 DECLARE a CHAR(16);
2119 DECLARE b,c INT;
2120 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2121 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2122 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2123 OPEN cur1;
2124 OPEN cur2;
2125 REPEAT
2126 FETCH cur1 INTO a, b;
2127 FETCH cur2 INTO c;
2128 IF NOT done THEN
2129 IF b < c THEN
2130 INSERT INTO t3 VALUES (a,b);
2131 ELSE
2132 INSERT INTO t3 VALUES (a,c);
2133 END IF;
2134 END IF;
2135 UNTIL done END REPEAT;
2136 CLOSE cur1;
2137 CLOSE cur2;
2138 END//
2139 ALTER PROCEDURE sp1 #DET# READS SQL DATA SQL SECURITY DEFINER ;
2140 CALL sp1();
2141 SELECT * FROM t1 ORDER BY id;
2142 id data
2143 1 1
2144 2 1
2145 3 4
2146 4 5
2147 SELECT * FROM t2 ORDER BY i;
2148 i
2149 1
2150 2
2151 3
2152 SELECT * FROM t3 ORDER BY id;
2153 id data
2154 1 1
2155 2 1
2156 3 3
2157 DROP PROCEDURE sp1;
2158 DROP TABLE t1,t2,t3;
2159 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2160 CREATE TABLE t2 (i INTEGER NULL);
2161 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2162 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2163 INSERT INTO t2 VALUES(1),(2),(3);
2164 CREATE PROCEDURE sp1()
2165 BEGIN
2166 DECLARE done INT DEFAULT 0;
2167 DECLARE a CHAR(16);
2168 DECLARE b,c INT;
2169 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2170 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2171 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2172 OPEN cur1;
2173 OPEN cur2;
2174 REPEAT
2175 FETCH cur1 INTO a, b;
2176 FETCH cur2 INTO c;
2177 IF NOT done THEN
2178 IF b < c THEN
2179 INSERT INTO t3 VALUES (a,b);
2180 ELSE
2181 INSERT INTO t3 VALUES (a,c);
2182 END IF;
2183 END IF;
2184 UNTIL done END REPEAT;
2185 CLOSE cur1;
2186 CLOSE cur2;
2187 END//
2188 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA SQL SECURITY DEFINER ;
2189 CALL sp1();
2190 SELECT * FROM t1 ORDER BY id;
2191 id data
2192 1 1
2193 2 1
2194 3 4
2195 4 5
2196 SELECT * FROM t2 ORDER BY i;
2197 i
2198 1
2199 2
2200 3
2201 SELECT * FROM t3 ORDER BY id;
2202 id data
2203 1 1
2204 2 1
2205 3 3
2206 DROP PROCEDURE sp1;
2207 DROP TABLE t1,t2,t3;
2208 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2209 CREATE TABLE t2 (i INTEGER NULL);
2210 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2211 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2212 INSERT INTO t2 VALUES(1),(2),(3);
2213 CREATE PROCEDURE sp1()
2214 BEGIN
2215 DECLARE done INT DEFAULT 0;
2216 DECLARE a CHAR(16);
2217 DECLARE b,c INT;
2218 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2219 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2220 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2221 OPEN cur1;
2222 OPEN cur2;
2223 REPEAT
2224 FETCH cur1 INTO a, b;
2225 FETCH cur2 INTO c;
2226 IF NOT done THEN
2227 IF b < c THEN
2228 INSERT INTO t3 VALUES (a,b);
2229 ELSE
2230 INSERT INTO t3 VALUES (a,c);
2231 END IF;
2232 END IF;
2233 UNTIL done END REPEAT;
2234 CLOSE cur1;
2235 CLOSE cur2;
2236 END//
2237 ALTER PROCEDURE sp1 #DET# SQL SECURITY DEFINER COMMENT 'comment' ;
2238 CALL sp1();
2239 SELECT * FROM t1 ORDER BY id;
2240 id data
2241 1 1
2242 2 1
2243 3 4
2244 4 5
2245 SELECT * FROM t2 ORDER BY i;
2246 i
2247 1
2248 2
2249 3
2250 SELECT * FROM t3 ORDER BY id;
2251 id data
2252 1 1
2253 2 1
2254 3 3
2255 DROP PROCEDURE sp1;
2256 DROP TABLE t1,t2,t3;
2257 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2258 CREATE TABLE t2 (i INTEGER NULL);
2259 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2260 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2261 INSERT INTO t2 VALUES(1),(2),(3);
2262 CREATE PROCEDURE sp1()
2263 BEGIN
2264 DECLARE done INT DEFAULT 0;
2265 DECLARE a CHAR(16);
2266 DECLARE b,c INT;
2267 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2268 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2269 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2270 OPEN cur1;
2271 OPEN cur2;
2272 REPEAT
2273 FETCH cur1 INTO a, b;
2274 FETCH cur2 INTO c;
2275 IF NOT done THEN
2276 IF b < c THEN
2277 INSERT INTO t3 VALUES (a,b);
2278 ELSE
2279 INSERT INTO t3 VALUES (a,c);
2280 END IF;
2281 END IF;
2282 UNTIL done END REPEAT;
2283 CLOSE cur1;
2284 CLOSE cur2;
2285 END//
2286 ALTER PROCEDURE sp1 #DET# CONTAINS SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2287 CALL sp1();
2288 SELECT * FROM t1 ORDER BY id;
2289 id data
2290 1 1
2291 2 1
2292 3 4
2293 4 5
2294 SELECT * FROM t2 ORDER BY i;
2295 i
2296 1
2297 2
2298 3
2299 SELECT * FROM t3 ORDER BY id;
2300 id data
2301 1 1
2302 2 1
2303 3 3
2304 DROP PROCEDURE sp1;
2305 DROP TABLE t1,t2,t3;
2306 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2307 CREATE TABLE t2 (i INTEGER NULL);
2308 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2309 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2310 INSERT INTO t2 VALUES(1),(2),(3);
2311 CREATE PROCEDURE sp1()
2312 BEGIN
2313 DECLARE done INT DEFAULT 0;
2314 DECLARE a CHAR(16);
2315 DECLARE b,c INT;
2316 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2317 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2318 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2319 OPEN cur1;
2320 OPEN cur2;
2321 REPEAT
2322 FETCH cur1 INTO a, b;
2323 FETCH cur2 INTO c;
2324 IF NOT done THEN
2325 IF b < c THEN
2326 INSERT INTO t3 VALUES (a,b);
2327 ELSE
2328 INSERT INTO t3 VALUES (a,c);
2329 END IF;
2330 END IF;
2331 UNTIL done END REPEAT;
2332 CLOSE cur1;
2333 CLOSE cur2;
2334 END//
2335 ALTER PROCEDURE sp1 #DET# NO SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2336 CALL sp1();
2337 SELECT * FROM t1 ORDER BY id;
2338 id data
2339 1 1
2340 2 1
2341 3 4
2342 4 5
2343 SELECT * FROM t2 ORDER BY i;
2344 i
2345 1
2346 2
2347 3
2348 SELECT * FROM t3 ORDER BY id;
2349 id data
2350 1 1
2351 2 1
2352 3 3
2353 DROP PROCEDURE sp1;
2354 DROP TABLE t1,t2,t3;
2355 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2356 CREATE TABLE t2 (i INTEGER NULL);
2357 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2358 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2359 INSERT INTO t2 VALUES(1),(2),(3);
2360 CREATE PROCEDURE sp1()
2361 BEGIN
2362 DECLARE done INT DEFAULT 0;
2363 DECLARE a CHAR(16);
2364 DECLARE b,c INT;
2365 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2366 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2367 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2368 OPEN cur1;
2369 OPEN cur2;
2370 REPEAT
2371 FETCH cur1 INTO a, b;
2372 FETCH cur2 INTO c;
2373 IF NOT done THEN
2374 IF b < c THEN
2375 INSERT INTO t3 VALUES (a,b);
2376 ELSE
2377 INSERT INTO t3 VALUES (a,c);
2378 END IF;
2379 END IF;
2380 UNTIL done END REPEAT;
2381 CLOSE cur1;
2382 CLOSE cur2;
2383 END//
2384 ALTER PROCEDURE sp1 #DET# READS SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
2385 CALL sp1();
2386 SELECT * FROM t1 ORDER BY id;
2387 id data
2388 1 1
2389 2 1
2390 3 4
2391 4 5
2392 SELECT * FROM t2 ORDER BY i;
2393 i
2394 1
2395 2
2396 3
2397 SELECT * FROM t3 ORDER BY id;
2398 id data
2399 1 1
2400 2 1
2401 3 3
2402 DROP PROCEDURE sp1;
2403 DROP TABLE t1,t2,t3;
2404 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2405 CREATE TABLE t2 (i INTEGER NULL);
2406 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2407 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2408 INSERT INTO t2 VALUES(1),(2),(3);
2409 CREATE PROCEDURE sp1()
2410 BEGIN
2411 DECLARE done INT DEFAULT 0;
2412 DECLARE a CHAR(16);
2413 DECLARE b,c INT;
2414 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2415 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2416 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2417 OPEN cur1;
2418 OPEN cur2;
2419 REPEAT
2420 FETCH cur1 INTO a, b;
2421 FETCH cur2 INTO c;
2422 IF NOT done THEN
2423 IF b < c THEN
2424 INSERT INTO t3 VALUES (a,b);
2425 ELSE
2426 INSERT INTO t3 VALUES (a,c);
2427 END IF;
2428 END IF;
2429 UNTIL done END REPEAT;
2430 CLOSE cur1;
2431 CLOSE cur2;
2432 END//
2433 ALTER PROCEDURE sp1 #DET# MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
2434 CALL sp1();
2435 SELECT * FROM t1 ORDER BY id;
2436 id data
2437 1 1
2438 2 1
2439 3 4
2440 4 5
2441 SELECT * FROM t2 ORDER BY i;
2442 i
2443 1
2444 2
2445 3
2446 SELECT * FROM t3 ORDER BY id;
2447 id data
2448 1 1
2449 2 1
2450 3 3
2451 DROP PROCEDURE sp1;
2452 DROP TABLE t1,t2,t3;
2453 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2454 CREATE TABLE t2 (i INTEGER NULL);
2455 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2456 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2457 INSERT INTO t2 VALUES(1),(2),(3);
2458 CREATE PROCEDURE sp1()
2459 BEGIN
2460 DECLARE done INT DEFAULT 0;
2461 DECLARE a CHAR(16);
2462 DECLARE b,c INT;
2463 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2464 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2465 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2466 OPEN cur1;
2467 OPEN cur2;
2468 REPEAT
2469 FETCH cur1 INTO a, b;
2470 FETCH cur2 INTO c;
2471 IF NOT done THEN
2472 IF b < c THEN
2473 INSERT INTO t3 VALUES (a,b);
2474 ELSE
2475 INSERT INTO t3 VALUES (a,c);
2476 END IF;
2477 END IF;
2478 UNTIL done END REPEAT;
2479 CLOSE cur1;
2480 CLOSE cur2;
2481 END//
2482 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# SQL SECURITY DEFINER ;
2483 CALL sp1();
2484 SELECT * FROM t1 ORDER BY id;
2485 id data
2486 1 1
2487 2 1
2488 3 4
2489 4 5
2490 SELECT * FROM t2 ORDER BY i;
2491 i
2492 1
2493 2
2494 3
2495 SELECT * FROM t3 ORDER BY id;
2496 id data
2497 1 1
2498 2 1
2499 3 3
2500 DROP PROCEDURE sp1;
2501 DROP TABLE t1,t2,t3;
2502 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2503 CREATE TABLE t2 (i INTEGER NULL);
2504 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2505 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2506 INSERT INTO t2 VALUES(1),(2),(3);
2507 CREATE PROCEDURE sp1()
2508 BEGIN
2509 DECLARE done INT DEFAULT 0;
2510 DECLARE a CHAR(16);
2511 DECLARE b,c INT;
2512 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2513 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2514 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2515 OPEN cur1;
2516 OPEN cur2;
2517 REPEAT
2518 FETCH cur1 INTO a, b;
2519 FETCH cur2 INTO c;
2520 IF NOT done THEN
2521 IF b < c THEN
2522 INSERT INTO t3 VALUES (a,b);
2523 ELSE
2524 INSERT INTO t3 VALUES (a,c);
2525 END IF;
2526 END IF;
2527 UNTIL done END REPEAT;
2528 CLOSE cur1;
2529 CLOSE cur2;
2530 END//
2531 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY DEFINER ;
2532 CALL sp1();
2533 SELECT * FROM t1 ORDER BY id;
2534 id data
2535 1 1
2536 2 1
2537 3 4
2538 4 5
2539 SELECT * FROM t2 ORDER BY i;
2540 i
2541 1
2542 2
2543 3
2544 SELECT * FROM t3 ORDER BY id;
2545 id data
2546 1 1
2547 2 1
2548 3 3
2549 DROP PROCEDURE sp1;
2550 DROP TABLE t1,t2,t3;
2551 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2552 CREATE TABLE t2 (i INTEGER NULL);
2553 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2554 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2555 INSERT INTO t2 VALUES(1),(2),(3);
2556 CREATE PROCEDURE sp1()
2557 BEGIN
2558 DECLARE done INT DEFAULT 0;
2559 DECLARE a CHAR(16);
2560 DECLARE b,c INT;
2561 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2562 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2563 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2564 OPEN cur1;
2565 OPEN cur2;
2566 REPEAT
2567 FETCH cur1 INTO a, b;
2568 FETCH cur2 INTO c;
2569 IF NOT done THEN
2570 IF b < c THEN
2571 INSERT INTO t3 VALUES (a,b);
2572 ELSE
2573 INSERT INTO t3 VALUES (a,c);
2574 END IF;
2575 END IF;
2576 UNTIL done END REPEAT;
2577 CLOSE cur1;
2578 CLOSE cur2;
2579 END//
2580 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL SQL SECURITY DEFINER ;
2581 CALL sp1();
2582 SELECT * FROM t1 ORDER BY id;
2583 id data
2584 1 1
2585 2 1
2586 3 4
2587 4 5
2588 SELECT * FROM t2 ORDER BY i;
2589 i
2590 1
2591 2
2592 3
2593 SELECT * FROM t3 ORDER BY id;
2594 id data
2595 1 1
2596 2 1
2597 3 3
2598 DROP PROCEDURE sp1;
2599 DROP TABLE t1,t2,t3;
2600 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2601 CREATE TABLE t2 (i INTEGER NULL);
2602 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2603 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2604 INSERT INTO t2 VALUES(1),(2),(3);
2605 CREATE PROCEDURE sp1()
2606 BEGIN
2607 DECLARE done INT DEFAULT 0;
2608 DECLARE a CHAR(16);
2609 DECLARE b,c INT;
2610 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2611 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2612 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2613 OPEN cur1;
2614 OPEN cur2;
2615 REPEAT
2616 FETCH cur1 INTO a, b;
2617 FETCH cur2 INTO c;
2618 IF NOT done THEN
2619 IF b < c THEN
2620 INSERT INTO t3 VALUES (a,b);
2621 ELSE
2622 INSERT INTO t3 VALUES (a,c);
2623 END IF;
2624 END IF;
2625 UNTIL done END REPEAT;
2626 CLOSE cur1;
2627 CLOSE cur2;
2628 END//
2629 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY DEFINER ;
2630 CALL sp1();
2631 SELECT * FROM t1 ORDER BY id;
2632 id data
2633 1 1
2634 2 1
2635 3 4
2636 4 5
2637 SELECT * FROM t2 ORDER BY i;
2638 i
2639 1
2640 2
2641 3
2642 SELECT * FROM t3 ORDER BY id;
2643 id data
2644 1 1
2645 2 1
2646 3 3
2647 DROP PROCEDURE sp1;
2648 DROP TABLE t1,t2,t3;
2649 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2650 CREATE TABLE t2 (i INTEGER NULL);
2651 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2652 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2653 INSERT INTO t2 VALUES(1),(2),(3);
2654 CREATE PROCEDURE sp1()
2655 BEGIN
2656 DECLARE done INT DEFAULT 0;
2657 DECLARE a CHAR(16);
2658 DECLARE b,c INT;
2659 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2660 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2661 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2662 OPEN cur1;
2663 OPEN cur2;
2664 REPEAT
2665 FETCH cur1 INTO a, b;
2666 FETCH cur2 INTO c;
2667 IF NOT done THEN
2668 IF b < c THEN
2669 INSERT INTO t3 VALUES (a,b);
2670 ELSE
2671 INSERT INTO t3 VALUES (a,c);
2672 END IF;
2673 END IF;
2674 UNTIL done END REPEAT;
2675 CLOSE cur1;
2676 CLOSE cur2;
2677 END//
2678 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY DEFINER ;
2679 CALL sp1();
2680 SELECT * FROM t1 ORDER BY id;
2681 id data
2682 1 1
2683 2 1
2684 3 4
2685 4 5
2686 SELECT * FROM t2 ORDER BY i;
2687 i
2688 1
2689 2
2690 3
2691 SELECT * FROM t3 ORDER BY id;
2692 id data
2693 1 1
2694 2 1
2695 3 3
2696 DROP PROCEDURE sp1;
2697 DROP TABLE t1,t2,t3;
2698 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2699 CREATE TABLE t2 (i INTEGER NULL);
2700 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2701 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2702 INSERT INTO t2 VALUES(1),(2),(3);
2703 CREATE PROCEDURE sp1()
2704 BEGIN
2705 DECLARE done INT DEFAULT 0;
2706 DECLARE a CHAR(16);
2707 DECLARE b,c INT;
2708 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2709 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2710 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2711 OPEN cur1;
2712 OPEN cur2;
2713 REPEAT
2714 FETCH cur1 INTO a, b;
2715 FETCH cur2 INTO c;
2716 IF NOT done THEN
2717 IF b < c THEN
2718 INSERT INTO t3 VALUES (a,b);
2719 ELSE
2720 INSERT INTO t3 VALUES (a,c);
2721 END IF;
2722 END IF;
2723 UNTIL done END REPEAT;
2724 CLOSE cur1;
2725 CLOSE cur2;
2726 END//
2727 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# SQL SECURITY DEFINER COMMENT 'comment' ;
2728 CALL sp1();
2729 SELECT * FROM t1 ORDER BY id;
2730 id data
2731 1 1
2732 2 1
2733 3 4
2734 4 5
2735 SELECT * FROM t2 ORDER BY i;
2736 i
2737 1
2738 2
2739 3
2740 SELECT * FROM t3 ORDER BY id;
2741 id data
2742 1 1
2743 2 1
2744 3 3
2745 DROP PROCEDURE sp1;
2746 DROP TABLE t1,t2,t3;
2747 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2748 CREATE TABLE t2 (i INTEGER NULL);
2749 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2750 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2751 INSERT INTO t2 VALUES(1),(2),(3);
2752 CREATE PROCEDURE sp1()
2753 BEGIN
2754 DECLARE done INT DEFAULT 0;
2755 DECLARE a CHAR(16);
2756 DECLARE b,c INT;
2757 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2758 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2759 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2760 OPEN cur1;
2761 OPEN cur2;
2762 REPEAT
2763 FETCH cur1 INTO a, b;
2764 FETCH cur2 INTO c;
2765 IF NOT done THEN
2766 IF b < c THEN
2767 INSERT INTO t3 VALUES (a,b);
2768 ELSE
2769 INSERT INTO t3 VALUES (a,c);
2770 END IF;
2771 END IF;
2772 UNTIL done END REPEAT;
2773 CLOSE cur1;
2774 CLOSE cur2;
2775 END//
2776 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2777 CALL sp1();
2778 SELECT * FROM t1 ORDER BY id;
2779 id data
2780 1 1
2781 2 1
2782 3 4
2783 4 5
2784 SELECT * FROM t2 ORDER BY i;
2785 i
2786 1
2787 2
2788 3
2789 SELECT * FROM t3 ORDER BY id;
2790 id data
2791 1 1
2792 2 1
2793 3 3
2794 DROP PROCEDURE sp1;
2795 DROP TABLE t1,t2,t3;
2796 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2797 CREATE TABLE t2 (i INTEGER NULL);
2798 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2799 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2800 INSERT INTO t2 VALUES(1),(2),(3);
2801 CREATE PROCEDURE sp1()
2802 BEGIN
2803 DECLARE done INT DEFAULT 0;
2804 DECLARE a CHAR(16);
2805 DECLARE b,c INT;
2806 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2807 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2808 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2809 OPEN cur1;
2810 OPEN cur2;
2811 REPEAT
2812 FETCH cur1 INTO a, b;
2813 FETCH cur2 INTO c;
2814 IF NOT done THEN
2815 IF b < c THEN
2816 INSERT INTO t3 VALUES (a,b);
2817 ELSE
2818 INSERT INTO t3 VALUES (a,c);
2819 END IF;
2820 END IF;
2821 UNTIL done END REPEAT;
2822 CLOSE cur1;
2823 CLOSE cur2;
2824 END//
2825 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# NO SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2826 CALL sp1();
2827 SELECT * FROM t1 ORDER BY id;
2828 id data
2829 1 1
2830 2 1
2831 3 4
2832 4 5
2833 SELECT * FROM t2 ORDER BY i;
2834 i
2835 1
2836 2
2837 3
2838 SELECT * FROM t3 ORDER BY id;
2839 id data
2840 1 1
2841 2 1
2842 3 3
2843 DROP PROCEDURE sp1;
2844 DROP TABLE t1,t2,t3;
2845 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2846 CREATE TABLE t2 (i INTEGER NULL);
2847 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2848 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2849 INSERT INTO t2 VALUES(1),(2),(3);
2850 CREATE PROCEDURE sp1()
2851 BEGIN
2852 DECLARE done INT DEFAULT 0;
2853 DECLARE a CHAR(16);
2854 DECLARE b,c INT;
2855 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2856 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2857 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2858 OPEN cur1;
2859 OPEN cur2;
2860 REPEAT
2861 FETCH cur1 INTO a, b;
2862 FETCH cur2 INTO c;
2863 IF NOT done THEN
2864 IF b < c THEN
2865 INSERT INTO t3 VALUES (a,b);
2866 ELSE
2867 INSERT INTO t3 VALUES (a,c);
2868 END IF;
2869 END IF;
2870 UNTIL done END REPEAT;
2871 CLOSE cur1;
2872 CLOSE cur2;
2873 END//
2874 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
2875 CALL sp1();
2876 SELECT * FROM t1 ORDER BY id;
2877 id data
2878 1 1
2879 2 1
2880 3 4
2881 4 5
2882 SELECT * FROM t2 ORDER BY i;
2883 i
2884 1
2885 2
2886 3
2887 SELECT * FROM t3 ORDER BY id;
2888 id data
2889 1 1
2890 2 1
2891 3 3
2892 DROP PROCEDURE sp1;
2893 DROP TABLE t1,t2,t3;
2894 CREATE TABLE t1 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2895 CREATE TABLE t2 (i INTEGER NULL);
2896 CREATE TABLE t3 (id INTEGER NOT NULL PRIMARY KEY, data INTEGER NULL);
2897 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2898 INSERT INTO t2 VALUES(1),(2),(3);
2899 CREATE PROCEDURE sp1()
2900 BEGIN
2901 DECLARE done INT DEFAULT 0;
2902 DECLARE a CHAR(16);
2903 DECLARE b,c INT;
2904 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2905 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2906 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2907 OPEN cur1;
2908 OPEN cur2;
2909 REPEAT
2910 FETCH cur1 INTO a, b;
2911 FETCH cur2 INTO c;
2912 IF NOT done THEN
2913 IF b < c THEN
2914 INSERT INTO t3 VALUES (a,b);
2915 ELSE
2916 INSERT INTO t3 VALUES (a,c);
2917 END IF;
2918 END IF;
2919 UNTIL done END REPEAT;
2920 CLOSE cur1;
2921 CLOSE cur2;
2922 END//
2923 ALTER PROCEDURE sp1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
2924 CALL sp1();
2925 SELECT * FROM t1 ORDER BY id;
2926 id data
2927 1 1
2928 2 1
2929 3 4
2930 4 5
2931 SELECT * FROM t2 ORDER BY i;
2932 i
2933 1
2934 2
2935 3
2936 SELECT * FROM t3 ORDER BY id;
2937 id data
2938 1 1
2939 2 1
2940 3 3
2941 DROP PROCEDURE sp1;
2942 DROP TABLE t1,t2,t3;