有没有一种方法可以提前确定执行存储过程或函数时要返回的结果集的数量? [英] Is there a way to determine ahead the number of result sets to be returned when executing a stored procedure or function?

问题描述

让我们在JDBC中假设以下虚拟示例:

Let's assume the following dummy example in JDBC:

   Statement statement = connection.createStatement();
   statement.execute("call sp1(); select * from X;");
   
   int uc = statement .getUpdateCount();
   boolean isResult = (uc == -1);

   do
   {
      if( isResult )
      {
         ResultSet rs = statement.getResultSet();

         if( rs != null )
         {
            doSomethingWithRS( rs );
         }
      }
      else
      {
         System.out.println( "update count: " + uc );
      }
   }
   while( ( isResult = statement.getMoreResults() ) || ( uc = statement.getUpdateCount() ) != -1 );

众所周知，某些数据库/驱动程序允许执行多条语句(以SQL Server为例).程序员不一定必须事先知道存储过程的详细信息.
有没有一种方法可以预先确定要从存储过程调用中返回的ResultSet的数量-以便可以将它们与随后的SELECT返回的ResultSet区别开来?

It is known that some Databases/Drivers allow multiple statement execution (SQL Server as an example). The details of a Stored Procedure are NOT necessarily know ahead to a programmer.
Is there a way to determine ahead the number of ResultSets to be returned from Stored Procedure calls - so that they could be distinguished from the ResultSets returned for the consequent SELECT?

推荐答案

否，JDBC中没有什么可以让您提前知道的，通常在底层数据库系统中甚至没有任何选项可以知道.例如，在SQL Server中，完全有可能编写一个存储过程，根据它的输入(包括它访问的表，环境配置等)，输出一个零，一个或几十个结果集(和更新计数).如果不自己执行，这种事情是不可判定的.

No, there is nothing in JDBC that allows you to know that ahead of time, and usually there is not even an option to know that in the underlying database system. For example, in SQL Server it is entirely possible to write a stored procedure that depending on its inputs (including in the tables it accesses, environment configuration, etc) outputs zero, one, or dozens of result sets (and update counts). Such a thing is not decidable, without doing the execution itself.

您唯一可以做的就是解释 execute()和 getMoreResults()和 getUpdateCount()和的结果getResultSet()正确，或者不要编写可能具有多个或动态结果的存储过程.

The only thing you can do is interpret the results of execute() and getMoreResults() and getUpdateCount() and getResultSet() correctly, or alternatively don't write stored procedures that can have multiple or dynamic results.

顺便说一句，您的初始代码不应调用 getUpdateCount()来确定它是否是结果集，而应使用 execute():

As an aside, your initial code should not call getUpdateCount() to decide if it is a result set or not, it should use the return value of execute():

boolean isResultSet = statement.execute("call sp1(); select * from X;");
while (true) {
  if (isResultset) {
    // Null-check not necessary, a correctly implemented JDBC driver 
    // should never return null if execute or getMoreResults returned true
    try (ResultSet rs = statement.getResultSet()) {
      // process rs
    }
  } else {
    int uc = statement.getUpdateCount();
    if (uc == -1) {
      // when isResultSet is false and update count is -1, 
      // there are no more results
      break;
    }
    System.out.println("update count: " + uc);
  }
  isResultSet = statement.getMoreResults()
}

这篇关于有没有一种方法可以提前确定执行存储过程或函数时要返回的结果集的数量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！