函数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;

}