The stored procedure in Oracle Middle Background has a fucntion (function) and procedure. Where function acquires the return value in the first way:? = CALL Client_no (?,?). Because function returns the result via the RETURN statement. The procedure is no Return statement. Therefore, there is only a parameter through the OUT type to return the result, but multiple results can be returned in this way (multiple definitions can be possible), which is the function that function cannot be implemented. So the procedure is called by Call Client_no (?,?). . . . . . . General flow
1. Get the CallableStatement statement: (About CallableStatement) Reference to another blog)
Callablestatement cs = conn.preparecall ("{call spname (?,?,?)}");
2. Pass input parameters and register output parameters
CS.SETXXX (INDEX, VALUE); // Enter parameter cs.registeroutparameter (index, type); // output parameter
3. Execute the stored procedure:
cs.execute ();
/ ** * Begin is not original * / a stored procedure is not returned by Boolean type: boolean flag = callablestatement.execute (); If Flag is True, then the description returns a result set, you can use GetResultSet () Get the result of the current line, and if you return to flash, what is it?
If you don't work, you can't explain, you can only explain that the current pointer is not ResultSet, it is possible to update the count (UpdateCount) may also be nothing.
So how do you handle when the current pointer is FLASE? We should get getUpdateCount (); if you return -1, it is neither a result set, not an update count. Description did not return. And if getUpdateCount () returns 0 or more than 0, The current pointer is a update count (possibly the DDL instruction when 0). Whether it is a return result set or an update count, then there may be other returns. Only in the current pointer getResultSet () == null && getupdateCount () == -1 Table not to return more.
The return of the stored procedure is similar. The return result of each process is equivalent to the ROW of the ResultSet, but the ROW of the stored procedure is first in the first line instead of the results, and the next is first until the first line, the stored procedure moves down One line with getMoreresults (), equivalent to the result () of ResultSet (). Similarly it returns to Boolean and the above Flag, just means that the current line is a resultset, if it is flash, you still want to judge whether it is UpdateCount, in each line, At the same time, it is judged whether it is RESULTSET or UpdateCount. If one is one of them, it is necessary to continue getMoreResults (). When not ResultSet is not UpdateCount, the description does not return the result, then acquire the output parameter.
/ ** * End * / The following example I have not used the above method (^ _ ^), not used! / ********************* *************** * Using the cursor to take the result set, in Oracle's single procedure * unable to return the result set. ************* ************************************************************ ************ * Stored Procedure Section Begin ***************************************** **** / // packages create or replace package gprmi is
- Author: Meconsea - Created: 2005-3-29 15:40:02 - Purpose: Details of processing information based on information ID and information TYPE - Public Type Declarations Type Outlist is Ref Cursor;
- Public Function and Procedure Declarations Procedure GetProremarkInfo
Infoidarg in workflow.processinfo.infoid% type, - // information ID
Infotypearg in workflow.processinfo.infotype% type, - // information type
Result_cursor out outlist
);
End gprmi;
// package bagies
Create or Replace Package Body GPRMI IS
- Function and Procedure Implementations Procedure GetProremarkInfo
Infoidarg in workflow.processinfo.infoid% type, - // information ID
Infotypearg in workflow.processinfo.infotype% type, - // information type
Result_cursor out outlist
IS
Begin
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;
/ ************************************ * Store procedure part end ****** *************************************************** ***************** * Program section begin *************************** ********** / ** * Query the processing of this information based on the information type and information ID
Ocs.registerOutParameter (3, Oracle.jdbc.Oracletypets.cursor); Ocs.Execute (); rs = opcute (); 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.setdate (5)); PRI. SetProcessDate (RS.GetDate (6)); pri.setprocessRemark (Rs.getstring (7)); pri.setttitude (rs.getlong (8)); al.add (pri);}}}}}}}}}}}}}}}}}}}}}}}}}}; ^ 4;} Rs.close (); OCS. Close ();} catch (exception e) {system.out.println ("Exception E" E.getMessage ());} Return Al;} / ************** ***************************************************************************************************************** ************* /
