Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2 #
3 # Testing exceptions in the top-level blocks
4 #
5 # No HANDLER declarations, no exceptions
6 CREATE FUNCTION f1 RETURN INT
7 AS
8 BEGIN
9 RETURN 10;
10 END;
11 /
12 SHOW FUNCTION CODE f1;
13 Pos Instruction
14 0 freturn int 10
15 SELECT f1();
16 f1()
17 10
18 DROP FUNCTION f1;
19 # No HANDLER declarations, no code, no exceptions
20 CREATE PROCEDURE p1 ()
21 IS
22 BEGIN
23 END;
24 /
25 SHOW PROCEDURE CODE p1;
26 Pos Instruction
27 0 jump 2
28 CALL p1;
29 DROP PROCEDURE p1;
30 # No HANDLER declarations, no code, some exceptions
31 CREATE PROCEDURE p1 (v IN OUT INT)
32 IS
33 BEGIN
34 EXCEPTION
35 WHEN 1002 THEN v:=225;
36 END;
37 /
38 SHOW PROCEDURE CODE p1;
39 Pos Instruction
40 0 jump 1
41 1 hpush_jump 4 1 EXIT
42 2 set v@0 225
43 3 hreturn 0 4
44 4 hpop 1
45 set @v= 10;
46 CALL p1(@v);
47 SELECT @v;
48 @v
49 10
50 DROP PROCEDURE p1;
51 # No HANDLER declarations, some code, some exceptions
52 CREATE PROCEDURE p1 (v IN OUT INT)
53 IS
54 BEGIN
55 v:=224;
56 EXCEPTION
57 WHEN 1002 THEN v:=225;
58 END;
59 /
60 SHOW PROCEDURE CODE p1;
61 Pos Instruction
62 0 jump 3
63 1 set v@0 224
64 2 jump 6
65 3 hpush_jump 1 1 EXIT
66 4 set v@0 225
67 5 hreturn 0 6
68 6 hpop 1
69 set @v= 10;
70 CALL p1(@v);
71 SELECT @v;
72 @v
73 224
74 DROP PROCEDURE p1;
75 # Some HANDLER declarations, no code, no exceptions
76 CREATE PROCEDURE p1 (v IN OUT INT)
77 IS
78 EXIT HANDLER FOR 1000
79 BEGIN
80 v:=123;
81 END;
82 BEGIN
83 END;
84 /
85 SHOW PROCEDURE CODE p1;
86 Pos Instruction
87 0 hpush_jump 3 1 EXIT
88 1 set v@0 123
89 2 hreturn 0 3
90 3 hpop 1
91 set @v= 10;
92 CALL p1(@v);
93 SELECT @v;
94 @v
95 10
96 DROP PROCEDURE p1;
97 # Some HANDLER declarations, no code, some exceptions
98 CREATE PROCEDURE p1 (v IN OUT INT)
99 IS
100 EXIT HANDLER FOR 1000
101 BEGIN
102 v:=123;
103 END;
104 BEGIN
105 EXCEPTION
106 WHEN 1002 THEN v:=225;
107 END;
108 /
109 SHOW PROCEDURE CODE p1;
110 Pos Instruction
111 0 hpush_jump 3 1 EXIT
112 1 set v@0 123
113 2 hreturn 0 6
114 3 hpush_jump 6 1 EXIT
115 4 set v@0 225
116 5 hreturn 0 6
117 6 hpop 2
118 set @v= 10;
119 CALL p1(@v);
120 SELECT @v;
121 @v
122 10
123 DROP PROCEDURE p1;
124 # Some HANDLER declarations, some code, no exceptions
125 CREATE PROCEDURE p1 (v IN OUT INT)
126 IS
127 EXIT HANDLER FOR 1000
128 BEGIN
129 v:=123;
130 END;
131 BEGIN
132 v:=223;
133 END;
134 /
135 SHOW PROCEDURE CODE p1;
136 Pos Instruction
137 0 hpush_jump 3 1 EXIT
138 1 set v@0 123
139 2 hreturn 0 4
140 3 set v@0 223
141 4 hpop 1
142 set @v= 10;
143 CALL p1(@v);
144 SELECT @v;
145 @v
146 223
147 DROP PROCEDURE p1;
148 # Some HANDLER declarations, some code, some exceptions
149 CREATE PROCEDURE p1 (v IN OUT VARCHAR2(20))
150 IS
151 EXIT HANDLER FOR 1000
152 BEGIN
153 v:=123;
154 END;
155 CONTINUE HANDLER FOR 1001
156 BEGIN
157 SET v=223;
158 END;
159 BEGIN
160 v:= 1;
161 EXCEPTION
162 WHEN 1002 THEN SET v=225;
163 END;
164 /
165 SHOW PROCEDURE CODE p1;
166 Pos Instruction
167 0 hpush_jump 3 1 EXIT
168 1 set v@0 123
169 2 hreturn 0 12
170 3 hpush_jump 8 1 CONTINUE
171 4 set v@0 223
172 5 hreturn 1
173 6 set v@0 1
174 7 jump 12
175 8 hpush_jump 6 1 EXIT
176 9 set v@0 225
177 10 hreturn 0 12
178 11 jump 6
179 12 hpop 3
180 DROP PROCEDURE p1;
181 #
182 # Testing EXCEPTIONS in internal blocks
183 #
184 # No HANDLER declarations, no code, no exceptions
185 CREATE PROCEDURE p1 (v IN OUT INT)
186 IS
187 BEGIN
188 v:=123;
189 BEGIN
190 END;
191 END;
192 /
193 SHOW PROCEDURE CODE p1;
194 Pos Instruction
195 0 set v@0 123
196 1 jump 5
197 SET @v=10;
198 CALL p1(@v);
199 SELECT @v;
200 @v
201 123
202 DROP PROCEDURE p1;
203 # No HANDLER declarations, no code, some exceptions
204 CREATE PROCEDURE p1 (v IN OUT INT)
205 IS
206 BEGIN
207 v:=123;
208 BEGIN
209 EXCEPTION
210 WHEN 20002 THEN v:=335;
211 END;
212 END;
213 /
214 SHOW PROCEDURE CODE p1;
215 Pos Instruction
216 0 set v@0 123
217 1 jump 2
218 2 hpush_jump 5 1 EXIT
219 3 set v@0 335
220 4 hreturn 0 5
221 5 hpop 1
222 SET @v=10;
223 CALL p1(@v);
224 SELECT @v;
225 @v
226 123
227 DROP PROCEDURE p1;
228 # No HANDLER declarations, some code, no exceptions
229 CREATE PROCEDURE p1 (v IN OUT INT)
230 IS
231 BEGIN
232 v:=123;
233 BEGIN
234 v:=223;
235 END;
236 END;
237 /
238 SHOW PROCEDURE CODE p1;
239 Pos Instruction
240 0 set v@0 123
241 1 set v@0 223
242 2 jump 6
243 SET @v=10;
244 CALL p1(@v);
245 SELECT @v;
246 @v
247 223
248 DROP PROCEDURE p1;
249 # No HANDLER declarations, some code, some exceptions
250 CREATE PROCEDURE p1 (v IN OUT INT)
251 IS
252 BEGIN
253 v:=123;
254 BEGIN
255 v:=223;
256 EXCEPTION
257 WHEN 20002 THEN v:=335;
258 END;
259 END;
260 /
261 SHOW PROCEDURE CODE p1;
262 Pos Instruction
263 0 set v@0 123
264 1 jump 4
265 2 set v@0 223
266 3 jump 7
267 4 hpush_jump 2 1 EXIT
268 5 set v@0 335
269 6 hreturn 0 7
270 7 hpop 1
271 SET @v=10;
272 CALL p1(@v);
273 SELECT @v;
274 @v
275 223
276 DROP PROCEDURE p1;
277 # Some HANDLER declarations, no code, no exceptions
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 END;
289 END;
290 /
291 SHOW PROCEDURE CODE p1;
292 Pos Instruction
293 0 set v@0 123
294 1 hpush_jump 4 1 EXIT
295 2 set v@0 323
296 3 hreturn 0 4
297 4 hpop 1
298 SET @v=10;
299 CALL p1(@v);
300 SELECT @v;
301 @v
302 123
303 DROP PROCEDURE p1;
304 # Some HANDLER declarations, no code, some exceptions
305 CREATE PROCEDURE p1 (v IN OUT INT)
306 IS
307 BEGIN
308 v:=123;
309 DECLARE
310 EXIT HANDLER FOR 1000
311 BEGIN
312 v:=323;
313 END;
314 BEGIN
315 EXCEPTION
316 WHEN 20002 THEN v:=335;
317 END;
318 END;
319 /
320 SHOW PROCEDURE CODE p1;
321 Pos Instruction
322 0 set v@0 123
323 1 hpush_jump 4 1 EXIT
324 2 set v@0 323
325 3 hreturn 0 7
326 4 hpush_jump 7 1 EXIT
327 5 set v@0 335
328 6 hreturn 0 7
329 7 hpop 2
330 SET @v=10;
331 CALL p1(@v);
332 SELECT @v;
333 @v
334 123
335 DROP PROCEDURE p1;
336 # Some HANDLER declarations, some code, no exceptions
337 CREATE PROCEDURE p1 (v IN OUT INT)
338 IS
339 BEGIN
340 v:=123;
341 DECLARE
342 EXIT HANDLER FOR 1000
343 BEGIN
344 v:=323;
345 END;
346 BEGIN
347 v:= 324;
348 END;
349 END;
350 /
351 SHOW PROCEDURE CODE p1;
352 Pos Instruction
353 0 set v@0 123
354 1 hpush_jump 4 1 EXIT
355 2 set v@0 323
356 3 hreturn 0 5
357 4 set v@0 324
358 5 hpop 1
359 SET @v=10;
360 CALL p1(@v);
361 SELECT @v;
362 @v
363 324
364 DROP PROCEDURE p1;
365 # Some HANDLER declarations, some code, some exceptions
366 CREATE PROCEDURE p1 (v IN OUT INT)
367 IS
368 BEGIN
369 v:=123;
370 DECLARE
371 EXIT HANDLER FOR 1000
372 BEGIN
373 v:=323;
374 END;
375 BEGIN
376 v:= 324;
377 EXCEPTION WHEN 2002 THEN v:= 325;
378 END;
379 END;
380 /
381 SHOW PROCEDURE CODE p1;
382 Pos Instruction
383 0 set v@0 123
384 1 hpush_jump 6 1 EXIT
385 2 set v@0 323
386 3 hreturn 0 9
387 4 set v@0 324
388 5 jump 9
389 6 hpush_jump 4 1 EXIT
390 7 set v@0 325
391 8 hreturn 0 9
392 9 hpop 2
393 SET @v=10;
394 CALL p1(@v);
395 SELECT @v;
396 @v
397 324
398 DROP PROCEDURE p1;
399 #
400 # Testing EXIT statement
401 #
402 CREATE FUNCTION f1 RETURN INT
403 IS
404 i INT := 0;
405 BEGIN
406 LOOP
407 i:= i + 1;
408 IF i >= 5 THEN
409 EXIT;
410 END IF;
411 END LOOP;
412 RETURN i;
413 END;
414 /
415 SHOW FUNCTION CODE f1;
416 Pos Instruction
417 0 set i@0 0
418 1 set i@0 i@0 + 1
419 2 jump_if_not 1(1) i@0 >= 5
420 3 jump 4
421 4 freturn int i@0
422 SELECT f1() FROM DUAL;
423 f1()
424 5
425 DROP FUNCTION f1;
426 CREATE FUNCTION f1 RETURN INT
427 IS
428 i INT := 0;
429 BEGIN
430 LOOP
431 i:= i + 1;
432 EXIT WHEN i >=5;
433 END LOOP;
434 RETURN i;
435 END;
436 /
437 SHOW FUNCTION CODE f1;
438 Pos Instruction
439 0 set i@0 0
440 1 set i@0 i@0 + 1
441 2 jump_if_not 1(0) i@0 >= 5
442 3 jump 4
443 4 freturn int i@0
444 SELECT f1() FROM DUAL;
445 f1()
446 5
447 DROP FUNCTION f1;
448 CREATE FUNCTION f1 RETURN INT
449 IS
450 i INT := 0;
451 BEGIN
452 LOOP
453 BEGIN
454 i:= i + 1;
455 IF i >= 5 THEN
456 EXIT;
457 END IF;
458 EXCEPTION
459 WHEN OTHERS THEN i:= 1000;
460 END;
461 END LOOP;
462 RETURN i;
463 END;
464 /
465 SHOW FUNCTION CODE f1;
466 Pos Instruction
467 0 set i@0 0
468 1 jump 5
469 2 set i@0 i@0 + 1
470 3 jump_if_not 8(8) i@0 >= 5
471 4 jump 10
472 5 hpush_jump 2 1 EXIT
473 6 set i@0 1000
474 7 hreturn 0 8
475 8 hpop 1
476 9 jump 5
477 10 freturn int i@0
478 SELECT f1() FROM DUAL;
479 f1()
480 5
481 DROP FUNCTION f1;
482 CREATE PROCEDURE p1(a IN OUT INT)
483 IS
484 i INT := 0;
485 BEGIN
486 LOOP
487 LOOP
488 BEGIN
489 i:= i + 1;
490 IF i >=5 THEN
491 EXIT;
492 END IF;
493 EXCEPTION
494 WHEN OTHERS THEN a:=1000;
495 END;
496 END LOOP;
497 i:= i + 100;
498 EXIT;
499 END LOOP;
500 a:= i;
501 EXCEPTION
502 WHEN OTHERS THEN a:=11;
503 END;
504 /
505 SHOW PROCEDURE CODE p1;
506 Pos Instruction
507 0 set i@1 0
508 1 jump 14
509 2 set i@1 i@1 + 1
510 3 jump_if_not 8(8) i@1 >= 5
511 4 jump 10
512 5 hpush_jump 2 2 EXIT
513 6 set a@0 1000
514 7 hreturn 0 8
515 8 hpop 1
516 9 jump 5
517 10 set i@1 i@1 + 100
518 11 jump 12
519 12 set a@0 i@1
520 13 jump 17
521 14 hpush_jump 5 2 EXIT
522 15 set a@0 11
523 16 hreturn 0 17
524 17 hpop 1
525 set @v= 10;
526 CALL p1(@v);
527 SELECT @v;
528 @v
529 105
530 DROP PROCEDURE p1;
531 # Testing RETURN in procedures
532 CREATE PROCEDURE p1 (a IN OUT INT)
533 AS
534 BEGIN
535 IF a < 10 THEN
536 BEGIN
537 a:= a + 1;
538 RETURN;
539 END;
540 END IF;
541 a:= 200;
542 EXCEPTION
543 WHEN OTHERS THEN
544 BEGIN
545 a:= 100;
546 RETURN;
547 END;
548 END;
549 /
550 SHOW PROCEDURE CODE p1;
551 Pos Instruction
552 0 jump 6
553 1 jump_if_not 4(4) a@0 < 10
554 2 set a@0 a@0 + 1
555 3 preturn
556 4 set a@0 200
557 5 jump 9
558 6 hpush_jump 1 1 EXIT
559 7 set a@0 100
560 8 preturn
561 9 hpop 1
562 DROP PROCEDURE p1;
563 # Testing FOR loop statement
564 CREATE FUNCTION f1 (a INT, b INT) RETURN INT
565 AS
566 total INT := 0;
567 BEGIN
568 FOR i IN 1 .. a
569 LOOP
570 total:= total + i;
571 IF i = b THEN
572 EXIT;
573 END IF;
574 END LOOP;
575 RETURN total;
576 END
577 /
578 SHOW FUNCTION CODE f1;
579 Pos Instruction
580 0 set total@2 0
581 1 set i@3 1
582 2 set [target_bound]@4 a@0
583 3 jump_if_not 9(9) i@3 <= [target_bound]@4
584 4 set total@2 total@2 + i@3
585 5 jump_if_not 7(7) i@3 = b@1
586 6 jump 9
587 7 set i@3 i@3 + 1
588 8 jump 3
589 9 freturn int total@2
590 SELECT f1(3, 100) FROM DUAL;
591 f1(3, 100)
592 6
593 SELECT f1(3, 2) FROM DUAL;
594 f1(3, 2)
595 3
596 DROP FUNCTION f1;
597 CREATE FUNCTION f1 (a INT, b INT) RETURN INT
598 AS
599 total INT := 0;
600 BEGIN
601 FOR i IN REVERSE 1..a
602 LOOP
603 total:= total + i;
604 IF i = b THEN
605 EXIT;
606 END IF;
607 END LOOP;
608 RETURN total;
609 END
610 /
611 SHOW FUNCTION CODE f1;
612 Pos Instruction
613 0 set total@2 0
614 1 set i@3 a@0
615 2 set [target_bound]@4 1
616 3 jump_if_not 9(9) i@3 >= [target_bound]@4
617 4 set total@2 total@2 + i@3
618 5 jump_if_not 7(7) i@3 = b@1
619 6 jump 9
620 7 set i@3 i@3 + -1
621 8 jump 3
622 9 freturn int total@2
623 SELECT f1(3, 100) FROM DUAL;
624 f1(3, 100)
625 6
626 SELECT f1(3, 2) FROM DUAL;
627 f1(3, 2)
628 5
629 DROP FUNCTION f1;
630 # Testing labeled FOR LOOP statement
631 CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURN INT
632 AS
633 total INT := 0;
634 BEGIN
635 <<la>>
636 FOR ia IN 1 .. a
637 LOOP
638 total:= total + 1000;
639 <<lb>>
640 FOR ib IN 1 .. b
641 LOOP
642 total:= total + 1;
643 EXIT lb WHEN ib = limitb;
644 EXIT la WHEN ia = limita;
645 END LOOP lb;
646 END LOOP la;
647 RETURN total;
648 END;
649 /
650 SHOW FUNCTION CODE f1;
651 Pos Instruction
652 0 set total@4 0
653 1 set ia@5 1
654 2 set [target_bound]@6 a@0
655 3 jump_if_not 17(17) ia@5 <= [target_bound]@6
656 4 set total@4 total@4 + 1000
657 5 set ib@7 1
658 6 set [target_bound]@8 b@2
659 7 jump_if_not 15(15) ib@7 <= [target_bound]@8
660 8 set total@4 total@4 + 1
661 9 jump_if_not 11(0) ib@7 = limitb@3
662 10 jump 15
663 11 jump_if_not 13(0) ia@5 = limita@1
664 12 jump 17
665 13 set ib@7 ib@7 + 1
666 14 jump 7
667 15 set ia@5 ia@5 + 1
668 16 jump 3
669 17 freturn int total@4
670 SELECT f1(2, 1, 2, 2) FROM DUAL;
671 f1(2, 1, 2, 2)
672 1001
673 SELECT f1(2, 2, 2, 2) FROM DUAL;
674 f1(2, 2, 2, 2)
675 2003
676 SELECT f1(2, 3, 2, 3) FROM DUAL;
677 f1(2, 3, 2, 3)
678 2004
679 DROP FUNCTION f1;
680 # Testing labeled ITERATE in a labeled FOR LOOP
681 CREATE FUNCTION f1(a INT) RETURN INT
682 AS
683 total INT:= 0;
684 BEGIN
685 <<li>>
686 FOR i IN 1 .. a
687 LOOP
688 total:= total + 1000;
689 IF i = 5 THEN
690 ITERATE li;
691 END IF;
692 total:= total + 1;
693 END LOOP;
694 RETURN total;
695 END;
696 /
697 SHOW FUNCTION CODE f1;
698 Pos Instruction
699 0 set total@1 0
700 1 set i@2 1
701 2 set [target_bound]@3 a@0
702 3 jump_if_not 11(11) i@2 <= [target_bound]@3
703 4 set total@1 total@1 + 1000
704 5 jump_if_not 8(8) i@2 = 5
705 6 set i@2 i@2 + 1
706 7 jump 3
707 8 set total@1 total@1 + 1
708 9 set i@2 i@2 + 1
709 10 jump 3
710 11 freturn int total@1
711 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
712 f1(3) f1(4) f1(5) f1(6)
713 3003 4004 5004 6005
714 DROP FUNCTION f1;
715 CREATE FUNCTION f1(a INT) RETURN INT
716 AS
717 total INT:= 0;
718 BEGIN
719 <<li>>
720 FOR i IN 1 .. a
721 LOOP
722 FOR j IN 1 .. 2
723 LOOP
724 total:= total + 1000;
725 IF i = 5 THEN
726 ITERATE li;
727 END IF;
728 total:= total + 1;
729 END LOOP;
730 END LOOP;
731 RETURN total;
732 END;
733 /
734 SHOW FUNCTION CODE f1;
735 Pos Instruction
736 0 set total@1 0
737 1 set i@2 1
738 2 set [target_bound]@3 a@0
739 3 jump_if_not 16(16) i@2 <= [target_bound]@3
740 4 set j@4 1
741 5 set [target_bound]@5 2
742 6 jump_if_not 14(14) j@4 <= [target_bound]@5
743 7 set total@1 total@1 + 1000
744 8 jump_if_not 11(11) i@2 = 5
745 9 set i@2 i@2 + 1
746 10 jump 3
747 11 set total@1 total@1 + 1
748 12 set j@4 j@4 + 1
749 13 jump 6
750 14 set i@2 i@2 + 1
751 15 jump 3
752 16 freturn int total@1
753 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
754 f1(3) f1(4) f1(5) f1(6)
755 6006 8008 9008 11010
756 DROP FUNCTION f1;
757 CREATE FUNCTION f1(a INT) RETURN INT
758 AS
759 total INT:= 0;
760 BEGIN
761 <<lj>>
762 FOR j IN 1 .. 2
763 LOOP
764 <<li>>
765 FOR i IN 1 .. a
766 LOOP
767 total:= total + 1000;
768 IF i = 5 THEN
769 ITERATE li;
770 END IF;
771 total:= total + 1;
772 END LOOP;
773 END LOOP;
774 RETURN total;
775 END;
776 /
777 SHOW FUNCTION CODE f1;
778 Pos Instruction
779 0 set total@1 0
780 1 set j@2 1
781 2 set [target_bound]@3 2
782 3 jump_if_not 16(16) j@2 <= [target_bound]@3
783 4 set i@4 1
784 5 set [target_bound]@5 a@0
785 6 jump_if_not 14(14) i@4 <= [target_bound]@5
786 7 set total@1 total@1 + 1000
787 8 jump_if_not 11(11) i@4 = 5
788 9 set i@4 i@4 + 1
789 10 jump 6
790 11 set total@1 total@1 + 1
791 12 set i@4 i@4 + 1
792 13 jump 6
793 14 set j@2 j@2 + 1
794 15 jump 3
795 16 freturn int total@1
796 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
797 f1(3) f1(4) f1(5) f1(6)
798 6006 8008 10008 12010
799 DROP FUNCTION f1;
800 # Testing CONTINUE statement
801 CREATE FUNCTION f1(a INT) RETURN INT
802 AS
803 total INT:= 0;
804 BEGIN
805 FOR i IN 1 .. a
806 LOOP
807 CONTINUE WHEN i=5;
808 total:= total + 1;
809 END LOOP;
810 RETURN total;
811 END;
812 /
813 SHOW FUNCTION CODE f1;
814 Pos Instruction
815 0 set total@1 0
816 1 set i@2 1
817 2 set [target_bound]@3 a@0
818 3 jump_if_not 10(10) i@2 <= [target_bound]@3
819 4 jump_if_not 7(0) i@2 = 5
820 5 set i@2 i@2 + 1
821 6 jump 3
822 7 set total@1 total@1 + 1
823 8 set i@2 i@2 + 1
824 9 jump 3
825 10 freturn int total@1
826 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
827 f1(3) f1(4) f1(5) f1(6)
828 3 4 4 5
829 DROP FUNCTION f1;
830 #
831 # Start of MDEV-10597 Cursors with parameters
832 #
833 CREATE PROCEDURE p1(arg_value_a VARCHAR, arg_value_b VARCHAR,
834 arg_pattern_a VARCHAR, arg_pattern_b VARCHAR)
835 AS
836 v_a VARCHAR(10);
837 v_b VARCHAR(20);
838 CURSOR c (p_value_a VARCHAR,
839 p_value_b VARCHAR,
840 p_pattern_a VARCHAR,
841 p_pattern_b VARCHAR,
842 p_limit_a INT,
843 p_limit_b INT,
844 p_unused TEXT) IS
845 (SELECT p_value_a, p_value_b FROM DUAL
846 WHERE p_value_a LIKE p_pattern_a LIMIT p_limit_a)
847 UNION
848 (SELECT p_value_b, p_value_a FROM DUAL
849 WHERE p_value_b LIKE p_pattern_b LIMIT p_limit_b);
850 BEGIN
851 OPEN c(arg_value_a, (SELECT arg_value_b),
852 arg_pattern_a, arg_pattern_b, 100, 101, 'x');
853 LOOP
854 FETCH c INTO v_a, v_b;
855 EXIT WHEN c%NOTFOUND;
856 SELECT v_a, v_b;
857 END LOOP;
858 CLOSE c;
859 END;
860 $$
861 CALL p1('aaa','bbb','aaa','bbb');
862 v_a v_b
863 aaa bbb
864 v_a v_b
865 bbb aaa
866 SHOW PROCEDURE CODE p1;
867 Pos Instruction
868 0 set v_a@4 NULL
869 1 set v_b@5 NULL
870 2 cpush c@0
871 3 set p_value_a@6 arg_value_a@0
872 4 set p_value_b@7 (select arg_value_b@1)
873 5 set p_pattern_a@8 arg_pattern_a@2
874 6 set p_pattern_b@9 arg_pattern_b@3
875 7 set p_limit_a@10 100
876 8 set p_limit_b@11 101
877 9 set p_unused@12 'x'
878 10 copen c@0
879 11 cfetch c@0 v_a@4 v_b@5
880 12 jump_if_not 14(0) "c"%NOTFOUND
881 13 jump 16
882 14 stmt 0 "SELECT v_a, v_b"
883 15 jump 11
884 16 cclose c@0
885 17 cpop 1
886 DROP PROCEDURE p1;
887 #
888 # End of MDEV-10597 Cursors with parameters
889 #
890 #
891 # MDEV-10914 ROW data type for stored routine variables
892 #
893 CREATE FUNCTION f1() RETURN INT
894 AS
895 a ROW(a INT, b INT);
896 BEGIN
897 a.b:= 200;
898 RETURN a.b;
899 END;
900 $$
901 SHOW FUNCTION CODE f1;
902 Pos Instruction
903 0 set a@0 NULL
904 1 set a.b@0[1] 200
905 2 freturn int a.b@0[1]
906 SELECT f1();
907 f1()
908 200
909 DROP FUNCTION f1;
910 CREATE PROCEDURE p1
911 AS
912 rec ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
913 BEGIN
914 rec:= ROW(10,20.123456,30.123,'test');
915 SELECT rec.a, rec.b, rec.c, rec.d;
916 END;
917 $$
918 SHOW PROCEDURE CODE p1;
919 Pos Instruction
920 0 set rec@0 NULL
921 1 set rec@0 (10,20.123456,30.123,'test')
922 2 stmt 0 "SELECT rec.a, rec.b, rec.c, rec.d"
923 CALL p1;
924 rec.a rec.b rec.c rec.d
925 10 20.123456 30.123 test
926 DROP PROCEDURE p1;
927 CREATE PROCEDURE p1
928 AS
929 rec ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) :=
930 ROW(10,20.123456,30.123,'test');
931 BEGIN
932 SELECT rec.a, rec.b, rec.c, rec.d;
933 END;
934 $$
935 SHOW PROCEDURE CODE p1;
936 Pos Instruction
937 0 set rec@0 (10,20.123456,30.123,'test')
938 1 stmt 0 "SELECT rec.a, rec.b, rec.c, rec.d"
939 CALL p1;
940 rec.a rec.b rec.c rec.d
941 10 20.123456 30.123 test
942 DROP PROCEDURE p1;
943 CREATE PROCEDURE p1
944 AS
945 rec1 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
946 rec2 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
947 BEGIN
948 rec1:= ROW(10,20.123456,30.123,'test');
949 rec2:= rec1;
950 SELECT rec2.a, rec2.b, rec2.c, rec2.d;
951 END;
952 $$
953 SHOW PROCEDURE CODE p1;
954 Pos Instruction
955 0 set rec1@0 NULL
956 1 set rec2@1 NULL
957 2 set rec1@0 (10,20.123456,30.123,'test')
958 3 set rec2@1 rec1@0
959 4 stmt 0 "SELECT rec2.a, rec2.b, rec2.c, rec2.d"
960 CALL p1;
961 rec2.a rec2.b rec2.c rec2.d
962 10 20.123456 30.123 test
963 DROP PROCEDURE p1;
964 CREATE PROCEDURE p1
965 AS
966 rec1 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) :=
967 ROW(10,20.123456,30.123,'test');
968 rec2 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) := rec1;
969 BEGIN
970 SELECT rec2.a, rec2.b, rec2.c, rec2.d;
971 END;
972 $$
973 SHOW PROCEDURE CODE p1;
974 Pos Instruction
975 0 set rec1@0 (10,20.123456,30.123,'test')
976 1 set rec2@1 rec1@0
977 2 stmt 0 "SELECT rec2.a, rec2.b, rec2.c, rec2.d"
978 CALL p1;
979 rec2.a rec2.b rec2.c rec2.d
980 10 20.123456 30.123 test
981 DROP PROCEDURE p1;
982 #
983 # End of MDEV-10914 ROW data type for stored routine variables
984 #
985 #
986 # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
987 #
988 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
989 CREATE PROCEDURE p1()
990 AS
991 rec1 t1%ROWTYPE;
992 BEGIN
993 rec1.a:= 10;
994 rec1.b:= 'bbb';
995 rec1.c:= 10e2;
996 rec1.d:= 10.12;
997 rec1.c:= rec1.d;
998 END;
999 $$
1000 SHOW PROCEDURE CODE p1;
1001 Pos Instruction
1002 0 set rec1@0 NULL
1003 1 set rec1.a@0["a"] 10
1004 2 set rec1.b@0["b"] 'bbb'
1005 3 set rec1.c@0["c"] 10e2
1006 4 set rec1.d@0["d"] 10.12
1007 5 set rec1.c@0["c"] rec1.d@0["d"]
1008 DROP PROCEDURE p1;
1009 DROP TABLE t1;
1010 #
1011 # MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable declarations
1012 #
1013 CREATE TABLE t1 (a INT, b VARCHAR(10));
1014 CREATE PROCEDURE p1()
1015 AS
1016 CURSOR cur1 IS SELECT * FROM t1;
1017 CURSOR cur2 IS SELECT * FROM t1;
1018 BEGIN
1019 DECLARE
1020 rec1,rec2 cur1%ROWTYPE;
1021 rec3 cur2%ROWTYPE;
1022 BEGIN
1023 rec1.a:= 10;
1024 rec1.b:= 'bbb';
1025 END;
1026 END;
1027 $$
1028 SHOW PROCEDURE CODE p1;
1029 Pos Instruction
1030 0 cpush cur1@0
1031 1 cpush cur2@1
1032 2 cursor_copy_struct cur1 rec1@0
1033 3 cursor_copy_struct cur1 rec2@1
1034 4 set rec1@0 NULL
1035 5 set rec2@1 NULL
1036 6 cursor_copy_struct cur2 rec3@2
1037 7 set rec3@2 NULL
1038 8 set rec1.a@0["a"] 10
1039 9 set rec1.b@0["b"] 'bbb'
1040 10 jump 11
1041 11 cpop 2
1042 DROP PROCEDURE p1;
1043 DROP TABLE t1;
1044 #
1045 # MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
1046 #
1047 CREATE PROCEDURE p1
1048 AS
1049 CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
1050 CURSOR cur1 IS SELECT 10 AS a, 'b0' AS b;
1051 CURSOR cur2 IS SELECT 10 AS a, 'b0' AS b;
1052 BEGIN
1053 FOR rec1 IN cur1
1054 LOOP
1055 SELECT rec1.a, rec1.b;
1056 rec1.a:= 11;
1057 rec1.b:= 'b1';
1058 SELECT rec1.a, rec1.b;
1059 END LOOP;
1060 FOR rec0 IN cur0
1061 LOOP
1062 rec0.a:= 10;
1063 rec0.b:='b0';
1064 END LOOP;
1065 FOR rec2 IN cur2
1066 LOOP
1067 rec2.a:= 10;
1068 rec2.b:='b0';
1069 END LOOP;
1070 END;
1071 $$
1072 SHOW PROCEDURE CODE p1;
1073 Pos Instruction
1074 0 cpush cur0@0
1075 1 cpush cur1@1
1076 2 cpush cur2@2
1077 3 cursor_copy_struct cur1 rec1@0
1078 4 copen cur1@1
1079 5 cfetch cur1@1 rec1@0
1080 6 jump_if_not 13(13) "cur1"%FOUND
1081 7 stmt 0 "SELECT rec1.a, rec1.b"
1082 8 set rec1.a@0["a"] 11
1083 9 set rec1.b@0["b"] 'b1'
1084 10 stmt 0 "SELECT rec1.a, rec1.b"
1085 11 cfetch cur1@1 rec1@0
1086 12 jump 6
1087 13 cclose cur1@1
1088 14 cursor_copy_struct cur0 rec0@1
1089 15 copen cur0@0
1090 16 cfetch cur0@0 rec0@1
1091 17 jump_if_not 22(22) "cur0"%FOUND
1092 18 set rec0.a@1["a"] 10
1093 19 set rec0.b@1["b"] 'b0'
1094 20 cfetch cur0@0 rec0@1
1095 21 jump 17
1096 22 cclose cur0@0
1097 23 cursor_copy_struct cur2 rec2@2
1098 24 copen cur2@2
1099 25 cfetch cur2@2 rec2@2
1100 26 jump_if_not 31(31) "cur2"%FOUND
1101 27 set rec2.a@2["a"] 10
1102 28 set rec2.b@2["b"] 'b0'
1103 29 cfetch cur2@2 rec2@2
1104 30 jump 26
1105 31 cclose cur2@2
1106 32 cpop 3
1107 DROP PROCEDURE p1;
1108 CREATE PROCEDURE p1
1109 AS
1110 CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
1111 BEGIN
1112 FOR rec0 IN cur0
1113 LOOP
1114 DECLARE
1115 CURSOR cur1 IS SELECT 11 AS a, 'b1' AS b;
1116 BEGIN
1117 rec0.a:= 11;
1118 rec0.b:= 'b0';
1119 FOR rec1 IN cur1
1120 LOOP
1121 rec1.a:= 11;
1122 rec1.b:= 'b1';
1123 DECLARE
1124 CURSOR cur2 IS SELECT 12 AS a, 'b2' AS b;
1125 BEGIN
1126 FOR rec2 IN cur2
1127 LOOP
1128 rec2.a:=12;
1129 rec2.b:='b2';
1130 END LOOP;
1131 END;
1132 END LOOP;
1133 END;
1134 END LOOP;
1135 END;
1136 $$
1137 SHOW PROCEDURE CODE p1;
1138 Pos Instruction
1139 0 cpush cur0@0
1140 1 cursor_copy_struct cur0 rec0@0
1141 2 copen cur0@0
1142 3 cfetch cur0@0 rec0@0
1143 4 jump_if_not 31(31) "cur0"%FOUND
1144 5 cpush cur1@1
1145 6 set rec0.a@0["a"] 11
1146 7 set rec0.b@0["b"] 'b0'
1147 8 cursor_copy_struct cur1 rec1@1
1148 9 copen cur1@1
1149 10 cfetch cur1@1 rec1@1
1150 11 jump_if_not 27(27) "cur1"%FOUND
1151 12 set rec1.a@1["a"] 11
1152 13 set rec1.b@1["b"] 'b1'
1153 14 cpush cur2@2
1154 15 cursor_copy_struct cur2 rec2@2
1155 16 copen cur2@2
1156 17 cfetch cur2@2 rec2@2
1157 18 jump_if_not 23(23) "cur2"%FOUND
1158 19 set rec2.a@2["a"] 12
1159 20 set rec2.b@2["b"] 'b2'
1160 21 cfetch cur2@2 rec2@2
1161 22 jump 18
1162 23 cclose cur2@2
1163 24 cpop 1
1164 25 cfetch cur1@1 rec1@1
1165 26 jump 11
1166 27 cclose cur1@1
1167 28 cpop 1
1168 29 cfetch cur0@0 rec0@0
1169 30 jump 4
1170 31 cclose cur0@0
1171 32 cpop 1
1172 DROP PROCEDURE p1;
1173 #
1174 # MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
1175 #
1176 CREATE PROCEDURE p1
1177 AS
1178 BEGIN
1179 FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
1180 LOOP
1181 SELECT rec1.a, rec1.b;
1182 rec1.a:= 11;
1183 rec1.b:= 'b1';
1184 SELECT rec1.a, rec1.b;
1185 END LOOP;
1186 FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
1187 LOOP
1188 rec0.a:= 10;
1189 rec0.b:='b0';
1190 END LOOP;
1191 FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
1192 LOOP
1193 rec2.a:= 10;
1194 rec2.b:='b0';
1195 END LOOP;
1196 END;
1197 $$
1198 SHOW PROCEDURE CODE p1;
1199 Pos Instruction
1200 0 cpush [implicit_cursor]@0
1201 1 cursor_copy_struct [implicit_cursor] rec1@0
1202 2 copen [implicit_cursor]@0
1203 3 cfetch [implicit_cursor]@0 rec1@0
1204 4 jump_if_not 11(11) "[implicit_cursor]"%FOUND
1205 5 stmt 0 "SELECT rec1.a, rec1.b"
1206 6 set rec1.a@0["a"] 11
1207 7 set rec1.b@0["b"] 'b1'
1208 8 stmt 0 "SELECT rec1.a, rec1.b"
1209 9 cfetch [implicit_cursor]@0 rec1@0
1210 10 jump 4
1211 11 cpop 1
1212 12 cpush [implicit_cursor]@0
1213 13 cursor_copy_struct [implicit_cursor] rec0@1
1214 14 copen [implicit_cursor]@0
1215 15 cfetch [implicit_cursor]@0 rec0@1
1216 16 jump_if_not 21(21) "[implicit_cursor]"%FOUND
1217 17 set rec0.a@1["a"] 10
1218 18 set rec0.b@1["b"] 'b0'
1219 19 cfetch [implicit_cursor]@0 rec0@1
1220 20 jump 16
1221 21 cpop 1
1222 22 cpush [implicit_cursor]@0
1223 23 cursor_copy_struct [implicit_cursor] rec2@2
1224 24 copen [implicit_cursor]@0
1225 25 cfetch [implicit_cursor]@0 rec2@2
1226 26 jump_if_not 31(31) "[implicit_cursor]"%FOUND
1227 27 set rec2.a@2["a"] 10
1228 28 set rec2.b@2["b"] 'b0'
1229 29 cfetch [implicit_cursor]@0 rec2@2
1230 30 jump 26
1231 31 cpop 1
1232 DROP PROCEDURE p1;
1233 CREATE PROCEDURE p1
1234 AS
1235 BEGIN
1236 FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
1237 LOOP
1238 rec0.a:= 11;
1239 rec0.b:= 'b0';
1240 FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
1241 LOOP
1242 rec1.a:= 11;
1243 rec1.b:= 'b1';
1244 FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
1245 LOOP
1246 rec2.a:=12;
1247 rec2.b:='b2';
1248 END LOOP;
1249 END LOOP;
1250 END LOOP;
1251 END;
1252 $$
1253 SHOW PROCEDURE CODE p1;
1254 Pos Instruction
1255 0 cpush [implicit_cursor]@0
1256 1 cursor_copy_struct [implicit_cursor] rec0@0
1257 2 copen [implicit_cursor]@0
1258 3 cfetch [implicit_cursor]@0 rec0@0
1259 4 jump_if_not 29(29) "[implicit_cursor]"%FOUND
1260 5 set rec0.a@0["a"] 11
1261 6 set rec0.b@0["b"] 'b0'
1262 7 cpush [implicit_cursor]@1
1263 8 cursor_copy_struct [implicit_cursor] rec1@1
1264 9 copen [implicit_cursor]@1
1265 10 cfetch [implicit_cursor]@1 rec1@1
1266 11 jump_if_not 26(26) "[implicit_cursor]"%FOUND
1267 12 set rec1.a@1["a"] 11
1268 13 set rec1.b@1["b"] 'b1'
1269 14 cpush [implicit_cursor]@2
1270 15 cursor_copy_struct [implicit_cursor] rec2@2
1271 16 copen [implicit_cursor]@2
1272 17 cfetch [implicit_cursor]@2 rec2@2
1273 18 jump_if_not 23(23) "[implicit_cursor]"%FOUND
1274 19 set rec2.a@2["a"] 12
1275 20 set rec2.b@2["b"] 'b2'
1276 21 cfetch [implicit_cursor]@2 rec2@2
1277 22 jump 18
1278 23 cpop 1
1279 24 cfetch [implicit_cursor]@1 rec1@1
1280 25 jump 11
1281 26 cpop 1
1282 27 cfetch [implicit_cursor]@0 rec0@0
1283 28 jump 4
1284 29 cpop 1
1285 DROP PROCEDURE p1;
1286 #
1287 # MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
1288 #
1289 #
1290 # Cursor declaration and cursor%ROWTYPE declaration in the same block
1291 #
1292 CREATE TABLE t1 (a INT, b VARCHAR(10));
1293 INSERT INTO t1 VALUES (1,'a');
1294 CREATE PROCEDURE p1()
1295 AS
1296 CURSOR cur1 IS SELECT a FROM t1;
1297 rec1 cur1%ROWTYPE;
1298 BEGIN
1299 rec1.a:= 10;
1300 END;
1301 $$
1302 SHOW PROCEDURE CODE p1;
1303 Pos Instruction
1304 0 cursor_copy_struct cur1 rec1@0
1305 1 set rec1@0 NULL
1306 2 cpush cur1@0
1307 3 set rec1.a@0["a"] 10
1308 4 cpop 1
1309 CALL p1;
1310 DROP PROCEDURE p1;
1311 DROP TABLE t1;
1312 #
1313 # Recursive cursor and cursor%ROWTYPE declarations in the same block
1314 #
1315 CREATE PROCEDURE p1
1316 AS
1317 a INT:=10;
1318 CURSOR cur1 IS SELECT a;
1319 rec1 cur1%ROWTYPE;
1320 CURSOR cur2 IS SELECT rec1.a + 1 "a";
1321 rec2 cur2%ROWTYPE;
1322 BEGIN
1323 OPEN cur1;
1324 FETCH cur1 INTO rec1;
1325 CLOSE cur1;
1326 SELECT rec1.a;
1327 open cur2;
1328 FETCH cur2 INTO rec2;
1329 CLOSE cur2;
1330 SELECT rec2.a;
1331 END;
1332 $$
1333 SHOW PROCEDURE CODE p1;
1334 Pos Instruction
1335 0 set a@0 10
1336 1 cursor_copy_struct cur1 rec1@1
1337 2 set rec1@1 NULL
1338 3 cursor_copy_struct cur2 rec2@2
1339 4 set rec2@2 NULL
1340 5 cpush cur1@0
1341 6 cpush cur2@1
1342 7 copen cur1@0
1343 8 cfetch cur1@0 rec1@1
1344 9 cclose cur1@0
1345 10 stmt 0 "SELECT rec1.a"
1346 11 copen cur2@1
1347 12 cfetch cur2@1 rec2@2
1348 13 cclose cur2@1
1349 14 stmt 0 "SELECT rec2.a"
1350 15 cpop 2
1351 CALL p1();
1352 rec1.a
1353 10
1354 rec2.a
1355 11
1356 DROP PROCEDURE p1;
1357 #
1358 # MDEV-12441 Variables declared after cursors with parameters lose values
1359 #
1360 CREATE PROCEDURE p1() AS
1361 x0 INT:=100;
1362 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1363 x1 INT:=101;
1364 BEGIN
1365 OPEN cur(10,11);
1366 CLOSE cur;
1367 SELECT x0, x1;
1368 END;
1369 $$
1370 SHOW PROCEDURE CODE p1;
1371 Pos Instruction
1372 0 set x0@0 100
1373 1 set x1@3 101
1374 2 cpush cur@0
1375 3 set cp1@1 10
1376 4 set cp2@2 11
1377 5 copen cur@0
1378 6 cclose cur@0
1379 7 stmt 0 "SELECT x0, x1"
1380 8 cpop 1
1381 CALL p1();
1382 x0 x1
1383 100 101
1384 DROP PROCEDURE p1;
1385 CREATE PROCEDURE p1() AS
1386 x0 INT:=100;
1387 CURSOR cur0(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1388 x1 INT:=101;
1389 CURSOR cur1(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1390 x2 INT:=102;
1391 CURSOR cur2(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1392 x3 INT:=103;
1393 BEGIN
1394 OPEN cur0(0,1);
1395 CLOSE cur0;
1396 SELECT x0, x1, x2, x3;
1397 OPEN cur1(10,11);
1398 CLOSE cur1;
1399 SELECT x0, x1, x2, x3;
1400 OPEN cur2(20,21);
1401 CLOSE cur2;
1402 SELECT x0, x1, x2, x3;
1403 END;
1404 $$
1405 SHOW PROCEDURE CODE p1;
1406 Pos Instruction
1407 0 set x0@0 100
1408 1 set x1@3 101
1409 2 set x2@6 102
1410 3 set x3@9 103
1411 4 cpush cur0@0
1412 5 cpush cur1@1
1413 6 cpush cur2@2
1414 7 set cp1@1 0
1415 8 set cp2@2 1
1416 9 copen cur0@0
1417 10 cclose cur0@0
1418 11 stmt 0 "SELECT x0, x1, x2, x3"
1419 12 set cp1@4 10
1420 13 set cp2@5 11
1421 14 copen cur1@1
1422 15 cclose cur1@1
1423 16 stmt 0 "SELECT x0, x1, x2, x3"
1424 17 set cp1@7 20
1425 18 set cp2@8 21
1426 19 copen cur2@2
1427 20 cclose cur2@2
1428 21 stmt 0 "SELECT x0, x1, x2, x3"
1429 22 cpop 3
1430 CALL p1();
1431 x0 x1 x2 x3
1432 100 101 102 103
1433 x0 x1 x2 x3
1434 100 101 102 103
1435 x0 x1 x2 x3
1436 100 101 102 103
1437 DROP PROCEDURE p1;
1438 CREATE TABLE t1 (a INT);
1439 CREATE PROCEDURE p1() AS
1440 x0 INT:=100;
1441 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1442 x1 t1.a%TYPE:=101;
1443 BEGIN
1444 OPEN cur(10,11);
1445 CLOSE cur;
1446 SELECT x0, x1;
1447 END;
1448 $$
1449 SHOW PROCEDURE CODE p1;
1450 Pos Instruction
1451 0 set x0@0 100
1452 1 set x1@3 101
1453 2 cpush cur@0
1454 3 set cp1@1 10
1455 4 set cp2@2 11
1456 5 copen cur@0
1457 6 cclose cur@0
1458 7 stmt 0 "SELECT x0, x1"
1459 8 cpop 1
1460 CALL p1();
1461 x0 x1
1462 100 101
1463 DROP PROCEDURE p1;
1464 DROP TABLE t1;
1465 CREATE PROCEDURE p1() AS
1466 x0 INT:=100;
1467 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
1468 x1 ROW(a INT,b INT):=ROW(101,102);
1469 BEGIN
1470 OPEN cur(10,11);
1471 CLOSE cur;
1472 SELECT x0, x1.a, x1.b;
1473 END;
1474 $$
1475 SHOW PROCEDURE CODE p1;
1476 Pos Instruction
1477 0 set x0@0 100
1478 1 set x1@3 (101,102)
1479 2 cpush cur@0
1480 3 set cp1@1 10
1481 4 set cp2@2 11
1482 5 copen cur@0
1483 6 cclose cur@0
1484 7 stmt 0 "SELECT x0, x1.a, x1.b"
1485 8 cpop 1
1486 CALL p1();
1487 x0 x1.a x1.b
1488 100 101 102
1489 DROP PROCEDURE p1;
1490 #
1491 # MDEV-19640 Wrong SHOW PROCEDURE output for SET GLOBAL sysvar1=expr, sysvar2=expr
1492 #
1493 CREATE OR REPLACE PROCEDURE p1() AS
1494 BEGIN
1495 SET GLOBAL max_allowed_packet=16000000, max_error_count=60;
1496 SELECT @@GLOBAL.max_allowed_packet, @@GLOBAL.max_error_count;
1497 END;
1498 $$
1499 SHOW PROCEDURE CODE p1;
1500 Pos Instruction
1501 0 stmt 31 "SET GLOBAL max_allowed_packet=16000000"
1502 1 stmt 31 "SET GLOBAL max_error_count=60"
1503 2 stmt 0 "SELECT @@GLOBAL.max_allowed_packet, @..."
1504 DROP PROCEDURE p1;
1505 #
1506 # MDEV-19639 sql_mode=ORACLE: Wrong SHOW PROCEDURE output for sysvar:=expr
1507 #
1508 CREATE OR REPLACE PROCEDURE p1() AS
1509 BEGIN
1510 max_error_count:=10;
1511 END;
1512 $$
1513 SHOW PROCEDURE CODE p1;
1514 Pos Instruction
1515 0 stmt 31 "max_error_count:=10"
1516 DROP PROCEDURE p1;
1517 # End of 10.5 tests
1518 # Start of 11.8 tests
1519 #
1520 # MDEV-10862 Stored procedures: default values for parameters (optional parameters)
1521 #
1522 CREATE OR REPLACE PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1)
1523 AS
1524 var1 INT DEFAULT 5;
1525 BEGIN
1526 SELECT par1, par2 FROM DUAL;
1527 END;
1528 $$
1529 SHOW PROCEDURE CODE p1;
1530 Pos Instruction
1531 0 set default param par2@1 1
1532 1 set var1@2 5
1533 2 stmt 0 "SELECT par1, par2 FROM DUAL"
1534 DROP PROCEDURE p1;
1535 # End of 11.8 tests