10.8 预览特性：CREATE FUNCTION 中的 IN, OUT, INOUT, IN OUT 参数 - MariaDB.org

作为 Oracle 兼容性语法扩展，存储函数参数的 IN, OUT 和 INOUT / IN OUT 限定符在 MDEV-10654 中被提出需求。据指出，Postgres 和 DB2 也支持此语法。几年后，得益于我们的社区贡献者，来自日本乐天证券的 Manohar KB，此功能得以实现。来自 MariaDB Corporation 的 Alexander Barkov 提供了评审，并将此 PL/SQL 语法扩展到非 SQL_MODE=ORACLE 模式下的 SQL/PSM 中可用。

函数拥有 OUT 参数使得嵌套函数可以实现多个返回值，而此前只能有一个返回值。在此特性之前，限定符仅在存储过程中可用。

Oracle 模式下提供的语法

此更改在 SQL_MODE=ORACLE 模式下的 PACKAGE 或 PACKAGE BODY 中支持的语法是

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

Oracle 模式在函数声明中将 IN/OUT/INOUT/IN OUT 放在参数标识符之后，而下一节中的 SQL/PSM 语法显示参数标识符在第二位。

你可以在 Oracle 模式下通过以下命令运行你自己的容器实例来体验此特性

$ 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

注意：所有示例中我都使用了 podman，但任何 OCI 运行时实现都同样适用。

然后使用 MariaDB monitor 接口，我们可以创建一些使用 IN, OUT 和 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)

注意，与下面的 SQL/PSM 模式一样，Oracle 模式下也存在限制，即不能在 SELECT 语句中使用包含 OUT 的限定符。

SQL/PSM（标准，非 Oracle）模式下提供的语法

不指定 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

在容器内运行 MariaDB monitor

$ 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 和 IN OUT 限定符可以在 SET 语句中使用，但不能在 SELECT 语句中使用。这在 Oracle 模式下也一样。以下示例可以证明这一点：

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

使用 SET 的一个有用之处在于可以利用其他函数或存储过程的结果，例如这些示例：

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)

游标也可以使用 IN 语法（Oracle 模式下也一样）

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)