Call Oracle stored procedure Summary in Java (GO)

1, call+ package name + stored procedure name (incoming, outgoing value with?) )     String str= "{call Smsbusiness.deletezhzmember (?,?,?)}"; /2, establish the connection Connection conn=null; Conn=drivermanager.getconnection ();//3, using java.sql.* class CallableStatement Cs=conn.preparecall (str);//4, incoming in value Cs.setint (1,id); Cs.setint (2,-2);//5, set out value Cs.registeroutparameter (3,types.numeric),//6, execute Cs.excuse (),//7, remove out value int flag= Cs.getint (3);//8, Guan Connection conn.close ();

Article referenced from: ********************************************************************************************************* This period of time began to learn to write stored procedures, the main reason or because of the work needs it, originally thought very simple, but after setbacks, heroic exhausted, but finally straightened out, in order to avoid the subsequent less detours, special account and this, but also to their own encouragement. One: Stored procedure with no return value stored procedure is: CREATE OR REPLACE PROCEDURE TESTA (PARA1 in Varchar2,para2 in VARCHAR2) Asbegin INSERT into HyQ. b_id (I_id,i_name) VALUES (PARA1, PARA2); END TESTA; Then, call it in Java with the following code:

Package com.hyq.src; Import Java.sql.*;import Java.sql.ResultSet; public class Testprocedureone {public Testprocedureone () {} public static void Main (string[] args) {String Driver =    "Oracle.jdbc.driver.OracleDriver";    String strURL = "Jdbc:oracle:thin:@127.0.0.1:1521:hyq";    Statement stmt = null;    ResultSet rs = null;    Connection conn = null;     CallableStatement cstmt = null;      try {class.forname (driver);      conn = Drivermanager.getconnection (strURL, "HyQ", "HyQ"); callablestatement proc = null; Create the object that executes the stored procedure proc = Conn.preparecall ("{Call HyQ. TESTA (?,?)} ");      Set the stored procedure call keyword. Proc.setstring (1, "100");      Set the first input parameter proc.setstring (2, "Testone");//Set the second input parameter Proc.execute ();//execute} catch (SQLException ex2) {    Ex2.printstacktrace ();    } catch (Exception ex2) {ex2.printstacktrace ();          } finally{try {if (rs! = null) {rs.close ();          if (stmt!=null) {stmt.close ();    }      if (conn!=null) {conn.close (); }}} catch (SQLException Ex1) {}}}}

Of course, this is the first request to build a table TESTTB, inside two fields (I_id,i_name).

Two: Stored procedure with return value (non-list) stored procedure: CREATE OR REPLACE PROCEDURE testb (PARA1 in Varchar2,para2 out VARCHAR2) asbegin SELECT into PARA2 From TESTTB WHERE i_id= PARA1; END Testb; Call in Java with the following code:

Package com.hyq.src; public class Testproceduretwo {public Testproceduretwo () {} public static void Main (string[] args) {String Driver =    "Oracle.jdbc.driver.OracleDriver";    String strURL = "Jdbc:oracle:thin:@127.0.0.1:1521:hyq";    Statement stmt = null;    ResultSet rs = null;    Connection conn = null;      try {class.forname (driver);      conn = Drivermanager.getconnection (strURL, "HyQ", "HyQ");      callablestatement proc = null; proc = Conn.preparecall ("{Call HyQ. TESTB (?,?)} "); Set the stored procedure proc.setstring (1, "100");//Set the first parameter input parameter Proc.registeroutparameter (2, Types.varchar);//The second parameter output parameter is VARCHAR Type Proc.execute ();//execute String testprint = proc.getstring (2);//Get output parameter System.out.println ("=testprint=is=" +t    Estprint);    } catch (SQLException ex2) {ex2.printstacktrace ();    } catch (Exception ex2) {ex2.printstacktrace ();          } finally{try {if (rs! = null) {rs.close (); if (stmt!=null) {STMT.close ();          } if (Conn!=null) {conn.close (); }}} catch (SQLException Ex1) {}}}}

Note that the value in Proc.getstring (2) Here is not arbitrary, but corresponds to the Out column in the stored procedure, if out is in the first position, that is proc.getstring (1), if it is a third position, it is proc.getstring (3), of course, you can also have multiple return values, that is, add a few more out parameters.

Three: Return list because the Oracle stored procedure does not return a value, all its return values are replaced by out parameters, and the list is no exception, but because it is a collection, it is not possible to use the general parameters, Pagkage must be used. So it's going to be divided into two parts, 1, to build a package. As follows: Create or REPLACE package testpackage as TYPE test_cursor is REF cursor;end testpackage;2, establish a stored procedure as: Create or Repla CE PROCEDURE TESTC (p_cursor out testpackage. Test_cursor) Isbegin OPEN p_cursor for SELECT * from HyQ. TESTTB; END TESTC; As you can see, it is a cursor (which can be understood as a pointer) that returns a value as an out parameter. Use the following code when calling in Java:

Package Com.hyq.src;import Java.sql.*;import Java.io.outputstream;import java.io.writer;import  Java.sql.preparedstatement;import Java.sql.resultset;import oracle.jdbc.driver.*; public class Testprocedurethree {public Testprocedurethree () {} public static void Main (string[] args) {String drive    r = "Oracle.jdbc.driver.OracleDriver";    String strURL = "Jdbc:oracle:thin:@127.0.0.1:1521:hyq";    Statement stmt = null;    ResultSet rs = null;     Connection conn = null;      try {class.forname (driver);       conn = Drivermanager.getconnection (strURL, "HyQ", "HyQ");      callablestatement proc = null; proc = Conn.preparecall ("{Call HYQ.TESTC (?)}"); Stored Procedure HyQ proc.registeroutparameter (1,oracle.jdbc.oracletypes.cursor);//Set output parameter is a cursor. The first parameter, the cursor type Proc.execute ( );//Perform rs = (ResultSet) proc.getobject (1); Get the first parameter is a cursor, converted to ResultSet type while (Rs.next ())///Get Data {System.out.println ("<tr><td>" + RS . getString (1) + "</td><td>" +rS.getstring (2) + "</td></tr>");    }} catch (SQLException ex2) {ex2.printstacktrace ();    } catch (Exception ex2) {ex2.printstacktrace ();          } finally{try {if (rs! = null) {rs.close ();          if (stmt!=null) {stmt.close ();          } if (Conn!=null) {conn.close (); }}} catch (SQLException Ex1) {}}}}

Note here that you must first put the Oracle driver package in the class path before execution, otherwise you will get an error.

Call Oracle stored procedure Summary in Java (GO)