ibatis对存储过程的调用

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>

 

DAO层代码:

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); }

 

存储过程:

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;