函数Demo

函数写法：
create or replace function f_getGoodsKuChun(gdId in varchar2)
  return varchar2
as
   v_gd_ismany varchar2(1);
   v_gd_id_child  varchar2(32);
   v_amount_num  number(12,2);
   v_yw_num  number(12,2); --业务数量
   v_min_num  number(12,2);
   type refcur_type is REF CURSOR;
   pcur refcur_type;
begin
v_gd_ismany := '0';
v_yw_num:=0;
v_amount_num:=0;

select t.GD_ISMANY into v_gd_ismany from t_ec_goods2 t where t.gd_id = gdId;

if v_gd_ismany='1' then --组合商品
     open pcur for
          select gd_id_child,cd_amount from t_ec_goodschild where gd_id=gdId order by cd_row;
          loop
          fetch pcur into v_gd_id_child,v_amount_num;
                if pcur%notfound then
                    return v_min_num;
                    exit;
                end if;
               
                select sum(nvl(GD_SL,0) - nvl(frozen_amount,0) - nvl(out_amount,0)) into v_yw_num
          from t_jxc_kc_factstock f,t_jxc_da_storeroom s
          where f.gd_id=v_gd_id_child
          and f.room_bm=s.room_bm and s.room_yn='Y';

                v_amount_num := FLOOR(v_yw_num/v_amount_num);
               
                if v_min_num is null then
                   v_min_num := v_amount_num;
                else
                   if v_amount_num < v_min_num then
                      v_min_num := v_amount_num;
                   end if;
                end if;

     end loop;
     Close pcur;
else --普通商品
      select sum(nvl(f.GD_SL,0) - nvl(f.frozen_amount,0) - nvl(f.out_amount,0)) into v_min_num
      from t_jxc_kc_factstock f,t_jxc_da_storeroom s
where f.room_bm = s.room_bm and  s.room_yn = 'Y' and f.gd_id = gdId;
end if;


  return v_min_num;


end f_getGoodsKuChun;

Java中调用函数的代码：
public int checkKucun(String gdId) {
Connection con = null;
CallableStatement ps = null;
int re = 0;
try {
con = commerceDao.getCurrentConnection();
// 调用存储过程
ps = con.prepareCall("{?=call f_getGoodsKuChun(?)}");
ps.registerOutParameter(1, Types.INTEGER);
ps.setString(2, gdId);

// 执行
ps.execute();
// 获得输出结果
re = ps.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
return re;

}