1)创建出参为游标的存储过程

--创建一个产品合作伙伴的package

create or replace package XXX.product_partner_proc is

  -- Author  : XXX

  -- Created : 2014/11/11 9:39:29

  -- Purpose : XXX

  -- Public function and procedure declarations

   type T_CURSOR is ref cursor; 

  procedure get_sale_partner_um_code(p_partner_code IN VARCHAR2,

                                     re_cursor   OUT T_CURSOR );                       

end product_partner_proc;


create or replace package body product_partner_proc is

  PROCEDURE get_sale_partner_um_code(p_partner_code IN VARCHAR2,re_cursor  OUT T_CURSOR) IS

  BEGIN

    OPEN re_cursor FOR

      SELECT distinct company_code as um_code

        FROM assistance_company;

  EXCEPTION

    WHEN OTHERS THEN

      OPEN re_cursor FOR

        SELECT '' um_code FROM dual WHERE 1 = 2;

  END get_sale_partner_um_code;

end product_partner_proc;


GRANT EXECUTE ON XXX.product_partner_proc TO prodopr;


2)ibatis配置:注意事项是，对应的参数要和存储过程中对应


<resultMap id="accountResult" class="AgentDTO">

   <result property="account" column="um_code"/>

</resultMap>

    

<parameterMap id="partnerAccountParam" class="java.util.Map">

          <parameter property="p_partner_code" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />

              <!-- parameter在配置是不要换行，换行可能会有问题 -->

           <parameter property="re_cursor" jdbcType="ORACLECURSOR" 

                             javaType="java.sql.ResultSet" mode="OUT" resultMap="accountResult"/>

</parameterMap>

     

<procedure id="queryPartnerAccouts" parameterMap="partnerAccountParam">

         {call proddata.product_partner_proc.get_sale_partner_um_code(?,?)}

</procedure>


3)java代码调用::注意事项是，对应的参数要和存储过程中对应

public List<AgentDTO> queryPartnerAccouts(String partnerCode) throws PafaDAOException {

try {

Map<String, Object> paramMap = new HashMap<String, Object>();

List cursorList = new ArrayList();

paramMap.put("p_partner_code", partnerCode);

paramMap.put("re_cursor", cursorList);

super._queryForObject("queryPartnerAccouts", paramMap);

List<AgentDTO> list = (List<AgentDTO>) paramMap.get("re_cursor");

return list;

} catch (Exception e) {

}

}