Java calls to Oracle stored procedures

One: Stored procedure with no return value

The stored procedure is:

CREATE OR REPLACE PROCEDURE inch VARCHAR2 inch VARCHAR2)   as BEGIN   INSERT  into VALUES (PARA1, PARA2); END TESTA;

Then, call it in Java with the following code:

ImportJava.sql.*;ImportJava.sql.ResultSet; Public classTestprocedureone { PublicTestprocedureone () {} Public Static voidMain (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; Proc= Conn.preparecall ("{Call HyQ. TESTA (?,?)} "); Proc.setstring (1, "100"); Proc.setstring (2, "Testone");    Proc.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)

The stored procedure is:

CREATE OR REPLACE PROCEDURE inch VARCHAR2 VARCHAR2)   as BEGIN   SELECT  into  from WHERE i_id=  PARA1; END TESTB;

Use the following code when calling in Java:

 Public classTestproceduretwo { PublicTestproceduretwo () {} Public Static voidMain (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 (?,?)} "); Proc.setstring (1, "100"); Proc.registeroutparameter (2, Types.varchar);      Proc.execute (); String Testprint= Proc.getstring (2); System.out.println ("=testprint=is=" +testprint); }    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 to 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, it must be used pagkage. So it's going to be divided into two parts,

1, build a package. As follows:

CREATE OR REPLACE Package Testpackage  as isCURSOR; End Testpackage;

2, establish the stored procedure, the stored procedure is:

CREATE OR REPLACE PROCEDURE  is BEGIN    OPEN  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:

ImportJava.sql.*;ImportJava.io.OutputStream;ImportJava.io.Writer;Importjava.sql.PreparedStatement;ImportJava.sql.ResultSet;ImportOracle.jdbc.driver.*; Public classTestprocedurethree { PublicTestprocedurethree () {} Public Static voidMain (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.TESTC (?)}")); Proc.registeroutparameter (1, Oracle.jdbc.OracleTypes.CURSOR);      Proc.execute (); RS= (ResultSet) proc.getobject (1);  while(Rs.next ()) {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) {} }}}

Java calls to Oracle stored procedures