這邊來寫一下JAVA連線SQL和執行StoreProcedure的做法.
先附上 oracle官方說明文件
https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html
範例如下
public static void getSupplierOfCoffee(String coffeeName, String[] supplierName) {
Connection con = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement pstmt = null;
ResultSet rs = null;
String query =
"select SUPPLIERS.SUP_NAME " +
"from SUPPLIERS, COFFEES " +
"where " +
"SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"and ? = COFFEES.COF_NAME";
pstmt = con.prepareStatement(query);
pstmt.setString(1, coffeeName);
rs = pstmt.executeQuery();
if (rs.next()) {
supplierName[0] = rs.getString(1);
} else {
supplierName[0] = null;
}
}
跟C#的作法可說沒什麼差別
建立Connection後以 PreparedStatement 執行.
最後的執行結果用ResultSet接回來.
叫用StoreProcedure
public static void myFunction(String inputParameter) {
Connection con = DriverManager.getConnection("jdbc:default:connection");
CallableStatement cs;
try {
// 設定 CallableStatement
cs = con.prepareCall("{call mySP(?)}");
// 定義 IN/OUT 參數的 Index 與型態
cs.registerOutParameter(1, Types.VARCHAR);
// 設定 IN/OUT 參數的 Index 及值
cs.setString(1, inputParameter);
// 執行並取回 IN/OUT 參數值
cs.execute();
String outParam = cs.getString(1); // IN/OUT 參數值
} catch (SQLException e) {
}
}
簡單列個範例,
有需要請在自行調整吧.