procedure_游标内循环游标匹配值

最新推荐文章于 2025-08-18 16:49:50 发布

原创最新推荐文章于 2025-08-18 16:49:50 发布 · 150 阅读

0 ·

CC 4.0 BY-SA版权

DateSource 专栏收录该内容

105 篇文章

订阅专栏

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