# 
# 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;