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")+"\t");

System.out.print(rs.getString("sname")+"\t");

System.out.print(rs.getString("ssex")+"\t");

System.out.print(rs.getDate("sbirthday")+"\t");

System.out.println(rs.getInt("class"));

}

 

} catch (SQLException e) {

e.printStackTrace();

}

DBHelper.destroy(conn, cs, rs);

}