//oracle中存储过程创建
create or replace procedure insdate(n_ins in varchar,n_categoryId in varchar,n_result out number) is
pro_row re_productinfo%rowtype;
v_count number(2);
begin
v_count := 0;
for pro_row in (select *
from re_productInfo product)
loop
select count(*) into v_count from re_productinfo pro where pro.periods=n_ins and pro.productcategoryid=n_categoryId ;
if v_count = 0 then
n_result :=1;
else
n_result :=2;
end if;
end loop;
commit;
end;
//
sql调用存储过程
<parameterMap class="java.util.Map" id="insdateParameter">
<parameter property="ins" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>
<parameter property="productCategoryId" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>
<parameter property="result" javaType="java.lang.Integer" jdbcType="INTEGER" mode="OUT" resultMap="n-result"/>
</parameterMap>
<resultMap class="java.util.Map" id="n-result">
<result property="result" javaType="java.lang.Integer" />
</resultMap>
<procedure id="getperiodsBycategoryId" parameterMap="insdateParameter" >
{call insdate(?,?,?)}
</procedure>
// 代码调用存储过程
public Integer getperiodsBycategoryId(
Map<String, Object> insdateParameter) {
this.getSqlMapClientTemplate().queryForObject("test.getperiodsBycategoryId", insdateParameter);
return (Integer) insdateParameter.get("result");
}