ibatis对存储过程的调用

from：‍http://blog.csdn.net/hfhwfw/archive/2010/05/05/5559409.aspx

‍IBatis映射文件：

<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" > 
<sqlMap namespace="BUSSINESS_B_T_PRODUCT" > 
     
<parameterMap id="hasAuthParam" class="java.util.HashMap" > 
    <parameter property="p_productCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/> 
    <parameter property="p_customerId" jdbcType="NUMBER" javaType="java.lang.Long" mode="IN"/> 
    <parameter property="p_callType" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/> 
    <parameter property="p_total" jdbcType="NUMBER" javaType="java.lang.Long" mode="IN"/> 
    <parameter property="result" jdbcType="INTEGER" javaType="java.lang.Integer" mode="INOUT"/> 
</parameterMap> 

<procedure id="hasAuth" parameterMap="hasAuthParam" > 
<!--[CDATA[   
      {call BUSINESSES_HAS_AUTH(?,?,?,?,?)}   
]]--> 
</procedure> 
     
</sqlMap> 
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="BUSSINESS_B_T_PRODUCT" >

<parameterMap id="hasAuthParam" class="java.util.HashMap" >
    <parameter property="p_productCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    <parameter property="p_customerId" jdbcType="NUMBER" javaType="java.lang.Long" mode="IN"/>
    <parameter property="p_callType" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    <parameter property="p_total" jdbcType="NUMBER" javaType="java.lang.Long" mode="IN"/>
    <parameter property="result" jdbcType="INTEGER" javaType="java.lang.Integer" mode="INOUT"/>
</parameterMap>

<procedure id="hasAuth" parameterMap="hasAuthParam" >
<!--[CDATA[
      {call BUSINESSES_HAS_AUTH(?,?,?,?,?)}
]]-->
</procedure>

</sqlMap>

DAO层代码:

view plaincopy to clipboardprint?
public Integer hasAuth(String productCode, Long customerId,String callType,Long total){   
        Map<String,Object> map = new HashMap<String,Object>();   
        map.put("p_productCode", productCode);   
        map.put("p_customerId", customerId);   
        map.put("p_callType", callType);   
        map.put("p_total", total);   
        map.put("result", -1);   
        queryData("BUSSINESS_B_T_PRODUCT.hasAuth", map);   
        Integer result = (Integer)map.get("result");   
        System.out.println(result);   
        return Integer.valueOf(result);   
    } 
public Integer hasAuth(String productCode, Long customerId,String callType,Long total){
   Map<String,Object> map = new HashMap<String,Object>();
   map.put("p_productCode", productCode);
   map.put("p_customerId", customerId);
   map.put("p_callType", callType);
   map.put("p_total", total);
   map.put("result", -1);
   queryData("BUSSINESS_B_T_PRODUCT.hasAuth", map);
   Integer result = (Integer)map.get("result");
   System.out.println(result);
        return Integer.valueOf(result);
}

存储过程:

view plaincopy to clipboardprint?
CREATE OR REPLACE PROCEDURE GBOSS.BUSINESSES_HAS_AUTH   
(p_productCode IN VARCHAR2, p_customerId IN number, p_callType IN VARCHAR2,p_total in number,result out integer)   
is   
v_count integer;   
v_stateCount integer;   
v_seviceTypeState varchar(1);   
v_outstanding C_T_CUSTOMER.OUTSTANDING%type;      
v_billingModel C_T_CUSTOMER_PRODUCT.CP_BILLING_MODEL%type; v_unitPrice C_T_CUSTOMER_PRODUCT.UNIT_PRICE%type;   
v_cpId C_T_CUSTOMER_PRODUCT.CP_ID%type;             
v_currConsu C_T_CUSTOMER_PRODUCT.Curr_Consu%type;     
v_q C_T_BLXS_PRICING.q%type;       
v_p2 C_T_BLXS_PRICING.P2%type;      
BEGIN   
result:=0;                      
select count(1) into v_count from C_T_CUSTOMER_PRODUCT cp,B_T_PRODUCT p,C_T_SERVICE_TYPE t   
where p.PRODUCT_ID=cp.PRODUCT_ID and cp.CP_ID=t.CP_ID and cp.OPER_STATE='1' and t.ST_SERVICE_TYPE=p_callType     
and p.PRODUCT_CODE=p_productCode and cp.CUSTOMER_ID=p_customerId;   
     
if v_count=0 then result:=41 ;         
elsif v_count>1 then result:=42 ;     
elsif v_count=1 then    

     select t.ST_SERVICE_TYPE_STATE,c.OUTSTANDING,cp.CP_BILLING_MODEL,cp.UNIT_PRICE,cp.cp_id,cp.curr_consu     
     into v_seviceTypeState,v_outstanding,v_billingModel,v_unitPrice,v_cpId,v_currConsu    
     from C_T_CUSTOMER_PRODUCT cp,B_T_PRODUCT p,C_T_SERVICE_TYPE t,C_T_CUSTOMER c   
     where p.PRODUCT_ID=cp.PRODUCT_ID and cp.CP_ID=t.CP_ID and cp.OPER_STATE='1' and t.ST_SERVICE_TYPE=p_callType and p.PRODUCT_CODE=p_productCode and cp.CUSTOMER_ID=p_customerId   
            and c.CUSTOMER_ID=cp.CUSTOMER_ID;   
               
     if v_seviceTypeState='2' then        
         select count(1) into v_stateCount from C_T_CUSTOMER_PRODUCT cp,B_T_PRODUCT p,C_T_SERVICE_TYPE t   
         where p.PRODUCT_ID=cp.PRODUCT_ID and cp.CP_ID=t.CP_ID and cp.OPER_STATE='1' and t.ST_SERVICE_TYPE=p_callType and p.PRODUCT_CODE=p_productCode   
                and cp.CUSTOMER_ID=p_customerId and t.ST_SERVICE_TYPE_STATE ='2' and cp.CP_INF_BONUS>cp.CP_INF_USE;   
         if v_stateCount>0 then result:=3;       
         else result:=43;                        
         end if;   

     elsif v_seviceTypeState='3' then     
         select count(1) into v_stateCount from C_T_CUSTOMER_PRODUCT cp,B_T_PRODUCT p,C_T_SERVICE_TYPE t   
         where p.PRODUCT_ID=cp.PRODUCT_ID and cp.CP_ID=t.CP_ID and cp.OPER_STATE='1' and t.ST_SERVICE_TYPE=p_callType and p.PRODUCT_CODE=p_productCode   
                and cp.CUSTOMER_ID=p_customerId and t.ST_SERVICE_TYPE_STATE ='3' and cp.CP_DATA_BONUS>cp.CP_DATA_USE;   
         if v_stateCount>0 then result:=4;      
         else result:=44;                       
         end if;   
     end if;   

end if;   
exception   
when others then   
result := 48;   
END;

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/hfhwfw/archive/2010/05/05/5559409.aspx