DECLARE
v VARCHAR2(4000);
type v_ar is table of number index by varchar2(5);
v_ar_tab v_ar;
l_idx NUMBER;
l_pick_fmt VARCHAR2(4000);
l_pick_uom VARCHAR2(3);
l_pick_qty NUMBER;
CURSOR c IS SELECT 'MC-25,IC-10' pick FROM dual
UNION ALL
SELECT 'MC-25,IC-10' pick FROM dual;
BEGIN
OPEN c;
loop
fetch c INTO v;
exit when c%notfound;
l_idx :=instr(v,',');
--dbms_output.put_line(substr(v,1,instr(v,'-')-1));
if l_idx =0 then
v_ar_tab(substr(v,1,instr(v,'-')-1)) :=to_number(substr(v,instr(v,'-')+1)) ;
ELSE
loop
l_idx :=instr(v,',');
IF l_idx >0 THEN
l_pick_fmt :=substr(v,1,l_idx-1);
v :=substr(v,l_idx+1);
ELSE
l_pick_fmt :=v;
v:=null;
END IF;
l_pick_uom:=substr(l_pick_fmt,1,instr(l_pick_fmt,'-')-1);
l_pick_qty :=to_number(substr(l_pick_fmt,instr(l_pick_fmt,'-')+1));
IF v_ar_tab.count >0 THEN
if (v_ar_tab.EXISTS(l_pick_uom)) THEN
v_ar_tab(l_pick_uom) :=v_ar_tab(l_pick_uom) + l_pick_qty;
ELSE
v_ar_tab(l_pick_uom) :=l_pick_qty;
END IF;
ELSE
v_ar_tab(l_pick_uom) :=l_pick_qty;
END IF;
exit when v is null ;
END loop;
END IF;
end loop;
l_pick_uom := v_ar_tab.FIRST; -- get subscript of first element
WHILE l_pick_uom IS NOT NULL LOOP
dbms_output.put_line(l_pick_uom);
dbms_output.put_line(v_ar_tab(l_pick_uom));
l_pick_uom := v_ar_tab.NEXT(l_pick_uom); -- get subscript of next element
END LOOP;
end;
No comments:
Post a Comment