1.測試新增資料的procedure
複製代碼代碼如下:
public void testProcedure() {
Connection con = getConnction();
// **1.測試新增資料的procedure
String procedure = "{call users_insert_proc(?,?,?,?) }";
CallableStatement cs = null;
try {
cs = con.prepareCall(procedure);
cs.setInt(1, 123450);
cs.setString(2, "xxiaox");
cs.setString(3, "Ww342864");
cs.setString(4, "[email protected]");
} catch (SQLException e) {
e.printStackTrace();
}
try {
cs.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
2.測試刪除資料的procedure
複製代碼代碼如下:
public void testDelPro() {
Connection con = getConnction();
// **2.測試刪除資料的procedure
String procedure = "{call delete_usersbyid_proc(?) }";
CallableStatement cs = null;
try {
cs = con.prepareCall(procedure);
cs.setInt(1, 123450);
} catch (SQLException e) {
e.printStackTrace();
}
try {
cs.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
3.測試更新資料的procedure
複製代碼代碼如下:
public void testDelPro() {
Connection con = getConnction();
// **3.測試更新資料的procedure
String procedure = "{call users_updatebyId_proc(?,?,?,?) }";
CallableStatement cs = null;
try {
cs = con.prepareCall(procedure);
cs.setInt(1, 101); cs.setString(2, "小第三方的浩"); cs.setString(3, "asdf342864"); cs.setString(4, "[email protected]");
} catch (SQLException e) {
e.printStackTrace();
}
try {
cs.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
4.測試查找資料的procedure
a)建包體
b)建立查詢的procedure
複製代碼代碼如下:
create or replace package userspackage as
type users_cursor is ref cursor;
end userspackage;
複製代碼代碼如下:
create or replace procedure users_packageAll(
s_id in number ,u_cursor out userspackage.users_cursor) is
begin
if s_id = 0 then
open u_cursor for select id,name,pword,email from users;
else
open u_cursor for select id,name,pword,email from users where id=s_id;
end if;
end;
c)Java調用
複製代碼代碼如下:
public void testDelPro() {
Connection con = getConnction();
// 回傳查詢procedure
String procedure = "{call users_packageAll(?,?) }";
CallableStatement cs = null;
try {
cs = con.prepareCall(procedure);
cs.setInt(1, 0);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
} catch (SQLException e) {
e.printStackTrace();
}
try {
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(2);
while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}