Debian Codegraph @

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

raw
1 -- source include/have_debug.inc
2
3 SET sql_mode=ORACLE;
4
5 --echo #
6 --echo # Testing exceptions in the top-level blocks
7 --echo #
8
9 --echo # No HANDLER declarations, no exceptions
10 DELIMITER /;
11 CREATE FUNCTION f1 RETURN INT
12 AS
13 BEGIN
14 RETURN 10;
15 END;
16 /
17 DELIMITER ;/
18 SHOW FUNCTION CODE f1;
19 SELECT f1();
20 DROP FUNCTION f1;
21
22 --echo # No HANDLER declarations, no code, no exceptions
23 DELIMITER /;
24 CREATE PROCEDURE p1 ()
25 IS
26 BEGIN
27 END;
28 /
29 DELIMITER ;/
30 SHOW PROCEDURE CODE p1;
31 CALL p1;
32 DROP PROCEDURE p1;
33
34
35 --echo # No HANDLER declarations, no code, some exceptions
36 DELIMITER /;
37 CREATE PROCEDURE p1 (v IN OUT INT)
38 IS
39 BEGIN
40 EXCEPTION
41 WHEN 1002 THEN v:=225;
42 END;
43 /
44 DELIMITER ;/
45 SHOW PROCEDURE CODE p1;
46 set @v= 10;
47 CALL p1(@v);
48 SELECT @v;
49 DROP PROCEDURE p1;
50
51
52 --echo # No HANDLER declarations, some code, some exceptions
53 DELIMITER /;
54 CREATE PROCEDURE p1 (v IN OUT INT)
55 IS
56 BEGIN
57 v:=224;
58 EXCEPTION
59 WHEN 1002 THEN v:=225;
60 END;
61 /
62 DELIMITER ;/
63 SHOW PROCEDURE CODE p1;
64 set @v= 10;
65 CALL p1(@v);
66 SELECT @v;
67 DROP PROCEDURE p1;
68
69
70 --echo # Some HANDLER declarations, no code, no exceptions
71 DELIMITER /;
72 CREATE PROCEDURE p1 (v IN OUT INT)
73 IS
74 EXIT HANDLER FOR 1000
75 BEGIN
76 v:=123;
77 END;
78 BEGIN
79 END;
80 /
81 DELIMITER ;/
82 SHOW PROCEDURE CODE p1;
83 set @v= 10;
84 CALL p1(@v);
85 SELECT @v;
86 DROP PROCEDURE p1;
87
88
89 --echo # Some HANDLER declarations, no code, some exceptions
90 DELIMITER /;
91 CREATE PROCEDURE p1 (v IN OUT INT)
92 IS
93 EXIT HANDLER FOR 1000
94 BEGIN
95 v:=123;
96 END;
97 BEGIN
98 EXCEPTION
99 WHEN 1002 THEN v:=225;
100 END;
101 /
102 DELIMITER ;/
103 SHOW PROCEDURE CODE p1;
104 set @v= 10;
105 CALL p1(@v);
106 SELECT @v;
107 DROP PROCEDURE p1;
108
109
110 --echo # Some HANDLER declarations, some code, no exceptions
111 DELIMITER /;
112 CREATE PROCEDURE p1 (v IN OUT INT)
113 IS
114 EXIT HANDLER FOR 1000
115 BEGIN
116 v:=123;
117 END;
118 BEGIN
119 v:=223;
120 END;
121 /
122 DELIMITER ;/
123 SHOW PROCEDURE CODE p1;
124 set @v= 10;
125 CALL p1(@v);
126 SELECT @v;
127 DROP PROCEDURE p1;
128
129 --echo # Some HANDLER declarations, some code, some exceptions
130 DELIMITER /;
131 CREATE PROCEDURE p1 (v IN OUT VARCHAR2(20))
132 IS
133 EXIT HANDLER FOR 1000
134 BEGIN
135 v:=123;
136 END;
137 CONTINUE HANDLER FOR 1001
138 BEGIN
139 SET v=223;
140 END;
141 BEGIN
142 v:= 1;
143 EXCEPTION
144 WHEN 1002 THEN SET v=225;
145 END;
146 /
147 DELIMITER ;/
148 SHOW PROCEDURE CODE p1;
149 DROP PROCEDURE p1;
150
151
152 --echo #
153 --echo # Testing EXCEPTIONS in internal blocks
154 --echo #
155
156 --echo # No HANDLER declarations, no code, no exceptions
157 DELIMITER /;
158 CREATE PROCEDURE p1 (v IN OUT INT)
159 IS
160 BEGIN
161 v:=123;
162 BEGIN
163 END;
164 END;
165 /
166 DELIMITER ;/
167 SHOW PROCEDURE CODE p1;
168 SET @v=10;
169 CALL p1(@v);
170 SELECT @v;
171 DROP PROCEDURE p1;
172
173 --echo # No HANDLER declarations, no code, some exceptions
174 DELIMITER /;
175 CREATE PROCEDURE p1 (v IN OUT INT)
176 IS
177 BEGIN
178 v:=123;
179 BEGIN
180 EXCEPTION
181 WHEN 20002 THEN v:=335;
182 END;
183 END;
184 /
185 DELIMITER ;/
186 SHOW PROCEDURE CODE p1;
187 SET @v=10;
188 CALL p1(@v);
189 SELECT @v;
190 DROP PROCEDURE p1;
191
192 --echo # No HANDLER declarations, some code, no exceptions
193 DELIMITER /;
194 CREATE PROCEDURE p1 (v IN OUT INT)
195 IS
196 BEGIN
197 v:=123;
198 BEGIN
199 v:=223;
200 END;
201 END;
202 /
203 DELIMITER ;/
204 SHOW PROCEDURE CODE p1;
205 SET @v=10;
206 CALL p1(@v);
207 SELECT @v;
208 DROP PROCEDURE p1;
209
210 --echo # No HANDLER declarations, some code, some exceptions
211 DELIMITER /;
212 CREATE PROCEDURE p1 (v IN OUT INT)
213 IS
214 BEGIN
215 v:=123;
216 BEGIN
217 v:=223;
218 EXCEPTION
219 WHEN 20002 THEN v:=335;
220 END;
221 END;
222 /
223 DELIMITER ;/
224 SHOW PROCEDURE CODE p1;
225 SET @v=10;
226 CALL p1(@v);
227 SELECT @v;
228 DROP PROCEDURE p1;
229
230 --echo # Some HANDLER declarations, no code, no exceptions
231 DELIMITER /;
232 CREATE PROCEDURE p1 (v IN OUT INT)
233 IS
234 BEGIN
235 v:=123;
236 DECLARE
237 EXIT HANDLER FOR 1000
238 BEGIN
239 v:=323;
240 END;
241 BEGIN
242 END;
243 END;
244 /
245 DELIMITER ;/
246 SHOW PROCEDURE CODE p1;
247 SET @v=10;
248 CALL p1(@v);
249 SELECT @v;
250 DROP PROCEDURE p1;
251
252 --echo # Some HANDLER declarations, no code, some exceptions
253 DELIMITER /;
254 CREATE PROCEDURE p1 (v IN OUT INT)
255 IS
256 BEGIN
257 v:=123;
258 DECLARE
259 EXIT HANDLER FOR 1000
260 BEGIN
261 v:=323;
262 END;
263 BEGIN
264 EXCEPTION
265 WHEN 20002 THEN v:=335;
266 END;
267 END;
268 /
269 DELIMITER ;/
270 SHOW PROCEDURE CODE p1;
271 SET @v=10;
272 CALL p1(@v);
273 SELECT @v;
274 DROP PROCEDURE p1;
275
276 --echo # Some HANDLER declarations, some code, no exceptions
277 DELIMITER /;
278 CREATE PROCEDURE p1 (v IN OUT INT)
279 IS
280 BEGIN
281 v:=123;
282 DECLARE
283 EXIT HANDLER FOR 1000
284 BEGIN
285 v:=323;
286 END;
287 BEGIN
288 v:= 324;
289 END;
290 END;
291 /
292 DELIMITER ;/
293 SHOW PROCEDURE CODE p1;
294 SET @v=10;
295 CALL p1(@v);
296 SELECT @v;
297 DROP PROCEDURE p1;
298
299 --echo # Some HANDLER declarations, some code, some exceptions
300 DELIMITER /;
301 CREATE PROCEDURE p1 (v IN OUT INT)
302 IS
303 BEGIN
304 v:=123;
305 DECLARE
306 EXIT HANDLER FOR 1000
307 BEGIN
308 v:=323;
309 END;
310 BEGIN
311 v:= 324;
312 EXCEPTION WHEN 2002 THEN v:= 325;
313 END;
314 END;
315 /
316 DELIMITER ;/
317 SHOW PROCEDURE CODE p1;
318 SET @v=10;
319 CALL p1(@v);
320 SELECT @v;
321 DROP PROCEDURE p1;
322
323 --echo #
324 --echo # Testing EXIT statement
325 --echo #
326
327 DELIMITER /;
328 CREATE FUNCTION f1 RETURN INT
329 IS
330 i INT := 0;
331 BEGIN
332 LOOP
333 i:= i + 1;
334 IF i >= 5 THEN
335 EXIT;
336 END IF;
337 END LOOP;
338 RETURN i;
339 END;
340 /
341 DELIMITER ;/
342 SHOW FUNCTION CODE f1;
343 SELECT f1() FROM DUAL;
344 DROP FUNCTION f1;
345
346
347 DELIMITER /;
348 CREATE FUNCTION f1 RETURN INT
349 IS
350 i INT := 0;
351 BEGIN
352 LOOP
353 i:= i + 1;
354 EXIT WHEN i >=5;
355 END LOOP;
356 RETURN i;
357 END;
358 /
359 DELIMITER ;/
360 SHOW FUNCTION CODE f1;
361 SELECT f1() FROM DUAL;
362 DROP FUNCTION f1;
363
364
365 DELIMITER /;
366 CREATE FUNCTION f1 RETURN INT
367 IS
368 i INT := 0;
369 BEGIN
370 LOOP
371 BEGIN
372 i:= i + 1;
373 IF i >= 5 THEN
374 EXIT;
375 END IF;
376 EXCEPTION
377 WHEN OTHERS THEN i:= 1000;
378 END;
379 END LOOP;
380 RETURN i;
381 END;
382 /
383 DELIMITER ;/
384 SHOW FUNCTION CODE f1;
385 SELECT f1() FROM DUAL;
386 DROP FUNCTION f1;
387
388
389 DELIMITER /;
390 CREATE PROCEDURE p1(a IN OUT INT)
391 IS
392 i INT := 0;
393 BEGIN
394 LOOP
395 LOOP
396 BEGIN
397 i:= i + 1;
398 IF i >=5 THEN
399 EXIT;
400 END IF;
401 EXCEPTION
402 WHEN OTHERS THEN a:=1000;
403 END;
404 END LOOP;
405 i:= i + 100;
406 EXIT;
407 END LOOP;
408 a:= i;
409 EXCEPTION
410 WHEN OTHERS THEN a:=11;
411 END;
412 /
413 DELIMITER ;/
414 SHOW PROCEDURE CODE p1;
415 set @v= 10;
416 CALL p1(@v);
417 SELECT @v;
418 DROP PROCEDURE p1;
419
420
421 --echo # Testing RETURN in procedures
422 DELIMITER /;
423 CREATE PROCEDURE p1 (a IN OUT INT)
424 AS
425 BEGIN
426 IF a < 10 THEN
427 BEGIN
428 a:= a + 1;
429 RETURN;
430 END;
431 END IF;
432 a:= 200;
433 EXCEPTION
434 WHEN OTHERS THEN
435 BEGIN
436 a:= 100;
437 RETURN;
438 END;
439 END;
440 /
441 DELIMITER ;/
442 SHOW PROCEDURE CODE p1;
443 DROP PROCEDURE p1;
444
445
446 --echo # Testing FOR loop statement
447 DELIMITER /;
448 CREATE FUNCTION f1 (a INT, b INT) RETURN INT
449 AS
450 total INT := 0;
451 BEGIN
452 FOR i IN 1 .. a
453 LOOP
454 total:= total + i;
455 IF i = b THEN
456 EXIT;
457 END IF;
458 END LOOP;
459 RETURN total;
460 END
461 /
462 DELIMITER ;/
463 SHOW FUNCTION CODE f1;
464 SELECT f1(3, 100) FROM DUAL;
465 SELECT f1(3, 2) FROM DUAL;
466 DROP FUNCTION f1;
467
468 DELIMITER /;
469 CREATE FUNCTION f1 (a INT, b INT) RETURN INT
470 AS
471 total INT := 0;
472 BEGIN
473 FOR i IN REVERSE 1..a
474 LOOP
475 total:= total + i;
476 IF i = b THEN
477 EXIT;
478 END IF;
479 END LOOP;
480 RETURN total;
481 END
482 /
483 DELIMITER ;/
484 SHOW FUNCTION CODE f1;
485 SELECT f1(3, 100) FROM DUAL;
486 SELECT f1(3, 2) FROM DUAL;
487 DROP FUNCTION f1;
488
489
490 --echo # Testing labeled FOR LOOP statement
491
492 DELIMITER /;
493 CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURN INT
494 AS
495 total INT := 0;
496 BEGIN
497 <<la>>
498 FOR ia IN 1 .. a
499 LOOP
500 total:= total + 1000;
501 <<lb>>
502 FOR ib IN 1 .. b
503 LOOP
504 total:= total + 1;
505 EXIT lb WHEN ib = limitb;
506 EXIT la WHEN ia = limita;
507 END LOOP lb;
508 END LOOP la;
509 RETURN total;
510 END;
511 /
512 DELIMITER ;/
513 SHOW FUNCTION CODE f1;
514 SELECT f1(2, 1, 2, 2) FROM DUAL;
515 SELECT f1(2, 2, 2, 2) FROM DUAL;
516 SELECT f1(2, 3, 2, 3) FROM DUAL;
517 DROP FUNCTION f1;
518
519
520 --echo # Testing labeled ITERATE in a labeled FOR LOOP
521
522 DELIMITER /;
523 CREATE FUNCTION f1(a INT) RETURN INT
524 AS
525 total INT:= 0;
526 BEGIN
527 <<li>>
528 FOR i IN 1 .. a
529 LOOP
530 total:= total + 1000;
531 IF i = 5 THEN
532 ITERATE li;
533 END IF;
534 total:= total + 1;
535 END LOOP;
536 RETURN total;
537 END;
538 /
539 DELIMITER ;/
540 SHOW FUNCTION CODE f1;
541 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
542 DROP FUNCTION f1;
543
544
545 DELIMITER /;
546 CREATE FUNCTION f1(a INT) RETURN INT
547 AS
548 total INT:= 0;
549 BEGIN
550 <<li>>
551 FOR i IN 1 .. a
552 LOOP
553 FOR j IN 1 .. 2
554 LOOP
555 total:= total + 1000;
556 IF i = 5 THEN
557 ITERATE li;
558 END IF;
559 total:= total + 1;
560 END LOOP;
561 END LOOP;
562 RETURN total;
563 END;
564 /
565 DELIMITER ;/
566 SHOW FUNCTION CODE f1;
567 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
568 DROP FUNCTION f1;
569
570
571 DELIMITER /;
572 CREATE FUNCTION f1(a INT) RETURN INT
573 AS
574 total INT:= 0;
575 BEGIN
576 <<lj>>
577 FOR j IN 1 .. 2
578 LOOP
579 <<li>>
580 FOR i IN 1 .. a
581 LOOP
582 total:= total + 1000;
583 IF i = 5 THEN
584 ITERATE li;
585 END IF;
586 total:= total + 1;
587 END LOOP;
588 END LOOP;
589 RETURN total;
590 END;
591 /
592 DELIMITER ;/
593 SHOW FUNCTION CODE f1;
594 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
595 DROP FUNCTION f1;
596
597
598 --echo # Testing CONTINUE statement
599
600 DELIMITER /;
601 CREATE FUNCTION f1(a INT) RETURN INT
602 AS
603 total INT:= 0;
604 BEGIN
605 FOR i IN 1 .. a
606 LOOP
607 CONTINUE WHEN i=5;
608 total:= total + 1;
609 END LOOP;
610 RETURN total;
611 END;
612 /
613 DELIMITER ;/
614 SHOW FUNCTION CODE f1;
615 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
616 DROP FUNCTION f1;
617
618 --echo #
619 --echo # Start of MDEV-10597 Cursors with parameters
620 --echo #
621
622 DELIMITER $$;
623 CREATE PROCEDURE p1(arg_value_a VARCHAR, arg_value_b VARCHAR,
624 arg_pattern_a VARCHAR, arg_pattern_b VARCHAR)
625 AS
626 v_a VARCHAR(10);
627 v_b VARCHAR(20);
628 CURSOR c (p_value_a VARCHAR,
629 p_value_b VARCHAR,
630 p_pattern_a VARCHAR,
631 p_pattern_b VARCHAR,
632 p_limit_a INT,
633 p_limit_b INT,
634 p_unused TEXT) IS
635 (SELECT p_value_a, p_value_b FROM DUAL
636 WHERE p_value_a LIKE p_pattern_a LIMIT p_limit_a)
637 UNION
638 (SELECT p_value_b, p_value_a FROM DUAL
639 WHERE p_value_b LIKE p_pattern_b LIMIT p_limit_b);
640 BEGIN
641 OPEN c(arg_value_a, (SELECT arg_value_b),
642 arg_pattern_a, arg_pattern_b, 100, 101, 'x');
643 LOOP
644 FETCH c INTO v_a, v_b;
645 EXIT WHEN c%NOTFOUND;
646 SELECT v_a, v_b;
647 END LOOP;
648 CLOSE c;
649 END;
650 $$
651 DELIMITER ;$$
652 CALL p1('aaa','bbb','aaa','bbb');
653 SHOW PROCEDURE CODE p1;
654 DROP PROCEDURE p1;
655
656
657 --echo #
658 --echo # End of MDEV-10597 Cursors with parameters
659 --echo #
660
661
662 --echo #
663 --echo # MDEV-10914 ROW data type for stored routine variables
664 --echo #
665 DELIMITER $$;
666 CREATE FUNCTION f1() RETURN INT
667 AS
668 a ROW(a INT, b INT);
669 BEGIN
670 a.b:= 200;
671 RETURN a.b;
672 END;
673 $$
674 DELIMITER ;$$
675 SHOW FUNCTION CODE f1;
676 SELECT f1();
677 DROP FUNCTION f1;
678
679
680 DELIMITER $$;
681 CREATE PROCEDURE p1
682 AS
683 rec ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
684 BEGIN
685 rec:= ROW(10,20.123456,30.123,'test');
686 SELECT rec.a, rec.b, rec.c, rec.d;
687 END;
688 $$
689 DELIMITER ;$$
690 SHOW PROCEDURE CODE p1;
691 CALL p1;
692 DROP PROCEDURE p1;
693
694
695 DELIMITER $$;
696 CREATE PROCEDURE p1
697 AS
698 rec ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) :=
699 ROW(10,20.123456,30.123,'test');
700 BEGIN
701 SELECT rec.a, rec.b, rec.c, rec.d;
702 END;
703 $$
704 DELIMITER ;$$
705 SHOW PROCEDURE CODE p1;
706 CALL p1;
707 DROP PROCEDURE p1;
708
709
710 DELIMITER $$;
711 CREATE PROCEDURE p1
712 AS
713 rec1 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
714 rec2 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
715 BEGIN
716 rec1:= ROW(10,20.123456,30.123,'test');
717 rec2:= rec1;
718 SELECT rec2.a, rec2.b, rec2.c, rec2.d;
719 END;
720 $$
721 DELIMITER ;$$
722 SHOW PROCEDURE CODE p1;
723 CALL p1;
724 DROP PROCEDURE p1;
725
726
727 DELIMITER $$;
728 CREATE PROCEDURE p1
729 AS
730 rec1 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) :=
731 ROW(10,20.123456,30.123,'test');
732 rec2 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) := rec1;
733 BEGIN
734 SELECT rec2.a, rec2.b, rec2.c, rec2.d;
735 END;
736 $$
737 DELIMITER ;$$
738 SHOW PROCEDURE CODE p1;
739 CALL p1;
740 DROP PROCEDURE p1;
741
742 --echo #
743 --echo # End of MDEV-10914 ROW data type for stored routine variables
744 --echo #
745
746 --echo #
747 --echo # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
748 --echo #
749
750 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
751 DELIMITER $$;
752 CREATE PROCEDURE p1()
753 AS
754 rec1 t1%ROWTYPE;
755 BEGIN
756 rec1.a:= 10;
757 rec1.b:= 'bbb';
758 rec1.c:= 10e2;
759 rec1.d:= 10.12;
760 rec1.c:= rec1.d;
761 END;
762 $$
763 DELIMITER ;$$
764 SHOW PROCEDURE CODE p1;
765 DROP PROCEDURE p1;
766 DROP TABLE t1;
767
768
769 --echo #
770 --echo # MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable declarations
771 --echo #
772 CREATE TABLE t1 (a INT, b VARCHAR(10));
773 DELIMITER $$;
774 CREATE PROCEDURE p1()
775 AS
776 CURSOR cur1 IS SELECT * FROM t1;
777 CURSOR cur2 IS SELECT * FROM t1;
778 BEGIN
779 DECLARE
780 rec1,rec2 cur1%ROWTYPE;
781 rec3 cur2%ROWTYPE;
782 BEGIN
783 rec1.a:= 10;
784 rec1.b:= 'bbb';
785 END;
786 END;
787 $$
788 DELIMITER ;$$
789 SHOW PROCEDURE CODE p1;
790 DROP PROCEDURE p1;
791 DROP TABLE t1;
792
793
794 --echo #
795 --echo # MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
796 --echo #
797
798 DELIMITER $$;
799 CREATE PROCEDURE p1
800 AS
801 CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
802 CURSOR cur1 IS SELECT 10 AS a, 'b0' AS b;
803 CURSOR cur2 IS SELECT 10 AS a, 'b0' AS b;
804 BEGIN
805 FOR rec1 IN cur1
806 LOOP
807 SELECT rec1.a, rec1.b;
808 rec1.a:= 11;
809 rec1.b:= 'b1';
810 SELECT rec1.a, rec1.b;
811 END LOOP;
812 FOR rec0 IN cur0
813 LOOP
814 rec0.a:= 10;
815 rec0.b:='b0';
816 END LOOP;
817 FOR rec2 IN cur2
818 LOOP
819 rec2.a:= 10;
820 rec2.b:='b0';
821 END LOOP;
822 END;
823 $$
824 DELIMITER ;$$
825 SHOW PROCEDURE CODE p1;
826 DROP PROCEDURE p1;
827
828
829 DELIMITER $$;
830 CREATE PROCEDURE p1
831 AS
832 CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
833 BEGIN
834 FOR rec0 IN cur0
835 LOOP
836 DECLARE
837 CURSOR cur1 IS SELECT 11 AS a, 'b1' AS b;
838 BEGIN
839 rec0.a:= 11;
840 rec0.b:= 'b0';
841 FOR rec1 IN cur1
842 LOOP
843 rec1.a:= 11;
844 rec1.b:= 'b1';
845 DECLARE
846 CURSOR cur2 IS SELECT 12 AS a, 'b2' AS b;
847 BEGIN
848 FOR rec2 IN cur2
849 LOOP
850 rec2.a:=12;
851 rec2.b:='b2';
852 END LOOP;
853 END;
854 END LOOP;
855 END;
856 END LOOP;
857 END;
858 $$
859 DELIMITER ;$$
860 SHOW PROCEDURE CODE p1;
861 DROP PROCEDURE p1;
862
863
864 --echo #
865 --echo # MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
866 --echo #
867
868 DELIMITER $$;
869 CREATE PROCEDURE p1
870 AS
871 BEGIN
872 FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
873 LOOP
874 SELECT rec1.a, rec1.b;
875 rec1.a:= 11;
876 rec1.b:= 'b1';
877 SELECT rec1.a, rec1.b;
878 END LOOP;
879 FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
880 LOOP
881 rec0.a:= 10;
882 rec0.b:='b0';
883 END LOOP;
884 FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
885 LOOP
886 rec2.a:= 10;
887 rec2.b:='b0';
888 END LOOP;
889 END;
890 $$
891 DELIMITER ;$$
892 SHOW PROCEDURE CODE p1;
893 DROP PROCEDURE p1;
894
895
896 DELIMITER $$;
897 CREATE PROCEDURE p1
898 AS
899 BEGIN
900 FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
901 LOOP
902 rec0.a:= 11;
903 rec0.b:= 'b0';
904 FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
905 LOOP
906 rec1.a:= 11;
907 rec1.b:= 'b1';
908 FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
909 LOOP
910 rec2.a:=12;
911 rec2.b:='b2';
912 END LOOP;
913 END LOOP;
914 END LOOP;
915 END;
916 $$
917 DELIMITER ;$$
918 SHOW PROCEDURE CODE p1;
919 DROP PROCEDURE p1;
920
921
922 --echo #
923 --echo # MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
924 --echo #
925
926 --echo #
927 --echo # Cursor declaration and cursor%ROWTYPE declaration in the same block
928 --echo #
929
930 CREATE TABLE t1 (a INT, b VARCHAR(10));
931 INSERT INTO t1 VALUES (1,'a');
932 DELIMITER $$;
933 CREATE PROCEDURE p1()
934 AS
935 CURSOR cur1 IS SELECT a FROM t1;
936 rec1 cur1%ROWTYPE;
937 BEGIN
938 rec1.a:= 10;
939 END;
940 $$
941 DELIMITER ;$$
942 SHOW PROCEDURE CODE p1;
943 CALL p1;
944 DROP PROCEDURE p1;
945 DROP TABLE t1;
946
947 --echo #
948 --echo # Recursive cursor and cursor%ROWTYPE declarations in the same block
949 --echo #
950
951 DELIMITER $$;
952 CREATE PROCEDURE p1
953 AS
954 a INT:=10;
955 CURSOR cur1 IS SELECT a;
956 rec1 cur1%ROWTYPE;
957 CURSOR cur2 IS SELECT rec1.a + 1 "a";
958 rec2 cur2%ROWTYPE;
959 BEGIN
960 OPEN cur1;
961 FETCH cur1 INTO rec1;
962 CLOSE cur1;
963 SELECT rec1.a;
964 open cur2;
965 FETCH cur2 INTO rec2;
966 CLOSE cur2;
967 SELECT rec2.a;
968 END;
969 $$
970 DELIMITER ;$$
971 SHOW PROCEDURE CODE p1;
972 CALL p1();
973 DROP PROCEDURE p1;
974
975
976 --echo #
977 --echo # MDEV-12441 Variables declared after cursors with parameters lose values
978 --echo #
979
980 DELIMITER $$;
981 CREATE PROCEDURE p1() AS
982 x0 INT:=100;
983 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
984 x1 INT:=101;
985 BEGIN
986 OPEN cur(10,11);
987 CLOSE cur;
988 SELECT x0, x1;
989 END;
990 $$
991 DELIMITER ;$$
992 SHOW PROCEDURE CODE p1;
993 CALL p1();
994 DROP PROCEDURE p1;
995
996
997 DELIMITER $$;
998 CREATE PROCEDURE p1() AS
999 x0 INT:=100;
1000 CURSOR cur0(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1001 x1 INT:=101;
1002 CURSOR cur1(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1003 x2 INT:=102;
1004 CURSOR cur2(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1005 x3 INT:=103;
1006 BEGIN
1007 OPEN cur0(0,1);
1008 CLOSE cur0;
1009 SELECT x0, x1, x2, x3;
1010 OPEN cur1(10,11);
1011 CLOSE cur1;
1012 SELECT x0, x1, x2, x3;
1013 OPEN cur2(20,21);
1014 CLOSE cur2;
1015 SELECT x0, x1, x2, x3;
1016 END;
1017 $$
1018 DELIMITER ;$$
1019 SHOW PROCEDURE CODE p1;
1020 CALL p1();
1021 DROP PROCEDURE p1;
1022
1023
1024 CREATE TABLE t1 (a INT);
1025 DELIMITER $$;
1026 CREATE PROCEDURE p1() AS
1027 x0 INT:=100;
1028 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1029 x1 t1.a%TYPE:=101;
1030 BEGIN
1031 OPEN cur(10,11);
1032 CLOSE cur;
1033 SELECT x0, x1;
1034 END;
1035 $$
1036 DELIMITER ;$$
1037 SHOW PROCEDURE CODE p1;
1038 CALL p1();
1039 DROP PROCEDURE p1;
1040 DROP TABLE t1;
1041
1042
1043 DELIMITER $$;
1044 CREATE PROCEDURE p1() AS
1045 x0 INT:=100;
1046 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1047 x1 ROW(a INT,b INT):=ROW(101,102);
1048 BEGIN
1049 OPEN cur(10,11);
1050 CLOSE cur;
1051 SELECT x0, x1.a, x1.b;
1052 END;
1053 $$
1054 DELIMITER ;$$
1055 SHOW PROCEDURE CODE p1;
1056 CALL p1();
1057 DROP PROCEDURE p1;
1058
1059 --echo #
1060 --echo # MDEV-19640 Wrong SHOW PROCEDURE output for SET GLOBAL sysvar1=expr, sysvar2=expr
1061 --echo #
1062
1063 DELIMITER $$;
1064 CREATE OR REPLACE PROCEDURE p1() AS
1065 BEGIN
1066 SET GLOBAL max_allowed_packet=16000000, max_error_count=60;
1067 SELECT @@GLOBAL.max_allowed_packet, @@GLOBAL.max_error_count;
1068 END;
1069 $$
1070 DELIMITER ;$$
1071 SHOW PROCEDURE CODE p1;
1072 DROP PROCEDURE p1;
1073
1074 --echo #
1075 --echo # MDEV-19639 sql_mode=ORACLE: Wrong SHOW PROCEDURE output for sysvar:=expr
1076 --echo #
1077
1078 DELIMITER $$;
1079 CREATE OR REPLACE PROCEDURE p1() AS
1080 BEGIN
1081 max_error_count:=10;
1082 END;
1083 $$
1084 DELIMITER ;$$
1085 SHOW PROCEDURE CODE p1;
1086 DROP PROCEDURE p1;
1087
1088 --echo # End of 10.5 tests
1089
1090 --echo # Start of 11.8 tests
1091
1092 --echo #
1093 --echo # MDEV-10862 Stored procedures: default values for parameters (optional parameters)
1094 --echo #
1095
1096 DELIMITER $$;
1097 CREATE OR REPLACE PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1)
1098 AS
1099 var1 INT DEFAULT 5;
1100 BEGIN
1101 SELECT par1, par2 FROM DUAL;
1102 END;
1103 $$
1104 DELIMITER ;$$
1105 SHOW PROCEDURE CODE p1;
1106 DROP PROCEDURE p1;
1107
1108 --echo # End of 11.8 tests