java调用Oracle存储过程的方法实例
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, "742621646@qq.com");
} 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, "742621646@qq.com");
} 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();
}
}