Spring invokes the result set of an Oracle stored procedure

Oracle is always different for advanced features (I hate this, if it's a product that needs to be programmed specifically for this product, and that's why I never looked at a platform like WebLogic), the big object access is set to use its own LOB objects, but luckily I can still pass long Raw to replace. So that the program does not need a specific encoding. But for the stored procedure (I mean the stored procedure that returns the result set), I have no way to handle Oracle with a generic program. Too many textbooks or articles that call stored procedures are simply irresponsible to perform some of the stored procedures that have a few or no results, making it impossible for most readers to know exactly how to invoke the result set of a stored procedure. In spring, there is no real complete introduction to the processing of the result set of the stored procedure, let alone the result set processing of the "special" stored procedure such as Oracle. Let's take a quick look at the general process of how we handle the result set of a stored procedure in JDBC:

1. Get CallableStatement statement:

CallableStatement cs = conn. prepareCall("{call spName(?,?,?)}");

2, incoming input parameters and registration output parameters

cs. setXXX(index,value);//输入参数
cs. registerOutParameter(index,type);//输出参数

3. Execute the stored procedure:

cs.execute();

For a stored procedure, if the result we know is known beforehand, it can be handled according to the order in which the stored procedure is defined (in fact generally not), but how do you define a generic process for handling complex, multiple result sets? Let's take a look at what JDBC itself can provide for us.

After a stored procedure executes, it returns a Boolean:

boolean flag = callableStatement. execute();

If flag is true, then the description returns a result set (ResultSet) type, and you can use Getresultset () to get the result of the current row, and if you return to flase, what does that mean? If you do not process, nothing can be explained, It can only be explained that the current pointer is not a resultset, possibly an update count (Updatecount) or nothing.

So what if the current pointer is flase? We should first Getupdatecount (); If return-1, it is neither a result set nor an update count. The instructions are not returned. If Getupdatecount () returns 0 or greater than 0, the current pointer is the update count (possibly a DDL directive at 0). Whether you return a result set or update count, you may continue to have other returns. Only the current pointer Getresultset () ==null && getupdatecount () = = 1 indicates no more returns.

The return of the stored procedure is similar to the ResultSet, and the return result of each processing is equivalent to the row of the ResultSet, except that the row of the stored procedure is first in the first row, not like ResultSet, before the first row, The stored procedure moves down one line with getmoreresults (), equivalent to the resultset next (). Also it returns Boolean and the above flag, just to show whether the current line is resultset, if it is flase, you still have to judge is not updatecount, in each row, must first of all be judged resultset or Updatecount , if one of them is to continue getmoreresults (), when it is not resultset or updatecount, the result is not returned, and then the output parameter is obtained.

Do you understand? So let's write a generic process based on the rules above:

First, we want to determine when we didn't say the result set:

if(cs.getResultSet() == null && cs. getUpdateCount() == -1)

Now let's do a loop:

ResultSet rs = null;
int updateCount = -1;
flag = cs。execute();
do{
updateCount = cs。getUpdateCount();
if(updateCount != -1){//说明当前行是一个更新计数
//处理。
cs。getMoreResults();
continue;//已经是更新计数了,处理完成后应该移动到下一行
//不再判断是否是ResultSet
}
rs = cs。getResultSet();
if(rs != null){//如果到了这里,说明updateCount == -1
//处理rs
cs。getMoreResults();
continue;
//是结果集,处理完成后应该移动到下一行
}
//如果到了这里,说明updateCount == -1 && rs == null,什么也没的了
}while(!(updateCount == -1 && rs == null));
cs.getXXX(int);//获取输出参数