IBATIS调用oracle function（函数）的方法实例

引用

 

create or replace function getClassifiedCode(p_planCode               in varchar2 -- 险种代码
                                            ,
                                             p_usageAttributeCode     in varchar2 -- 使用性质代码
                                            ,
                                             p_ownershipAttributeCode in varchar2 -- 所属性质代码
                                            ,
                                             p_vehicleTypeCode        in varchar2 -- 车辆种类代码
                                             ) return varchar2 is
  v_classified_code varchar2(10);
  v_vehicleTypeCode varchar2(200) := p_vehicleTypeCode;
  v_message         varchar2(2000);
  -- 定义字符串数组类型
  type type_array is varray(9) of varchar2(200);
  vehicleArray type_array := type_array('A010:A011:A012',
                                        'A020:A021:A022:A032',
                                        'A030:A031:A042:A052',
                                        'B010:B011:B012',
                                        'B020:B021:B022:B032',
                                        'B030:B031:B042',
                                        'C010:C011:C020:C021:C030:C031:B140:B141:E010:E011:E110:E111:C012:C022:C032:C042:B142:E012:E022:E112:E122',
                                        'D110:D111:D010:D011:D012:D022:D112',
                                        'C012:C112:C122:C132:C142:C152:C162:C172:C182');

begin

  for i in 1 .. vehicleArray.count loop
        if (instr(vehicleArray(i), p_vehicleTypeCode, 1, 1) > 0) then
      v_vehicleTypeCode := vehicleArray(i);
       exit;
    end if;
  end loop;

  select max(class_rule_code)
    into v_classified_code
    from class_rule_factor
   where factor_code = 'F00085'
     and factor_value_set = p_planCode;
  

  if (v_classified_code is not null) then
    return v_classified_code;
  end if;

  select max(class_rule_code)
    into v_classified_code
    from class_rule_factor
   where class_rule_code in
         (select distinct class_rule_code
            from class_rule_factor
           where class_rule_code in
                 (select distinct class_rule_code
                    from class_rule_factor
                   where factor_code = 'F00039'
                     and factor_value_set = p_usageAttributeCode)
             and factor_code = 'F00040'
             and factor_value_set = p_ownershipAttributeCode)
     and factor_code = 'F00042'
     and factor_value_set = v_vehicleTypeCode;

  if (v_classified_code is not null) then
    return v_classified_code;
  end if;

  select max(class_rule_code)
    into v_classified_code
    from class_rule_factor
   where class_rule_code in
         (select distinct class_rule_code
            from class_rule_factor
           where factor_code = 'F00039'
             and factor_value_set = p_usageAttributeCode)
     and factor_code = 'F00042'
     and factor_value_set = v_vehicleTypeCode;

  if (v_classified_code is not null) then
    return v_classified_code;
  end if;

  select distinct  max(class_rule_code)
    into v_classified_code
    from class_rule_factor
   where factor_code = 'F00999'
     and factor_value_set = 'OTHER';

  return v_classified_code;
exception
  when others then
    v_message := sysdate || '日运行错误，错误号:' || SQLCODE || '错误原因:' ||
                 substr(sqlerrm, 1, 1800);
    -- dbms_output.put_line(v_message);
    return null;
end getClassifiedCode;

 

	@Override
	public String getClassifiedCode(Map paramMap) throws PafaDAOException {
		String classifiedCode = null;
		try{
			this.getSqlMapClientTemplate().queryForObject("apply.getClassifiedCode", paramMap);
			classifiedCode = (String) paramMap.get("result");
		}catch(DataAccessException e){
			throw NBAExceptionFactory.createPafaDAOException(this.getClass().getName(), "getClassifiedCode", "根据险种代码查找classifiedCode异常 .", "参数：paramMap = "+paramMap, e);
		}
		return classifiedCode;
	}

  

 

<!-- 获取任务分类 -->    	
<parameterMap id="getClassifiedCodeParamMap" class="java.util.Map">         
		<parameter property="result" 					jdbcType="VARCHAR"   		 javaType="java.lang.String" mode="OUT" />
		<parameter property="planCode" 					jdbcType="VARCHAR"           javaType="java.lang.String" mode="IN" />         
		<parameter property="usageAttributeCode" 		jdbcType="VARCHAR"           javaType="java.lang.String" mode="IN" />         
		<parameter property="ownershipAttributeCode" 	jdbcType="VARCHAR"           javaType="java.lang.String" mode="IN"/>
		<parameter property="vehicleTypeCode" 			jdbcType="VARCHAR"           javaType="java.lang.String" mode="IN"/>
</parameterMap>  

<procedure  id="getClassifiedCode"  parameterMap="getClassifiedCodeParamMap">           
	{ ?= call epciscde.getClassifiedCode(?,?,?,?)}            
</procedure>