10.8 preview feature: IN, OUT, INOUT, IN OUT parameters in CREATE FUNCTION

As an Oracle compatibility syntax extension, the IN, OUT, and INOUT / IN OUT quantifiers of stored function parameters was requested in MDEV-10654. It was noted that Postgres and DB2 also support this syntax. A few years later thanks to our community contributor Manohar KB of Rakuten Securities Japan, this functionality was created. Alexander Barkov from the MariaDB Corporation provided reviews and extended this PL/SQL syntax to be available in the non SQL_MODE=ORACLE mode of SQL/PSM.

Having OUT parameters on functions enables the nesting of functions will multiple return values where previously there was only the single return value possible. Prior to this feature quantifiers were only available on stored procedures.

Provided Syntax in Oracle Mode

The syntax supported in SQL_MODE=ORACLE within a PACKAGE or PACKAGE BODY by this change is:

FUNCTION func(a [IN | OUT | INOUT  | IN OUT] INT) RETURN INT

Oracle mode has the IN/OUT/INOUT/IN OUT after the parameter identifier in the function declaration while the SQL/PSM syntax in the next section shows the parameter identifier being second.

You can run your own container instance with this feature in Oracle mode with:

$ podman run --rm --detach --env MARIADB_RANDOM_ROOT_PASSWORD=1 --env MARIADB_DATABASE=test --env MARIADB_USER=testuser --env MARIADB_PASSWORD=testpassword --name mdb108_inout quay.io/mariadb-foundation/mariadb-devel:10.8-mdev-10654-inout --sql-mode=ORACLE

Note: I’ve use podman in all examples however any OCI runtime implementation will work equally well.

Then using the MariaDB monitor interface we can create some functions using IN, OUT and INOUT.

$ podman exec -ti mdb108_inout mariadb -u testuser -ptestpassword test
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.8.0-MariaDB-MDEV-10654-inout mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> DELIMITER #
MariaDB [test]> CREATE OR REPLACE PACKAGE pkg2
    -> AS
    -> FUNCTION add_func2(a IN INT, b IN INT) RETURN INT;
    -> END
    -> #
MariaDB [test]> 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;
-> #
Query OK, 0 rows affected (0.005 sec)

MariaDB [test]> select pkg2.add_func2(3, 9)
-> #
+----------------------+
| pkg2.add_func2(3, 9) |
+----------------------+
|                   12 |
+----------------------+
1 row in set (0.000 sec)

Note that the restrictions of not using quantifiers containing OUT in SELECT statements also apply in Oracle mode like they do in the SQL/PSM mode below.

Provided Syntax in SQL/PSM (Standard, Non-Oracle) Mode

Running the container without specifying SQL_MODE:

$ podman run --rm -d -e MARIADB_RANDOM_ROOT_PASSWORD=1 -e MARIADB_DATABASE=test -e MARIADB_USER=testuser -e MARIADB_PASSWORD=testpassword --name mdb108_inout quay.io/mariadb-foundation/mariadb-devel:10.8-mdev-10654-inout

Running the MariaDB monitor inside the container:

$ podman exec -ti mdb108_inout mariadb -u testuser -ptestpassword test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.8.0-MariaDB-MDEV-10654-inout mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> CREATE FUNCTION Hypotenuse (IN a DOUBLE, IN b DOUBLE) RETURNS DOUBLE DETERMINISTIC CONTAINS SQL RETURN SQRT(a*a + b*b);
Query OK, 0 rows affected (0.022 sec)

MariaDB [test]> select Hypotenuse(3.0, 4.0);
+----------------------+
| Hypotenuse(3.0, 4.0) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.000 sec)

OUT, INOUT, and IN OUT quantifiers can be use in SET statements, but not SELECT statements. This is also true in Oracle mode. This is evident with:

MariaDB [test]> delimiter #
MariaDB [test]> CREATE FUNCTION add_func3(IN a INT, IN b INT, OUT c INT) RETURNS INT BEGIN   SET c = 100;   RETURN a + b; END; #                       
Query OK, 0 rows affected (0.007 sec)

MariaDB [test]> delimiter ;
MariaDB [test]> SET @a = 2; SET @b = 3; SET @c = 0; SET @res= add_func3(@a, @b, @c);
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select @a, @b, @c, @res;
+------+------+------+------+
| @a   | @b   | @c   | @res |
+------+------+------+------+
|    2 |    3 |  100 |    5 |
+------+------+------+------+
1 row in set (0.000 sec)

MariaDB [test]>  select add_func3(@a, @b, @c);
ERROR 4186 (HY000): OUT or INOUT argument 3 for function add_func3 is not allowed here

The useful aspect of using SET is using the results from other functions or stored procedures like these examples:

MariaDB [test]> delimiter #
MariaDB [test]> begin not atomic
    -> declare res int;
    -> declare c int;      
    -> set res = add_func3(2, 4, c);
    -> select if(c > 99, res, 0);
    -> end;
    -> #
+--------------------+
| if(c > 99, res, 0) |
+--------------------+
|                  6 |
+--------------------+
1 row in set (0.001 sec)

MariaDB [test]>  CREATE FUNCTION add_func4(IN a INT, IN b INT, d INT) RETURNS INT
BEGIN
  DECLARE c, res INT;
  SET res = add_func3(a, b, c) + d;
  if (c > 99) then
    return  3;
  else
    return res;
  end if;
END; #
Query OK, 0 rows affected (0.024 sec)

MariaDB [test]> select add_func4(1,2,3)#
+------------------+
| add_func4(1,2,3) |
+------------------+
|                3 |
+------------------+
1 row in set (0.000 sec)

Cursors can also use the IN syntax (also in Oracle mode):

MariaDB [test]> delimiter #
MariaDB [test]> BEGIN NOT ATOMIC
    ->   DECLARE va INT;
    ->   DECLARE cur CURSOR (IN a INT) FOR SELECT a FROM dual;
    ->   OPEN cur(1);
    ->   FETCH cur INTO va;
    ->   CLOSE cur;
    ->   SELECT va;
    -> END;
    -> #
+------+
| va   |
+------+
|    1 |
+------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Thanks

Thanks again Manohar KB for writing this feature and its test cases and your employer Rakuten Securities Japan for making this possible. Its initiatives like this that turn outstanding feature requests into features for all MariaDB users. We encourage anyone with programming ability to make a similar contribution of their favourite supportable feature extension to MariaDB.