1. 简介
本教程将学习如何为H2数据库引擎编写存储过程（在H2中称为用户自定义函数）。我们将了解它们是什么，如何创建以及如何使用它们。
2. 什么是存储过程？
存储过程是许多数据库引擎支持的一种机制，允许我们在数据库内创建自定义功能。就像其他编程语言一样，我们可以创建一个带有名称和一组输入参数的新过程，执行必要功能后返回适当结果。然后我们将这些过程直接存储在数据库中。
在H2数据库引擎中，这些被称为用户自定义函数（User-Defined Functions），尽管它们只是不同名称下的同一概念。这个名称是为了区别于H2已经提供的内置函数，例如UPPER()或DATEDIFF()。
大多数数据库引擎使用存储过程这个名称，所以我们只需记住，在H2中，存储过程和用户自定义函数是同一回事。这允许我们在H2中使用用户自定义函数来替代我们在其他数据库引擎中用存储过程实现的功能。
这有很多用途，包括存储过程的所有常见用途，还允许我们在使用H2进行测试时用存根版本替换它们。
3. 在H2中使用函数
我们可以在H2的SQL查询中直接使用函数。例如，像这样的SQL语句很常见：
SELECT * FROM posts WHERE UPPER(title) = UPPER(?)
这将对title列和绑定参数都应用UPPER()函数，使查询不区分大小写。
用户自定义函数同样适用。一旦创建，它们的行为就像内置函数，我们可以以相同的方式使用：
SELECT * FROM numbers WHERE IS_PRIME(number) = TRUE
这里，IS_PRIME()是一个用户自定义函数。H2没有为我们提供这个函数，而是我们自己编写的。但是，使用它的方式与内置函数完全相同。
4. 创建用户自定义函数
既然我们已经了解了如何使用用户自定义函数，现在需要能够实际创建自己的函数。在H2中有两种方法：我们可以直接向数据库提供函数的源代码，或者用Java编写函数并告知H2它的存在。
4.1. 提供源代码
我们使用数据库中的CREATE ALIAS命令创建新的用户自定义函数。该命令接受函数名和一个作为函数源代码的字符串：
CREATE ALIAS SAY_HELLO AS '
String sayHello() {
return "Hello, World!";
}
';
这创建了一个总是返回字符串"Hello, World!"的用户自定义函数。完成此操作后，我们可以按预期调用该函数：
SELECT SAY_HELLO();
然后执行我们的新函数并返回结果。
要创建用户自定义函数，我们只需提供一个Java方法定义。H2会自动将其包装在适当的样板代码中以创建可编译的类，然后将其编译为类路径上可调用的实际Java类。因此，我们也可以引用其他类：
CREATE ALIAS JAVA_TIME_NOW AS '
String javaTimeNow() {
return java.time.Instant.now().toString();
}
';
这引用了JVM中的java.time.Instant类，但完全可以用类路径上的任何其他类（包括我们自己的代码和依赖项）做同样的事情。唯一要求是加载H2的类加载器必须有权访问我们想要调用的类。
然而，需要完全限定每个类可能会很繁琐。因此，H2允许我们将函数分为两部分，用字符串@CODE分隔：
CREATE ALIAS JAVA_TIME_NOW AS '
import java.time.Instant;
@CODE
String javaTimeNow() {
return Instant.now().toString();
}
';
第一部分（位于类定义之上）允许我们包含import语句。第二部分是我们已经看到的函数定义。这样我们就可以编写与之前相同的代码，但更简洁，因为我们可以正常导入其他类。
4.2. 预编译代码
除了在用户自定义函数中直接指定Java代码外，我们还可以创建指向类路径上现有代码的函数。这意味着我们可以用任何想要的方式编写代码，只要最终能被H2访问即可。唯一的注意事项是：目标方法必须是静态的，并且方法和包含类都必须是公共的。
我们再次使用CREATE ALIAS语句创建这种形式的用户自定义函数，但这次我们指向完全限定的函数名，而不是提供源代码：
CREATE ALIAS JAVA_RANDOM FOR "java.lang.Math.random";
这会产生与我们自己编写代码相同的结果，我们可以用相同的方式调用：
SELECT JAVA_RANDOM();
这里，我们指向了Java标准库中的一个方法——Math.random()。但是，我们同样可以轻松指向类路径上任何位置的方法，包括我们自己的代码：
CREATE ALIAS HELLO FOR "com.baeldung.h2functions.CompiledFunctionUnitTest.hello";
这样，我们就可以从用户自定义函数中执行任何静态方法可以执行的操作——例如，查询远程服务、访问Spring bean，任何符合我们需求的事情。
5. 方法参数
我们已经了解了如何编写自己的用户自定义函数并在查询中使用它们。但是，通常我们希望能够为这些函数提供值以使它们有用。
大多数情况下，这的工作方式与预期完全一致。只要我们使用遵循与JDBC相同的数据类型转换规则的参数类型，它们就会正确工作：
CREATE ALIAS IS_ODD AS '
Boolean isOdd(Integer value) {
if (value == null) {
return null;
}
return (value % 2) != 0;
}
';
此函数接受任何与Integer兼容的类型的单个参数，并返回一个Boolean：
SELECT IS_ODD(5); -- True
值得注意的是，由于我们使用的是包装类型，我们也可以用NULL值调用它，并且需要处理这种情况：
SELECT IS_ODD(NULL); -- NULL
如果我们愿意，可以改为接受基本类型：
CREATE ALIAS IS_ODD AS '
boolean isOdd(int value) {
return (value % 2) != 0;
}
';
这样做意味着我们永远不能用NULL值调用该函数。如果发生这种情况，H2不会调用函数，而是为我们返回NULL。
5.1. 访问数据库
作为一种特殊情况，我们还可以接受java.sql.Connection类型的方法参数。这必须是方法的第一个参数，并将接收用于当前查询的同一数据库连接。这允许我们在用户自定义函数内部与数据库交互。
看一个例子：
CREATE ALIAS SUM_BETWEEN AS '
int sumBetween(Connection con, int lower, int higher) throws SQLException {
try (Statement statement = con.createStatement()) {
ResultSet rs = statement.executeQuery("SELECT number FROM numbers");
int result = 0;
while (rs.next()) {
int value = rs.getInt(1);
if (value > lower && value < higher) {
result += value;
}
}
return result;
}
}
';
这个用户自定义函数执行另一个查询并对返回的结果执行逻辑。在本例中，它从表中选择一组数字，并对所有落在某个范围内的数字求和。
值得注意的是，我们获得的是与调用用户自定义函数相同的连接。这意味着它参与同一个事务，并且可以像函数外部代码一样完全访问所有内容。
6. 异常处理
在某些情况下，我们的用户自定义函数可能需要抛出异常来表示错误。H2允许我们这样做，并按预期处理。
我们可以简单地编写用户自定义函数来抛出所需的任何异常，就像任何其他Java代码一样：
CREATE ALIAS EXCEPTIONAL AS '
int exceptional() {
throw new IllegalStateException("Oops");
}
';
由于这是标准Java代码，我们需要在方法签名中声明任何想要抛出的受检异常：
CREATE ALIAS EXCEPTIONAL AS '
import java.io.IOException;
@CODE
int exceptional() throws IOException {
throw new IOException("Oops");
}
'
当发生这种情况时，H2会自动捕获异常并将其包装在SQLException中，以便它可以通过JDBC API正确传递：
SQLException exception = assertThrows(SQLException.class, () -> statement.executeQuery("SELECT EXCEPTIONAL()"));
assertTrue(exception.getCause() instanceof IllegalStateException);
assertEquals("Oops", exception.getCause().getMessage());
这里我们可以看到，SQLException的原因正是从我们的用户自定义函数中抛出的异常。
7. 总结
在本文中，我们简要介绍了如何在H2数据库中编写自己的用户自定义函数以及如何使用它们。下次当您需要在H2数据库中编写自定义代码时，为什么不尝试一下呢？
与往常一样，本文中的所有示例都可以在GitHub上找到。