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;