Spring調用Oracle的Function函數

最近搜尋了很多網上講Spring 操作Oracle內部對象的例子，基本上是介紹調用預存程序的~直接調用Oracle內建函式Function的文章真的不多~這裡，法老參考了Spring論壇上外國友人的例子，寫一個DEMO供大家參考~

測試Oracle函數:
Here is a basic function that returns sysdate in a REF CURSOR:

FUNCTION MY_DEMO_FNC
    RETURN    MY_REFCURSOR_PKG.RefCursor
AS    
    return_date MY_REFCURSOR_PKG.RefCursor;
BEGIN
    OPEN return_date FOR 'select  sysdate from dual';
    return return_date;
END MY_DEMO_FNC;

測試程式:
Here is the class that accesses it:import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.StoredProcedure;

public class RefCursorTestDao 
...{
  public static void main(String[] args) throws Exception 
  ...{
    new RefCursorTestDao().execute();
  }

  public void execute() throws Exception 
  ...{
    DataSource ds = new DriverManagerDataSource(
        "oracle.jdbc.driver.OracleDriver",
        "jdbc:oracle:thin:@localhost:1521:SID1", 
        "user", "password");

    DemoStoredProcedure proc = new DemoStoredProcedure(ds);
    Map params = new HashMap();
    proc.execute(params);
  }

  private class DemoStoredProcedure extends StoredProcedure 
  ...{
    public static final String SQL = "MY_TEST_PKG.MY_DEMO_FNC";

    public DemoStoredProcedure(DataSource ds) 
    ...{
      setDataSource(ds);
      setSql(SQL);
      setFunction(true);
      declareParameter(
         new SqlOutParameter(
             "whatever", OracleTypes.CURSOR,  new DemoRowMapper()));
      compile();
    }
  }

  private class DemoRowMapper implements RowCallbackHandler 
  ...{
    public void processRow(ResultSet rs) throws SQLException 
    ...{
      System.out.println(rs.getTimestamp(1));
    }
  }
  
}

The process is the same for a real procedure with the REF CURSOR as an OUT parameter, but #setFunction should be false instead of true.
參考地址:http://forum.springframework.org/archive/index.php/t-10054.html