Debian Codegraph @

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

raw
1 --source include/default_charset.inc
2
3 SET sql_mode=ORACLE;
4
5 --enable_prepare_warnings
6 --disable_ps2_protocol
7
8 --echo #
9 --echo # Creating a body of a non-existing package
10 --echo #
11 DELIMITER $$;
12 --error ER_SP_DOES_NOT_EXIST
13 CREATE PACKAGE BODY test2 AS
14 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
15 END;
16 $$
17 DELIMITER ;$$
18
19 --echo #
20 --echo # Dropping a non-existing package
21 --echo #
22 --error ER_SP_DOES_NOT_EXIST
23 DROP PACKAGE test2;
24 DROP PACKAGE IF EXISTS test2;
25 --error ER_SP_DOES_NOT_EXIST
26 DROP PACKAGE BODY test2;
27
28
29 --echo #
30 --echo # Bad combinations of OR REPLACE and IF EXISTS
31 --echo #
32
33 DELIMITER $$;
34 --error ER_WRONG_USAGE
35 CREATE OR REPLACE PACKAGE IF NOT EXISTS pkg AS
36 PROCEDURE p1;
37 END;
38 $$
39 DELIMITER ;$$
40
41 DELIMITER $$;
42 --error ER_WRONG_USAGE
43 CREATE OR REPLACE PACKAGE BODY IF NOT EXISTS pkg AS
44 PROCEDURE p1 AS BEGIN NULL; END;
45 END;
46 $$
47 DELIMITER ;$$
48
49
50 --echo #
51 --echo # PACKAGE and PS
52 --echo #
53
54 PREPARE stmt FROM 'CREATE PACKAGE test2 AS FUNCTION f1 RETURN INT; END test2';
55
56 DELIMITER $$;
57 CREATE PACKAGE test2 AS
58 FUNCTION f1 RETURN INT;
59 END;
60 $$
61 DELIMITER ;$$
62 PREPARE stmt FROM 'CREATE PACKAGE BODY test2 AS'
63 ' FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;'
64 'END test2';
65 DROP PACKAGE test2;
66
67
68 --echo #
69 --echo # Package and READ ONLY transactions
70 --echo #
71
72 SET SESSION TRANSACTION READ ONLY;
73
74 DELIMITER $$;
75 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
76 CREATE PACKAGE test2 AS
77 FUNCTION f1 RETURN INT;
78 PROCEDURE p1;
79 END
80 $$
81 DELIMITER ;$$
82
83 SET SESSION TRANSACTION READ WRITE;
84
85 DELIMITER $$;
86 CREATE PACKAGE test2 AS
87 FUNCTION f1 RETURN INT;
88 FUNCTION f2 RETURN INT;
89 END;
90 $$
91 SET SESSION TRANSACTION READ ONLY
92 $$
93 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
94 CREATE PACKAGE BODY test2 AS
95 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
96 FUNCTION f2 RETURN INT AS BEGIN RETURN f1(); END;
97 PROCEDURE p1 AS
98 BEGIN
99 SELECT f2();
100 END;
101 END;
102 $$
103 DELIMITER ;$$
104 SET SESSION TRANSACTION READ WRITE;
105 DROP PACKAGE test2;
106
107 SET SESSION TRANSACTION READ ONLY;
108 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
109 DROP PACKAGE test2;
110 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
111 DROP PACKAGE BODY test2;
112
113 SET SESSION TRANSACTION READ WRITE;
114
115
116 --echo #
117 --echo # Syntax error inside a CREATE PACKAGE, inside a routine definition
118 --echo #
119
120 DELIMITER $$;
121 --error ER_PARSE_ERROR
122 CREATE PACKAGE test2 AS
123 FUNCTION f1 RETURN INT;
124 FUNCTION f2 RETURN INT;
125 FUNCTION f3;
126 FUNCTION f4 RETURN INT;
127 END
128 $$
129 DELIMITER ;$$
130
131
132 --echo #
133 --echo # Syntax error inside a CREATE PACKAGE, outside of a routine definition
134 --echo #
135
136 # The definition "FUNCTION f3 RETURN INT AS BEGIN RETURN 10; END;"
137 # is valid in CREATE PACKAGE BODY, but not in CREATE PACKAGE.
138 # Syntax error happens after parsing "FUNCTION f3 RETURN INT".
139
140 DELIMITER $$;
141 --error ER_PARSE_ERROR
142 CREATE PACKAGE test2 AS
143 FUNCTION f1 RETURN INT;
144 FUNCTION f2 RETURN INT;
145 FUNCTION f3 RETURN INT AS BEGIN RETURN 10; END;
146 FUNCTION f4 RETURN INT;
147 END
148 $$
149 DELIMITER ;$$
150
151
152 --echo #
153 --echo # Syntax error inside a CREATE PACKAGE BODY, inside a routine definition
154 --echo #
155
156 DELIMITER $$;
157 CREATE PACKAGE test2 AS
158 FUNCTION f1 RETURN INT;
159 FUNCTION f2 RETURN INT;
160 END;
161 $$
162 --error ER_PARSE_ERROR
163 CREATE PACKAGE BODY test2 AS
164 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
165 FUNCTION f2 RETURN INT SA BEGIN RETURN 10; END; -- Notice "SA" vs "AS"
166 END
167 $$
168 DELIMITER ;$$
169 DROP PACKAGE test2;
170
171 --echo #
172 --echo # Syntax error inside a CREATE PACKAGE BODY, outside a routine definition
173 --echo #
174
175 DELIMITER $$;
176 CREATE PACKAGE test2 AS
177 FUNCTION f1 RETURN INT;
178 FUNCTION f2 RETURN INT;
179 END;
180 $$
181 --error ER_PARSE_ERROR
182 CREATE PACKAGE BODY test2 AS
183 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
184 SOME SYNTAX ERROR;
185 FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
186 END
187 $$
188 DELIMITER ;$$
189 DROP PACKAGE test2;
190
191
192 --echo #
193 --echo # Syntax error inside a CREATE PACKAGE BODY executable section
194 --echo #
195
196 DELIMITER $$;
197 CREATE PACKAGE test2 AS
198 FUNCTION f1 RETURN INT;
199 END;
200 $$
201 --error ER_PARSE_ERROR
202 CREATE PACKAGE BODY test2 AS
203 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
204 BEGIN
205 SOME SYNTAX ERROR;
206 END
207 $$
208 DELIMITER ;$$
209 DROP PACKAGE test2;
210
211
212 --echo #
213 --echo # CREATE PROCEDURE inside a package PROCEDURE is not allowed
214 --echo #
215
216 DELIMITER $$;
217 CREATE PACKAGE test2 AS
218 PROCEDURE p1;
219 END;
220 $$
221 --error ER_SP_NO_RECURSIVE_CREATE
222 CREATE PACKAGE BODY test2 AS
223 PROCEDURE p1 AS
224 BEGIN
225 CREATE PROCEDURE p1 AS BEGIN NULL; END;
226 END;
227 END;
228 $$
229 DELIMITER ;$$
230 DROP PACKAGE test2;
231
232
233 --echo #
234 --echo # CREATE PACKAGE inside a package PROCEDURE is not allowed
235 --echo #
236
237 DELIMITER $$;
238 CREATE PACKAGE test2 AS
239 PROCEDURE p1;
240 END;
241 $$
242 --error ER_SP_NO_RECURSIVE_CREATE
243 CREATE PACKAGE BODY test2 AS
244 PROCEDURE p1 AS
245 BEGIN
246 CREATE PACKAGE p1 AS PROCEDURE p1; END;
247 END;
248 END;
249 $$
250 DELIMITER ;$$
251 DROP PACKAGE test2;
252
253
254 --echo #
255 --echo # CREATE PROCEDURE inside a package executable section is not allowed
256 --echo #
257
258 DELIMITER $$;
259 CREATE PACKAGE test2 AS
260 PROCEDURE p1;
261 END;
262 $$
263 --error ER_SP_NO_RECURSIVE_CREATE
264 CREATE PACKAGE BODY test2 AS
265 PROCEDURE p1 AS BEGIN NULL; END;
266 BEGIN
267 CREATE PROCEDURE p1 AS BEGIN NULL; END;
268 END;
269 $$
270 DELIMITER ;$$
271 DROP PACKAGE test2;
272
273
274 --echo #
275 --echo # CREATE FUNCTION inside a package executable section is not allowed
276 --echo #
277
278 DELIMITER $$;
279 CREATE PACKAGE test2 AS
280 PROCEDURE p1;
281 END;
282 $$
283 --error ER_SP_NO_RECURSIVE_CREATE
284 CREATE PACKAGE BODY test2 AS
285 PROCEDURE p1 AS BEGIN NULL; END;
286 BEGIN
287 CREATE FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
288 END;
289 $$
290 DELIMITER ;$$
291 DROP PACKAGE test2;
292
293
294 --echo #
295 --echo # CREATE PACKAGE inside a package executable section is not allowed
296 --echo #
297
298 DELIMITER $$;
299 CREATE PACKAGE test2 AS
300 PROCEDURE p1;
301 END;
302 $$
303 --error ER_SP_NO_RECURSIVE_CREATE
304 CREATE PACKAGE BODY test2 AS
305 PROCEDURE p1 AS BEGIN NULL; END;
306 BEGIN
307 CREATE PACKAGE p1 AS PROCEDURE p1; END;
308 END;
309 $$
310 DELIMITER ;$$
311 DROP PACKAGE test2;
312
313
314 --echo #
315 --echo # Broken CREATE PACKAGE at CREATE PACKAGE BODY time
316 --echo #
317
318 DELIMITER $$;
319 CREATE PACKAGE test2 AS
320 FUNCTION f1 RETURN INT;
321 END;
322 $$
323 DELIMITER ;$$
324
325 UPDATE mysql.proc SET `body`='garbage'
326 WHERE db='test' AND name='test2' AND type='PACKAGE';
327
328 DELIMITER $$;
329 --error ER_SP_PROC_TABLE_CORRUPT
330 CREATE PACKAGE BODY test2 AS
331 FUNCTION f1 RETURN INT
332 AS BEGIN
333 RETURN f2();
334 END;
335 END;
336 $$
337 DELIMITER ;$$
338 show warnings;
339
340 DROP PACKAGE test2;
341
342
343 --echo #
344 --echo # Broken CREATE PACKAGE at a package function call time
345 --echo #
346
347 DELIMITER $$;
348 CREATE PACKAGE test2 AS
349 FUNCTION f1 RETURN INT;
350 END;
351 $$
352 DELIMITER ;$$
353
354 DELIMITER $$;
355 CREATE PACKAGE BODY test2 AS
356 FUNCTION f1 RETURN INT
357 AS BEGIN
358 RETURN f2();
359 END;
360 END;
361 $$
362 DELIMITER ;$$
363
364 --error ER_SP_DOES_NOT_EXIST
365 SELECT test2.f1();
366 UPDATE mysql.proc SET `body`='garbage'
367 WHERE db='test' AND name='test2' AND type='PACKAGE';
368 --source sp-cache-invalidate.inc
369 --error ER_SP_DOES_NOT_EXIST
370 SELECT test2.f1();
371 show warnings;
372 --error ER_SP_DOES_NOT_EXIST
373 SELECT test2.f1();
374 show warnings;
375 --error ER_SP_DOES_NOT_EXIST
376 SELECT test2.f1();
377 show warnings;
378
379 DROP PACKAGE test2;
380
381
382 --echo #
383 --echo # Broken CREATE PACKAGE at a package procedure call time
384 --echo #
385
386 DELIMITER $$;
387 CREATE PACKAGE test2 AS
388 PROCEDURE p1;
389 END;
390 $$
391 DELIMITER ;$$
392
393 DELIMITER $$;
394 CREATE PACKAGE BODY test2 AS
395 PROCEDURE p1
396 AS BEGIN
397 CALL p2;
398 END;
399 END;
400 $$
401 DELIMITER ;$$
402
403 --error ER_SP_DOES_NOT_EXIST
404 CALL test2.f1();
405 UPDATE mysql.proc SET `body`='garbage'
406 WHERE db='test' AND name='test2' AND type='PACKAGE';
407 --source sp-cache-invalidate.inc
408 --error ER_SP_DOES_NOT_EXIST
409 CALL test2.p1();
410 show warnings;
411 --error ER_SP_DOES_NOT_EXIST
412 CALL test2.p1();
413 show warnings;
414 --error ER_SP_DOES_NOT_EXIST
415 CALL test2.p1();
416 show warnings;
417
418 DROP PACKAGE test2;
419
420
421 --echo #
422 --echo # Bad routine names
423 --echo #
424
425 DELIMITER $$;
426 --error ER_TOO_LONG_IDENT
427 CREATE PACKAGE p1 AS
428 PROCEDURE pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp1;
429 END;
430 $$
431 DELIMITER ;$$
432
433 DELIMITER $$;
434 --error ER_TOO_LONG_IDENT
435 CREATE PACKAGE p1 AS
436 FUNCTION fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff1
437 RETURN INT;
438 END;
439 $$
440 DELIMITER ;$$
441
442
443 DELIMITER $$;
444 --error ER_SP_WRONG_NAME
445 CREATE PACKAGE p1 AS
446 PROCEDURE "p1 ";
447 END;
448 $$
449 DELIMITER ;$$
450
451 DELIMITER $$;
452 --error ER_SP_WRONG_NAME
453 CREATE PACKAGE p1 AS
454 FUNCTION "f1 " RETURN INT;
455 END;
456 $$
457 DELIMITER ;$$
458
459 DELIMITER $$;
460 --error ER_SP_WRONG_NAME
461 CREATE PACKAGE p1 AS
462 PROCEDURE "p1.p1";
463 END;
464 $$
465 DELIMITER ;$$
466
467 DELIMITER $$;
468 --error ER_SP_WRONG_NAME
469 CREATE PACKAGE p1 AS
470 FUNCTION "f1.f1" RETURN INT;
471 END;
472 $$
473 DELIMITER ;$$
474
475
476 --echo #
477 --echo # Duplicate PROCEDURE in CREATE PACKAGE
478 --echo #
479
480 DELIMITER $$;
481 --error ER_SP_ALREADY_EXISTS,
482 CREATE PACKAGE test2 AS
483 PROCEDURE p1;
484 PROCEDURE p1;
485 END;
486 $$
487 DELIMITER ;$$
488
489
490 DELIMITER $$;
491 --error ER_SP_ALREADY_EXISTS,
492 CREATE PACKAGE test2 AS
493 PROCEDURE p1;
494 PROCEDURE P1;
495 END;
496 $$
497 DELIMITER ;$$
498
499
500 --echo #
501 --echo # Duplicate FUNCTION in CREATE PACKAGE
502 --echo #
503
504 DELIMITER $$;
505 --error ER_SP_ALREADY_EXISTS,
506 CREATE PACKAGE test2 AS
507 FUNCTION f1 RETURN INT;
508 FUNCTION f1 RETURN INT;
509 END;
510 $$
511 DELIMITER ;$$
512
513 DELIMITER $$;
514 --error ER_SP_ALREADY_EXISTS,
515 CREATE PACKAGE test2 AS
516 FUNCTION f1 RETURN INT;
517 FUNCTION F1 RETURN INT;
518 END;
519 $$
520 DELIMITER ;$$
521
522
523 --echo #
524 --echo # Duplicate PROCEDURE in CREATE PACKAGE BODY
525 --echo #
526
527 DELIMITER $$;
528 CREATE PACKAGE test2 AS
529 PROCEDURE p1;
530 END;
531 $$
532 --error ER_SP_ALREADY_EXISTS
533 CREATE PACKAGE BODY test2 AS
534 PROCEDURE p1 AS BEGIN NULL; END;
535 PROCEDURE p1 AS BEGIN NULL; END;
536 END;
537 $$
538 --error ER_SP_ALREADY_EXISTS
539 CREATE PACKAGE BODY test2 AS
540 PROCEDURE p1 AS BEGIN NULL; END;
541 PROCEDURE P1 AS BEGIN NULL; END;
542 END;
543 $$
544 DELIMITER ;$$
545 DROP PACKAGE test2;
546
547
548 --echo #
549 --echo # Duplicate FUNCTION in CREATE PACKAGE BODY
550 --echo #
551
552 DELIMITER $$;
553 CREATE PACKAGE test2 AS
554 FUNCTION f1 RETURN INT;
555 END;
556 $$
557 --error ER_SP_ALREADY_EXISTS
558 CREATE PACKAGE BODY test2 AS
559 FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
560 FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
561 END;
562 $$
563 --error ER_SP_ALREADY_EXISTS
564 CREATE PACKAGE BODY test2 AS
565 FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
566 FUNCTION F1 RETURN INT AS BEGIN RETURN 0; END;
567 END;
568 $$
569 DELIMITER ;$$
570 DROP PACKAGE test2;
571
572
573 --echo #
574 --echo # Routines declared in CREATE PACKAGE missing in CREATE PACKAGE BODY
575 --echo #
576
577 DELIMITER $$;
578 CREATE PACKAGE test2 AS
579 PROCEDURE p1;
580 END;
581 $$
582 --error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
583 CREATE PACKAGE BODY test2 AS
584 PROCEDURE p2 AS BEGIN NULL; END;
585 END;
586 $$
587 DELIMITER ;$$
588 DROP PACKAGE test2;
589
590 DELIMITER $$;
591 CREATE PACKAGE test2 AS
592 FUNCTION f1 RETURN INT;
593 END;
594 $$
595 --error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
596 CREATE PACKAGE BODY test2 AS
597 FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
598 END;
599 $$
600 DELIMITER ;$$
601 DROP PACKAGE test2;
602
603 DELIMITER $$;
604 CREATE PACKAGE test2 AS
605 PROCEDURE p1;
606 END;
607 $$
608 --error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
609 CREATE PACKAGE BODY test2 AS
610 FUNCTION p1 RETURN INT AS BEGIN RETURN 10; END;
611 END;
612 $$
613 DELIMITER ;$$
614 DROP PACKAGE test2;
615
616 DELIMITER $$;
617 CREATE PACKAGE test2 AS
618 PROCEDURE p1;
619 END;
620 $$
621 --error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
622 CREATE PACKAGE BODY test2 AS
623 PROCEDURE p1(a INT) AS BEGIN NULL; END; -- Notice different prototype
624 END;
625 $$
626 DELIMITER ;$$
627 DROP PACKAGE test2;
628
629 --echo #
630 --echo # Forward declarations in CREATE PACKAGE BODY with missing implementations
631 --echo #
632
633 DELIMITER $$;
634 CREATE PACKAGE test2 AS
635 PROCEDURE p1;
636 END;
637 $$
638 --error ER_PACKAGE_ROUTINE_FORWARD_DECLARATION_NOT_DEFINED
639 CREATE PACKAGE BODY test2 AS
640 PROCEDURE p1 AS BEGIN NULL; END;
641 PROCEDURE p2;
642 END;
643 $$
644 --error ER_PACKAGE_ROUTINE_FORWARD_DECLARATION_NOT_DEFINED
645 CREATE PACKAGE BODY test2 AS
646 FUNCTION f1 RETURN INT;
647 PROCEDURE p1 AS BEGIN NULL; END;
648 END;
649 $$
650 DELIMITER ;$$
651 DROP PACKAGE test2;
652
653
654 --echo #
655 --echo # Creating a new package
656 --echo #
657
658 DELIMITER $$;
659 CREATE PACKAGE test2 COMMENT 'package-test2-comment' AS
660 FUNCTION f1 RETURN INT DETERMINISTIC;
661 FUNCTION f2(a INT) RETURN INT;
662 FUNCTION concat RETURN INT;
663 PROCEDURE p1;
664 PROCEDURE p2(a INT);
665 END
666 $$
667 DELIMITER ;$$
668
669 --vertical_results
670 --replace_column 13 # 14 #
671 SELECT * FROM mysql.proc WHERE db='test' AND name='test2';
672 --replace_column 24 # 25 #
673 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
674 --horizontal_results
675
676 DELIMITER $$;
677 CREATE PACKAGE IF NOT EXISTS test2 AS
678 FUNCTION f1 RETURN INT;
679 END test2
680 $$
681 DELIMITER ;$$
682
683
684 DELIMITER $$;
685 CREATE PACKAGE BODY test2 COMMENT 'package-body-test2-comment' AS
686 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
687 FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
688 FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
689 PROCEDURE p1 AS
690 BEGIN
691 SELECT f2(0);
692 END;
693 PROCEDURE p2(a INT) AS
694 BEGIN
695 SELECT f2(a);
696 END;
697 END;
698 $$
699 DELIMITER ;$$
700
701 # This should do nothing and return a warning
702 DELIMITER $$;
703 CREATE PACKAGE BODY IF NOT EXISTS test2 AS
704 FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
705 FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
706 FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
707 PROCEDURE p1 AS
708 BEGIN
709 SELECT f2(0);
710 END;
711 PROCEDURE p2(a INT) AS
712 BEGIN
713 SELECT f2(a);
714 END;
715 END;
716 $$
717 DELIMITER ;$$
718
719 #
720 # The next query issues a warning about "concat" name collision,
721 # raised during compilation of the package body.
722 # However, "mtr --ps" does not produce the warning.
723 # It's not a package specific issue. The same difference exists for
724 # standalone functions. So just suppress warning for now.
725 #
726 --disable_warnings
727 SELECT test2.f1();
728 --enable_warnings
729 SELECT test2.f2(1);
730 CALL test2.p1();
731 CALL test2.p2(1);
732
733 --vertical_results
734 --replace_column 13 # 14 #
735 SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'test2.%';
736 --replace_column 24 # 25 #
737 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
738 --replace_column 24 # 25 #
739 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME LIKE 'test2.%';
740 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
741 SHOW PACKAGE STATUS;
742 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
743 SHOW PACKAGE BODY STATUS;
744 SHOW CREATE PACKAGE test2;
745 SHOW CREATE PACKAGE BODY test2;
746 --horizontal_results
747
748
749
750 DROP PACKAGE BODY test2;
751 --error ER_SP_DOES_NOT_EXIST
752 SELECT test2.f1();
753 --error ER_SP_DOES_NOT_EXIST
754 SELECT test2.f2();
755 --error ER_SP_DOES_NOT_EXIST
756 CALL test2.p1();
757
758 DROP PACKAGE BODY IF EXISTS test2;
759
760 --error ER_SP_DOES_NOT_EXIST
761 DROP PACKAGE BODY test2;
762
763
764 DROP PACKAGE test2;
765
766
767 --echo #
768 --echo # Creating a new package in a remote database
769 --echo #
770
771 CREATE DATABASE test2;
772
773 DELIMITER $$;
774 CREATE PACKAGE test2.test2 COMMENT 'package-test2-comment' AS
775 FUNCTION f1 RETURN INT;
776 PROCEDURE p1;
777 END
778 $$
779 DELIMITER ;$$
780
781 DELIMITER $$;
782 CREATE PACKAGE BODY test2.test2 COMMENT 'package-body-test2-comment' AS
783 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
784 PROCEDURE p1 AS BEGIN SELECT f1(); END;
785 END;
786 $$
787 DELIMITER ;$$
788
789 --vertical_results
790 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
791 SHOW PACKAGE STATUS;
792 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
793 SHOW PACKAGE BODY STATUS;
794 --horizontal_results
795
796 USE test2;
797 SELECT test2.f1();
798 CALL test2.p1();
799 USE test;
800 DROP PACKAGE BODY test2.test2;
801 DROP PACKAGE test2.test2;
802 DROP DATABASE test2;
803
804
805 --echo #
806 --echo # Only public routines are available outside
807 --echo #
808
809 DELIMITER $$;
810 CREATE PACKAGE test2 AS
811 FUNCTION f1 RETURN INT;
812 PROCEDURE p1;
813 END;
814 $$
815 CREATE PACKAGE BODY test2 AS
816 -- Public routines
817 FUNCTION f1 RETURN TEXT AS
818 BEGIN
819 RETURN 'This is test2.f1';
820 END;
821 PROCEDURE p1 AS
822 BEGIN
823 SELECT 'This is test2.p1';
824 END;
825 -- Private routines
826 FUNCTION f2 RETURN TEXT AS
827 BEGIN
828 RETURN 'This is test2.f2';
829 END;
830 PROCEDURE p2 AS
831 BEGIN
832 SELECT 'This is test2.p2';
833 END;
834 END;
835 $$
836 DELIMITER ;$$
837 SELECT test2.f1();
838 CALL test2.p1();
839 --error ER_SP_DOES_NOT_EXIST
840 SELECT test2.f2();
841 --error ER_SP_DOES_NOT_EXIST
842 CALL test2.p2();
843 DROP PACKAGE test2;
844
845
846 --echo #
847 --echo # PACKAGE BODY with forward declarations
848 --echo #
849
850 DELIMITER $$;
851 CREATE PACKAGE test2 AS
852 FUNCTION f1 RETURN INT;
853 PROCEDURE p1;
854 END;
855 $$
856 CREATE PACKAGE BODY test2 AS
857 -- Forward declarations
858 FUNCTION f2private RETURN TEXT;
859 PROCEDURE p2private;
860 -- Public routines
861 FUNCTION f1 RETURN TEXT AS
862 BEGIN
863 RETURN f2private();
864 END;
865 PROCEDURE p1 AS
866 BEGIN
867 CALL p2private;
868 END;
869 -- Definitions for the forward declarations
870 FUNCTION f2private RETURN TEXT AS
871 BEGIN
872 RETURN 'This is f2private';
873 END;
874 PROCEDURE p2private AS
875 BEGIN
876 SELECT 'This is p2private';
877 END;
878 END;
879 $$
880 DELIMITER ;$$
881 SELECT test2.f1();
882 CALL test2.p1();
883 DROP PACKAGE test2;
884
885
886 --echo #
887 --echo # Calling private routines with forward declarations,
888 --echo # using qualified notation, e.g. "CALL pkg.proc"
889 --echo #
890
891 DELIMITER $$;
892 CREATE PACKAGE test2 AS
893 FUNCTION f1 RETURN INT;
894 PROCEDURE p1;
895 END;
896 $$
897 CREATE PACKAGE BODY test2 AS
898 -- Forward declarations
899 FUNCTION f2private RETURN TEXT;
900 PROCEDURE p2private;
901 -- Public routines
902 FUNCTION f1 RETURN TEXT AS
903 BEGIN
904 RETURN test2.f2private();
905 END;
906 PROCEDURE p1 AS
907 BEGIN
908 CALL test2.p2private;
909 END;
910 -- Definitions for the forward declarations
911 FUNCTION f2private RETURN TEXT AS
912 BEGIN
913 RETURN 'This is f2private';
914 END;
915 PROCEDURE p2private AS
916 BEGIN
917 SELECT 'This is p2private' AS msg;
918 END;
919 END;
920 $$
921 DELIMITER ;$$
922 SELECT test2.f1();
923 CALL test2.p1();
924 DROP PACKAGE test2;
925
926
927 --echo #
928 --echo # Calling private routines, using qualified notation, e.g. "pkg.proc"
929 --echo #
930
931 DELIMITER $$;
932 CREATE PACKAGE test2 AS
933 FUNCTION f1 RETURN INT;
934 PROCEDURE p1;
935 END;
936 $$
937 CREATE PACKAGE BODY test2 AS
938 -- Private routines
939 FUNCTION f2private RETURN TEXT AS
940 BEGIN
941 RETURN 'This is f2private';
942 END;
943 PROCEDURE p2private AS
944 BEGIN
945 SELECT 'This is p2private' AS msg;
946 END;
947 -- Public routines
948 FUNCTION f1 RETURN TEXT AS
949 BEGIN
950 RETURN test2.f2private();
951 END;
952 PROCEDURE p1 AS
953 BEGIN
954 CALL test2.p2private;
955 END;
956 END;
957 $$
958 DELIMITER ;$$
959 SELECT test2.f1();
960 CALL test2.p1();
961 DROP PACKAGE test2;
962
963
964 --echo #
965 --echo # Calling private routines from the package initialization section,
966 --echo # using qualified notation, e.g. "pkg.proc"
967 --echo #
968
969 DELIMITER $$;
970 CREATE PACKAGE test2 AS
971 PROCEDURE p1;
972 END;
973 $$
974 CREATE PACKAGE BODY test2 AS
975 -- Private routines
976 FUNCTION f2private RETURN TEXT AS
977 BEGIN
978 RETURN 'This is f2private';
979 END;
980 PROCEDURE p2private AS
981 BEGIN
982 SELECT 'This is p2private' AS msg;
983 END;
984 -- Public routines
985 PROCEDURE p1 AS
986 BEGIN
987 SELECT 'This is p1' AS msg;
988 END;
989 BEGIN
990 SELECT test2.f2private();
991 CALL test2.p2private();
992 END;
993 $$
994 DELIMITER ;$$
995 CALL test2.p1();
996 DROP PACKAGE test2;
997
998
999 --echo #
1000 --echo # Testing OR REPLACE
1001 --echo #
1002
1003 DELIMITER $$;
1004 CREATE OR REPLACE PACKAGE pkg AS
1005 FUNCTION f0 RETURN INT;
1006 END;
1007 $$
1008 CREATE OR REPLACE PACKAGE pkg AS
1009 FUNCTION f1 RETURN INT;
1010 END;
1011 $$
1012 DELIMITER ;$$
1013 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1014
1015 DELIMITER $$;
1016 CREATE OR REPLACE PACKAGE BODY pkg AS
1017 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
1018 END;
1019 $$
1020 DELIMITER ;$$
1021 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1022 SELECT pkg.f1();
1023
1024 DELIMITER $$;
1025 CREATE OR REPLACE PACKAGE BODY pkg AS
1026 FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
1027 END;
1028 $$
1029 DELIMITER ;$$
1030 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1031 SELECT pkg.f1();
1032
1033 DELIMITER $$;
1034 CREATE OR REPLACE PACKAGE pkg AS
1035 FUNCTION f1 RETURN BIGINT;
1036 END;
1037 $$
1038 DELIMITER ;$$
1039 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1040 --error ER_SP_DOES_NOT_EXIST
1041 SELECT pkg.f1();
1042
1043 DELIMITER $$;
1044 CREATE OR REPLACE PACKAGE BODY pkg AS
1045 FUNCTION f1 RETURN INT AS BEGIN RETURN 30; END;
1046 END;
1047 $$
1048 DELIMITER ;$$
1049 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1050 SELECT pkg.f1();
1051
1052 DROP PACKAGE pkg;
1053
1054
1055 --echo #
1056 --echo # Package routines accessing tables
1057 --echo #
1058 CREATE TABLE t1 (a INT);
1059 DELIMITER $$;
1060 CREATE PACKAGE test2 AS
1061 PROCEDURE p1(a INT);
1062 END;
1063 $$
1064 CREATE PACKAGE BODY test2 AS
1065 PROCEDURE p1(a INT) AS
1066 BEGIN
1067 INSERT INTO t1 VALUES (10);
1068 END;
1069 END;
1070 $$
1071 DELIMITER ;$$
1072 CALL test2.p1(10);
1073 SELECT * FROM t1;
1074 DROP PACKAGE test2;
1075 DROP TABLE t1;
1076
1077
1078 --echo #
1079 --echo # CREATE PACKAGE: Optional package name after the "END" keyword
1080 --echo #
1081
1082 DELIMITER $$;
1083 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
1084 CREATE PACKAGE test2 AS
1085 FUNCTION f1 RETURN INT;
1086 PROCEDURE p1;
1087 END test2.test2
1088 $$
1089 DELIMITER ;$$
1090
1091 DELIMITER $$;
1092 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
1093 CREATE PACKAGE test2 AS
1094 FUNCTION f1 RETURN INT;
1095 PROCEDURE p1;
1096 END test3
1097 $$
1098 DELIMITER ;$$
1099
1100 DELIMITER $$;
1101 CREATE PACKAGE test2 AS
1102 FUNCTION f1 RETURN INT;
1103 PROCEDURE p1;
1104 END test2
1105 $$
1106 DELIMITER ;$$
1107 DROP PACKAGE test2;
1108
1109
1110 --echo #
1111 --echo # MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword
1112 --echo #
1113
1114
1115 DELIMITER $$;
1116 CREATE PACKAGE test2 AS
1117 FUNCTION f1 RETURN INT;
1118 PROCEDURE p1;
1119 END test2;
1120 $$
1121 DELIMITER ;$$
1122
1123
1124 DELIMITER $$;
1125 --error ER_PARSE_ERROR
1126 CREATE PACKAGE BODY test2 AS
1127 FUNCTION f1 RETURN INT AS
1128 BEGIN
1129 RETURN 10;
1130 END f1.f1;
1131 END test2;
1132 $$
1133 DELIMITER ;$$
1134
1135
1136 DELIMITER $$;
1137 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
1138 CREATE PACKAGE BODY test2 AS
1139 FUNCTION f1 RETURN INT AS
1140 BEGIN
1141 RETURN 10;
1142 END f2;
1143 END test2;
1144 $$
1145 DELIMITER ;$$
1146
1147
1148 DELIMITER $$;
1149 --error ER_PARSE_ERROR
1150 CREATE PACKAGE BODY test2 AS
1151 PROCEDURE p1 AS
1152 BEGIN
1153 NULL;
1154 END p1.p1;
1155 END test2;
1156 $$
1157 DELIMITER ;$$
1158
1159
1160 DELIMITER $$;
1161 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
1162 CREATE PACKAGE BODY test2 AS
1163 PROCEDURE p1 AS
1164 BEGIN
1165 NULL;
1166 END p2;
1167 END test2;
1168 $$
1169 DELIMITER ;$$
1170
1171
1172 DELIMITER $$;
1173 CREATE PACKAGE BODY test2 AS
1174 FUNCTION f1 RETURN INT AS
1175 BEGIN
1176 RETURN 10;
1177 END f1;
1178 PROCEDURE p1 AS
1179 BEGIN
1180 NULL;
1181 END p1;
1182 END test2;
1183 $$
1184 DELIMITER ;$$
1185 DROP PACKAGE test2;
1186
1187 --echo #
1188 --echo # Package and package routine name and end name are case insensitive
1189 --echo #
1190
1191 DELIMITER $$;
1192 CREATE PACKAGE test2 AS
1193 FUNCTION f1 RETURN TEXT;
1194 PROCEDURE p1;
1195 END TEST2;
1196 $$
1197 DELIMITER ;$$
1198
1199 DELIMITER $$;
1200 CREATE PACKAGE BODY test2 AS
1201 FUNCTION f1 RETURN TEXT AS
1202 BEGIN
1203 RETURN 'This is f1';
1204 END F1;
1205 PROCEDURE P1 AS
1206 BEGIN
1207 SELECT 'This is p1' AS msg;
1208 END p1;
1209 END TEST2;
1210 $$
1211 DELIMITER ;$$
1212 SELECT TEST2.F1();
1213 SELECT test2.f1();
1214 CALL TEST2.p1();
1215 CALL test2.P1();
1216 DROP PACKAGE BODY TEST2;
1217 DROP PACKAGE TEST2;
1218
1219
1220 --echo #
1221 --echo # Testing various qualified/non-qualified db/package SP call chains
1222 --echo #
1223
1224 DELIMITER $$;
1225 CREATE FUNCTION f3() RETURN TEXT AS
1226 BEGIN
1227 SET @track= @track || ' ' || 'test.f3()';
1228 RETURN '';
1229 END;
1230 $$
1231 CREATE PROCEDURE p3() AS
1232 BEGIN
1233 SET @track= @track || ' ' || 'test.p3()';
1234 END;
1235 $$
1236 CREATE FUNCTION ff2(task TEXT) RETURN TEXT AS
1237 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1238 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1239 rc TEXT;
1240 BEGIN
1241 SET @track= @track || ' ' || 'test.ff2()';
1242 CASE step
1243 WHEN '' THEN NULL;
1244 WHEN 'p3' THEN CALL p3();
1245 WHEN 'f3' THEN rc:= f3();
1246 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1247 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1248 WHEN 'pack.p3' THEN CALL pack.p3();
1249 WHEN 'pack.f3' THEN rc:= pack.f3();
1250 WHEN 'test.p3' THEN CALL test.p3();
1251 WHEN 'test.f3' THEN rc:= test.f3();
1252 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1253 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1254 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1255 END CASE;
1256 RETURN '';
1257 END;
1258 $$
1259 CREATE PROCEDURE pp2(task TEXT) AS
1260 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1261 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1262 rc TEXT;
1263 BEGIN
1264 SET @track= @track || ' ' || 'test.pp2()';
1265 CASE step
1266 WHEN '' THEN NULL;
1267 WHEN 'p3' THEN CALL p3();
1268 WHEN 'f3' THEN rc:= f3();
1269 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1270 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1271 WHEN 'pack.p3' THEN CALL pack.p3();
1272 WHEN 'pack.f3' THEN rc:= pack.f3();
1273 WHEN 'test.p3' THEN CALL test.p3();
1274 WHEN 'test.f3' THEN rc:= test.f3();
1275 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1276 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1277 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1278 END CASE;
1279 END;
1280 $$
1281 CREATE PACKAGE pack AS
1282 PROCEDURE p1(task TEXT);
1283 PROCEDURE p2(task TEXT);
1284 FUNCTION f1(task TEXT) RETURN TEXT;
1285 FUNCTION f2(step2 TEXT) RETURN TEXT;
1286 FUNCTION f3 RETURN TEXT;
1287 PROCEDURE p3;
1288 END;
1289 $$
1290 CREATE PACKAGE BODY pack AS
1291 PROCEDURE p1(task TEXT) AS
1292 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1293 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1294 rc TEXT;
1295 BEGIN
1296 SET @track= 'test.pack.p1()';
1297 CASE step
1298 WHEN '' THEN NULL;
1299 WHEN 'p2' THEN CALL p2(tail);
1300 WHEN 'f2' THEN rc:= f2(tail);
1301 WHEN 'p3' THEN CALL p3();
1302 WHEN 'f3' THEN rc:= f3();
1303 WHEN 'px' THEN CALL px();
1304 WHEN 'fx' THEN rc:= fx();
1305 WHEN 'pp2' THEN CALL pp2(tail);
1306 WHEN 'ff2' THEN rc:= ff2(tail);
1307 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1308 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1309 WHEN 'pack.p3' THEN CALL pack.p3();
1310 WHEN 'pack.f3' THEN rc:= pack.f3();
1311 WHEN 'pack.px' THEN CALL pack.px();
1312 WHEN 'pack.fx' THEN rc:= pack.fx();
1313 WHEN 'test.p3' THEN CALL test.p3();
1314 WHEN 'test.f3' THEN rc:= test.f3();
1315 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1316 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1317 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1318 END CASE;
1319 SELECT @track;
1320 END;
1321
1322 FUNCTION f1(task TEXT) RETURN TEXT AS
1323 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1324 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1325 rc TEXT;
1326 BEGIN
1327 SET @track= 'test.pack.f1()';
1328 CASE step
1329 WHEN '' THEN NULL;
1330 WHEN 'p2' THEN CALL p2(tail);
1331 WHEN 'f2' THEN rc:= f2(tail);
1332 WHEN 'p3' THEN CALL p3();
1333 WHEN 'f3' THEN rc:= f3();
1334 WHEN 'px' THEN CALL px();
1335 WHEN 'fx' THEN rc:= fx();
1336 WHEN 'pp2' THEN CALL pp2(tail);
1337 WHEN 'ff2' THEN rc:= ff2(tail);
1338 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1339 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1340 WHEN 'pack.p3' THEN CALL pack.p3();
1341 WHEN 'pack.f3' THEN rc:= pack.f3();
1342 WHEN 'pack.px' THEN CALL pack.px();
1343 WHEN 'pack.fx' THEN rc:= pack.fx();
1344 WHEN 'test.p3' THEN CALL test.p3();
1345 WHEN 'test.f3' THEN rc:= test.f3();
1346 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1347 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1348 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1349 END CASE;
1350 SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT=@track;
1351 RETURN '';
1352 END;
1353
1354 PROCEDURE p2(task TEXT) AS
1355 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1356 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1357 rc TEXT;
1358 BEGIN
1359 SET @track= @track || ' ' || 'test.pack.p2()';
1360 CASE step
1361 WHEN '' THEN NULL;
1362 WHEN 'p2' THEN CALL p2(tail);
1363 WHEN 'f2' THEN rc:= f2(tail);
1364 WHEN 'p3' THEN CALL p3();
1365 WHEN 'f3' THEN rc:= f3();
1366 WHEN 'px' THEN CALL px();
1367 WHEN 'fx' THEN rc:= fx();
1368 WHEN 'pp2' THEN CALL pp2(tail);
1369 WHEN 'ff2' THEN rc:= ff2(tail);
1370 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1371 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1372 WHEN 'pack.p3' THEN CALL pack.p3();
1373 WHEN 'pack.f3' THEN rc:= pack.f3();
1374 WHEN 'pack.px' THEN CALL pack.px();
1375 WHEN 'pack.fx' THEN rc:= pack.fx();
1376 WHEN 'test.p3' THEN CALL test.p3();
1377 WHEN 'test.f3' THEN rc:= test.f3();
1378 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1379 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1380 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1381 END CASE;
1382 END;
1383
1384 FUNCTION f2(task TEXT) RETURN TEXT AS
1385 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1386 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1387 rc TEXT;
1388 BEGIN
1389 SET @track= @track || ' ' || 'test.pack.f2()';
1390 CASE step
1391 WHEN '' THEN NULL;
1392 WHEN 'p2' THEN CALL p2(tail);
1393 WHEN 'f2' THEN rc:= f2(tail);
1394 WHEN 'p3' THEN CALL p3();
1395 WHEN 'f3' THEN rc:= f3();
1396 WHEN 'px' THEN CALL px();
1397 WHEN 'fx' THEN rc:= fx();
1398 WHEN 'pp2' THEN CALL pp2(tail);
1399 WHEN 'ff2' THEN rc:= ff2(tail);
1400 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1401 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1402 WHEN 'pack.p3' THEN CALL pack.p3();
1403 WHEN 'pack.f3' THEN rc:= pack.f3();
1404 WHEN 'pack.px' THEN CALL pack.px();
1405 WHEN 'pack.fx' THEN rc:= pack.fx();
1406 WHEN 'test.p3' THEN CALL test.p3();
1407 WHEN 'test.f3' THEN rc:= test.f3();
1408 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1409 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1410 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1411 END CASE;
1412 RETURN '';
1413 END;
1414 PROCEDURE p3 AS
1415 BEGIN
1416 SET @track= @track || ' ' || 'test.pack.p3()';
1417 END;
1418 FUNCTION f3 RETURN TEXT AS
1419 BEGIN
1420 SET @track= @track || ' ' || 'test.pack.f3()';
1421 RETURN '';
1422 END;
1423
1424 END pack;
1425 $$
1426 DELIMITER ;$$
1427
1428 SET max_sp_recursion_depth=10;
1429
1430 --echo # pack.routine -> *
1431
1432 CALL pack.p1('p2');
1433 CALL pack.p1('f2');
1434 --error ER_SP_DOES_NOT_EXIST
1435 CALL pack.p1('px');
1436 --error ER_SP_DOES_NOT_EXIST
1437 CALL pack.p1('fx');
1438
1439 CALL pack.p1('pp2');
1440 CALL pack.p1('ff2');
1441
1442 CALL pack.p1('pack.p2');
1443 CALL pack.p1('pack.f2');
1444 --error ER_SP_DOES_NOT_EXIST
1445 CALL pack.p1('pack.px');
1446 --error ER_SP_DOES_NOT_EXIST
1447 CALL pack.p1('pack.fx');
1448
1449 CALL pack.p1('test.pp2');
1450 CALL pack.p1('test.ff2');
1451
1452 DO pack.f1('p2');
1453 DO pack.f1('f2');
1454 --error ER_SP_DOES_NOT_EXIST
1455 DO pack.p1('px');
1456 --error ER_SP_DOES_NOT_EXIST
1457 DO pack.p1('fx');
1458
1459 DO pack.f1('pp2');
1460 DO pack.f1('ff2');
1461
1462 DO pack.f1('pack.p2');
1463 DO pack.f1('pack.f2');
1464 --error ER_SP_DOES_NOT_EXIST
1465 SELECT pack.f1('pack.px');
1466 --error ER_SP_DOES_NOT_EXIST
1467 SELECT pack.f1('pack.fx');
1468
1469 DO pack.f1('test.pp2');
1470 DO pack.f1('test.ff2');
1471
1472 --echo #
1473 --echo # Qualified_package_routine -> Non_qualified_package_routine
1474 --echo #
1475
1476 --echo # pack.routine -> [pack.]routine -> pack.routine
1477
1478 CALL pack.p1('p2 pack.p3');
1479 CALL pack.p1('p2 pack.f3');
1480 CALL pack.p1('f2 pack.p3');
1481 CALL pack.p1('f2 pack.f3');
1482
1483 DO pack.f1('p2 pack.p3');
1484 DO pack.f1('p2 pack.f3');
1485 DO pack.f1('f2 pack.p3');
1486 DO pack.f1('f2 pack.f3');
1487
1488 --echo # pack.routine -> [pack.]routine -> [pack]routine
1489
1490 CALL pack.p1('p2 p3');
1491 CALL pack.p1('p2 f3');
1492 CALL pack.p1('f2 p3');
1493 CALL pack.p1('f2 f3');
1494
1495 DO pack.f1('p2 p3');
1496 DO pack.f1('p2 f3');
1497 DO pack.f1('f2 p3');
1498 DO pack.f1('f2 f3');
1499
1500 --echo # pack.routine -> [pack.]routine -> test.routine
1501
1502 CALL pack.p1('p2 test.p3');
1503 CALL pack.p1('p2 test.f3');
1504 CALL pack.p1('f2 test.p3');
1505 CALL pack.p1('f2 test.f3');
1506
1507 DO pack.f1('p2 test.p3');
1508 DO pack.f1('p2 test.f3');
1509 DO pack.f1('f2 test.p3');
1510 DO pack.f1('f2 test.f3');
1511
1512 --echo # pack.routine -> [pack.]routine -> [test.]routine
1513
1514 CALL pack.p1('p2 pp2');
1515 CALL pack.p1('p2 ff2');
1516 CALL pack.p1('f2 pp2');
1517 CALL pack.p1('f2 ff2');
1518
1519 DO pack.f1('p2 pp2');
1520 DO pack.f1('p2 ff2');
1521 DO pack.f1('f2 pp2');
1522 DO pack.f1('f2 ff2');
1523
1524
1525 --echo #
1526 --echo # Qualified_package_routine -> Non_qualified_database_routine
1527 --echo #
1528
1529 --echo # pack.routine -> [test.]routine -> pack.routine
1530
1531 CALL pack.p1('pp2 pack.p3');
1532 CALL pack.p1('pp2 pack.f3');
1533 CALL pack.p1('ff2 pack.p3');
1534 CALL pack.p1('ff2 pack.f3');
1535
1536 DO pack.f1('pp2 pack.p3');
1537 DO pack.f1('pp2 pack.f3');
1538 DO pack.f1('ff2 pack.p3');
1539 DO pack.f1('ff2 pack.f3');
1540
1541 --echo # pack.routine -> [test.]routine -> test.routine
1542
1543 CALL pack.p1('pp2 test.p3');
1544 CALL pack.p1('pp2 test.f3');
1545 CALL pack.p1('ff2 test.p3');
1546 CALL pack.p1('ff2 test.f3');
1547
1548 DO pack.f1('pp2 test.p3');
1549 DO pack.f1('pp2 test.f3');
1550 DO pack.f1('ff2 test.p3');
1551 DO pack.f1('ff2 test.f3');
1552
1553 --echo # pack.routine -> [test.]routine -> [test.]routine
1554
1555 CALL pack.p1('pp2 p3');
1556 CALL pack.p1('pp2 f3');
1557 CALL pack.p1('ff2 p3');
1558 CALL pack.p1('ff2 f3');
1559
1560 DO pack.f1('pp2 p3');
1561 DO pack.f1('pp2 f3');
1562 DO pack.f1('ff2 p3');
1563 DO pack.f1('ff2 f3');
1564
1565
1566 --echo #
1567 --echo # Qualified_package_routine -> Qualified_package_routine
1568 --echo #
1569
1570 --echo # pack.routine -> pack.routine -> pack.routine
1571
1572 CALL pack.p1('pack.p2 pack.p3');
1573 CALL pack.p1('pack.p2 pack.f3');
1574 CALL pack.p1('pack.f2 pack.p3');
1575 CALL pack.p1('pack.f2 pack.f3');
1576
1577 DO pack.f1('pack.p2 pack.p3');
1578 DO pack.f1('pack.p2 pack.f3');
1579 DO pack.f1('pack.f2 pack.p3');
1580 DO pack.f1('pack.f2 pack.f3');
1581
1582 --echo # pack.routine -> pack.routine -> [pack.]routine
1583
1584 CALL pack.p1('pack.p2 p3');
1585 CALL pack.p1('pack.p2 f3');
1586 CALL pack.p1('pack.f2 p3');
1587 CALL pack.p1('pack.f2 f3');
1588
1589 DO pack.f1('pack.p2 p3');
1590 DO pack.f1('pack.p2 f3');
1591 DO pack.f1('pack.f2 p3');
1592 DO pack.f1('pack.f2 f3');
1593
1594 --echo # pack.routine -> pack.routine -> test.routine
1595
1596 CALL pack.p1('pack.p2 test.p3');
1597 CALL pack.p1('pack.p2 test.f3');
1598 CALL pack.p1('pack.f2 test.p3');
1599 CALL pack.p1('pack.f2 test.f3');
1600
1601 DO pack.f1('pack.p2 test.p3');
1602 DO pack.f1('pack.p2 test.f3');
1603 DO pack.f1('pack.f2 test.p3');
1604 DO pack.f1('pack.f2 test.f3');
1605
1606 --echo # pack.routine -> pack.routine -> [test.]routine
1607
1608 CALL pack.p1('pack.p2 pp2');
1609 CALL pack.p1('pack.p2 ff2');
1610 CALL pack.p1('pack.f2 pp2');
1611 CALL pack.p1('pack.f2 ff2');
1612
1613 DO pack.f1('pack.p2 pp2');
1614 DO pack.f1('pack.p2 ff2');
1615 DO pack.f1('pack.f2 pp2');
1616 DO pack.f1('pack.f2 ff2');
1617
1618
1619 --echo #
1620 --echo # Qualified_package_routine -> Qualified_database_routine
1621 --echo #
1622
1623 --echo pack.routine -> test.routine -> pack.routine
1624
1625 CALL pack.p1('test.pp2 pack.p3');
1626 CALL pack.p1('test.pp2 pack.f3');
1627 CALL pack.p1('test.ff2 pack.p3');
1628 CALL pack.p1('test.ff2 pack.f3');
1629
1630 DO pack.f1('test.pp2 pack.p3');
1631 DO pack.f1('test.pp2 pack.f3');
1632 DO pack.f1('test.ff2 pack.p3');
1633 DO pack.f1('test.ff2 pack.f3');
1634
1635 --echo pack.routine -> test.routine -> test.routine
1636
1637 CALL pack.p1('test.pp2 test.p3');
1638 CALL pack.p1('test.pp2 test.f3');
1639 CALL pack.p1('test.ff2 test.p3');
1640 CALL pack.p1('test.ff2 test.f3');
1641
1642 DO pack.f1('test.pp2 test.p3');
1643 DO pack.f1('test.pp2 test.f3');
1644 DO pack.f1('test.ff2 test.p3');
1645 DO pack.f1('test.ff2 test.f3');
1646
1647 --echo pack.routine -> test.routine -> [test.]routine
1648
1649 CALL pack.p1('test.pp2 p3');
1650 CALL pack.p1('test.pp2 f3');
1651 CALL pack.p1('test.ff2 p3');
1652 CALL pack.p1('test.ff2 f3');
1653
1654 DO pack.f1('test.pp2 p3');
1655 DO pack.f1('test.pp2 f3');
1656 DO pack.f1('test.ff2 p3');
1657 DO pack.f1('test.ff2 f3');
1658
1659
1660 --echo # Longer chains
1661
1662 CALL pack.p1('p2 f2 p2 test.pp2 test.ff2 pack.p3');
1663 CALL pack.p1('p2 test.pp2 pack.p2 pack.f2 test.ff2 pack.p3');
1664
1665
1666 DROP PACKAGE pack;
1667 DROP FUNCTION f3;
1668 DROP PROCEDURE p3;
1669 DROP FUNCTION ff2;
1670 DROP PROCEDURE pp2;
1671
1672
1673 --echo #
1674 --echo # Calling a standalone function from a non-current database,
1675 --echo # which calls a package routine from the same non-current database.
1676 --echo #
1677
1678 DELIMITER $$;
1679 CREATE PROCEDURE p1 AS
1680 BEGIN
1681 CALL pkg1.p1;
1682 END;
1683 $$
1684 CREATE PACKAGE pkg1 AS
1685 PROCEDURE p1;
1686 END;
1687 $$
1688 CREATE PACKAGE BODY pkg1 AS
1689 PROCEDURE p1 AS
1690 BEGIN
1691 SELECT database();
1692 END;
1693 END;
1694 $$
1695 DELIMITER ;$$
1696 # Current database
1697 CALL p1;
1698 CREATE DATABASE test2;
1699 USE test2;
1700 # Non-current database
1701 CALL test.p1;
1702 DROP DATABASE test2;
1703 # No current database at all
1704 CALL test.p1;
1705 USE test;
1706 DROP PACKAGE pkg1;
1707 DROP PROCEDURE p1;
1708
1709
1710 --echo #
1711 --echo # Creating a package with a different DEFINER
1712 --echo #
1713
1714 CREATE USER xxx@localhost;
1715 DELIMITER $$;
1716 CREATE DEFINER=xxx@localhost PACKAGE p1 AS
1717 PROCEDURE p1;
1718 END;
1719 $$
1720 CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS
1721 PROCEDURE p1 AS
1722 BEGIN
1723 NULL;
1724 END;
1725 END;
1726 $$
1727 DELIMITER ;$$
1728 SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
1729 DROP PACKAGE p1;
1730 DROP USER xxx@localhost;
1731
1732 --echo #
1733 --echo # Creating a package with a different DEFINER, with SQL SECURITY INVOKER
1734 --echo #
1735
1736 CREATE USER xxx@localhost;
1737 DELIMITER $$;
1738 CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS
1739 PROCEDURE p1;
1740 END;
1741 $$
1742 CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS
1743 PROCEDURE p1 AS
1744 BEGIN
1745 NULL;
1746 END;
1747 END;
1748 $$
1749 DELIMITER ;$$
1750 SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
1751 DROP PACKAGE p1;
1752 DROP USER xxx@localhost;
1753
1754 --echo #
1755 --echo # A package with an initialization section
1756 --echo #
1757
1758 DELIMITER $$;
1759 CREATE PACKAGE p1 AS
1760 PROCEDURE p1;
1761 FUNCTION f1 RETURN INT;
1762 END;
1763 $$
1764 CREATE PACKAGE BODY p1 AS
1765 PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
1766 FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
1767 BEGIN
1768 SET @a:=10;
1769 END;
1770 $$
1771 DELIMITER ;$$
1772 CALL p1.p1();
1773 CALL p1.p1();
1774 SELECT p1.f1();
1775 SELECT p1.f1();
1776 --source sp-cache-invalidate.inc
1777 SELECT p1.f1();
1778 CALL p1.p1();
1779 SELECT p1.f1();
1780 CALL p1.p1();
1781 DROP PACKAGE p1;
1782
1783
1784 --echo #
1785 --echo # A package with an initialization section calling
1786 --echo # routines from the same package, and standalone routines.
1787 --echo #
1788
1789 DELIMITER $$;
1790 CREATE PROCEDURE init20 AS
1791 BEGIN
1792 SET @msg= @msg || '[init20]';
1793 END;
1794 $$
1795 CREATE PACKAGE p1 AS
1796 PROCEDURE init1;
1797 PROCEDURE init2;
1798 FUNCTION init3 RETURN INT;
1799 PROCEDURE p1;
1800 FUNCTION f1 RETURN TEXT;
1801 END;
1802 $$
1803 CREATE PACKAGE BODY p1 AS
1804 PROCEDURE init1 AS
1805 BEGIN
1806 SET @msg= @msg || '[p1.init1]';
1807 END;
1808 PROCEDURE init2 AS
1809 BEGIN
1810 SET @msg= @msg || '[p1.init2]';
1811 END;
1812 FUNCTION init3 RETURN INT AS
1813 BEGIN
1814 SET @msg= @msg || '[p1.init3]';
1815 RETURN 0;
1816 END;
1817 PROCEDURE p1 AS
1818 BEGIN
1819 SET @msg= @msg || '[p1.p1]';
1820 SELECT @msg;
1821 END;
1822 FUNCTION f1 RETURN TEXT AS
1823 BEGIN
1824 SET @msg= @msg || '[p1.f1]';
1825 RETURN @msg;
1826 END;
1827 BEGIN
1828 SET @msg= '';
1829 init1();
1830 init2();
1831 DO init3();
1832 init20();
1833 END;
1834 $$
1835 DELIMITER ;$$
1836 CALL p1.p1();
1837 CALL p1.p1();
1838 SELECT p1.f1();
1839 SELECT p1.f1();
1840 --source sp-cache-invalidate.inc
1841 SELECT p1.f1();
1842 CALL p1.p1();
1843 SELECT p1.f1();
1844 CALL p1.p1();
1845 DROP PACKAGE p1;
1846 DROP PROCEDURE init20;
1847
1848
1849 --echo #
1850 --echo # EXECUTE IMMEDIATE in the package initialization section
1851 --echo #
1852
1853 SET @a=1000;
1854 CREATE TABLE t1 AS SELECT 10 AS a;
1855 DELIMITER $$;
1856 CREATE PACKAGE p1 AS
1857 PROCEDURE p1;
1858 FUNCTION f1 RETURN INT;
1859 END;
1860 $$
1861 CREATE PACKAGE BODY p1 AS
1862 PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
1863 FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
1864 BEGIN
1865 EXECUTE IMMEDIATE 'SELECT MAX(a) FROM t1 INTO @a';
1866 END;
1867 $$
1868 DELIMITER ;$$
1869 CALL p1.p1();
1870 CALL p1.p1();
1871 SELECT p1.f1();
1872 SELECT p1.f1();
1873 --source sp-cache-invalidate.inc
1874 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
1875 SELECT p1.f1();
1876 DROP PACKAGE p1;
1877 DROP TABLE t1;
1878
1879
1880 --echo #
1881 --echo # A package with an initialization section, loading table data into a user variable
1882 --echo #
1883
1884 SET @a=1000;
1885 CREATE TABLE t1 AS SELECT 10 AS a;
1886 DELIMITER $$;
1887 CREATE PACKAGE p1 AS
1888 PROCEDURE p1;
1889 FUNCTION f1 RETURN INT;
1890 END;
1891 $$
1892 CREATE PACKAGE BODY p1 AS
1893 PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
1894 FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
1895 BEGIN
1896 SELECT MAX(a) FROM t1 INTO @a;
1897 END;
1898 $$
1899 DELIMITER ;$$
1900 CALL p1.p1();
1901 CALL p1.p1();
1902 SELECT p1.f1();
1903 SELECT p1.f1();
1904 --source sp-cache-invalidate.inc
1905 SELECT p1.f1();
1906 DROP PACKAGE p1;
1907 DROP TABLE t1;
1908
1909 --echo #
1910 --echo # A package with an initialization section producing an error
1911 --echo #
1912
1913 DELIMITER $$;
1914 CREATE PACKAGE p1 AS
1915 PROCEDURE p1;
1916 FUNCTION f1 RETURN TEXT;
1917 END;
1918 $$
1919 CREATE PACKAGE BODY p1 AS
1920 PROCEDURE p1 AS BEGIN SELECT 'This is p1' AS msg; END;
1921 FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
1922 BEGIN
1923 SELECT 1 FROM t1 INTO @a;
1924 END;
1925 $$
1926 DELIMITER ;$$
1927 --error ER_NO_SUCH_TABLE
1928 CALL p1.p1();
1929 --error ER_NO_SUCH_TABLE
1930 SELECT p1.f1();
1931 --source sp-cache-invalidate.inc
1932 --error ER_NO_SUCH_TABLE
1933 SELECT p1.f1();
1934 --error ER_NO_SUCH_TABLE
1935 CALL p1.p1();
1936 --error ER_NO_SUCH_TABLE
1937 SELECT p1.f1();
1938 CREATE TABLE t1 (a INT) AS SELECT 1;
1939 CALL p1.p1();
1940 --source sp-cache-invalidate.inc
1941 SELECT p1.f1();
1942 --source sp-cache-invalidate.inc
1943 CALL p1.p1();
1944 DROP TABLE t1;
1945 DROP PACKAGE p1;
1946
1947
1948 --echo #
1949 --echo # A package with SF-unsafe statements in the initialization section
1950 --echo #
1951
1952 DELIMITER $$;
1953 CREATE PACKAGE p1 AS
1954 PROCEDURE p1;
1955 FUNCTION f1 RETURN TEXT;
1956 END;
1957 $$
1958 CREATE PACKAGE BODY p1 AS
1959 PROCEDURE p1 AS BEGIN SELECT 'This is p1' AS msg; END;
1960 FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
1961 BEGIN
1962 CREATE TABLE IF NOT EXISTS t1 (a INT);
1963 DROP TABLE IF EXISTS t1;
1964 END;
1965 $$
1966 DELIMITER ;$$
1967 CALL p1.p1();
1968 SELECT p1.f1();
1969 --source sp-cache-invalidate.inc
1970 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1971 SELECT p1.f1();
1972 CALL p1.p1();
1973 SELECT p1.f1();
1974 DROP PACKAGE p1;
1975
1976
1977 --echo #
1978 --echo # MDEV-13139 Package-wide variables in CREATE PACKAGE
1979 --echo #
1980
1981 DELIMITER $$;
1982 CREATE PACKAGE p1 AS
1983 PROCEDURE p1;
1984 FUNCTION f1 RETURN INT;
1985 END;
1986 $$
1987 --error ER_SP_DUP_VAR
1988 CREATE PACKAGE BODY p1 AS
1989 a INT;
1990 a INT;
1991 PROCEDURE p1 AS
1992 BEGIN
1993 CREATE VIEW v1 AS SELECT a;
1994 END;
1995 END;
1996 $$
1997 --error ER_PARSE_ERROR
1998 CREATE PACKAGE BODY p1 AS
1999 a INT;
2000 PROCEDURE p1 AS
2001 BEGIN
2002 NULL;
2003 END;
2004 b INT; -- Variables cannot go after routine definitions
2005 END;
2006 $$
2007 --error ER_VIEW_SELECT_VARIABLE
2008 CREATE PACKAGE BODY p1 AS
2009 a INT;
2010 PROCEDURE p1 AS
2011 BEGIN
2012 CREATE VIEW v1 AS SELECT a;
2013 END;
2014 END;
2015 $$
2016 CREATE PACKAGE BODY p1 AS
2017 a INT:=NULL;
2018 PROCEDURE p1 AS
2019 BEGIN
2020 SELECT a;
2021 a:=COALESCE(a,0)+100;
2022 SET a=a+1;
2023 END;
2024 FUNCTION f1 RETURN INT AS
2025 BEGIN
2026 RETURN a;
2027 END;
2028 END;
2029 $$
2030 DELIMITER ;$$
2031 CALL p1.p1;
2032 CALL p1.p1;
2033 CALL p1.p1;
2034 SELECT p1.f1();
2035 DROP PACKAGE p1;
2036
2037
2038 --echo #
2039 --echo # One package variable with a default value
2040 --echo #
2041
2042 DELIMITER $$;
2043 CREATE PACKAGE p1 AS
2044 PROCEDURE p1;
2045 FUNCTION f1 RETURN INT;
2046 END;
2047 $$
2048 CREATE PACKAGE BODY p1 AS
2049 a INT:=10;
2050 PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
2051 FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
2052 END;
2053 $$
2054 DELIMITER ;$$
2055 CALL p1.p1();
2056 CALL p1.p1();
2057 SELECT p1.f1();
2058 SELECT p1.f1();
2059 --source sp-cache-invalidate.inc
2060 SELECT p1.f1();
2061 CALL p1.p1();
2062 SELECT p1.f1();
2063 CALL p1.p1();
2064 DROP PACKAGE p1;
2065
2066
2067 DELIMITER $$;
2068 CREATE PACKAGE p1 AS
2069 PROCEDURE p1;
2070 FUNCTION f1 RETURN INT;
2071 END;
2072 $$
2073 CREATE PACKAGE BODY p1 AS
2074 a ROW (a INT, b TEXT):=ROW(10,'bbb');
2075 PROCEDURE p1 AS
2076 BEGIN
2077 a.a:= a.a+1;
2078 a.b:= a.b || 'B';
2079 SELECT a.a, a.b;
2080 END;
2081 FUNCTION f1 RETURN INT AS BEGIN a.a:= a.a+1; RETURN a.a; END;
2082 END;
2083 $$
2084 DELIMITER ;$$
2085 CALL p1.p1();
2086 CALL p1.p1();
2087 SELECT p1.f1();
2088 SELECT p1.f1();
2089 --source sp-cache-invalidate.inc
2090 SELECT p1.f1();
2091 CALL p1.p1();
2092 SELECT p1.f1();
2093 CALL p1.p1();
2094 DROP PACKAGE p1;
2095
2096
2097 CREATE TABLE t1 (a INT);
2098 DELIMITER $$;
2099 CREATE PACKAGE p1 AS
2100 PROCEDURE p1;
2101 FUNCTION f1 RETURN INT;
2102 END;
2103 $$
2104 CREATE PACKAGE BODY p1 AS
2105 a t1.a%TYPE:=10;
2106 PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
2107 FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
2108 END;
2109 $$
2110 DELIMITER ;$$
2111 CALL p1.p1();
2112 CALL p1.p1();
2113 SELECT p1.f1();
2114 SELECT p1.f1();
2115 --source sp-cache-invalidate.inc
2116 SELECT p1.f1();
2117 CALL p1.p1();
2118 SELECT p1.f1();
2119 CALL p1.p1();
2120 DROP PACKAGE p1;
2121 DROP TABLE t1;
2122
2123
2124 CREATE TABLE t1 (a INT, b TEXT);
2125 DELIMITER $$;
2126 CREATE PACKAGE p1 AS
2127 PROCEDURE p1;
2128 FUNCTION f1 RETURN INT;
2129 END;
2130 $$
2131 CREATE PACKAGE BODY p1 AS
2132 a t1%ROWTYPE:=ROW(10,'bbb');
2133 PROCEDURE p1 AS
2134 BEGIN
2135 a.a:= a.a+1;
2136 a.b:= a.b || 'B';
2137 SELECT a.a, a.b;
2138 END;
2139 FUNCTION f1 RETURN INT AS BEGIN a.a:= a.a+1; RETURN a.a; END;
2140 END;
2141 $$
2142 DELIMITER ;$$
2143 CALL p1.p1();
2144 CALL p1.p1();
2145 SELECT p1.f1();
2146 SELECT p1.f1();
2147 --source sp-cache-invalidate.inc
2148 SELECT p1.f1();
2149 CALL p1.p1();
2150 SELECT p1.f1();
2151 CALL p1.p1();
2152 DROP PACKAGE p1;
2153 DROP TABLE t1;
2154
2155
2156 --echo #
2157 --echo # One package variable, set in the package initialization section
2158 --echo #
2159
2160 DELIMITER $$;
2161 CREATE PACKAGE p1 AS
2162 PROCEDURE p1;
2163 FUNCTION f1 RETURN INT;
2164 END;
2165 $$
2166 CREATE PACKAGE BODY p1 AS
2167 a INT;
2168 PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
2169 FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
2170 BEGIN
2171 a:=10;
2172 END;
2173 $$
2174 DELIMITER ;$$
2175 CALL p1.p1();
2176 CALL p1.p1();
2177 SELECT p1.f1();
2178 SELECT p1.f1();
2179 --source sp-cache-invalidate.inc
2180 SELECT p1.f1();
2181 CALL p1.p1();
2182 SELECT p1.f1();
2183 CALL p1.p1();
2184 DROP PACKAGE p1;
2185
2186
2187 --echo #
2188 --echo # A package with an initialization section,
2189 --echo # loading table data into a package variable
2190 --echo #
2191
2192 CREATE TABLE t1 AS SELECT 10 AS a;
2193 DELIMITER $$;
2194 CREATE PACKAGE p1 AS
2195 PROCEDURE p1;
2196 FUNCTION f1 RETURN INT;
2197 END;
2198 $$
2199 CREATE PACKAGE BODY p1 AS
2200 a INT;
2201 PROCEDURE p1 AS BEGIN SET a=a+1; SELECT a; END;
2202 FUNCTION f1 RETURN INT AS BEGIN SET a=a+1; RETURN a; END;
2203 BEGIN
2204 a:=(SELECT MAX(t1.a) FROM t1);
2205 END;
2206 $$
2207 DELIMITER ;$$
2208 CALL p1.p1();
2209 CALL p1.p1();
2210 SELECT p1.f1();
2211 SELECT p1.f1();
2212 --source sp-cache-invalidate.inc
2213 SELECT p1.f1();
2214 DROP PACKAGE p1;
2215 DROP TABLE t1;
2216
2217 --echo #
2218 --echo # Package variables and XPath
2219 --echo #
2220
2221 DELIMITER $$;
2222 CREATE PACKAGE p1 AS
2223 FUNCTION f1 RETURN TEXT;
2224 END;
2225 $$
2226 CREATE PACKAGE BODY p1 AS
2227 i INT:=0;
2228 xml TEXT:= '<a><b>b1</b><b>b2</b><b>b3</b></a>';
2229 FUNCTION f1 RETURN TEXT AS
2230 BEGIN
2231 SET i=i+1;
2232 RETURN ExtractValue(xml, '/a/b[$i]');
2233 END;
2234 END;
2235 $$
2236 DELIMITER ;$$
2237 SELECT p1.f1();
2238 SELECT p1.f1();
2239 SELECT p1.f1();
2240 DROP PACKAGE p1;
2241
2242 --echo #
2243 --echo # Package variables as OUT routine parameter
2244 --echo #
2245
2246 DELIMITER $$;
2247 CREATE PACKAGE p1 AS
2248 PROCEDURE p1;
2249 END;
2250 $$
2251 CREATE PACKAGE BODY p1 AS
2252 a INT;
2253 b INT;
2254 c INT:=10;
2255 PROCEDURE p2(a OUT INT) AS
2256 BEGIN
2257 a:=c;
2258 c:=c+1;
2259 END;
2260 PROCEDURE p1 AS
2261 BEGIN
2262 CALL p2(b);
2263 SELECT a,b;
2264 END;
2265 BEGIN
2266 CALL p2(a);
2267 END;
2268 $$
2269 DELIMITER ;$$
2270 CALL p1.p1;
2271 DROP PACKAGE p1;
2272
2273
2274 DELIMITER $$;
2275 CREATE PACKAGE p1 AS
2276 PROCEDURE p1;
2277 END;
2278 $$
2279 CREATE PACKAGE BODY p1 AS
2280 a ROW(a INT, b TEXT);
2281 b ROW(a INT, b TEXT);
2282 c ROW(a INT, b TEXT):=ROW(1,'b');
2283 PROCEDURE p2(x OUT ROW(a INT,b TEXT)) AS
2284 BEGIN
2285 x:=c;
2286 x.a:=c.a+100;
2287 x.b:=c.b||'X';
2288 c.a:=c.a+1;
2289 c.b:=c.b||'B';
2290 END;
2291 PROCEDURE p1 AS
2292 BEGIN
2293 CALL p2(b);
2294 SELECT a.a,a.b,b.a,b.b;
2295 END;
2296 BEGIN
2297 CALL p2(a);
2298 END;
2299 $$
2300 DELIMITER ;$$
2301 CALL p1.p1;
2302 DROP PACKAGE p1;
2303
2304
2305 CREATE TABLE t1 (a INT,b TEXT);
2306 DELIMITER $$;
2307 CREATE PACKAGE p1 AS
2308 PROCEDURE p1;
2309 END;
2310 $$
2311 CREATE PACKAGE BODY p1 AS
2312 a t1%ROWTYPE;
2313 b t1%ROWTYPE;
2314 c t1%ROWTYPE:=ROW(1,'b');
2315 PROCEDURE p2(x OUT t1%ROWTYPE) AS
2316 BEGIN
2317 x:=c;
2318 x.a:=c.a+100;
2319 x.b:=c.b||'X';
2320 c.a:=c.a+1;
2321 c.b:=c.b||'B';
2322 END;
2323 PROCEDURE p1 AS
2324 BEGIN
2325 CALL p2(b);
2326 SELECT a.a,a.b,b.a,b.b;
2327 END;
2328 BEGIN
2329 CALL p2(a);
2330 END;
2331 $$
2332 DELIMITER ;$$
2333 CALL p1.p1;
2334 DROP PACKAGE p1;
2335 DROP TABLE t1;
2336
2337
2338 --echo #
2339 --echo # Package variable fields as OUT routine parameters
2340 --echo #
2341
2342 CREATE TABLE t1 (a INT,b TEXT);
2343 DELIMITER $$;
2344 CREATE PACKAGE p1 AS
2345 PROCEDURE p1;
2346 END;
2347 $$
2348 CREATE PACKAGE BODY p1 AS
2349 a t1%ROWTYPE;
2350 x t1%ROWTYPE:=ROW(10,'b');
2351 PROCEDURE p2(a OUT INT,b OUT TEXT) AS
2352 BEGIN
2353 a:=x.a;
2354 b:=x.b;
2355 x.a:=x.a+1;
2356 x.b:=x.b||'B';
2357 END;
2358 PROCEDURE p1 AS
2359 BEGIN
2360 CALL p2(a.a, a.b);
2361 SELECT a.a,a.b;
2362 END;
2363 BEGIN
2364 CALL p2(a.a, a.b);
2365 SELECT a.a, a.b;
2366 END;
2367 $$
2368 DELIMITER ;$$
2369 CALL p1.p1;
2370 DROP PACKAGE p1;
2371 DROP TABLE t1;
2372
2373
2374 --echo #
2375 --echo # Package variables as SELECT INTO targets
2376 --echo #
2377
2378 DELIMITER $$;
2379 CREATE PACKAGE p1 AS
2380 PROCEDURE p1;
2381 END;
2382 $$
2383 CREATE PACKAGE BODY p1 AS
2384 a INT;
2385 b INT;
2386 PROCEDURE p1 AS
2387 BEGIN
2388 SELECT 2 INTO b;
2389 SELECT a,b;
2390 END;
2391 BEGIN
2392 SELECT 1 INTO a;
2393 END;
2394 $$
2395 DELIMITER ;$$
2396 CALL p1.p1;
2397 DROP PACKAGE p1;
2398
2399
2400 CREATE TABLE t1 (a INT, b TEXT);
2401 INSERT INTO t1 VALUES (10,'b');
2402 DELIMITER $$;
2403 CREATE PACKAGE p1 AS
2404 PROCEDURE p1;
2405 END;
2406 $$
2407 CREATE PACKAGE BODY p1 AS
2408 a t1%ROWTYPE;
2409 b t1%ROWTYPE;
2410 PROCEDURE p1 AS
2411 BEGIN
2412 SELECT * FROM t1 INTO a;
2413 SELECT a.a,a.b;
2414 END;
2415 BEGIN
2416 SELECT * FROM t1 INTO b;
2417 SELECT b.a, b.b;
2418 END;
2419 $$
2420 DELIMITER ;$$
2421 CALL p1.p1;
2422 DROP PACKAGE p1;
2423 DROP TABLE t1;
2424
2425
2426 --echo #
2427 --echo # Package variable fields as SELECT INTO targets
2428 --echo #
2429
2430 DELIMITER $$;
2431 CREATE PACKAGE p1 AS
2432 PROCEDURE p1;
2433 END;
2434 $$
2435 CREATE PACKAGE BODY p1 AS
2436 a ROW(a INT, b TEXT);
2437 b ROW(a INT, b TEXT);
2438 PROCEDURE p1 AS
2439 BEGIN
2440 SELECT 20,'x2' INTO b.a,b.b;
2441 SELECT a.a,a.b,b.a,b.b;
2442 END;
2443 BEGIN
2444 SELECT 10,'x1' INTO a.a,a.b;
2445 END;
2446 $$
2447 DELIMITER ;$$
2448 CALL p1.p1;
2449 DROP PACKAGE p1;
2450
2451
2452 --echo #
2453 --echo # Recursive package procedure calls
2454 --echo # Makes sure that the non-top sp_head instances created by
2455 --echo # sp_clone_and_link_routine() correctly reproduce the package context:
2456 --echo # package variables, package routines.
2457 --echo #
2458
2459 DELIMITER $$;
2460 CREATE PACKAGE p1 AS
2461 PROCEDURE p1(c INT);
2462 END p1;
2463 $$
2464 CREATE PACKAGE BODY p1 AS
2465 pv1 INT:=10;
2466 FUNCTION f1 RETURN INT AS BEGIN RETURN pv1+100; END;
2467 PROCEDURE p1(c INT) AS
2468 BEGIN
2469 SELECT c, pv1, f1();
2470 IF c>0 THEN
2471 pv1:=pv1+1;
2472 CALL p1(c-1);
2473 END IF;
2474 END;
2475 END;
2476 $$
2477 DELIMITER ;$$
2478 SET max_sp_recursion_depth=5;
2479 CALL p1.p1(5);
2480 SET max_sp_recursion_depth=0;
2481 CALL p1.p1(0);
2482 --error ER_SP_RECURSION_LIMIT
2483 CALL p1.p1(1);
2484 DROP PACKAGE p1;
2485
2486
2487 --echo #
2488 --echo # Non-reserved keywords as package body variable names
2489 --echo #
2490
2491 DELIMITER $$;
2492 CREATE PACKAGE p1 AS
2493 PROCEDURE p1;
2494 END p1;
2495 $$
2496 CREATE PACKAGE BODY p1 AS
2497 ascii INT:=10;
2498 action INT:=20;
2499 PROCEDURE p1 AS
2500 BEGIN
2501 SELECT ascii, action;
2502 END;
2503 BEGIN
2504 ascii := ascii + 1;
2505 action := action + 1;
2506 END;
2507 $$
2508 DELIMITER ;$$
2509 CALL p1.p1;
2510 DROP PACKAGE p1;
2511
2512
2513 --echo #
2514 --echo # Package routines calling routines of another package
2515 --echo #
2516
2517 DELIMITER $$;
2518 CREATE PACKAGE p1 AS
2519 PROCEDURE p1;
2520 FUNCTION f1 RETURN TEXT;
2521 END;
2522 $$
2523 CREATE PACKAGE p2 AS
2524 PROCEDURE p1;
2525 FUNCTION f1 RETURN TEXT;
2526 END;
2527 $$
2528 CREATE PACKAGE BODY p1 AS
2529 PROCEDURE p1 AS
2530 BEGIN
2531 SELECT 'This is p1.p1' AS msg;
2532 END;
2533 FUNCTION f1 RETURN TEXT AS
2534 BEGIN
2535 RETURN 'This is p1.f1';
2536 END;
2537 END;
2538 $$
2539 CREATE PACKAGE BODY p2 AS
2540 PROCEDURE p1 AS
2541 BEGIN
2542 CALL p1.p1;
2543 END;
2544 FUNCTION f1 RETURN TEXT AS
2545 BEGIN
2546 RETURN p1.f1();
2547 END;
2548 END;
2549 $$
2550 DELIMITER ;$$
2551 CALL p1.p1;
2552 CALL p2.p1;
2553 SELECT p1.f1(), p2.f1();
2554 DROP PACKAGE p2;
2555 DROP PACKAGE p1;
2556
2557 --echo #
2558 --echo # Package names with dot characters
2559 --echo #
2560
2561 DELIMITER $$;
2562 CREATE PACKAGE "p1.p1" AS
2563 PROCEDURE p1;
2564 FUNCTION f1 RETURN TEXT;
2565 END;
2566 $$
2567 CREATE PACKAGE BODY "p1.p1" AS
2568 PROCEDURE p1 AS
2569 BEGIN
2570 SELECT 'This is p1' AS msg;
2571 END;
2572 FUNCTION f1 RETURN TEXT AS
2573 BEGIN
2574 RETURN 'This is f1';
2575 END;
2576 END;
2577 $$
2578 DELIMITER ;$$
2579 CALL "p1.p1"."p1";
2580 SELECT "p1.p1"."f1"();
2581 DROP PACKAGE "p1.p1";
2582
2583
2584 --echo #
2585 --echo # MDEV-15070 Crash when doing a CREATE VIEW inside a package routine
2586 --echo #
2587
2588 SET sql_mode=ORACLE;
2589 DELIMITER $$;
2590 CREATE OR REPLACE PACKAGE pkg1 AS
2591 PROCEDURE p00();
2592 END;
2593 $$
2594 CREATE OR REPLACE PACKAGE BODY pkg1 AS
2595 PROCEDURE p01() AS
2596 BEGIN
2597 SELECT 'This is p01' AS msg;
2598 END;
2599 PROCEDURE p00() AS
2600 BEGIN
2601 CREATE OR REPLACE VIEW v1 AS SELECT 1;
2602 DROP VIEW v1;
2603 CALL p01();
2604 END;
2605 END;
2606 $$
2607 DELIMITER ;$$
2608 CALL pkg1.p00;
2609 DROP PACKAGE pkg1;
2610
2611
2612 CREATE OR REPLACE TABLE t1 (a INT);
2613 CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
2614 DELIMITER $$;
2615 CREATE OR REPLACE PACKAGE pkg1 AS
2616 PROCEDURE p00();
2617 END;
2618 $$
2619 CREATE OR REPLACE PACKAGE BODY pkg1 AS
2620 PROCEDURE p01() AS
2621 BEGIN
2622 SELECT 'This is p01' AS msg;
2623 END;
2624 PROCEDURE p00() AS
2625 BEGIN
2626 DROP TRIGGER tr1;
2627 CALL p01();
2628 END;
2629 END;
2630 $$
2631 DELIMITER ;$$
2632 CALL pkg1.p00;
2633 DROP PACKAGE pkg1;
2634 DROP TABLE t1;
2635
2636
2637 --echo #
2638 --echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
2639 --echo #
2640
2641 CREATE TABLE t1 (
2642 CTR varchar(2) NOT NULL,
2643 COR varchar(3) NOT NULL,
2644 DATE datetime NOT NULL,
2645 CHAN varchar(4) NOT NULL,
2646 CNO varchar(20) NOT NULL,
2647 JOBN varchar(18) NOT NULL,
2648 C1 varchar(30) DEFAULT NULL,
2649 C2 varchar(30) DEFAULT NULL,
2650 TIME datetime DEFAULT NULL,
2651 AMT decimal(12,2) DEFAULT NULL,
2652 DT datetime NOT NULL,
2653 pk int(11) NOT NULL,
2654 PRIMARY KEY (pk),
2655 KEY Indx1 (JOBN)
2656 );
2657
2658 DELIMITER $$;
2659
2660 CREATE PACKAGE xyz IS
2661 PROCEDURE xyz123(ctr IN VARCHAR2,Jn IN VARCHAR2,R OUT VARCHAR2);
2662 END;
2663 $$
2664
2665 CREATE OR REPLACE PACKAGE BODY xyz IS
2666 PROCEDURE xyz123(
2667 ctr IN VARCHAR2,
2668 Jn IN VARCHAR2,
2669 R OUT VARCHAR2)
2670 AS
2671 lS NUMBER(10) :=0;
2672 CURSOR cBPD IS
2673 SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT
2674 FROM t1 WHERE JOBN=Jn;
2675 BEGIN
2676 FOR lbpd IN cBPD
2677 LOOP
2678 lS:=lS+1;
2679 END LOOP;
2680 EXCEPTION
2681 WHEN OTHERS THEN
2682 BEGIN
2683 SELECT SQLERRM;
2684 END;
2685 END;
2686 END $$
2687 DELIMITER ;$$
2688
2689 CALL xyz.xyz123(17,18,@R);
2690 DROP PACKAGE xyz;
2691 DROP TABLE t1;
2692 --disable_prepare_warnings
2693
2694
2695 --echo #
2696 --echo # MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func()
2697 --echo #
2698
2699 --error ER_WRONG_DB_NAME
2700 SELECT `db `.pkg.func();
2701 --error ER_SP_WRONG_NAME
2702 SELECT db.`pkg `.func();
2703 --error ER_SP_WRONG_NAME
2704 SELECT db.pkg.`func `();
2705
2706
2707 CREATE DATABASE db1;
2708 USE db1;
2709
2710 DELIMITER $$;
2711 CREATE PACKAGE pkg1 AS
2712 FUNCTION f1 RETURN TEXT;
2713 FUNCTION f2_db1_pkg1_f1 RETURN TEXT;
2714 FUNCTION f2_pkg1_f1 RETURN TEXT;
2715 FUNCTION f2_f1 RETURN TEXT;
2716 END;
2717 $$
2718 CREATE PACKAGE BODY pkg1
2719 AS
2720 FUNCTION f1 RETURN TEXT IS
2721 BEGIN
2722 RETURN 'This is db1.pkg1.f1';
2723 END;
2724 FUNCTION f2_db1_pkg1_f1 RETURN TEXT IS
2725 BEGIN
2726 RETURN db1.pkg1.f1();
2727 END;
2728 FUNCTION f2_pkg1_f1 RETURN TEXT IS
2729 BEGIN
2730 RETURN pkg1.f1();
2731 END;
2732 FUNCTION f2_f1 RETURN TEXT IS
2733 BEGIN
2734 RETURN f1();
2735 END;
2736 END;
2737 $$
2738 DELIMITER ;$$
2739
2740 USE db1;
2741 SELECT pkg1.f2_db1_pkg1_f1();
2742 SELECT pkg1.f2_pkg1_f1();
2743 SELECT pkg1.f2_f1();
2744
2745 SELECT db1.pkg1.f2_db1_pkg1_f1();
2746 SELECT db1.pkg1.f2_pkg1_f1();
2747 SELECT db1.pkg1.f2_f1();
2748
2749 USE test;
2750 SELECT db1.pkg1.f2_db1_pkg1_f1();
2751 SELECT db1.pkg1.f2_pkg1_f1();
2752 SELECT db1.pkg1.f2_f1();
2753
2754 DROP DATABASE db1;
2755
2756
2757 #
2758 # Testing db.pkg.func() in the package initialization section
2759 #
2760
2761 CREATE DATABASE db1;
2762 CREATE DATABASE db2;
2763
2764 DELIMITER $$;
2765 CREATE PACKAGE db1.pkg1 AS
2766 FUNCTION f1 RETURN TEXT;
2767 END;
2768 $$
2769 CREATE PACKAGE BODY db1.pkg1 AS
2770 FUNCTION f1 RETURN TEXT AS
2771 BEGIN
2772 RETURN 'This is db1.pkg1.f1';
2773 END;
2774 END;
2775 $$
2776 DELIMITER ;$$
2777
2778
2779 DELIMITER $$;
2780 CREATE PACKAGE db2.pkg1 AS
2781 FUNCTION f1 RETURN TEXT;
2782 FUNCTION var1 RETURN TEXT;
2783 FUNCTION var2 RETURN TEXT;
2784 END;
2785 $$
2786 CREATE PACKAGE BODY db2.pkg1 AS
2787 m_var1 TEXT;
2788 m_var2 TEXT;
2789 FUNCTION f1 RETURN TEXT AS
2790 BEGIN
2791 RETURN 'This is db2.pkg1.f1';
2792 END;
2793 FUNCTION var1 RETURN TEXT AS
2794 BEGIN
2795 RETURN m_var1;
2796 END;
2797 FUNCTION var2 RETURN TEXT AS
2798 BEGIN
2799 RETURN m_var2;
2800 END;
2801 BEGIN
2802 m_var1:= db1.pkg1.f1();
2803 m_var2:= db2.pkg1.f1();
2804 END;
2805 $$
2806 DELIMITER ;$$
2807
2808 SELECT db2.pkg1.var1(), db2.pkg1.var2();
2809
2810 DROP DATABASE db1;
2811 DROP DATABASE db2;
2812
2813 #
2814 # Make sure fully qualified package function call does not support AS syntax:
2815 # SELECT db.pkg.func(10 AS a);
2816 #
2817
2818 DELIMITER $$;
2819 CREATE PACKAGE pkg1 AS
2820 FUNCTION f1(a TEXT) RETURN TEXT;
2821 END;
2822 $$
2823 CREATE PACKAGE BODY pkg1 AS
2824 FUNCTION f1(a TEXT) RETURN TEXT AS
2825 BEGIN
2826 RETURN a;
2827 END;
2828 END;
2829 $$
2830 DELIMITER ;$$
2831 SELECT test.pkg1.f1('xxx');
2832 --error ER_PARSE_ERROR
2833 SELECT test.pkg1.f1('xxx' AS a);
2834 DROP PACKAGE pkg1;
2835
2836
2837 --echo #
2838 --echo # MDEV-19328 sql_mode=ORACLE: Package function in VIEW
2839 --echo #
2840
2841 SET sql_mode=ORACLE;
2842 DELIMITER $$;
2843 CREATE PACKAGE test1 AS
2844 FUNCTION f_test RETURN number;
2845 END test1;
2846 $$
2847 CREATE PACKAGE BODY test1
2848 AS
2849 FUNCTION f_test RETURN NUMBER IS
2850 BEGIN
2851 RETURN 1;
2852 END;
2853 END test1;
2854 $$
2855 DELIMITER ;$$
2856
2857 #
2858 # A VIEW created with sql_mode=ORACLE, calling a package routine
2859 #
2860 SET sql_mode=ORACLE;
2861 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
2862 SELECT * FROM v_test;
2863 --vertical_results
2864 SHOW CREATE VIEW v_test;
2865 --horizontal_results
2866 #
2867 # It also works with sql_mode=DEFALT
2868 #
2869 SET sql_mode=DEFAULT;
2870 SELECT * FROM v_test;
2871 --vertical_results
2872 SHOW CREATE VIEW v_test;
2873 --horizontal_results
2874 DROP VIEW v_test;
2875
2876 #
2877 # A VIEW created with sql_mode=DEFAULT, calling a package routine
2878 #
2879 SET sql_mode=DEFAULT;
2880 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
2881 SELECT * FROM v_test;
2882 --vertical_results
2883 SHOW CREATE VIEW v_test;
2884 #
2885 # It also works with sql_mode=ORACLE
2886 #
2887 --horizontal_results
2888 SET sql_mode=ORACLE;
2889 SELECT * FROM v_test;
2890 --vertical_results
2891 SHOW CREATE VIEW v_test;
2892 --horizontal_results
2893 DROP VIEW v_test;
2894
2895
2896 SET sql_mode=ORACLE;
2897 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
2898 SELECT * FROM v_test;
2899 --vertical_results
2900 SHOW CREATE VIEW v_test;
2901 --horizontal_results
2902 SET sql_mode=DEFAULT;
2903 SELECT * FROM v_test;
2904 --vertical_results
2905 SHOW CREATE VIEW v_test;
2906 --horizontal_results
2907 DROP VIEW v_test;
2908
2909
2910 SET sql_mode=DEFAULT;
2911 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
2912 SELECT * FROM v_test;
2913 --vertical_results
2914 SHOW CREATE VIEW v_test;
2915 --horizontal_results
2916 SET sql_mode=ORACLE;
2917 SELECT * FROM v_test;
2918 --vertical_results
2919 SHOW CREATE VIEW v_test;
2920 --horizontal_results
2921 DROP VIEW v_test;
2922
2923 SET sql_mode=ORACLE;
2924 DROP PACKAGE test1;
2925
2926
2927 --echo #
2928 --echo # MDEV-19804 sql_mode=ORACLE: call procedure in packages
2929 --echo #
2930
2931 --error ER_WRONG_DB_NAME
2932 CALL `db1 `.pkg.p;
2933 --error ER_SP_WRONG_NAME
2934 CALL db1.`pkg `.p;
2935 --error ER_SP_WRONG_NAME
2936 CALL db1.pkg.`p `;
2937
2938
2939 SET sql_mode=ORACLE;
2940 DELIMITER $$;
2941 CREATE PACKAGE pkg1 as
2942 PROCEDURE p1();
2943 END;
2944 $$
2945 CREATE PACKAGE BODY pkg1 as
2946 PROCEDURE p1() as
2947 BEGIN
2948 SELECT 'test-function' AS c1;
2949 END;
2950 END;
2951 $$
2952 DELIMITER ;$$
2953
2954 CALL pkg1.p1;
2955 CALL test.pkg1.p1;
2956
2957 # In sql_mode=DEFAULT we support fully qualified package function names
2958 # (this is needed for VIEWs). Let's make sure we also support fully
2959 # qualified package procedure names, for symmetry
2960
2961 SET sql_mode=DEFAULT;
2962 CALL test.pkg1.p1;
2963 SET sql_mode=ORACLE;
2964
2965 DELIMITER $$;
2966 BEGIN
2967 CALL pkg1.p1;
2968 CALL test.pkg1.p1;
2969 END
2970 $$
2971 DELIMITER ;$$
2972
2973 DELIMITER $$;
2974 BEGIN
2975 pkg1.p1;
2976 test.pkg1.p1;
2977 END
2978 $$
2979 DELIMITER ;$$
2980
2981 DROP PACKAGE pkg1;
2982
2983
2984 #
2985 # Testing packages in different databases calling each other
2986 # in routines and in the initialization section.
2987 #
2988
2989 CREATE DATABASE db1;
2990 DELIMITER $$;
2991 CREATE PACKAGE db1.pkg1 AS
2992 PROCEDURE p1(a OUT TEXT);
2993 END;
2994 $$
2995 CREATE PACKAGE BODY db1.pkg1 AS
2996 PROCEDURE p1(a OUT TEXT) AS
2997 BEGIN
2998 a:= 'This is db1.pkg1.p1';
2999 END;
3000 END;
3001 $$
3002 DELIMITER ;$$
3003
3004 CREATE DATABASE db2;
3005 DELIMITER $$;
3006 CREATE PACKAGE db2.pkg1 AS
3007 FUNCTION var1 RETURN TEXT;
3008 PROCEDURE p1(a OUT TEXT);
3009 PROCEDURE p2_db1_pkg1_p1;
3010 END;
3011 $$
3012 CREATE PACKAGE BODY db2.pkg1 AS
3013 m_var1 TEXT;
3014 FUNCTION var1 RETURN TEXT AS
3015 BEGIN
3016 RETURN m_var1;
3017 END;
3018 PROCEDURE p1(a OUT TEXT) AS
3019 BEGIN
3020 a:= 'This is db2.pkg1.p1';
3021 END;
3022 PROCEDURE p2_db1_pkg1_p1 AS
3023 a TEXT;
3024 BEGIN
3025 db1.pkg1.p1(a);
3026 SELECT a;
3027 END;
3028 BEGIN
3029 db1.pkg1.p1(m_var1);
3030 END;
3031 $$
3032 DELIMITER ;$$
3033
3034 SELECT db2.pkg1.var1();
3035 CALL db2.pkg1.p2_db1_pkg1_p1;
3036 --enable_ps2_protocol
3037
3038 DROP DATABASE db1;
3039 DROP DATABASE db2;
3040
3041
3042 --echo #
3043 --echo # MDEV-29370 Functions in packages are slow and seems to ignore deterministic
3044 --echo #
3045
3046 SET SQL_MODE=ORACLE;
3047
3048 CREATE TABLE t1 (c1 CHAR(1));
3049
3050 DELIMITER //;
3051 CREATE FUNCTION f1_deterministic()
3052 RETURN CHAR(1)
3053 DETERMINISTIC
3054 IS
3055 BEGIN
3056 RETURN 'X';
3057 END;
3058 //
3059
3060 CREATE FUNCTION f2_not_deterministic()
3061 RETURN CHAR(1)
3062 IS
3063 BEGIN
3064 RETURN 'X';
3065 END;
3066 //
3067
3068 CREATE PACKAGE pkg1
3069 IS
3070 PROCEDURE t1_populate(numrows INTEGER);
3071 FUNCTION f3_deterministic() RETURN CHAR(1) DETERMINISTIC;
3072 FUNCTION f4_not_deterministic() RETURN CHAR(1);
3073 END;
3074 //
3075
3076 CREATE PACKAGE BODY pkg1
3077 IS
3078 PROCEDURE t1_populate(numrounds INTEGER)
3079 IS
3080 i INTEGER;
3081 BEGIN
3082 INSERT INTO t1 VALUES('Y');
3083 FOR i IN 1..numrounds LOOP
3084 INSERT INTO t1 SELECT * FROM t1;
3085 END LOOP;
3086 END;
3087 FUNCTION f3_deterministic() RETURN CHAR(1) DETERMINISTIC COMMENT 'xxx'
3088 IS
3089 BEGIN
3090 RETURN 'X';
3091 END;
3092 FUNCTION f4_not_deterministic() RETURN CHAR(1)
3093 IS
3094 BEGIN
3095 RETURN 'X';
3096 END;
3097 END;
3098 //
3099 DELIMITER ;//
3100
3101 CALL pkg1.t1_populate(3);
3102
3103 EXPLAIN EXTENDED SELECT 'Deterministic function', COUNT(*) FROM t1 WHERE c1 = f1_deterministic();
3104 EXPLAIN EXTENDED SELECT 'Non-deterministic function', COUNT(*) FROM t1 WHERE c1 = f2_not_deterministic();
3105 EXPLAIN EXTENDED SELECT 'Deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f3_deterministic();
3106 EXPLAIN EXTENDED SELECT 'Non-deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f4_not_deterministic();
3107
3108 DROP TABLE t1;
3109 DROP FUNCTION f1_deterministic;
3110 DROP FUNCTION f2_not_deterministic;
3111 DROP PACKAGE pkg1;
3112
3113 --echo #
3114 --echo # Start of 11.7 tests
3115 --echo #
3116
3117 --echo #
3118 --echo # MDEV-12252 ROW data type for stored function return values
3119 --echo #
3120
3121 --echo #
3122 --echo # Testing fixed ROW type with package routines
3123 --echo #
3124
3125 DELIMITER $$;
3126 CREATE PACKAGE pkg
3127 AS
3128 FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32));
3129 PROCEDURE p1(r ROW(a INT, b VARCHAR(32)));
3130 PROCEDURE p2();
3131 END;
3132 $$
3133 CREATE PACKAGE BODY pkg
3134 AS
3135 FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32)) AS
3136 BEGIN
3137 RETURN ROW(1,'b1');
3138 END;
3139 PROCEDURE p1(r ROW(a INT, b VARCHAR(32))) AS
3140 BEGIN
3141 SELECT r.a, r.b;
3142 END;
3143 PROCEDURE p2() AS
3144 BEGIN
3145 CALL p1(f1());
3146 END;
3147 END;
3148 $$
3149 DELIMITER ;$$
3150 CALL pkg.p1(pkg.f1());
3151 CALL pkg.p2;
3152 DROP PACKAGE pkg;
3153
3154
3155 --echo #
3156 --echo # Testing table%ROWTYPE with package routines
3157 --echo #
3158
3159 CREATE TABLE t1 (a INT, b VARCHAR(32));
3160 INSERT INTO t1 VALUES (1,'b1');
3161 DELIMITER /;
3162 CREATE PACKAGE pkg
3163 AS
3164 FUNCTION f1 RETURN t1%ROWTYPE;
3165 PROCEDURE p1(r t1%ROWTYPE);
3166 PROCEDURE p2;
3167 END;
3168 /
3169 CREATE PACKAGE BODY pkg
3170 AS
3171 FUNCTION f1 RETURN t1%ROWTYPE AS
3172 r t1%ROWTYPE;
3173 BEGIN
3174 SELECT * INTO r FROM t1;
3175 RETURN r;
3176 END;
3177 PROCEDURE p1(r t1%ROWTYPE) AS
3178 BEGIN
3179 SELECT r.a || ' ' || r.b;
3180 END;
3181 PROCEDURE p2 AS
3182 BEGIN
3183 p1(f1());
3184 END;
3185 END;
3186 /
3187 DELIMITER ;/
3188 CALL pkg.p1(pkg.f1());
3189 CALL pkg.p2;
3190 DROP PACKAGE pkg;
3191 DROP TABLE t1;
3192
3193 --echo #
3194 --echo # End of 11.7 tests
3195 --echo #