操作大批量数据或复杂逻辑，考虑到执行效率和代码量的时候，存储过程和函数在数据库中是预编译好的，调用执行效率高
// 调用过程 {call 过程名称(?,?,?)}
public static void test1() throws Exception {
Connection connection = request.getSession().connection();
String sql = "{call addmethod(?,?,?)}";
CallableStatement call = connection.prepareCall(sql);
// 设置输入参数
call.setInt(1, 3);
call.setInt(2, 7);
// 设置输出参数
call.registerOutParameter(3, Types.INTEGER);
call.execute();
int ans = call.getInt(3);
System.out.println(ans);
}
// 调用函数{?= call 函数名称(?,?)}
public static void test3() throws Exception {
Connection connection = request.getSession().getConnection();
String sql = "{?= call addmethod1(?,?)}";
CallableStatement call = connection.prepareCall(sql);
// 设置输入参数
call.setInt(2, 3);
call.setInt(3, 7);
// 设置返回值
call.registerOutParameter(1, Types.INTEGER);
call.execute();
int ans = call.getInt(1);
System.out.println(ans);
}
oracle存储过程
create or replace procedure addmethod(n1 in number,n2 in number,ans out number) as
begin
select n1 + n2 into ans from dual;
end;
oracle函数
create or replace function addmethod1(n1 number,n2 number,ans out number)
Result number;
begin
select n1 + n2 into ans from dual;
return ans;
end;