653
Java中CallableStatement調用Oracle存儲過程總結
一、無返回值的存儲過程調用
CREATE OR REPLACE PROCEDURE PRO_1(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
INSERT INTO DBO.EMP (ID,NAME) VALUES (PARA1, PARA2);
END PRO_1;
import java.sql.*;
import java.sql.ResultSet;
public class CallProcedureTest1 {
public CallProcedureTest1() {
super();
}
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl ";
String user = "admin";
String pwd = "password";
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, pwd);
cs = conn.prepareCall("{ call DBO.PRO_1(?,?) }");
cs.setString(1, "10");
cs.setString(2, "Peter");
cs.execute();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
}
}
}
}
二、有返回值的存儲過程（非結果集）
CREATE OR REPLACE PROCEDURE PRO_2(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT name INTO PARA2 FROM EMP WHERE ID= PARA1;
END PRO_2;
import java.sql.*;
public class CallProcedureTest2 {
public CallProcedureTest2() {
super();
}
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "admin";
String pwd = "password";
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, pwd);
cs = conn.prepareCall("{ call DBO.PRO_2(?,?) }");
cs.setString(1, "10");
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String name = cs.getString(2);
System.out.println("name: " + name);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
}
}
}
}
cs.getString(2)中的數值2並非任意的，而是和存儲過程中的out列對應的，如果out是在第一個位置，那就是proc.getString(1)，如果是第三個位置，就是proc.getString(3)，
當然也可以同時有多個返回值，那就是再多加幾個out參數了。
三、返回列表
由於oracle存儲過程沒有返回值，它的所有返回值都是通過out參數來替代的，列表同樣也不例外，但由於是集合，所以不能用一般的參數，必須要用pagkage了.所以要分兩部分，
1 建一個程序包
CREATE OR REPLACE PACKAGE MYPACKAGE AS
TYPE MY_CURSOR IS REF CURSOR;
end MYPACKAGE;
2 建立存儲過程
CREATE OR REPLACE PROCEDURE PRO_3(p_CURSOR out MYPACKAGE.MY_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM DBO.EMP;
END PRO_3;
把遊標(可以理解為一個指針)作為一個out參數來返回值的。
import java.sql.*;
import java.sql.ResultSet;
public class CallProcedureTest2 {
public CallProcedureTest2() {
super();
}
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "admin";
String pwd = "password";
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, pwd);
cs = conn.prepareCall("{ call DBO.PRO_3(?) }");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
rs = (ResultSet) cs.getObject(1);
while (rs.next()) {
System.out.println("\t" + rs.getString(1) + "\t"
+ rs.getString(2) + "\t");
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
}
} catch (SQLException e) {
}
}
}
}
在執行前一定要先把oracle的驅動包放到class路徑裏，否則會報錯。
四、綜合的例子
// CallableStatement 1 - 調用一個含有out參數的procedure
CallableStatement cs=conn.prepareCall("{call mytestprc10(?,?,?)}");
cs.setString(1,"10");
cs.setString(2,"20");
cs.registerOutParameter(3,Types.INTEGER); // 注意此處對返回參數的設置方法
cs.executeUpdate();
int t=cs.getInt(3);
System.out.println("CallableStatement 1 :"+t);
cs.close();
// CallableStatement 2 - 調用一個返回數值型參數的函數
CallableStatement cs2=conn.prepareCall("{ ? = call mytestpkg1.myf_mult(?,?)}");
cs2.registerOutParameter(1,Types.INTEGER);// 注意此處對返回參數的設置方法
cs2.setInt(2,2);
cs2.setInt(3,3);
cs2.executeUpdate();
int t2=cs2.getInt(1);
System.out.println("CallableStatement 2 : " + t2);
cs2.close();
//CallableStatement 3 - 調用一個返回Cursor類型參數的函數
CallableStatement cs3=conn.prepareCall("{?=call mytestpkg1.myf_rtnrcd(?)}");
cs3.registerOutParameter(1, OracleTypes.CURSOR); // 注意此處對返回參數的設置方法，和上麵的有所不同
cs3.setInt(2,20);
cs3.executeUpdate();
ResultSet rs3=(ResultSet)cs3.getObject(1);
rs3.next();
System.out.println("CallableStatement 3 : " + rs3.getString(2));
rs3.close();
cs3.close();
原帖地址：https://wenku.baidu.com/view/34d4be13227916888486d71d.html
最後更新：2017-04-03 15:22:11