Oracle存储过程：游标与动态SQL(二)
utput.put_line('v_stockid:'||v_stockName||',fitemcode:'||v_itemcode||',instockdate:'||v_instockDate);
EXIT WHEN cur_query%notfound;
if (v_stockid is not null ) and (v_itemcode is not null) then
-- 遍历记录，如果不是同一仓库与品种.则从新获取对应仓库品种中的期初库存或上日结存数量
if (v_temp_stockid is null or v_temp_stockid!= v_stockid) and (v_temp_itemcode is null or v_temp_itemcode!= v_itemcode) then
v_temp_stockid := v_stockid;
v_temp_itemcode := v_itemcode;
v_temp_last_store := 0;
-- 调用存储过程，获取结存数量
prc_stock_historystore(v_temp_stockid,p_fbegdate,v_temp_itemcode,v_qc_store);
dbms_output.put_line('期初数量:'||v_qc_store);
v_temp_last_store := nvl(v_qc_store,0)+ nvl(v_last_store,0);
else
v_temp_last_store := v_temp_last_store + nvl(v_last_store,0);
end if;
-- 通过结存数量，计算每笔的期初数量 -- 结存=期初+入库-出库-损耗
insert into yy_temp_store_detail (fstockid, fstockcode, finstorkdate, fitemcode, in_store, out_store, lost_store, last_store)
values (v_stockid, '', v_instockDate, v_itemcode, v_in_store, v_out_store, v_lost_store, v_temp_last_store);
end if;
--dbms_output.put_line('name:'||v_stockName||',itemcode:'||v_itemcode||',date:'||v_instockDate||',in_store:'||v_in_store||',out_store:'||v_out_store);
END LOOP;
CLOSE cur_query;
-- 返回集合
open cur_result for select a.fname,t.finstorkdate,t.fitemcode,t.in_store,t.out_store,t.lost_store,t.last_store
from yy_temp_store_detail t
left join yy_bd_stock a on t.fstockid = a.pk_id
order by a.fcode asc,t.fitemcode, t.finstorkdate asc ;
END;