以后要在项目中多用用存储函数
1 先别写函数
2 ibatis sql
3 调用
直接调用ibatis查询方法即可
1 先别写函数
create or replace function select_funtion(v_id in varchar2, --号码
v_type in varchar2 --类型
) return number is
Result number;
begin
if v_type = 'type1' then
select count(1)
into Result
from tb_test
where C_LEVEL = '1'
and c_id = v_id
and C_CONTITION_1 is not null
and C_CONTITION_2 is null
and C_CONTITION_3 is null
and C_CONTITION_4 is null
and c_Del = '0';
end if;
if v_type = 'type2' then
--得到数量1
select count(1)
into Result
from tb_test
where c_id = v_id
and C_CONTITION_1 is not null
and C_CONTITION_2 is not null
and C_CONTITION_3 is null
and C_CONTITION_4 is null
and C_TEAM is null
and c_Del = '0';
end if;
if v_user_type = 'type3' then
--得到数量2
select ((select count(1)
from tb_test
where c_id = v_id
and C_CONTITION_1 is not null
and C_CONTITION_2 is not null
and C_CONTITION_3 is not null
and C_CONTITION_4 is null
and c_Del = '0'
) +
(select count(1)
from tb_test_2 t
where C_FK_ID = v_id
and t.c_stat = '1'
and c_Del = '0'))
into Result
from dual;
end if;
exception
when no_data_found then
Result := 0;
dbms_output.put_line(Result);
return(0);
end select_function;
2 ibatis sql
<select id="select_funtion" resultClass="java.lang.Integer" parameterClass="java.util.HashMap">
select select_funtion(#c_nme#,#c_phone#) as a from dual
</select>
3 调用
直接调用ibatis查询方法即可