diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/compat/oracle/r/sp-inout.result | |
parent | Initial commit. (diff) | |
download | mariadb-upstream.tar.xz mariadb-upstream.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/compat/oracle/r/sp-inout.result')
-rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-inout.result | 2571 |
1 files changed, 2571 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/r/sp-inout.result b/mysql-test/suite/compat/oracle/r/sp-inout.result new file mode 100644 index 00000000..fa6f5076 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-inout.result @@ -0,0 +1,2571 @@ +# +# MDEV-10654 IN, OUT, INOUT parameters in CREATE FUNCTION +# +SET sql_mode=ORACLE; +# +# CREATE PACKAGE with procedure and function with IN, OUT, INOUT qualifiers +# And SHOW CREATE PACKAGE +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c INOUT INT, d OUT INT); +FUNCTION func_sub(d OUT INT, a IN INT, b IN INT, c INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c INOUT INT, d OUT INT) +AS +res INT; +BEGIN +res := func_sub(d, a, b, c); +d := d + c + res; +END; +FUNCTION func_sub(d OUT INT, a IN INT, b IN INT, c INOUT INT) RETURN INT +AS +BEGIN +c := c + 6; +d := 10; +RETURN a - b; +END; +END; +$$ +SHOW CREATE PACKAGE pkg2; +Package sql_mode Create Package character_set_client collation_connection Database Collation +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 +PROCEDURE proc_main(a IN INT, b IN INT, c INOUT INT, d OUT INT); +FUNCTION func_sub(d OUT INT, a IN INT, b IN INT, c INOUT INT) RETURN INT; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE BODY pkg2; +Package body sql_mode Create Package Body character_set_client collation_connection Database Collation +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 +PROCEDURE proc_main(a IN INT, b IN INT, c INOUT INT, d OUT INT) +AS +res INT; +BEGIN +res := func_sub(d, a, b, c); +d := d + c + res; +END; +FUNCTION func_sub(d OUT INT, a IN INT, b IN INT, c INOUT INT) RETURN INT +AS +BEGIN +c := c + 6; +d := 10; +RETURN a - b; +END; +END latin1 latin1_swedish_ci latin1_swedish_ci +DROP PACKAGE pkg2; +# +# CREATE FUNCTION with IN, OUT, INOUT qualifiers +# SHOW CREATE FUNCTION +# +CREATE OR REPLACE FUNCTION add_func(a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT +AS +BEGIN +c := 100; +d := d + 1; +RETURN a + b; +END; +$$ +SHOW CREATE FUNCTION add_func; +Function sql_mode Create Function character_set_client collation_connection Database Collation +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) +AS +BEGIN +c := 100; +d := d + 1; +RETURN a + b; +END latin1 latin1_swedish_ci latin1_swedish_ci +DROP FUNCTION add_func; +# +# CREATE PROCEDURE with IN, OUT, INOUT qualifiers +# SHOW CREATE PROCEDURE +# +CREATE OR REPLACE PROCEDURE add_proc(a IN INT, b IN INT, c INOUT INT, d OUT INT) +AS +BEGIN +d := a + b + c + d; +END; +$$ +SHOW CREATE PROCEDURE add_proc; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +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) +AS +BEGIN +d := a + b + c + d; +END latin1 latin1_swedish_ci latin1_swedish_ci +DROP PROCEDURE add_proc; +# +# Call function from SELECT query +# SELECT > FUNCTION(IN) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION add_func2 (a IN INT, b IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION add_func2(a IN INT, b IN INT) RETURN INT +AS +BEGIN +RETURN a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +select pkg2.add_func2(@a, @b); +pkg2.add_func2(@a, @b) +5 +DROP PACKAGE pkg2; +# +# Call function from SELECT query +# SELECT > FUNCTION(OUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION add_func3 (a IN INT, b IN INT, c OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION add_func3(a IN INT, b IN INT, c OUT INT) RETURN INT +AS +BEGIN +c := 100; +RETURN a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @c = 0; +select pkg2.add_func3(@a, @b, @c); +ERROR HY000: OUT or INOUT argument 3 for function pkg2.add_func3 is not allowed here +DROP PACKAGE pkg2; +# +# Call function from SELECT query +# SELECT > FUNCTION(INOUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION add_func4 (a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION add_func4(a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT +AS +BEGIN +c := 100; +d := d + 1; +RETURN a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @c = 0; +set @d = 9; +select pkg2.add_func4(@a, @b, @c, @d); +ERROR HY000: OUT or INOUT argument 3 for function pkg2.add_func4 is not allowed here +DROP PACKAGE pkg2; +# +# Call from procedure +# PROCEDURE(OUT) > FUNCTION(IN) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE add_proc2 (a IN INT, b IN INT, c OUT INT); +FUNCTION add_func2 (a IN INT, b IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE add_proc2(a IN INT, b IN INT, c OUT INT) +AS +BEGIN +c := add_func2(a, b); +END; +FUNCTION add_func2(a IN INT, b IN INT) RETURN INT +AS +BEGIN +RETURN a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.add_proc2(@a, @b, @c); +select @c; +@c +5 +DROP PACKAGE pkg2; +# +# Call from procedure +# PROCEDURE(OUT) > FUNCTION(OUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE add_proc3 (a IN INT, b IN INT, c OUT INT); +FUNCTION add_func3 (a IN INT, b IN INT, c OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE add_proc3(a IN INT, b IN INT, c OUT INT) +AS +res INT; +BEGIN +res := add_func3(a, b, c); +END; +FUNCTION add_func3(a IN INT, b IN INT, c OUT INT) RETURN INT +AS +BEGIN +c := 100; +RETURN a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.add_proc3(@a, @b, @c); +select @c; +@c +100 +DROP PACKAGE pkg2; +# +# Call from procedure +# PROCEDURE(OUT) > FUNCTION(INOUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE add_proc4 (a IN INT, b IN INT, c OUT INT); +FUNCTION add_func4 (a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE add_proc4(a IN INT, b IN INT, res OUT INT) +AS +c INT; +d INT; +BEGIN +d := 30; +res := add_func4(a, b, c, d); +res := c + d; +END; +FUNCTION add_func4(a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT +AS +BEGIN +c := 100; +d := d + 1; +RETURN a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @res = 0; +call pkg2.add_proc4(@a, @b, @res); +select @res; +@res +131 +DROP PACKAGE pkg2; +# +# Call from procedure +# PROCEDURE(OUT) > PROCEDURE(OUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE test_proc1 (a IN INT, b IN INT, c OUT INT); +PROCEDURE add_proc (a IN INT, b IN INT, c OUT INT); +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE test_proc1(a IN INT, b IN INT, c OUT INT) +AS +BEGIN +call pkg2.add_proc(a, b, c); +END; +PROCEDURE add_proc(a IN INT, b IN INT, c OUT INT) +AS +BEGIN +c := a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.test_proc1(@a, @b, @c); +select @c; +@c +5 +DROP PACKAGE pkg2; +# +# Argument's order change +# PROCEDURE(a IN, b IN, c OUT) > FUNCTION(b IN, a IN, c OUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c OUT INT); +FUNCTION func_sub(b IN INT, a IN INT, c OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c OUT INT) +AS +res INT; +BEGIN +res := func_sub(b, a, c); +END; +FUNCTION func_sub(b IN INT, a IN INT, c OUT INT) RETURN INT +AS +res INT; +BEGIN +c := a - b; +res := a; +RETURN res; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.proc_main(@a, @b, @c); +select @c; +@c +-1 +DROP PACKAGE pkg2; +# +# Argument's order change +# PROCEDURE(a IN, b IN, c OUT) > FUNCTION(c OUT, b IN, a IN) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c OUT INT); +FUNCTION func_sub(c OUT INT, b IN INT, a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c OUT INT) +AS +res INT; +BEGIN +res := func_sub(c, b, a); +END; +FUNCTION func_sub(c OUT INT, b IN INT, a IN INT) RETURN INT +AS +res INT; +BEGIN +c := a - b; +res := a; +RETURN res; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.proc_main(@a, @b, @c); +select @c; +@c +-1 +DROP PACKAGE pkg2; +# +# Argument's order change +# PROCEDURE(a IN, b IN, c INOUT, d OUT) > FUNCTION(d OUT, a IN, b IN, c INOUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c INOUT INT, d OUT INT); +FUNCTION func_sub(d OUT INT, a IN INT, b IN INT, c INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c INOUT INT, d OUT INT) +AS +res INT; +BEGIN +res := func_sub(d, a, b, c); +d := d + c + res; +END; +FUNCTION func_sub(d OUT INT, a IN INT, b IN INT, c INOUT INT) RETURN INT +AS +BEGIN +c := c + 6; +d := 10; +RETURN a - b; +END; +END; +$$ +set @a = 15; +set @b = 5; +set @c = 4; +set @d= 0; +call pkg2.proc_main(@a, @b, @c, @d); +select @d; +@d +30 +DROP PACKAGE pkg2; +# +# Argument's order change +# 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) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c INOUT INT, d OUT INT); +FUNCTION func_sub1(c INOUT INT, b IN INT) RETURN INT; +FUNCTION func_sub2(d OUT INT, a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b IN INT, c INOUT INT, d OUT INT) +AS +res1 INT; +res2 INT; +BEGIN +res1 := func_sub1(c, b); +res2 := func_sub2(d, a); +d := d + c; +END; +FUNCTION func_sub1(c INOUT INT, b IN INT) RETURN INT +AS +BEGIN +c := c + b; +RETURN 0; +END; +FUNCTION func_sub2(d OUT INT, a IN INT) RETURN INT +AS +BEGIN +d := 5 + a; +RETURN 0; +END; +END; +$$ +set @a = 15; +set @b = 6; +set @c = 4; +set @d= 0; +call pkg2.proc_main(@a, @b, @c, @d); +select @d; +@d +30 +DROP PACKAGE pkg2; +# +# Argument's order change +# FUNCTION1(a IN, b IN) > FUNCTION2(b IN, c OUT, a IN) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT, b IN INT) RETURN INT; +FUNCTION func_sub(b IN INT, c OUT INT, a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT, b IN INT) RETURN INT +AS +c INT; +res INT; +BEGIN +res := func_sub(b, c, a); +RETURN res + c; +END; +FUNCTION func_sub(b IN INT, c OUT INT, a IN INT) RETURN INT +AS +BEGIN +c := 100; +RETURN a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +select pkg2.func_main(@a, @b); +pkg2.func_main(@a, @b) +105 +DROP PACKAGE pkg2; +# +# Call procedure inside function +# FUNCTION1(a IN, b IN) > PROCEDURE(a IN, b IN, c OUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(b IN INT, a IN INT) RETURN INT; +PROCEDURE proc_sub(a IN INT, b IN INT, c OUT INT); +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(b IN INT, a IN INT) RETURN INT +AS +c INT; +BEGIN +call proc_sub(a, b, c); +RETURN c; +END; +PROCEDURE proc_sub(a IN INT, b IN INT, c OUT INT) +AS +BEGIN +c := a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +select pkg2.func_main(@a, @b); +pkg2.func_main(@a, @b) +5 +DROP PACKAGE pkg2; +# +# Call procedure inside function +# FUNCTION1(a IN, b IN) > PROCEDURE(a IN, b INOUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(b IN INT, a IN INT) RETURN INT; +PROCEDURE proc_sub(a IN INT, b INOUT INT); +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(b IN INT, a IN INT) RETURN INT +AS +BEGIN +call proc_sub(a, b); +RETURN b; +END; +PROCEDURE proc_sub(a IN INT, b INOUT INT) +AS +BEGIN +b := a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +select pkg2.func_main(@a, @b); +pkg2.func_main(@a, @b) +5 +DROP PACKAGE pkg2; +# +# Call procedure inside function +# FUNCTION1(a IN, b IN, c OUT) > PROCEDURE(a IN, b IN, c OUT) +# +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(b IN INT, a IN INT, c OUT INT) RETURN INT; +PROCEDURE proc_sub(a IN INT, b IN INT, c OUT INT); +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(b IN INT, a IN INT, c OUT INT) RETURN INT +AS +res INT; +BEGIN +call proc_sub(a, b, c); +RETURN 0; +END; +PROCEDURE proc_sub(a IN INT, b IN INT, c OUT INT) +AS +BEGIN +c := a + b; +END; +END; +$$ +set @a = 2; +set @b = 3; +set @c = 0; +select pkg2.func_main(@a, @b, @c); +ERROR HY000: OUT or INOUT argument 3 for function pkg2.func_main is not allowed here +DROP PACKAGE pkg2; +# +# Call function from UPDATE query +# UPDATE <table> SET <column> = FUNCTION(a IN) +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a IN INT) RETURN INT +AS +BEGIN +RETURN a * 10; +END; +END; +$$ +set @a = 5; +UPDATE Persons SET Age = pkg2.func(@a) WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# Call function from UPDATE query +# UPDATE <table> SET <column> = FUNCTION(a OUT) +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a OUT INT) RETURN INT +AS +BEGIN +a := 5; +RETURN 80; +END; +END; +$$ +set @a = 0; +UPDATE Persons SET Age = pkg2.func(@a) WHERE ID = 1; +ERROR HY000: OUT or INOUT argument 1 for function pkg2.func is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# Call function from INSERT query +# INSERT INTO <table> SELECT <val1>, <val2>, FUNCTION(a IN) +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a IN INT) RETURN INT +AS +BEGIN +RETURN a * 10; +END; +END; +$$ +set @a = 4; +INSERT INTO Persons SELECT 4, 'DDD', PKG2.func(@a); +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# Call function from INSERT query +# INSERT INTO <table> SELECT <val1>, <val2>, FUNCTION(a OUT) +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a OUT INT) RETURN INT +AS +BEGIN +a := 45; +RETURN 40; +END; +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +set @a = 0; +INSERT INTO Persons SELECT 5, 'EEE', PKG2.func(@a); +ERROR HY000: OUT or INOUT argument 1 for function PKG2.func is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# Call function from DELETE query +# DELETE FROM <table> WHERE <column> = FUNCTION(a IN) +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a IN INT) RETURN INT +AS +BEGIN +RETURN a; +END; +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +set @a = 4; +DELETE FROM Persons WHERE ID = PKG2.func(@a); +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# Call function from DELETE query +# DELETE FROM <table> WHERE <column> = FUNCTION(a OUT) +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a OUT INT) RETURN INT +AS +BEGIN +a := 40; +RETURN 4; +END; +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +set @a = 0; +DELETE FROM Persons WHERE ID = PKG2.func(@a); +ERROR HY000: OUT or INOUT argument 1 for function PKG2.func is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# SELECT query inside function +# FUNCTION(a IN) > SELECT … FROM <table> +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT +AS +c INT; +BEGIN +SELECT AGE INTO c FROM Persons WHERE ID = a; +RETURN c; +END; +END; +$$ +set @a = 3; +select pkg2.func_main(@a); +pkg2.func_main(@a) +30 +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# SELECT query inside function +# FUNCTION(a OUT) > SELECT … FROM <table> +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a OUT INT) RETURN INT +AS +BEGIN +SELECT AGE INTO a FROM Persons WHERE ID = 3; +RETURN 0; +END; +END; +$$ +set @a = 0; +select pkg2.func_main(@a); +ERROR HY000: OUT or INOUT argument 1 for function pkg2.func_main is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# SELECT query inside function +# FUNCTION(a INOUT) > SELECT … FROM <table> +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a INOUT INT) RETURN INT +AS +BEGIN +SELECT AGE INTO a FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +set @a = 1; +select pkg2.func_main(@a); +ERROR HY000: OUT or INOUT argument 1 for function pkg2.func_main is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# SELECT query inside function +# FUNCTION(a IN) > FUNCTION(a IN, b OUT) > SELECT … FROM <table> +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT +AS +b INT; +res INT; +BEGIN +res := func_sub(a, b); +RETURN b; +END; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT +AS +BEGIN +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +set @a = 2; +select pkg2.func_main(@a); +pkg2.func_main(@a) +20 +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# UPDATE query inside function +# FUNCTION(a IN) > UPDATE <table> SET … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'EEE', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT +AS +c INT; +BEGIN +UPDATE Persons SET AGE = 50 WHERE ID = a; +SELECT AGE INTO c FROM Persons WHERE ID = a; +RETURN c; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 40 +set @a = 5; +select pkg2.func_main(@a); +pkg2.func_main(@a) +50 +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 50 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# UPDATE query inside function +# FUNCTION(a IN, b OUT) > UPDATE <table> SET … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'EEE', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT, b OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT, b OUT INT) RETURN INT +AS +BEGIN +UPDATE Persons SET AGE = 60 WHERE ID = a; +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +set @a = 5; +set @b = 0; +select pkg2.func_main(@a, @b); +ERROR HY000: OUT or INOUT argument 2 for function pkg2.func_main is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# UPDATE query inside function +# FUNCTION(a IN, b INOUT) > UPDATE <table> SET … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'EEE', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT, b INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT, b INOUT INT) RETURN INT +AS +BEGIN +UPDATE Persons SET AGE = 60 WHERE ID = a; +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +set @a = 5; +set @b = 0; +select pkg2.func_main(@a, @b); +ERROR HY000: OUT or INOUT argument 2 for function pkg2.func_main is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# UPDATE query inside function +# FUNCTION(a IN) > FUNCTION(a IN, b OUT) > UPDATE <table> SET … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 80); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'EEE', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT +AS +b INT; +res INT; +BEGIN +res := func_sub(a, b); +RETURN b; +END; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT +AS +BEGIN +UPDATE Persons SET AGE = 10 WHERE ID = a; +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 80 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 40 +set @a = 1; +select pkg2.func_main(@a); +pkg2.func_main(@a) +10 +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 40 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# INSERT query inside function +# FUNCTION(a IN) > INSERT INTO <table> VALUES … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'EEE', 50); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT +AS +b INT; +BEGIN +INSERT INTO Persons VALUE (a, 'FFF', 60); +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN b; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 50 +set @a = 6; +select pkg2.func_main(@a); +pkg2.func_main(@a) +60 +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 50 +6 FFF 60 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# INSERT query inside function +# FUNCTION(a IN, b OUT) > INSERT INTO <table> VALUES … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'EEE', 50); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT, b OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT, b OUT INT) RETURN INT +AS +BEGIN +INSERT INTO Persons VALUE (a, 'FFF', 60); +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 50 +set @a = 6; +set @b = 0; +select pkg2.func_main(@a, @b); +ERROR HY000: OUT or INOUT argument 2 for function pkg2.func_main is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# INSERT query inside function +# FUNCTION(a IN, b INOUT) > INSERT INTO <table> VALUES … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'EEE', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT, b INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT, b INOUT INT) RETURN INT +AS +BEGIN +INSERT INTO Persons VALUE (a, 'FFF', 60); +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 40 +set @a = 6; +set @b = 0; +select pkg2.func_main(@a, @b); +ERROR HY000: OUT or INOUT argument 2 for function pkg2.func_main is not allowed here +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# INSERT query inside function +# FUNCTION(a IN) > FUNCTION(a IN, b OUT) > INSERT INTO <table> VALUES … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'EEE', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func_main(a IN INT) RETURN INT +AS +b INT; +res INT; +BEGIN +res := func_sub(a, b); +RETURN b; +END; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT +AS +BEGIN +INSERT INTO Persons VALUE (a, 'FFF', 60); +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 40 +set @a = 6; +select pkg2.func_main(@a); +pkg2.func_main(@a) +60 +select * from Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 EEE 40 +6 FFF 60 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(IN) > SELECT FROM <table> … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +BEGIN +b := func_sub(a); +END; +FUNCTION func_sub(a IN INT) RETURN INT +AS +b INT; +BEGIN +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN b; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +set @a = 2; +set @b = 0; +call pkg2.proc_main(@a, @b); +select @b; +@b +20 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(OUT) > SELECT FROM <table> … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +res INT; +BEGIN +res := func_sub(a, b); +END; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT +AS +BEGIN +SELECT AGE INTO b FROM Persons WHERE ID = a; +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +set @a = 1; +set @b = 0; +call pkg2.proc_main(@a, @b); +select @b; +@b +50 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(INOUT) > SELECT FROM <table> … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT, b INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +c INT; +res INT; +BEGIN +c := 5; +res := func_sub(a, c); +b := c; +END; +FUNCTION func_sub(a IN INT, c INOUT INT) RETURN INT +AS +res INT; +BEGIN +SELECT AGE INTO res FROM Persons WHERE ID = a; +c := c * 100; +RETURN res; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +set @a = 2; +set @b = 0; +call pkg2.proc_main(@a, @b); +select @b; +@b +500 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(IN) > INSESRT INTO <table> … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +BEGIN +b := func_sub(a); +END; +FUNCTION func_sub(a IN INT) RETURN INT +AS +BEGIN +INSERT INTO Persons VALUE (a, 'FFF', 50); +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +set @a = 5; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 50 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(OUT) > INSESRT INTO <table> … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'FFF', 50); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +res INT; +BEGIN +res := func_sub(a, b); +END; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT +AS +BEGIN +INSERT INTO Persons VALUE (a, 'GGG', 60); +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 50 +set @a = 6; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 50 +6 GGG 60 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(INOUT) > INSESRT INTO <table> … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'FFF', 50); +INSERT INTO Persons VALUES (6, 'GGG', 60); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT, b INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +c INT; +res INT; +BEGIN +c := 5; +res := func_sub(a, c); +b := c; +END; +FUNCTION func_sub(a IN INT, c INOUT INT) RETURN INT +AS +res INT; +BEGIN +INSERT INTO Persons VALUE (a, 'HHH', 70); +c := c * 100; +RETURN res; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 50 +6 GGG 60 +set @a = 7; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 50 +6 GGG 60 +7 HHH 70 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(IN) > UPDATE <table> SET … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'FFF', 50); +INSERT INTO Persons VALUES (6, 'GGG', 60); +INSERT INTO Persons VALUES (7, 'HHH', 70); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +BEGIN +b := func_sub(a); +END; +FUNCTION func_sub(a IN INT) RETURN INT +AS +BEGIN +UPDATE Persons SET AGE = 100 WHERE ID = a; +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 50 +6 GGG 60 +7 HHH 70 +set @a = 5; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 100 +6 GGG 60 +7 HHH 70 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(OUT) > UPDATE <table> SET … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'FFF', 100); +INSERT INTO Persons VALUES (6, 'GGG', 60); +INSERT INTO Persons VALUES (7, 'HHH', 70); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +res INT; +BEGIN +res := func_sub(a, b); +END; +FUNCTION func_sub(a IN INT, b OUT INT) RETURN INT +AS +BEGIN +UPDATE Persons SET AGE = 100 WHERE ID = a; +b := 1; +RETURN 0; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 100 +6 GGG 60 +7 HHH 70 +set @a = 6; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 100 +6 GGG 100 +7 HHH 70 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# PROCEDURE > FUNCTION > SQL query +# PROCEDURE(OUT) > FUNCTION(INOUT) > UPDATE <table> SET … +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +INSERT INTO Persons VALUES (4, 'DDD', 40); +INSERT INTO Persons VALUES (5, 'FFF', 100); +INSERT INTO Persons VALUES (6, 'GGG', 100); +INSERT INTO Persons VALUES (7, 'HHH', 70); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT); +FUNCTION func_sub(a IN INT, b INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc_main(a IN INT, b OUT INT) +AS +c INT; +res INT; +BEGIN +c := 5; +res := func_sub(a, c); +b := c; +END; +FUNCTION func_sub(a IN INT, c INOUT INT) RETURN INT +AS +res INT; +BEGIN +UPDATE Persons SET AGE = 100 WHERE ID = a; +c := c * 100; +RETURN res; +END; +END; +$$ +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 100 +6 GGG 100 +7 HHH 70 +set @a = 7; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +4 DDD 40 +5 FFF 100 +6 GGG 100 +7 HHH 100 +DROP TABLE Persons; +DROP PACKAGE pkg2; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 20 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 20 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > FUNCTION(IN) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a IN INT) RETURN INT +AS +BEGIN +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +RETURN 0; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +DECLARE +a INT; +res INT; +BEGIN +a := 10; +res := 0; +res := pkg2.func(a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 30 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 30 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > FUNCTION(OUT) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 40); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a OUT INT) RETURN INT +AS +BEGIN +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +a := 100; +RETURN 0; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +DECLARE +a INT; +res INT; +BEGIN +a := 10; +res := 0; +res := pkg2.func(a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 40 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 50 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > FUNCTION(INOUT) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 50); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +FUNCTION func(a INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +FUNCTION func(a INOUT INT) RETURN INT +AS +BEGIN +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +a := 100; +RETURN 0; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +DECLARE +a INT; +res INT; +BEGIN +a := 10; +res := 0; +res := pkg2.func(a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 60 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 60 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(IN) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc(a IN INT); +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc(a IN INT) +AS +BEGIN +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +BEGIN +call pkg2.proc(@a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 30 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 30 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc(a OUT INT); +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc(a OUT INT) +AS +BEGIN +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +BEGIN +call pkg2.proc(@a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 50 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(INOUT) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc(a INOUT INT); +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc(a INOUT INT) +AS +BEGIN +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +a := 100; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +BEGIN +set @a = 2; +call pkg2.proc(@a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 50 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 50 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > FUNCTION(IN) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc(a OUT INT); +FUNCTION func(a IN INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc(a OUT INT) +AS +res INT; +BEGIN +a := 100; +res := func(a); +END; +FUNCTION func(a IN INT) RETURN INT +AS +BEGIN +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +RETURN 0; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +BEGIN +call pkg2.proc(@a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 60 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 60 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > FUNCTION(OUT) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc(a OUT INT); +FUNCTION func(a OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc(a OUT INT) +AS +res INT; +BEGIN +a := 100; +res := func(a); +END; +FUNCTION func(a OUT INT) RETURN INT +AS +BEGIN +a := 200; +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +RETURN 0; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +BEGIN +call pkg2.proc(@a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 80 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 80 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > FUNCTION(INOUT) > UPDATE TABLE2 +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc(a OUT INT); +FUNCTION func(a INOUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc(a OUT INT) +AS +res INT; +BEGIN +a := 100; +res := func(a); +END; +FUNCTION func(a INOUT INT) RETURN INT +AS +BEGIN +a := 200; +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +RETURN 0; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +BEGIN +call pkg2.proc(@a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 90 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 90 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +1 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Trigger +# TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > FUNCTION(OUT) > UPDATE TABLE2 with OUT argument (to check if OUT is returning by reference) +# +CREATE TABLE Persons ( +ID int, +Name varchar(255), +Age int +); +INSERT INTO Persons VALUES (1, 'AAA', 10); +INSERT INTO Persons VALUES (2, 'BBB', 20); +INSERT INTO Persons VALUES (3, 'CCC', 30); +CREATE TABLE PersonsLog ( +UpdateCount int +); +INSERT INTO PersonsLog VALUES (0); +CREATE OR REPLACE PACKAGE pkg2 +AS +PROCEDURE proc(a OUT INT); +FUNCTION func(a OUT INT) RETURN INT; +END; +$$ +CREATE OR REPLACE PACKAGE BODY pkg2 +AS +PROCEDURE proc(a OUT INT) +AS +res INT; +BEGIN +res := func(a); +UPDATE PersonsLog SET UpdateCount = a; +END; +FUNCTION func(a OUT INT) RETURN INT +AS +BEGIN +a := 111; +UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +RETURN 0; +END; +END; +$$ +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW +BEGIN +call pkg2.proc(@a); +END; +$$ +SELECT * FROM Persons; +ID Name Age +1 AAA 10 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +0 +UPDATE Persons SET Age = 80 WHERE ID = 1; +SELECT * FROM Persons; +ID Name Age +1 AAA 80 +2 BBB 20 +3 CCC 30 +SELECT * FROM PersonsLog; +UpdateCount +111 +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; +# +# Package BODY variables as OUT parameters +# +CREATE PACKAGE pkg1 AS +FUNCTION f1(b IN OUT INT) RETURN INT; +FUNCTION show_private_variables() RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +pa INT:= 0; +pb INT:= 10; +FUNCTION f1(b IN OUT INT) RETURN INT AS +BEGIN +b:= b + 100; +RETURN 500+b-100; +END; +FUNCTION show_private_variables() RETURN TEXT AS +BEGIN +RETURN 'Private variables: pa=' || pa || ' pb=' || pb; +END; +BEGIN +SET pa=f1(pb); +END; +$$ +SELECT pkg1.show_private_variables(); +pkg1.show_private_variables() +Private variables: pa=510 pb=110 +DROP PACKAGE pkg1; |