从另一个存储过程中获取ResultSet结果集

Question

我有一个调用另一个存储过程的存储过程。内部存储过程返回一个结果集。在使用CallableStatement执行调用存储过程后，我无法获取被调用存储过程返回的结果集。

我尝试了execute和executeQuery两种方法来执行可调用的语句。当我从SQL Server执行调用存储过程时，我得到了正确的结果。

调用过程：

ALTER PROC [User].[Get_Data]
(@UserID NVARCHAR(20))
AS
BEGIN
Select 'User Data'
Exec [Order].[Get_Order] @UserID
END

被称为过程：

ALTER PROC [Order].[Get_Order]
(@UserID NVARCHAR(20))
AS
BEGIN
Select * from orders where userId=@UserID
END

- Jayant

Answer 1

您的外部存储过程返回了两个结果集：

1. Select 'User Data' 的结果
2. Exec [Order].[Get_Order] @UserID 的结果

您需要调用 .getMoreResults() 来检索第二个结果集，例如：

CallableStatement cs = connection.prepareCall("{CALL Get_Data (?)}");
cs.setString(1, "gord");
ResultSet rs = cs.executeQuery();
System.out.println("[First result set]");
while (rs.next()) {
    System.out.printf("(No column name): %s%n", rs.getString(1));
}
cs.getMoreResults();
rs = cs.getResultSet();
System.out.println();
System.out.println("[Second result set]");
while (rs.next()) {
    System.out.printf("userId: %s, orderId: %s%n", 
            rs.getString("userId"), rs.getString("orderId"));
}

生产

[First result set]
(No column name): User Data

[Second result set]
userId: gord, orderId: order1
userId: gord, orderId: order2

(已测试，使用mssql-jdbc-6.2.1.jre8.jar连接到SQL Server 2014.)

更多细节，请参见

如何使用JDBC从存储过程获取所有内容？

- Gord Thompson

---

已经尝试过了。当一个过程调用另一个过程时，getMoreResults() 不起作用。 - Jayant

@Jayant - 对我来说它是有效的。在我发布代码之前，我已经对其进行了测试。 - Gord Thompson

Answer 2

在 SQL Server 中，您无法直接选择存储过程的结果。您需要首先将结果插入到临时表中，如下面的示例所示。

使用示例：

-- Create a tempoary table to store the results.
CREATE TABLE #UserOrderDetail
(
    UserData NVARCHAR(50) -- Your columns here
)

-- Insert result into temp table.
-- Note that the columns returned from procedure has to match columns in your temp table.
INSERT INTO #UserOrderDetail
EXEC [Order].[Get_Order] @UserID

-- Select the results out of the temp table.
SELECT *
FROM    #UserOrderDetail

如果意图只是将一个或多个结果集返回给客户端应用程序，您应该确保在存储过程的顶部添加SET NOCOUNT ON语句，这将防止SQL Server为存储过程中的每个语句向客户端发送DONE_IN_PROC消息。像ODBC、JDBC和OLEDB这样的数据库库可能会因为SQL Server存储过程中执行的各种插入和更新语句返回的行数而感到困惑。您的原始存储过程将如下所示：

ALTER PROC [User].[Get_Data]
(
    @UserID NVARCHAR(20)
)
AS
BEGIN

    SET NOCOUNT ON

    SELECT 'User Data'
    EXEC [Order].[Get_Order] @UserID

END

- Edmond Quinton

---

抱歉，但不需要临时表。 - Gord Thompson

@Jayant，再次审查了您的问题后，我意识到您的问题可能只是存储过程中未设置NOCOUNT选项的情况。我已更新我的答案并提供了更多信息。 - Edmond Quinton

Answer 3

JDBC 中正确的方法

在 JDBC 中正确地完成这个任务是相当困难的。Gord Thompson 提供的答案 可能有效，但它并没有完全遵循 JDBC 规范，因此可能会有一些边缘情况导致失败，例如在存在交错更新计数（已知或意外）或异常/消息的情况下。

我在这里详细介绍了正确的方法。如果你需要，甚至 Oracle 版本更加棘手。以下是方法：

// If you're daring, use an infinite loop. But you never know...
fetchLoop:
for (int i = 0, updateCount = 0; i < 256; i++) {

    // Use execute(), instead of executeQuery() to handle
    // leading update counts or exceptions
    boolean result = (i == 0)
        ? s.execute()
        : s.getMoreResults();
 
    // Warnings here
    SQLWarning w = s.getWarnings();
    for (int j = 0; j < 255 && w != null; j++) {
        System.out.println("Warning     : " + w.getMessage());
        w = w.getNextWarning();
    }
 
    // Don't forget this
    s.clearWarnings();
 
    if (result)
        try (ResultSet rs = s.getResultSet()) {
            System.out.println("Result      :");
 
            while (rs.next())
                System.out.println("  " + rs.getString(1));
        }
    else if ((updateCount = s.getUpdateCount()) != -1)
        System.out.println("Update Count: " + updateCount);
    else
        break fetchLoop;
}

使用 jOOQ

请注意，如果您正在使用 jOOQ，您可以利用代码生成器为您的存储过程，并调用简化的 API 以仅几行代码完成此操作：

GetDatap = new GetData();
p.setUserId("gord");
p.execute(configuration);
Results results = p.getResults();
 
for (Result<?> result : results)
  for (Record record : result)
    System.out.println(record);

声明：我是 jOOQ 背后公司的员工

- Lukas Eder