create or replace procedure insert_screen_asn_pre(PAGE_NUMBER int)
as
type cursors is ref cursor;
curs cursors;
V_SUP_NAME SCREEN_ASN_PRE.Sup_Name%type;
V_ASN_CODE SCREEN_ASN_PRE.Asn_Code%type;
V_ARRIVAL_PRE SCREEN_ASN_PRE.Arrival_Pre%type;
nums int:=0;
begin
open curs for select sup.name,a.code,a.estimate_date from wms_asn a
left join wms_organization sup on sup.id = a.supplier_id
where a.status in ('OPEN','ACTIVE') and a.estimate_date is not null
and not exists(select 1 from SCREEN_ASN_PRE sc where sc.asn_code = a.code)
;
loop
fetch curs into V_SUP_NAME,V_ASN_CODE,V_ARRIVAL_PRE;
exit when curs%notfound;
insert into SCREEN_ASN_PRE(TYPE,Sup_Name,ASN_CODE,ARRIVAL_PRE)
values('预到货预警',V_SUP_NAME,V_ASN_CODE,V_ARRIVAL_PRE);
nums:= nums+1;
--dbms_output.put_line('loop:'||nums);
if nums >= PAGE_NUMBER
then
commit;
nums:=0;
end if;
end loop;
--dbms_output.put_line('end loop:'||nums);
if nums >0
then
commit;
nums:=0;
end if;
--dbms_output.put_line('end if:'||nums);
exception
when others
then
dbms_output.enable(buffer_size => null);
dbms_output.put_line(dbms_utility.format_error_backtrace);
--dbms_output.put_line('sqlcode:'||sqlcode);
dbms_output.put_line('sqlerrm:'||sqlerrm);
rollback;
close curs;
end;
begin
insert_screen_asn_pre(3);
end;
--输出,假设curs值等于10
loop:1
loop:2
loop:3
loop:1
loop:2
loop:3
loop:1
loop:2
loop:3
loop:1
end loop:1
end if:0