几种应用程序调用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()