java调用oracle存诸过程实例

 

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import KPIState;

/**
 * @author yangbagang
 *
 */
public class KPIStateViewDao extends JdbcDaoSupport {

    private Log logger = LogFactory.getLog(this.getClass());
    
    
    @SuppressWarnings("unchecked")
   public List getKPIState(String posid, String empid,String from,Stringto,String isMain, String type) throws CannotGetJdbcConnectionException,SQLException {
        logger.debug("enter KPIStateViewDao#getKPIState");
        logger.debug("posid is " + posid);
        logger.debug("empid is " + empid);
        logger.debug("begin at " + from);
        logger.debug("end at " + to);
        logger.debug("isMain " + isMain);
        logger.debug("type is " + type);
        List list = new ArrayList();
        List kList = new ArrayList();
        List result = new ArrayList();
        ResultSet rs = null;
        ResultSet rs2 = null;
        CallableStatement cs = this.getConnection().prepareCall("{call Pkg_Meuubi_Stat.p_Stat_Target(?,?,?,?,?,?,?,?,?,?)}");
        cs.setString(1, from);
        cs.setString(2, to);
        cs.setString(3, type);
        cs.setString(4, "0");
        //According to DBA a "'" was added
        cs.setString(5, "'" + empid + "'");
        cs.setString(6, "0");
        //According to DBA a "'" was added
        cs.setString(7, "'" + posid + "'");
        cs.setString(8, isMain);
        cs.registerOutParameter(9, oracle.jdbc.OracleTypes.CURSOR);
        cs.registerOutParameter(10, oracle.jdbc.OracleTypes.CURSOR);
        cs.execute();
        rs = (ResultSet) cs.getObject(9);
        rs2 = (ResultSet) cs.getObject(10);
        while (rs.next()) {
            KPIState state = new KPIState();
            state.setName(rs.getString("target"));
            state.setRef(rs.getString("ref_val"));
            state.setReal(rs.getString("real_val"));
            state.setTarget(rs.getString("target_val"));
            state.setSame(rs.getString("period_val"));
            state.setDiff(rs.getString("def_val"));
            state.setDrate(rs.getFloat("def_rate"));
            list.add(state);
        }
        while (rs2.next()) {
            kList.add(rs2.getString(1));
        }
        result.add(list);
        result.add(kList);
        rs=rs2=null;
        return result;
    }
}

 

原文：http://lanxincao.blog.ccidnet.com/blog-htm-itemid-159830-do-showone-type-blog-uid-2307.html