调用存储函数和过程

PostgreSQL 支持两种类型的存储对象，可以返回结果值的函数，和从版本 11 开始支持的可以执行事务控制的存储过程。两种类型的存储对象都使用CallableStatement和标准 JDBC 转义调用语法{call storedobject(?)}。连接属性escapeSyntaxCallMode控制驱动程序转换调用语法以调用函数或过程的方式。

默认模式select，支持向后兼容现有应用程序并只支持函数调用。这是调用返回 void 的函数所必需的。

对于新应用程序，使用escapeSyntaxCallMode=callIfNoReturn可以将带返回值的CallableStatements映射到存储函数，将无返回值的CallableStatements映射到存储过程。

例 6.1.调用内置存储函数

此示例演示如何调用 PostgreSQL 内置函数upper，该函数只是将提供的字符串参数转换为大写。

CallableStatement upperFunc = conn.prepareCall("{? = call upper( ? ) }");
upperFunc.registerOutParameter(1, Types.VARCHAR);
upperFunc.setString(2, "lowercase to uppercase");
upperFunc.execute();
String upperCased = upperFunc.getString(1);
upperFunc.close();

从存储函数获取`ResultSet`

PostgreSQL的™存储函数可以通过两种不同的方式返回结果。该函数可能返回refcursor值或某种数据类型的SETOF集合。根据使用这些返回方法中的哪一个来确定应如何调用函数。

从函数返回类型`SETOF`

将数据作为集合返回的函数不应通过CallableStatement接口调用，而应使用普通的Statement或PreparedStatement接口。

例 6.2.从函数中获取SETOF类型值

Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION setoffunc() RETURNS SETOF int AS " +
    "' SELECT 1 UNION SELECT 2;' LANGUAGE sql");
ResultSet rs = stmt.executeQuery("SELECT * FROM setoffunc()");
while (rs.next()) {
    // do something
}
rs.close();
stmt.close();

从函数返回`refcursor`

调用返回refcursor的函数时，必须将getObject返回的类型强制转换为ResultSet。

当前对从 refcursor 创建的ResultSet的支持有一个需要注意的限制是，即使它是一个游标承载的ResultSet，所有数据都将被检索并缓存在客户端上。Statement的抓取大小参数会被忽略，这个参数在名为“基于游标获取结果”一节中会有描述。此限制是 JDBC 驱动程序的缺陷，而不是服务器的缺陷，从技术上讲可以解决它，我们只是没有找到时间。

例 6.3.从函数获取 refcursor 值

// Setup function to call.
Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '" +
    " DECLARE " +
    "    mycurs refcursor; " +
    " BEGIN " +
    "    OPEN mycurs FOR SELECT 1 UNION SELECT 2; " +
    "    RETURN mycurs; " +
    " END;' language plpgsql");
stmt.close();

// We must be inside a transaction for cursors to work.
conn.setAutoCommit(false);

// Function call.
CallableStatement func = conn.prepareCall("{? = call refcursorfunc() }");
func.registerOutParameter(1, Types.OTHER);
func.execute();
ResultSet results = (ResultSet) func.getObject(1);
while (results.next()) {
    // do something with the results.
}
results.close();
func.close();

也可以直接将refcursor返回值视为游标名称。为此，请使用ResultSet的getString方法。使用基础的游标名称，您可以自由地直接在其上使用光标命令，例如FETCH和MOVE。

例 6.4.将 refcursor 视为游标名称

conn.setAutoCommit(false);
CallableStatement func = conn.prepareCall("{? = call refcursorfunc() }");
func.registerOutParameter(1, Types.OTHER);
func.execute();
String cursorName = func.getString(1);
func.close();

例 6.5.调用存储过程

此示例演示如何调用使用事务控制的 PostgreSQL 过程。

// set up a connection
String url = "jdbc:postgresql://localhost/test";
Properties props = new Properties();
...other properties...
    // Ensure EscapeSyntaxCallmode property set to support procedures if no return value
    props.setProperty("escapeSyntaxCallMode", "callIfNoReturn");
Connection con = DriverManager.getConnection(url, props);

// Setup procedure to call.
Statement stmt = con.createStatement();
stmt.execute("CREATE TEMP TABLE temp_val ( some_val bigint )");
stmt.execute("CREATE OR REPLACE PROCEDURE commitproc(a INOUT bigint) AS '" +
    " BEGIN " +
    "    INSERT INTO temp_val values(a); " +
    "    COMMIT; " +
    " END;' LANGUAGE plpgsql");
stmt.close();

// As of v11, we must be outside a transaction for procedures with transactions to work.
con.setAutoCommit(true);

// Procedure call with transaction
CallableStatement proc = con.prepareCall("{call commitproc( ? )}");
proc.setInt(1, 100);
proc.execute();
proc.close();

调用存储函数和过程

从存储函数获取ResultSet

从函数返回类型SETOF

从函数返回refcursor

从存储函数获取`ResultSet`

从函数返回类型`SETOF`

从函数返回`refcursor`