java call oracle stored procedure (JDBC)

java call oracle stored procedure (JDBC)oracle stored procedure in the background, there fucntion (functions) and procedure (process) of points.One function is a way to get through the first return value:? = Call client_no (?,?)。Because the function is through the return statement to return the results.The procedure is no return statement. Therefore, only through out the type of parameters to return results, but this way you can return multiple results (more than a few out parameters can be defined), which is function can not be achieved.Therefore procedure call is through call client_no (?,?)。 . . . . . .Common processes in general1. Get CallableStatement statement: (the use of information on callablestatement another one blog)CallableStatement cs = conn.prepareCall ("(call spName (?,?,?)}");2. Pass input parameters and output parameters registercs.setXXX (index, value); / / input parameterscs.registerOutParameter (index, type); / / output parameters3. Implementation of the stored procedure:cs.execute ();/ *** Begin not the original* /After the execution of a stored procedure returns the boolean type:boolean flag = callableStatement.execute ();If the flag is true, then the instructions to return a result set (ResultSet) type, you can use getResultSet () get the current line where theResults, and if returns flase, say?If you do not deal with what can not explain, can only show the current pointer is not a ResultSet, may be updated count (updateCount)May also be due to nothing against.So if the current pointer for the flase how to deal with? We should first getUpdateCount (); return -1 if neither is the result set, thenNot update the count was. Shows no return of the. And if getUpdateCount () returns 0 or greater than 0, it indicates the current target is to update the count (0 when there may be DDL command). Whether to return a result set or update count, then the other may also continue to return. Only in the presentMeans the pointer getResultSet () == null & & getUpdateCount () == -1 return only shows no more.ResultSet stored procedure returns and similar returns results for each treatment is the equivalent of ResultSet's Row, only stored procedures RowFirst in the first line of not only as the ResultSet first next to the first row, moving down the line to use the stored procedure getMoreResults (), phaseWhen the ResultSet's next (). As it returns boolean and the same as the above flag, only to illustrate the current row is not a ResultSet, ifflase, you still have to determine is not updateCount, each line must first determine whether the same or ResultSet UpdateCount, such asFruit is one will have to continue getMoreResults (), if not the ResultSet is not updateCount, the description does not return results, andThe next time you get output parameters./ *** End* /The following examples use the above method I have not (^_^), are not very good!/************************************** Used to take the cursor result set, in a single procedure in oracle* Can not return result sets.*************************************//************************************** Stored procedure part of the begin*************************************// / Packagescreate or replace package gprmi is- Author: meconsea- Created: 2005-3-29 15:40:02- Purpose: id, and information according to type of information dealt with the details of the information- Public type declarationstype outlist is ref cursor;- Public function and procedure declarationsprocedure getproremarkinfo (infoIdArg in workflow.processinfo.infoid% type, - / / message idinfoTypeArg in workflow.processinfo.infotype% type, - / / information typeresult_cursor out outlist);end gprmi;/ / Package badiescreate or replace package body gprmi is- Function and procedure implementationsprocedure getproremarkinfo (infoIdArg in workflow.processinfo.infoid% type, - / / message idinfoTypeArg in workflow.processinfo.infotype% type, - / / information typeresult_cursor out outlist) Isbegin open result_cursor for select a.currentprocessor, c.name as personname, b.currenttache, d.tachename, a.receivedate, a.processdate, a.processremark, a.attitude from processinfoattach a, processinfo b, operator c, Tache d where a.processinfoid = b.objectid and a.processstatus = 1 and b.infotype = infoTypeArg and b.infoid = infoIdArg and a.currentProcessor = c.objectid and b.currentTache = d.objectid order by a.processdate;end;end gprmi;/************************************** Stored procedure part of the end*************************************//************************************** Some programs begin*************************************// ** * Based on the information type and information ID check processing of the information * @ Param infoId - / / message id * @ Param infoType - / / information type * @ Return * /public ArrayList getProcessRemarkInfo (long infoId, long infoType) ( ArrayList al = new ArrayList (); ProcessRemarkInfo pri = null; oracle.jdbc.OracleCallableStatement ocs = null; String sqlStr = "(call gprmi.getproremarkinfo (?,?,?)}"; ResultSet rs = null; try ( ocs = (oracle.jdbc.OracleCallableStatement) conn.prepareCall (sqlStr); ocs.setLong (1, infoId); ocs.setLong (2, infoType); ocs.registerOutParameter (3, oracle.jdbc.OracleTypes.CURSOR); ocs.execute (); rs = ocs.getCursor (3); while (rs.next ()) ( pri = new ProcessRemarkInfo (); long currentProcessor = rs.getLong (1); pri.setCurrentProcessor (currentProcessor); pri.setPersonName (rs.getString (2)); pri.setCurrentTache (rs.getLong (3)); pri.setTacheName (rs.getString (4)); pri.setReceiveDate (rs.getDate (5)); pri.setProcessDate (rs.getDate (6)); pri.setProcessRemark (rs.getString (7)); pri.setAttitude (rs.getLong (8)); al.add (pri); ) rs.close (); ocs.close (); ) Catch (Exception e) ( System.out.println ("Exception e" + e.getMessage ()); ) return al;)/************************************** Process section end*************************************/