diff options
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-inout.result | 2571 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/sp-inout.test | 2497 | ||||
| -rw-r--r-- | sql/share/errmsg-utf8.txt | 2 | ||||
| -rw-r--r-- | sql/sp_head.cc | 195 | ||||
| -rw-r--r-- | sql/sp_head.h | 12 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 64 |
6 files changed, 5250 insertions, 91 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 00000000000..fa6f5076d59 --- /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; diff --git a/mysql-test/suite/compat/oracle/t/sp-inout.test b/mysql-test/suite/compat/oracle/t/sp-inout.test new file mode 100644 index 00000000000..9f9d3bdb26a --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-inout.test @@ -0,0 +1,2497 @@ +--echo # +--echo # MDEV-10654 IN, OUT, INOUT parameters in CREATE FUNCTION +--echo # + +SET sql_mode=ORACLE; + +--echo # +--echo # CREATE PACKAGE with procedure and function with IN, OUT, INOUT qualifiers +--echo # And SHOW CREATE PACKAGE +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SHOW CREATE PACKAGE pkg2; +SHOW CREATE PACKAGE BODY pkg2; +DROP PACKAGE pkg2; + +--echo # +--echo # CREATE FUNCTION with IN, OUT, INOUT qualifiers +--echo # SHOW CREATE FUNCTION +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SHOW CREATE FUNCTION add_func; +DROP FUNCTION add_func; + +--echo # +--echo # CREATE PROCEDURE with IN, OUT, INOUT qualifiers +--echo # SHOW CREATE PROCEDURE +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SHOW CREATE PROCEDURE add_proc; +DROP PROCEDURE add_proc; + +--echo # +--echo # Call function from SELECT query +--echo # SELECT > FUNCTION(IN) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +select pkg2.add_func2(@a, @b); +DROP PACKAGE pkg2; + +--echo # +--echo # Call function from SELECT query +--echo # SELECT > FUNCTION(OUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @c = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.add_func3(@a, @b, @c); +DROP PACKAGE pkg2; + +--echo # +--echo # Call function from SELECT query +--echo # SELECT > FUNCTION(INOUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @c = 0; +set @d = 9; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.add_func4(@a, @b, @c, @d); +DROP PACKAGE pkg2; + +--echo # +--echo # Call from procedure +--echo # PROCEDURE(OUT) > FUNCTION(IN) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.add_proc2(@a, @b, @c); +select @c; +DROP PACKAGE pkg2; + +--echo # +--echo # Call from procedure +--echo # PROCEDURE(OUT) > FUNCTION(OUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.add_proc3(@a, @b, @c); +select @c; +DROP PACKAGE pkg2; + +--echo # +--echo # Call from procedure +--echo # PROCEDURE(OUT) > FUNCTION(INOUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @res = 0; +call pkg2.add_proc4(@a, @b, @res); +select @res; +DROP PACKAGE pkg2; + +--echo # +--echo # Call from procedure +--echo # PROCEDURE(OUT) > PROCEDURE(OUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.test_proc1(@a, @b, @c); +select @c; +DROP PACKAGE pkg2; + +--echo # +--echo # Argument's order change +--echo # PROCEDURE(a IN, b IN, c OUT) > FUNCTION(b IN, a IN, c OUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.proc_main(@a, @b, @c); +select @c; +DROP PACKAGE pkg2; + +--echo # +--echo # Argument's order change +--echo # PROCEDURE(a IN, b IN, c OUT) > FUNCTION(c OUT, b IN, a IN) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @c = 0; +call pkg2.proc_main(@a, @b, @c); +select @c; +DROP PACKAGE pkg2; + +--echo # +--echo # Argument's order change +--echo # PROCEDURE(a IN, b IN, c INOUT, d OUT) > FUNCTION(d OUT, a IN, b IN, c INOUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 15; +set @b = 5; +set @c = 4; +set @d= 0; +call pkg2.proc_main(@a, @b, @c, @d); +select @d; +DROP PACKAGE pkg2; + +--echo # +--echo # Argument's order change +--echo # PROCEDURE(a IN INT, b IN INT, c INOUT INT, d OUT INT) > FUNCTION1(c INOUT INT, b IN INT) > FUNCTION2(d OUT INT, a IN INT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 15; +set @b = 6; +set @c = 4; +set @d= 0; +call pkg2.proc_main(@a, @b, @c, @d); +select @d; +DROP PACKAGE pkg2; + +--echo # +--echo # Argument's order change +--echo # FUNCTION1(a IN, b IN) > FUNCTION2(b IN, c OUT, a IN) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +select pkg2.func_main(@a, @b); +DROP PACKAGE pkg2; + +--echo # +--echo # Call procedure inside function +--echo # FUNCTION1(a IN, b IN) > PROCEDURE(a IN, b IN, c OUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +select pkg2.func_main(@a, @b); +DROP PACKAGE pkg2; + +--echo # +--echo # Call procedure inside function +--echo # FUNCTION1(a IN, b IN) > PROCEDURE(a IN, b INOUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +select pkg2.func_main(@a, @b); +DROP PACKAGE pkg2; + +--echo # +--echo # Call procedure inside function +--echo # FUNCTION1(a IN, b IN, c OUT) > PROCEDURE(a IN, b IN, c OUT) +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +set @b = 3; +set @c = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.func_main(@a, @b, @c); +DROP PACKAGE pkg2; + +--echo # +--echo # Call function from UPDATE query +--echo # UPDATE <table> SET <column> = FUNCTION(a IN) +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 5; +UPDATE Persons SET Age = pkg2.func(@a) WHERE ID = 1; +SELECT * FROM Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # Call function from UPDATE query +--echo # UPDATE <table> SET <column> = FUNCTION(a OUT) +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +UPDATE Persons SET Age = pkg2.func(@a) WHERE ID = 1; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # Call function from INSERT query +--echo # INSERT INTO <table> SELECT <val1>, <val2>, FUNCTION(a IN) +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 4; +INSERT INTO Persons SELECT 4, 'DDD', PKG2.func(@a); +SELECT * FROM Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # Call function from INSERT query +--echo # INSERT INTO <table> SELECT <val1>, <val2>, FUNCTION(a OUT) +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +set @a = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +INSERT INTO Persons SELECT 5, 'EEE', PKG2.func(@a); +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # Call function from DELETE query +--echo # DELETE FROM <table> WHERE <column> = FUNCTION(a IN) +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +set @a = 4; +DELETE FROM Persons WHERE ID = PKG2.func(@a); +SELECT * FROM Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # Call function from DELETE query +--echo # DELETE FROM <table> WHERE <column> = FUNCTION(a OUT) +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +set @a = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +DELETE FROM Persons WHERE ID = PKG2.func(@a); +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # SELECT query inside function +--echo # FUNCTION(a IN) > SELECT … FROM <table> +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 3; +select pkg2.func_main(@a); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # SELECT query inside function +--echo # FUNCTION(a OUT) > SELECT … FROM <table> +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.func_main(@a); +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # SELECT query inside function +--echo # FUNCTION(a INOUT) > SELECT … FROM <table> +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 1; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.func_main(@a); +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # SELECT query inside function +--echo # FUNCTION(a IN) > FUNCTION(a IN, b OUT) > SELECT … FROM <table> +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 2; +select pkg2.func_main(@a); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # UPDATE query inside function +--echo # FUNCTION(a IN) > UPDATE <table> SET … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 5; +select pkg2.func_main(@a); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # UPDATE query inside function +--echo # FUNCTION(a IN, b OUT) > UPDATE <table> SET … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 5; +set @b = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.func_main(@a, @b); +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # UPDATE query inside function +--echo # FUNCTION(a IN, b INOUT) > UPDATE <table> SET … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +set @a = 5; +set @b = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.func_main(@a, @b); +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # UPDATE query inside function +--echo # FUNCTION(a IN) > FUNCTION(a IN, b OUT) > UPDATE <table> SET … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 1; +select pkg2.func_main(@a); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # INSERT query inside function +--echo # FUNCTION(a IN) > INSERT INTO <table> VALUES … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 6; +select pkg2.func_main(@a); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # INSERT query inside function +--echo # FUNCTION(a IN, b OUT) > INSERT INTO <table> VALUES … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 6; +set @b = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.func_main(@a, @b); +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # INSERT query inside function +--echo # FUNCTION(a IN, b INOUT) > INSERT INTO <table> VALUES … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 6; +set @b = 0; +--error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED +select pkg2.func_main(@a, @b); +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # INSERT query inside function +--echo # FUNCTION(a IN) > FUNCTION(a IN, b OUT) > INSERT INTO <table> VALUES … +--echo # + +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); + + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 6; +select pkg2.func_main(@a); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(IN) > SELECT FROM <table> … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 2; +set @b = 0; +call pkg2.proc_main(@a, @b); +select @b; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(OUT) > SELECT FROM <table> … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 1; +set @b = 0; +call pkg2.proc_main(@a, @b); +select @b; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(INOUT) > SELECT FROM <table> … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 2; +set @b = 0; +call pkg2.proc_main(@a, @b); +select @b; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(IN) > INSESRT INTO <table> … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 5; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(OUT) > INSESRT INTO <table> … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 6; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(INOUT) > INSESRT INTO <table> … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 7; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(IN) > UPDATE <table> SET … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 5; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(OUT) > UPDATE <table> SET … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 6; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # PROCEDURE > FUNCTION > SQL query +--echo # PROCEDURE(OUT) > FUNCTION(INOUT) > UPDATE <table> SET … +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +select * from Persons; +set @a = 7; +set @b = 0; +call pkg2.proc_main(@a, @b); +select * from Persons; +DROP TABLE Persons; +DROP PACKAGE pkg2; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +CREATE OR REPLACE TRIGGER my_trigger +AFTER UPDATE ON Persons +FOR EACH ROW + UPDATE PersonsLog SET UpdateCount = UpdateCount+1; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 20 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > FUNCTION(IN) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 30 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > FUNCTION(OUT) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 50 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > FUNCTION(INOUT) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 60 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(IN) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 30 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 50 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(INOUT) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 50 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > FUNCTION(IN) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 60 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > FUNCTION(OUT) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 80 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > FUNCTION(INOUT) > UPDATE TABLE2 +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 90 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + +--echo # +--echo # Trigger +--echo # TRIGGER AFTER UPDATE ON TABLE1 > PROCEDURE(OUT) > FUNCTION(OUT) > UPDATE TABLE2 with OUT argument (to check if OUT is returning by reference) +--echo # + +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); + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +UPDATE Persons SET Age = 80 WHERE ID = 1; +SELECT * FROM Persons; +SELECT * FROM PersonsLog; +DROP TRIGGER my_trigger; +DROP PACKAGE pkg2; +DROP TABLE Persons; +DROP TABLE PersonsLog; + + +--echo # +--echo # Package BODY variables as OUT parameters +--echo # + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +SELECT pkg1.show_private_variables(); +DROP PACKAGE pkg1; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 3b5c8010a6d..c9599f283bb 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -8915,3 +8915,5 @@ ER_PROVIDER_NOT_LOADED eng "MariaDB tried to use the %s, but its provider plugin is not loaded" ER_JSON_HISTOGRAM_PARSE_FAILED eng "Failed to parse histogram for table %s.%s: %s at offset %d." +ER_SF_OUT_INOUT_ARG_NOT_ALLOWED + eng "OUT or INOUT argument %d for function %s is not allowed here" diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 97905f6a5b4..88c1a38aa45 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -2079,7 +2079,8 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, /* Arguments must be fixed in Item_func_sp::fix_fields */ DBUG_ASSERT(argp[arg_no]->fixed()); - if ((err_status= (*func_ctx)->set_parameter(thd, arg_no, &(argp[arg_no])))) + err_status= bind_input_param(thd, argp[arg_no], arg_no, *func_ctx, TRUE); + if (err_status) goto err_with_cleanup; } @@ -2202,6 +2203,19 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, my_error(ER_SP_NORETURNEND, MYF(0), m_name.str); err_status= TRUE; } + else + { + /* + Copy back all OUT or INOUT values to the previous frame, or + set global user variables + */ + for (arg_no= 0; arg_no < argcount; arg_no++) + { + err_status= bind_output_param(thd, argp[arg_no], arg_no, octx, *func_ctx); + if (err_status) + break; + } + } } #ifndef NO_EMBEDDED_ACCESS_CHECKS @@ -2324,50 +2338,9 @@ sp_head::execute_procedure(THD *thd, List<Item> *args) if (!arg_item) break; - sp_variable *spvar= m_pcont->find_variable(i); - - if (!spvar) - continue; - - if (spvar->mode != sp_variable::MODE_IN) - { - Settable_routine_parameter *srp= - arg_item->get_settable_routine_parameter(); - - if (!srp) - { - my_error(ER_SP_NOT_VAR_ARG, MYF(0), i+1, ErrConvDQName(this).ptr()); - err_status= TRUE; - break; - } - - srp->set_required_privilege(spvar->mode == sp_variable::MODE_INOUT); - } - - if (spvar->mode == sp_variable::MODE_OUT) - { - Item_null *null_item= new (thd->mem_root) Item_null(thd); - Item *tmp_item= null_item; - - if (!null_item || - nctx->set_parameter(thd, i, &tmp_item)) - { - DBUG_PRINT("error", ("set variable failed")); - err_status= TRUE; - break; - } - } - else - { - if (nctx->set_parameter(thd, i, it_args.ref())) - { - DBUG_PRINT("error", ("set variable 2 failed")); - err_status= TRUE; - break; - } - } - - TRANSACT_TRACKER(add_trx_state_from_thd(thd)); + err_status= bind_input_param(thd, arg_item, i, nctx, FALSE); + if (err_status) + break; } /* @@ -2477,31 +2450,9 @@ sp_head::execute_procedure(THD *thd, List<Item> *args) if (!arg_item) break; - sp_variable *spvar= m_pcont->find_variable(i); - - if (spvar->mode == sp_variable::MODE_IN) - continue; - - Settable_routine_parameter *srp= - arg_item->get_settable_routine_parameter(); - - DBUG_ASSERT(srp); - - if (srp->set_value(thd, octx, nctx->get_variable_addr(i))) - { - DBUG_PRINT("error", ("set value failed")); - err_status= TRUE; + err_status= bind_output_param(thd, arg_item, i, octx, nctx); + if (err_status) break; - } - - Send_field *out_param_info= new (thd->mem_root) Send_field(thd, nctx->get_parameter(i)); - out_param_info->db_name= m_db; - out_param_info->table_name= m_name; - out_param_info->org_table_name= m_name; - out_param_info->col_name= spvar->name; - out_param_info->org_col_name= spvar->name; - - srp->set_out_param_info(out_param_info); } } @@ -2532,6 +2483,112 @@ sp_head::execute_procedure(THD *thd, List<Item> *args) DBUG_RETURN(err_status); } +bool +sp_head::bind_input_param(THD *thd, + Item *arg_item, + uint arg_no, + sp_rcontext *nctx, + bool is_function) +{ + DBUG_ENTER("sp_head::bind_input_param"); + + sp_variable *spvar= m_pcont->find_variable(arg_no); + if (!spvar) + DBUG_RETURN(FALSE); + + if (spvar->mode != sp_variable::MODE_IN) + { + Settable_routine_parameter *srp= + arg_item->get_settable_routine_parameter(); + + if (!srp) + { + my_error(ER_SP_NOT_VAR_ARG, MYF(0), arg_no+1, ErrConvDQName(this).ptr()); + DBUG_RETURN(TRUE); + } + + if (is_function) + { + /* + Check if the function is called from SELECT/INSERT/UPDATE/DELETE query + and parameter is OUT or INOUT. + If yes, it is an invalid call - throw error. + */ + if (thd->lex->sql_command == SQLCOM_SELECT || + thd->lex->sql_command == SQLCOM_INSERT || + thd->lex->sql_command == SQLCOM_INSERT_SELECT || + thd->lex->sql_command == SQLCOM_UPDATE || + thd->lex->sql_command == SQLCOM_DELETE) + { + my_error(ER_SF_OUT_INOUT_ARG_NOT_ALLOWED, MYF(0), arg_no+1, m_name.str); + DBUG_RETURN(TRUE); + } + } + + srp->set_required_privilege(spvar->mode == sp_variable::MODE_INOUT); + } + + if (spvar->mode == sp_variable::MODE_OUT) + { + Item_null *null_item= new (thd->mem_root) Item_null(thd); + Item *tmp_item= null_item; + + if (!null_item || + nctx->set_parameter(thd, arg_no, &tmp_item)) + { + DBUG_PRINT("error", ("set variable failed")); + DBUG_RETURN(TRUE); + } + } + else + { + if (nctx->set_parameter(thd, arg_no, &arg_item)) + { + DBUG_PRINT("error", ("set variable 2 failed")); + DBUG_RETURN(TRUE); + } + } + + TRANSACT_TRACKER(add_trx_state_from_thd(thd)); + + DBUG_RETURN(FALSE); +} + +bool +sp_head::bind_output_param(THD *thd, + Item *arg_item, + uint arg_no, + sp_rcontext *octx, + sp_rcontext *nctx) +{ + DBUG_ENTER("sp_head::bind_output_param"); + + sp_variable *spvar= m_pcont->find_variable(arg_no); + if (spvar->mode == sp_variable::MODE_IN) + DBUG_RETURN(FALSE); + + Settable_routine_parameter *srp= + arg_item->get_settable_routine_parameter(); + + DBUG_ASSERT(srp); + + if (srp->set_value(thd, octx, nctx->get_variable_addr(arg_no))) + { + DBUG_PRINT("error", ("set value failed")); + DBUG_RETURN(TRUE); + } + + Send_field *out_param_info= new (thd->mem_root) Send_field(thd, nctx->get_parameter(arg_no)); + out_param_info->db_name= m_db; + out_param_info->table_name= m_name; + out_param_info->org_table_name= m_name; + out_param_info->col_name= spvar->name; + out_param_info->org_col_name= spvar->name; + + srp->set_out_param_info(out_param_info); + + DBUG_RETURN(FALSE); +} /** Reset lex during parsing, before we parse a sub statement. diff --git a/sql/sp_head.h b/sql/sp_head.h index 475f1e0d424..495de855e8e 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -505,6 +505,18 @@ private: sp_assignment_lex *param_lex, Item_args *parameters); + bool bind_input_param(THD *thd, + Item *arg_item, + uint arg_no, + sp_rcontext *nctx, + bool is_function); + + bool bind_output_param(THD *thd, + Item *arg_item, + uint arg_no, + sp_rcontext *octx, + sp_rcontext *nctx); + public: /** Generate a code for an "OPEN cursor" statement. diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 315eba18703..2991f235357 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -3063,20 +3063,6 @@ sp_param_name: } ; -sp_param_name_and_type: - sp_param_name field_type - { - if (unlikely(Lex->sp_param_fill_definition($$= $1, $2))) - MYSQL_YYABORT; - } - | sp_param_name ROW_SYM row_type_body - { - if (unlikely(Lex->sphead->spvar_fill_row(thd, $$= $1, $3))) - MYSQL_YYABORT; - } - | sp_param_name_and_type_anchored - ; - /* Stored PROCEDURE parameter declaration list */ sp_pdparam_list: /* Empty */ @@ -18039,6 +18025,20 @@ sp_decl_variable_list_anchored: } ; +sp_param_name_and_type: + sp_param_name field_type + { + if (unlikely(Lex->sp_param_fill_definition($$= $1, $2))) + MYSQL_YYABORT; + } + | sp_param_name ROW_SYM row_type_body + { + if (unlikely(Lex->sphead->spvar_fill_row(thd, $$= $1, $3))) + MYSQL_YYABORT; + } + | sp_param_name_and_type_anchored + ; + sp_param_name_and_type_anchored: sp_param_name TYPE_SYM OF_SYM ident '.' ident { @@ -18913,25 +18913,45 @@ sp_decl_variable_list_anchored: } ; +sp_param_name_and_type: + sp_param_name sp_opt_inout field_type + { + $1->mode= $2; + if (unlikely(Lex->sp_param_fill_definition($$= $1, $3))) + MYSQL_YYABORT; + } + | sp_param_name sp_opt_inout ROW_SYM row_type_body + { + $1->mode= $2; + if (unlikely(Lex->sphead->spvar_fill_row(thd, $$= $1, $4))) + MYSQL_YYABORT; + } + | sp_param_name_and_type_anchored + ; + sp_param_name_and_type_anchored: - sp_param_name sp_decl_ident '.' ident PERCENT_ORACLE_SYM TYPE_SYM + sp_param_name sp_opt_inout sp_decl_ident '.' ident PERCENT_ORACLE_SYM TYPE_SYM { - if (unlikely(Lex->sphead->spvar_fill_type_reference(thd, $$= $1, $2, $4))) + $1->mode= $2; + if (unlikely(Lex->sphead->spvar_fill_type_reference(thd, $$= $1, $3, $5))) MYSQL_YYABORT; } - | sp_param_name sp_decl_ident '.' ident '.' ident PERCENT_ORACLE_SYM TYPE_SYM + | sp_param_name sp_opt_inout sp_decl_ident '.' ident '.' ident PERCENT_ORACLE_SYM TYPE_SYM { - if (unlikely(Lex->sphead->spvar_fill_type_reference(thd, $$= $1, $2, $4, $6))) + $1->mode= $2; + if (unlikely(Lex->sphead->spvar_fill_type_reference(thd, $$= $1, $3, $5, $7))) MYSQL_YYABORT; } - | sp_param_name sp_decl_ident PERCENT_ORACLE_SYM ROWTYPE_ORACLE_SYM + | sp_param_name sp_opt_inout sp_decl_ident PERCENT_ORACLE_SYM ROWTYPE_ORACLE_SYM { - if (unlikely(Lex->sphead->spvar_fill_table_rowtype_reference(thd, $$= $1, $2))) + $1->mode= $2; + if (unlikely(Lex->sphead->spvar_fill_table_rowtype_reference(thd, $$= $1, $3))) MYSQL_YYABORT; } - | sp_param_name sp_decl_ident '.' ident PERCENT_ORACLE_SYM ROWTYPE_ORACLE_SYM + | sp_param_name sp_opt_inout sp_decl_ident '.' ident PERCENT_ORACLE_SYM ROWTYPE_ORACLE_SYM { - if (unlikely(Lex->sphead->spvar_fill_table_rowtype_reference(thd, $$= $1, $2, $4))) + $1->mode= $2; + if (unlikely(Lex->sphead->spvar_fill_table_rowtype_reference(thd, $$= $1, $3, $5))) MYSQL_YYABORT; } ; |