Debian Codegraph @

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

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