--游标 bbs 内部循环游标 C_EVENT,当匹配上值时后台打印信息
create or replace procedure insert_SCREEN_INVENTORY_LACK
as
v_1 varchar2(100);v_2 varchar2(100);v_3 varchar2(100);v_4 float;
type bbs is ref cursor;
bb bbs;
CURSOR C_EVENT is select t.sup_code,t.item_code,t.item_status,t.quantity_lack
from SCREEN_INVENTORY_LACK_RULE t where t.type = '报缺库存规则表';
p_1 SCREEN_INVENTORY_LACK_RULE.Sup_Code%TYPE;
p_2 SCREEN_INVENTORY_LACK_RULE.Item_Code%type;
p_3 SCREEN_INVENTORY_LACK_RULE.Item_Status%type;
p_4 SCREEN_INVENTORY_LACK_RULE.Quantity_Lack%type;
v_num int;
begin
open bb for
select sup.code,item.code,ik.extend_propc1,sum(i.quantity_bu)
from wms_inventory i left join wms_item_key ik on ik.id = i.item_key_id
left join wms_item item on item.id = ik.item_id
left join wms_location l on l.id = i.location_id
left join wms_organization sup on sup.id = ik.supplier_id
where 1=1 and l.type = 'STORAGE'
group by sup.code,item.code,ik.extend_propc1;
Dbms_Output.enable(buffer_size => null);
v_num:=0;
loop
fetch bb into v_1,v_2,v_3,v_4;
exit when bb%notfound;
OPEN C_EVENT;--打开游标
FETCH C_EVENT into p_1,p_2,p_3,p_4; --取值
v_num:=1;
while C_EVENT%found and v_num=1
Loop
if p_1 = v_1 and p_2=v_2 and p_3=v_3
then
v_num:=v_num+1;
dbms_output.put_line('---------'||p_1||':'||p_2||':'||p_3||':'||v_num);
end if;
FETCH C_EVENT into p_1,p_2,p_3,p_4; --取值
End Loop;
CLOSE C_EVENT;
dbms_output.put_line(v_1||':'||v_2||':'||v_3||':'||v_4||':'||v_num);
end loop;
close bb;
end;