IBATIS 使用数据库函数

最新推荐文章于 2021-03-06 03:39:16 发布

最新推荐文章于 2021-03-06 03:39:16 发布 · 444 阅读

文章标签：

#iBATIS #C #C++ #C# #SQL

数据库专栏收录该内容

42 篇文章

订阅专栏

本文介绍了一个存储函数的具体实现，该函数根据不同输入参数查询数据库并返回相应记录数。通过三种不同类型的查询展示了如何根据条件筛选数据，并使用异常处理确保函数健壮性。

以后要在项目中多用用存储函数
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查询方法即可