引用
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>
分享到:
相关推荐
ibatis调用oracle的函数,存储过程的方法,从网上收集的,还没有整理。
ibatis调用oracle存储过程分页
ibatis调用oracle存储过程
Ibatis调用Oracle存储过程,返回自定义的类型。
NULL 博文链接:https://fengtiejun.iteye.com/blog/1603851
ibatis+oracle简单实例,其中包括ibatis jar包以及oracle驱动包
ibatis 读取oracle clob类型
ibatis oracle ibatis-2.3.3.jar ojdbc14.jar ibatis连接oracle所需的jar包
iBatis调用存储过程
struts2+spring+ibatis+oracle+分页搜索+上传附件实例!完整版!
ibatis调用存储过程使用示例,处理带参数及无参数类型的存储过程,是一个不错的例子。
spring+ibatis+oracle分页缓存源码
用ibatis调用存储过程的介绍,使用java语言进行开发
ibatis调用存储过程.txt [removed] var share_info = {}; [removed]
使用ibatis+oracle完成简单的增删改查的例子,把源码导入myeclipse,修改链接数据库信息,数据库需要自己建立
本资源适合刚刚接触ibatis+oracle的小伙伴使用,欢迎交流,吐槽。
Spring MVC+ibatis+Oracle资源整合,实现简单的增删改查
IBATIS调用存储过程
spring mvc+ibatis+oracle单表增删改(包都有)