Sample Code Analysis for calling the Oracle stored procedure using Java

OracleIn the databaseStored ProcedureCan be usedJavaJava calls Oracle stored procedures are divided into calling stored procedures without return values and stored procedures with returned values. This article will introduce this part. Next let's take a look at it.

1. Stored Procedure without return values

The stored procedure is:

 
 

  
  
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS  
  
  
 
  
  
BEGIN  
  
  
 
  
  
INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);  
  
  
 
  
  
END TESTA; 
 
 

Then, the following code is used for calling in java:

 
 

  
  
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;  
  
  
 
  
  
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, we need to create a table named TESTTB, which contains two fields: I _ID and I _NAME ).

2. Stored Procedures with returned values are not listed)

The stored procedure is:

 
 

  
  
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS  
  
  
 
  
  
BEGIN  
  
  
 
  
  
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;  
  
  
 
  
  
END TESTB; 
 
 

Use the following code when calling in java:

 
 

  
  
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(?,?) }");  
  
  
 
  
  
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:Proc. the value 2 in getString (2) is not arbitrary, but corresponds to the out column in the stored procedure. If the out column is in the first position, it is proc. getString (1). If it is the third position, it is proc. getString (3), of course, you can also have multiple return values at the same time, that is, add a few more out parameters.

The example of calling the stored procedure of Oracle Database in Java is described here. I hope this introduction will help you.