转：http://blog.csdn.net/dancelonely/article/details/9363939

1、使用jdbcTemplate调用存储过程

Spring的SimpleJdbcTemplate将存储过程的调用进行了良好的封装。

一）无返回值的存储过程调用

CREATE OR REPLACE PROCEDURE TESTPRO(PARAM1 IN VARCHAR2,PARAM2 IN VARCHAR2) AS   
  BEGIN  
     INSERT INTO TESTTABLE (ID,NAME) VALUES (PARAM1, PARAM2);  
  END TESTPRO;  

?

   package com.dragon.test; 
  import org.springframework.jdbc.core.JdbcTemplate; 
  public class JdbcTemplateTest { 
     private JdbcTemplate jdbcTemplate; 
     public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
         this.jdbcTemplate = jdbcTemplate; 
     } 
     public void test(){ 
         this.jdbcTemplate.execute("call testpro('p1','p2')"); 
     } 
} 

?二）有返回值的存储过程（非结果集）

public void test() {    
  String param2Value = (String) jdbcTemplate.execute(    
     new CallableStatementCreator() {    
        public CallableStatement createCallableStatement(Connection con) throws SQLException {    
           String storedProc = "{call testpro(?,?)}";// 调用的sql    
           CallableStatement cs = con.prepareCall(storedProc);    
           cs.setString(1, "p1");// 设置输入参数的值    
             cs.registerOutParameter(2, OracleTypes.VARCHAR);// 注册输出参数的类型    
             return cs;    
        }    
     }, new CallableStatementCallback() {    
         public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {    
             cs.execute();    
             return cs.getString(2);// 获取输出参数的值    
          }    
  });    
}  

?三）有返回值的存储过程（结果集）

因oracle存储过程所有返回值都是通过out参数返回的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage。?

public void test() {    
  List resultList = (List) jdbcTemplate.execute(    
     new CallableStatementCreator() {    
        public CallableStatement createCallableStatement(Connection con) throws SQLException {    
           String storedProc = "{call testpro(?,?)}";// 调用的sql    
           CallableStatement cs = con.prepareCall(storedProc);    
           cs.setString(1, "p1");// 设置输入参数的值    
           cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型    
           return cs;    
        }    
     }, new CallableStatementCallback() {    
        public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {    
           List resultsMap = new ArrayList();    
           cs.execute();    
           ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值    
           while (rs.next()) {// 转换每行的返回值到Map中    
              Map rowMap = new HashMap();    
              rowMap.put("id", rs.getString("id"));    
              rowMap.put("name", rs.getString("name"));    
              resultsMap.add(rowMap);    
           }    
           rs.close();    
           return resultsMap;    
        }    
  });    
  for (int i = 0; i < res