ibatis对存储过程的调用

精选转载

winters1224 2012-03-07 10:24:23 博主文章分类：SSI框架

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" >

</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>

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;