H2数据库和单独模式中的功能 [英] H2 database and functions in separate schemas

问题描述

我正在尝试创建一个测试数据库（使用H2数据库）。我在生产中使用Oracle，并且在h2中具有oracle兼容模式似乎很好。

I'm trying to create a test database (with H2 database). I'm using Oracle in production, and it seems nice to have oracle compatibility mode in h2.

但是我在翻译oracle构造时遇到问题：

However I've got a problem with translating oracle construction:

create or replace PACKAGE permission_tools IS
    FUNCTION get_role_access_level(
          p_role_id IN NUMBER,
          p_permiss IN VARCHAR2)
    RETURN NUMBER;
END permission_tools;

我打电话的是：

select permission_tools.get_access_level(?, ?) from dual;

换算成H2。我一直在尝试类似的东西：

into H2 equivalent. I've been trying something like:

创建架构许可工具；

CREATE ALIAS PERMISSION_TOOLS.GET_ACCESS_LEVEL as $$
String nextPrime(String value) {
    return new BigInteger(value).nextProbablePrime().toString();
}
$$;

但这给了我编译错误：

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "

CREATE ALIAS PERMISSION_TOOLS.[*]GET_ACCESS_LEVEL AS   
String nextPrime(String value) {
    return new BigInteger(value).nextProbablePrime().toString();
}
   "; expected "FOR"; SQL statement:


CREATE ALIAS PERMISSION_TOOLS.GET_ACCESS_LEVEL as $$
String nextPrime(String value) {
    return new BigInteger(value).nextProbablePrime().toString();
}
$$ [42001-131]

任何帮助表示赞赏。

推荐答案

H2不支持软件包。您可以做的是使用其他名称创建函数，例如：PERMISSION_TOOLS_GET_ACCESS_LEVEL。缺点是您还需要更改查询。或者，您创建一个模式PERMISSION_TOOLS及其那里的方法：

H2 doesn't support packages. What you could do is create the function using a different name, for example: PERMISSION_TOOLS_GET_ACCESS_LEVEL. The disadvantage is that you need to change the query as well. Or, you create a schema PERMISSION_TOOLS and the method there:

create schema PERMISSION_TOOLS;
CREATE ALIAS PERMISSION_TOOLS.GET_ACCESS_LEVEL as $$
String nextPrime(String value) {
    return new BigInteger(value).nextProbablePrime().toString();
}
$$;
select permission_tools.get_access_level(1) from dual;

请注意，这在H2版本1.2.131（您正在使用的版本）中将不起作用根据收到的错误消息代码）。原因是架构中的功能是最近才实现的（在1.2.135版中）。实际上，我建议升级到1.2.138版本，因为在早期版本中已修复了与此功能相关的错误。创建该方法的缺点是有一个特殊的模式：如果您在PUBLIC之外的模式中确实创建了此类函数，则无法使用旧版本的H2打开数据库。

Please not this will not yet work in H2 version 1.2.131 (which is the version you are using according to the error message code you got). The reason is that 'functions in schemas' was just recently implemented (in version 1.2.135). Actually I suggest to upgrade to version 1.2.138, because there was a bug fixed related to this feature in earlier versions. The disadvantage of creating the method is a special schema is: if you do create such functions in schemas other than PUBLIC, then the database can not be opened with older versions of H2.

这篇关于H2数据库和单独模式中的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！