几种应用程序调用Oracle存储过程的方法

一、Java中调用带Cursor的存储过程：

存储过程定义：

PROCEDURE GET_CURSOR_RESULT2(CR  OUT CURSOR_RESULT) IS
     BEGIN
       OPEN CR FOR
      select t1.id, t1.account_id, t2.account, t1.key, t1.total, t1.status
        from table t1;
END GET_CURSOR_RESULT2;

调用GET_CURSOR_RESULT2存储过程：

jdbcTemplate.execute(new CallableStatementCreator() {
	public CallableStatement createCallableStatement(Connection con) throws SQLException {
	CallableStatement cs = con.prepareCall("{call TRAIL_GETCURSOR.GET_CURSOR_RESULT2(?)}");
	cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
	return cs;
}				
}, new CallableStatementCallback<List<Account>>() {
	public List<Account> doInCallableStatement(CallableStatement cs) {
	List<Account> accounts = null;
	ResultSet rs = null;
	cs.execute();
	rs = (ResultSet) cs.getObject(1);
	while(rs.next()) {				
		long id = rs.getLong("id");
		String accountName = rs.getString("account");
		long accountID = rs.getLong("account_id");
		long key = rs.getLong("key");
		long total = rs.getLong("total");
		accounts.set...
	}
	return accounts;
});

jdbcTemplate.execute()有两个参数：CallableStatement和CallableStatementCallback

二、Java中保证调用存储过程的事务

transTemplate.execute(new TransactionCallback() {
	public Object doInTransaction(TransactionStatus status) {
		try{
			success=jdbcTemplate.execute(...);
			if(!success) {
				status.setRollbackOnly();
			}
		} catch(e) {
			status.setRollbackOnly();
		}

);

在jdbcTemplate外面包上一个transTemplate，通过判断jdbcTemplate执行结果决定是否回退 status.setRollbackOnly()