java调用数据库中的函数和存储过程
1.调用函数
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
{call <procedure-name>[(<arg1>,<arg2>, ...)]}*/
public int testFunction() {
String sql = "{?= call cal_add(?, ?)}";
conn = DBHelper.getOracleConnection();
int r = -1;
try {
cs = conn.prepareCall(sql);
cs.registerOutParameter(1, OracleTypes.NUMBER);
cs.setInt(2, 5);
cs.setInt(3, 7);
cs.execute();
r = cs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}
DBHelper.destroy(conn, cs, rs);
return r;
}
2. 调用存储过程
public void testProcedure() {
String sql = "{call myprowithout(?,?,?)}";
conn = DBHelper.getOracleConnection();
try {
cs = conn.prepareCall(sql);
cs.setInt(1, 109);
cs.registerOutParameter(2, OracleTypes.VARCHAR);
cs.registerOutParameter(3, OracleTypes.VARCHAR);
cs.execute();
System.out.println(cs.getString(2));
System.out.println(cs.getString(3));
} catch (SQLException e) {
e.printStackTrace();
}
DBHelper.destroy(conn, cs, rs);
}
3.调用带游标的存储过程
public void testProcedureWithCursor() {
String sql = "{call print_stu.put_stuinfo(?,?)}";
conn = DBHelper.getOracleConnection();
try {
cs = conn.prepareCall(sql);
cs.setInt(1, 95031);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
OracleCallableStatement ocs = (OracleCallableStatement)cs;
rs = ocs.getCursor(2);
while(rs.next()) {
System.out.print(rs.getString("sno")+" ");
System.out.print(rs.getString("sname")+" ");
System.out.print(rs.getString("ssex")+" ");
System.out.print(rs.getDate("sbirthday")+" ");
System.out.println(rs.getInt("class"));
}
} catch (SQLException e) {
e.printStackTrace();
}
DBHelper.destroy(conn, cs, rs);
}