ssm调用存贮过程

service:
/**
* 修改状态
* @param receiveEntity
* @return
/
public String[] updateStatus(ReceiveEntity receiveEntity){
String id = super.save(receiveEntity);
Map<String,Object> map = new HashMap<String, Object>();
map.put(“receiveids”,receiveEntity.getId());
// 点击确认按钮，保存收货单明细到库存
if (HsjgConstant.STATUS_SURE.equals(receiveEntity.getStatus())){
map.put(“operating”,1);
// 点击取消确认按钮
}else{
map.put(“operating”,-1);
}
repertoryDao.CalculationStock(map);
Integer status = (Integer)map.get(“status”);
String message = (String)map.get(“message”);
return new String[]{status.toString(),message};
}
dao:
/*
* 采购入库存储过程调用
* @param map
* @return
*/
Map<String, Object> CalculationStock(Map<String, Object> map);
xmL:


{call Prochase_Storage(
#{receiveids, mode=IN, jdbcType=VARCHAR},
#{operating, mode=IN, jdbcType=VARCHAR},
#{status, mode=OUT, jdbcType=INTEGER},
#{message, mode=OUT, jdbcType=VARCHAR}
)}

oracle:
CREATE OR REPLACE
PROCEDURE Prochase_Storage(receiveids in varchar2,operating in varchar2,status out Integer,message out varchar2) IS
cursor recelive_list is --获取传入采购收货单的所有收货数量和物料id
select a.materid,a.actualnum,b.id,b.quantity,c.materielname from HSJG_TB_NORMAL_RECEIVED a
LEFT JOIN hsjg_tb_stock_repertory b on a.materid = b.materid
left join HSJG_TB_NORMAL_MATERIELMAIN c on c.id = A.MATERID
where a.receiveid in (select * from table (strsplit(receiveids)));

BEGIN
for list_rows in recelive_list LOOP
if list_rows.id is not null then
if operating = 1 then --确认
update hsjg_tb_stock_repertory set quantity = quantity + list_rows.actualnum where materid = list_rows.materid;
else – 取消确认
if list_rows.quantity >= list_rows.actualnum then
update hsjg_tb_stock_repertory set quantity = quantity - list_rows.actualnum where materid = list_rows.materid;
else
status := -1;
message := list_rows.materielname||‘库存总量小于订单量,取消确认失败’;
DBMS_OUTPUT.PUT_LINE(message);
RETURN;
end if;
end if;
else
insert into hsjg_tb_stock_repertory
select s_hsjg_tb_stock_repertory.nextval,list_rows.actualnum,list_rows.materid from dual;
end if;
end loop;
status := 1;
message := ‘采购入库存储过程执行成功’;
DBMS_OUTPUT.PUT_LINE(message);
COMMIT;
Exception
When others then
status := -1;
message := ‘采购入库存储过程异常’;
DBMS_OUTPUT.PUT_LINE(message);
Rollback;
END Prochase_Storage;