procedure_游标内循环游标匹配值


--游标 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;

 

本站评论列表

评论内容请遵守相关法律法规,所有评论内容人工审核,一旦确定为垃圾评论,永久禁止用户发言。
使用qq免注册登陆:使用qq登陆
      
本站声明
本文转载自:ITEYE博客频道     作者:minyongcheng    发布日期:2015-09-09
本站转载的文章为个人学习借鉴使用,本站对版权不负任何法律责任。如果侵犯了您的隐私权益,请联系我们删除。


 
© 2014-2016 ITdaan.com 粤ICP备14056181号