--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;
--disable_ps2_protocol
select pkg2.func_main(@a);
--enable_ps2_protocol
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;
--disable_ps2_protocol
select pkg2.func_main(@a);
--enable_ps2_protocol
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;