Debian Codegraph @

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

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