清除记录 [英] Purging records
问题描述
我正在尝试使用sql游标清除案例： -
游标all_cases是
游标all_cases是
选择c.id00_warehouse，c.id00_case_nbr，c.id00_status_flag，c.id00_dlm
来自phpick00 a，chcart00 b，idcase00 c
其中a.ph00_shipto =''特尔福德''
和a.ph00_soldto =''Buncrana''
和b.ch00_pkt_ctl_nbr = a .ph00_pkt_ctl_nbr
和b.ch00_ref_case_nbr = c.id00_case_nbr
和c.id00_warehouse =''KL''
和a.ph00_warehouse =' 'KL''
和b.ch00_warehouse =''KL''
和c.id00_status_flag> ='''90''
和a.ph00_pkt_stat_flg =''我'
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm）< =（选择trunc（last_day（add_mont hs（sysdate，-1）））+ 1来自双）
顺序c.id00_dlm
联合所有
选择c.id00_warehouse，c.id00_case_nbr，c.id00_status_flag，c.id00_dlm
来自phpick00 a，chcart00 b，idcase00 c
其中a.ph00_shipto =''Kaisersl''
和a.ph00_soldto =''Buncrana''
和b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
和b.ch00_ref_case_nbr = c.id00_case_nbr
和c.id00_warehouse =''TE''
和a.ph00_warehouse =''TE''
和b.ch00_warehouse =' 'TE''
和c.id00_status_flag> ='''90''
和a.ph00_pkt_stat_flg =''我'
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm）< =（select trunc（last_day（ add_months（sysdate，-1）））+ 1来自双）
命令b y c.id00_dlm;
/ *我曾经有过一次，用户以某种方式交换了案件，并且发运了一些案件
逃过了我的第一次查询。这就是为什么还有第二个!! * /
光标all_cases_backup是
选择cases.id00_warehouse，cases.id00_case_nbr
来自
（从idcase00中选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse，id00_dlm
其中id00_warehouse =''TE''
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm）< =（select trunc（last_day（add_months（sysdate，-1） ））+ 1来自双）
订购id00_dlm
）案例，chcart00 b，phpick00 c
其中b.ch00_case_nbr = cases.id00_reference_carton_nbr
和b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
和c.ph00_shipto =''Kaisersl''
和c.ph00_soldto> =' 'Buncrana''/ *欺骗优化器* /
和c.ph00_soldto< =''Buncrana''
和c。 ph00_pkt_stat_flg =''我'
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm）< =（从双重中选择trunc（last_day（add_months（sysdate，-1）））+ 1）
order by cases.id00_dlm
联盟所有
select cases.id00_warehouse，cases.id00_case_nbr
来自
（选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse，id00_dlm
$ b来自idcase00的$ b
其中id00_warehouse =''KL''
和id00_status_flag ='''95''
和trunc（id00_dlm）> ; =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm）< =（select trunc（last_day（add_months（sysdate，-1） ）））+ 1来自双）
命令由id00_dlm
）个案，chcart00 b，phpick00 c
其中b.ch00_case_nbr =个案。 id00_reference_car ton_nbr
和b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
和c.ph00_shipto =''Telford''
和c.ph00_soldto> = ''Buncrana''/ *欺骗优化器* /
和c.ph00_soldto< =''Buncrana''
和c.ph00_pkt_stat_flg =''我' '
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm） < =（选择trunc（last_day（add_months（sysdate，-1）））+ 1来自双）
order by cases.id00_dlm
UNION ALL
选择id00_warehouse，id00_case_nbr
来自
（选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse，id00_dlm
来自idcase00
其中id00_warehouse =''KL''
和id00_status_flag ='''95''
和trunc（id00_dlm）> =（选择trun c（last_day（add_months（sysdate，-81））+ 1）来自dual）
和trunc（id00_dlm）< =（select trunc（last_day（add_months（sysdate，-1）））+ 1来自双）
）案例，
chcart00 b，
phpick00 c
其中b.ch00_case_nbr = id00_reference_carton_nbr
和b.ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
和c.ph00_shipto in（''Edingen''，''EDINGEN''，''edingen''）
和c.ph00_soldto> =''Buncrana''/ *欺骗优化器* /
和c.ph00_soldto< =''Buncrana''
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm）< =（select trunc（ last_day（add_months（sysdate，-1）））+ 1来自双）
order by cases.id00_dlm;
case_rec all_cases％rowtype ;
case_b_rec all_cases_backup％ro wtype;
计数器整数;
total_recs整数;
total_back整数;
开始
dbms_output.put_line（''启动程序FOL_PURGE_CASES''）;
dbms_output.put_line（''开始主循环''）;
counter：= 1;
total_recs：= 0;
for case_rec in all_cases
loop
if （case_rec.id00_status_flag< ''90''）然后
退出; - 这应该永远不会发生
结束如果;
从idcase00删除
其中id00_case_nbr = case_rec.id00_case_nbr
和id00_warehouse = case_rec.id00_warehouse
和id00_status_flag> ='''90''; - 让我们玩起来更加安全！
total_recs：= total_recs + 1;
counter：= counter + 1;
如果计数器> 1000然后
commit;
counter：= 1;
end if;
end loop;
提交;
计数器：= 1;
total_back：= 0;
for case_b_rec in all_cases_backup
循环
从idcase00删除
其中id00_case_nbr = case_b_rec.id00_case_nbr
和id00_warehouse = case_b_rec.id00_warehouse
和id00_status_flag> ='''90''; - 让我们玩起来更加安全！
total_back：= total_back + 1;
counter：= counter + 1;
如果计数器> 1000然后
commit;
counter：= 1;
end if;
end loop;
commit;
dbms_output.put_line（''正常结束程序FOL_PURGE_CASES''）;
dbms_output.put_line（to_char（total_recs） ||''记录被清除!!''）;
dbms_output.put_line（to_char（total_back）||''记录被清除... WITH BACKUP QUERY！''）;
/ *这实际上与清除案件无关但我必须把它放在某处！
它解决了pkms程序中的一个小错误。 * /
更新illocn00
设置il00_com_div =''''
其中il00_com_div> ''''
和il00_sku =''''
和il00_actl_inventory_cases = 0
和il00_directed_putaway_cases = 0;
提交;
/ *你可能不会相信它，但是这里有另一个更新来解决pkms程序中的问题。 * /
更新illocn00
设置il00_max_inventory_units = 0，
il00_remn_units_capacity = 0
其中il00_max_inventory_units > 0;
提交;
dbms_output.put_line（''地点已清理过！''）;
EXCEPTION
当其他的时候 - 处理所有其他错误
dbms_output.put_line（''EXCEPTION发生!!''）;
dbms_output.put_line（SQLERRM）;
ROLLBACK;
end;
/
我收到错误消息
连接到：
Oracle8i企业版版本8.1.7.4.0 - 生产
使用分区选项
JServer版本8.1.7.4.0 - 生产
启动程序FOL_PURGE_CASES
清洁位置
清理位置！
----- ****** -----
没有上架位置错误解决方法！
没有上架位置bug解决方法 - 清理！
开始主循环
all_cases cusor完成
EXCEPTION发生!!
ORA-01555：快照太旧：回滚段名称为ROLL24的第24号太小了
PL / SQL程序顺利完成。
我想更改查询，使其更加强大，并可能使用另一个光标在特定日期之间选择数据，
回滚段已增加但仍然出错？任何人都可以帮忙？
Hi,
I am trying to purge cases using an sql cursor:-
cursor all_cases is
cursor all_cases is
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = ''Telford''
and a.ph00_soldto = ''Buncrana''
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
and c.id00_warehouse = ''KL''
and a.ph00_warehouse = ''KL''
and b.ch00_warehouse = ''KL''
and c.id00_status_flag >= ''90''
and a.ph00_pkt_stat_flg = ''I''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by c.id00_dlm
union all
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = ''Kaisersl''
and a.ph00_soldto = ''Buncrana''
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
and c.id00_warehouse = ''TE''
and a.ph00_warehouse = ''TE''
and b.ch00_warehouse = ''TE''
and c.id00_status_flag >= ''90''
and a.ph00_pkt_stat_flg = ''I''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by c.id00_dlm;
/* I have had it once that the users had somehow swapped cases and some of the cases shipped had
escaped my first query. That''s why there is a second !! */
cursor all_cases_backup is
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''TE''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = ''Kaisersl''
and c.ph00_soldto >= ''Buncrana'' /* to trick the optimizer */
and c.ph00_soldto <= ''Buncrana''
and c.ph00_pkt_stat_flg = ''I''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
union all
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''KL''
and id00_status_flag = ''95''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = ''Telford''
and c.ph00_soldto >= ''Buncrana'' /* to trick the optimizer */
and c.ph00_soldto <= ''Buncrana''
and c.ph00_pkt_stat_flg = ''I''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
UNION ALL
select id00_warehouse, id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''KL''
and id00_status_flag = ''95''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
) cases,
chcart00 b,
phpick00 c
where b.ch00_case_nbr = id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
and c.ph00_shipto in (''Edingen'',''EDINGEN'',''edingen'')
and c.ph00_soldto >= ''Buncrana'' /* to trick the optimizer */
and c.ph00_soldto <= ''Buncrana''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm;
case_rec all_cases%rowtype;
case_b_rec all_cases_backup%rowtype;
counter integer;
total_recs integer;
total_back integer;
begin
dbms_output.put_line(''Starting procedure FOL_PURGE_CASES'');
dbms_output.put_line(''Starting main loop'');
counter := 1;
total_recs := 0;
for case_rec in all_cases
loop
if (case_rec.id00_status_flag < ''90'') then
exit; -- this should never happen
end if;
delete from idcase00
where id00_case_nbr = case_rec.id00_case_nbr
and id00_warehouse = case_rec.id00_warehouse
and id00_status_flag >= ''90''; -- let''s play extra safe !
total_recs := total_recs + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;
counter := 1;
total_back := 0;
for case_b_rec in all_cases_backup
loop
delete from idcase00
where id00_case_nbr = case_b_rec.id00_case_nbr
and id00_warehouse = case_b_rec.id00_warehouse
and id00_status_flag >= ''90''; -- let''s play extra safe !
total_back := total_back + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;
dbms_output.put_line(''Normal end of procedure FOL_PURGE_CASES'');
dbms_output.put_line(to_char(total_recs)||'' records purged !!'');
dbms_output.put_line(to_char(total_back)||'' records purged ... WITH BACKUP QUERY !'');
/* This has actually nothing to do with purging cases but I have to put it somewhere !
It solves a small error in the pkms programs. */
update illocn00
set il00_com_div = '' ''
where il00_com_div > '' ''
and il00_sku = '' ''
and il00_actl_inventory_cases = 0
and il00_directed_putaway_cases = 0;
commit;
/* You shall probably not believe it but here comes another update to solve a problem in
the pkms programs. */
update illocn00
set il00_max_inventory_units = 0,
il00_remn_units_capacity = 0
where il00_max_inventory_units > 0;
commit;
dbms_output.put_line(''Locations cleaned too !'');
EXCEPTION
WHEN OTHERS THEN -- handles all other errors
dbms_output.put_line(''EXCEPTION occurred !!'');
dbms_output.put_line(SQLERRM);
ROLLBACK;
end;
/
I get an error message
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Starting procedure FOL_PURGE_CASES
Cleaning locations
Locations cleaned!
-----******-----
No putaway locations bug workaround!
No putaway locations bug workaround - cleaned!
Starting main loop
all_cases cusor complete
EXCEPTION occurred !!
ORA-01555: snapshot too old: rollback segment number 24 with name "ROLL24" too small
PL/SQL procedure successfully completed.
I would like to change the query for it to be more robust and use maybe another cursor to choose data inbetween a certain date period,
The rollback segment has been increases but still get error? can anyone help?
推荐答案
在开始程序之前，检查最大可用的回滚段。
从DBA_ROLLBACK_SEGS中选择*，其中STATUS =''可用'';
如果AVAILABLE最大回滚段是 RBS_XX ，请使用以下语句并在同一会话中开始执行该过程。
SET TRANSACTION USE ROLLBACK SEGMENT RBS_XX ;
如果回滚段已损坏，请使用以下语句再次重新创建它。
在检查可用性之前，使用选择*来自DBA_ROLLBACK_SEGS，其中STATUS =''AVAILABLE'';"
ALTER ROLLBACK SEGMENT RBS_XX OFFLINE;
DROP ROLLBACK SEGMENT RBS_XX ;
CREATE ROLLBACK SEGMENT RBS_XX TABLESPACE RBS_REPORT ;
ALTER ROLLBACK SEGMENT RBS_XX ONLINE;
否则，
你可以尝试doi部分提交（可能在处理每1000行之后）。
Before starting the procedure check for the biggest available rollback segment.
Select * from DBA_ROLLBACK_SEGS where STATUS = ''AVAILABLE'';
If the AVAILABLE biggest rollback segment is RBS_XX, use the following statement and in the same session, start executing the procedure.
SET TRANSACTION USE ROLLBACK SEGMENT RBS_XX;
If the rollback segment is corrupted, use the following statements to recreate it again.
Before that check for availability by using "Select * from DBA_ROLLBACK_SEGS where STATUS = ''AVAILABLE'';"
ALTER ROLLBACK SEGMENT RBS_XX OFFLINE;
DROP ROLLBACK SEGMENT RBS_XX;
CREATE ROLLBACK SEGMENT RBS_XX TABLESPACE RBS_REPORT;
ALTER ROLLBACK SEGMENT RBS_XX ONLINE;
Otherwise,
You can try doing a partial commit (May be after processing every 1000 rows).
我真的想在100行之后执行1000.（TYPO ERROR）。
我觉得，程序是一个问题。你真的应该清理它。请看以下：
光标all_cases是
选择c.id00_warehouse，c.id00_case_nbr，c.id00_status_flag，c.id00_dlm
来自phpick00 a，chcart00 b，idcase00 c
其中a.ph00_shipto =''Telford''
和a.ph00_soldto ='''Buncrana''
和b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
和b.ch00_ref_case_nbr = c.id00_case_nbr
- 和c.id00_warehouse =''KL''
- 和a.ph00_warehouse =''KL''
AND A.PH00_WAREHOUSE = C.ID00_WAREHOUSE
AND A.PH00_WAREHOUSE = B.CH00_WAREHOUSE
和c.ch00_warehouse =''KL''
和c.id00_status_flag> ='''90''
- 什么样的datatype / fieldtype是c.id00_status_flag ????
和a.ph00_pkt_stat_flg =''我'
- 和trunc（id00_dlm）> =（select trunc （last_day（add_months（sysdate，-81））+ 1）from dual）
- 和trunc（id00_dlm）< =（select trunc（last_day（ad） d_months（sysdate，-1）））+ 1来自双）
- 这使搜索更快。
和TRUNC（ID00_DLM）BETWEEN（选择TRUNC（LAST_DAY） ADD_MONTHS（SYSDATE，-81））+ 1）FROM DUAL）
AND（选择TRUNC（LAST_DAY（ADD_MONTHS（SYSDATE，-1）））+ 1 FROM DUAL）
按c.id00_dlm订购
- 将以下所有内容与以下查询完全相同
联合所有
选择c.id00_warehouse，c.id00_case_nbr ，c.id00_status_flag，c.id00_dlm
来自phpick00 a，chcart00 b，idcase00 c
其中a.ph00_shipto =''Kaisersl''
和a.ph00_soldto =''Buncrana''
和b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
和b.ch00_ref_case_nbr = c.id00_case_nbr
和c.id00_warehouse =''TE''
和a.ph00_warehouse =''TE''
和b.ch00_warehouse =''TE''
和c.id00_status_flag> ='''90''
和a.ph00_pkt_stat_flg =''我'
和trunc（ id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm）< =（select trunc（last_day（add_months（sysdate） ，-1）））+ 1来自双）
顺序c.id00_dlm;
/ *我曾经有过一次用户以某种方式交换过案件和一些发货的案件已经转移了我的第一个查询。这就是为什么还有第二个!! * /
光标all_cases_backup是
选择cases.id00_warehouse，cases.id00_case_nbr
来自
（从idcase00中选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse，id00_dlm
其中id00_warehouse =''TE''
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm）< =（select trunc（last_day（add_months（sysdate，-1） ））+ 1来自双）
订购id00_dlm
）案例，chcart00 b，phpick00 c
其中b.ch00_case_nbr = cases.id00_reference_carton_nbr
和b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
和c.ph00_shipto =''Kaisersl''
和c.ph00_soldto> =' 'Buncrana''/ *欺骗优化器* /
和c.ph00_soldto< =''Buncrana''
和c.ph00_pkt_stat_flg =''我''
和trunc（id00_dlm）> =（选择trunc（last_day（add_months（sysdate，-8） 1））+ 1）来自双）
和trunc（id00_dlm）< =（选择trunc（last_day（add_months（sysdate，-1）））+ 1来自双）
order by cases.id00_dlm
union all
select cases.id00_warehouse，cases.id00_case_nbr
from
（从idcase00中选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse，id00_dlm
其中id00_warehouse =''KL''
和id00_status_flag =''95 ''
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（id00_dlm） ）< =（选择trunc（last_day（add_months（sysdate，-1）））+ 1来自双）
命令由id00_dlm
）个案，chcart00 b，phpick00 c
其中b.ch00_case_nbr = cases.id00_reference_carton_nbr
和b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
和c.ph00_shipto =''Telford' '
和c.ph00_soldto> =''Buncrana''/ *欺骗优化器* /
和c .ph00_soldto< =''Buncrana''
和c.ph00_pkt_stat_flg =''我'
和trunc（id00_dlm）> =（select trunc（last_day（ add_months（sysdate，-81））+ 1）from dual）
和trunc（id00_dlm）< =（选择trunc（last_day（add_months（sysdate，-1）））+ 1来自dual）
order by cases.id00_dlm
UNION ALL
选择id00_warehouse，id00_case_nbr
来自
（选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse，id00_dlm
来自idcase00
其中id00_warehouse =''KL''
和id00_status_flag ='' 95''
和trunc（id00_dlm）> =（从双重中选择trunc（last_day（add_months（sysdate，-81））+ 1）
和trunc（ id00_dlm）< =（选择trunc（last_day（add_months（sysdate，-1）））+ 1来自双）
）个案，
chcart00 b，
phpick00 c
其中b.ch00_case_nbr = id00_reference_carton_nbr
和b.ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
- 和c.ph00_shipto in（''Edingen''，''EDINGEN''，''edingen''）
- 这个更快
和UPPER（C.PH00_SHIPTO）=''EDINGEN''
- 我真的不理解以下两个条件
和c.ph00_soldto> =' 'Buncrana''/ *欺骗优化器* /
和c.ph00_soldto< =''Buncrana''
和trunc（id00_dlm）> =（选择trunc（last_day（add_months（sysdate，-81））+ 1）from dual）
和trunc（id00_dlm）< =（select trunc（last_day（add_months（sysdate，-1）））+ 1来自双）
order by cases.id00_dlm;
case_rec all_cases％rowtype;
case_b_rec all_cases_backup％rowtype;
计数器整数;
total_recs整数;
total_back整数;
begin
dbms_output.put_line（''启动程序FOL_PURGE_CASES''）;
dbms_output.put_line（''开始主循环''）;
counte r：= 1;
total_recs：= 0;
$ _ $ b for case_rec in all_cases
loop
if（case_rec .id00_status_flag< ''90''）然后
退出; - 这应该永远不会发生
结束如果;
从idcase00删除
其中id00_case_nbr = case_rec.id00_case_nbr
和id00_warehouse = case_rec.id00_warehouse
和id00_status_flag> ='''90''; - 让我们玩起来更加安全！
total_recs：= total_recs + 1;
counter：= counter + 1;
如果计数器> 1000然后
commit;
counter：= 1;
end if;
end loop;
提交;
计数器：= 1;
total_back：= 0;
for case_b_rec in all_cases_backup
循环
从idcase00删除
其中id00_case_nbr = case_b_rec.id00_case_nbr
和id00_warehouse = case_b_rec.id00_warehouse
和id00_status_flag> ='''90''; - 让我们玩起来更加安全！
total_back：= total_back + 1;
counter：= counter + 1;
如果计数器> 1000然后
commit;
counter：= 1;
end if;
end loop;
commit;
dbms_output.put_line（''正常结束程序FOL_PURGE_CASES''）;
dbms_output.put_line（to_char（total_recs） ||''记录被清除!!''）;
dbms_output.put_line（to_char（total_back）||''记录被清除... WITH BACKUP QUERY！''）;
/ *这实际上与清除案件无关但我必须把它放在某处！
它解决了pkms程序中的一个小错误。 * /
更新illocn00
设置il00_com_div =''''
其中il00_com_div> ''''
和il00_sku =''''
和il00_actl_inventory_cases = 0
和il00_directed_putaway_cases = 0;
提交;
/ *你可能不会相信它，但是这里有另一个更新来解决pkms程序中的问题。 * /
更新illocn00
设置il00_max_inventory_units = 0，
il00_remn_units_capacity = 0
其中il00_max_inventory_units > 0;
提交;
dbms_output.put_line（''地点已清理过！''）;
EXCEPTION
当其他时间 - 处理所有其他错误
dbms_output.put_line（''EXCEPTION !!!!''）;
dbms_output.put_line（SQLERRM）;
ROLLBACK;
end;
/
I REALLY MEANT TO COMMIT AFTER 100 ROWS INSTEAD OF 1000. (A TYPO ERROR).
I FEEL, THE PROCEDURE IS A MESS. YOU SHOULD REALLY CLEAN IT. SEE THE FOLLOWING:
cursor all_cases is
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = ''Telford''
and a.ph00_soldto = ''Buncrana''
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
--and c.id00_warehouse = ''KL''
--and a.ph00_warehouse = ''KL''
AND A.PH00_WAREHOUSE = C.ID00_WAREHOUSE
AND A.PH00_WAREHOUSE = B.CH00_WAREHOUSE
and c.ch00_warehouse = ''KL''
and c.id00_status_flag >= ''90''
-- what kinda datatype/fieldtype is c.id00_status_flag????
and a.ph00_pkt_stat_flg = ''I''
--and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
--and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
-- THIS MAKES THE SEARCH FASTER.
AND TRUNC(ID00_DLM) BETWEEN (SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-81))+1) FROM DUAL)
AND (SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1 FROM DUAL)
order by c.id00_dlm
-- SIMILARLY TRY THESE TO ALL THE FOLLOWING QUERIES
union all
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = ''Kaisersl''
and a.ph00_soldto = ''Buncrana''
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
and c.id00_warehouse = ''TE''
and a.ph00_warehouse = ''TE''
and b.ch00_warehouse = ''TE''
and c.id00_status_flag >= ''90''
and a.ph00_pkt_stat_flg = ''I''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by c.id00_dlm;
/* I have had it once that the users had somehow swapped cases and some of the cases shipped had
escaped my first query. That''s why there is a second !! */
cursor all_cases_backup is
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''TE''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = ''Kaisersl''
and c.ph00_soldto >= ''Buncrana'' /* to trick the optimizer */
and c.ph00_soldto <= ''Buncrana''
and c.ph00_pkt_stat_flg = ''I''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
union all
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''KL''
and id00_status_flag = ''95''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = ''Telford''
and c.ph00_soldto >= ''Buncrana'' /* to trick the optimizer */
and c.ph00_soldto <= ''Buncrana''
and c.ph00_pkt_stat_flg = ''I''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
UNION ALL
select id00_warehouse, id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''KL''
and id00_status_flag = ''95''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
) cases,
chcart00 b,
phpick00 c
where b.ch00_case_nbr = id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
--and c.ph00_shipto in (''Edingen'',''EDINGEN'',''edingen'')
-- THIS FASTER
AND UPPER(C.PH00_SHIPTO) = ''EDINGEN''
-- I REALLY DON''T UNDERSTAND THE FOLLOWING TWO CONDITIONS
and c.ph00_soldto >= ''Buncrana'' /* to trick the optimizer */
and c.ph00_soldto <= ''Buncrana''
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm;
case_rec all_cases%rowtype;
case_b_rec all_cases_backup%rowtype;
counter integer;
total_recs integer;
total_back integer;
begin
dbms_output.put_line(''Starting procedure FOL_PURGE_CASES'');
dbms_output.put_line(''Starting main loop'');
counter := 1;
total_recs := 0;
for case_rec in all_cases
loop
if (case_rec.id00_status_flag < ''90'') then
exit; -- this should never happen
end if;
delete from idcase00
where id00_case_nbr = case_rec.id00_case_nbr
and id00_warehouse = case_rec.id00_warehouse
and id00_status_flag >= ''90''; -- let''s play extra safe !
total_recs := total_recs + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;
counter := 1;
total_back := 0;
for case_b_rec in all_cases_backup
loop
delete from idcase00
where id00_case_nbr = case_b_rec.id00_case_nbr
and id00_warehouse = case_b_rec.id00_warehouse
and id00_status_flag >= ''90''; -- let''s play extra safe !
total_back := total_back + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;
dbms_output.put_line(''Normal end of procedure FOL_PURGE_CASES'');
dbms_output.put_line(to_char(total_recs)||'' records purged !!'');
dbms_output.put_line(to_char(total_back)||'' records purged ... WITH BACKUP QUERY !'');
/* This has actually nothing to do with purging cases but I have to put it somewhere !
It solves a small error in the pkms programs. */
update illocn00
set il00_com_div = '' ''
where il00_com_div > '' ''
and il00_sku = '' ''
and il00_actl_inventory_cases = 0
and il00_directed_putaway_cases = 0;
commit;
/* You shall probably not believe it but here comes another update to solve a problem in the pkms programs. */
update illocn00
set il00_max_inventory_units = 0,
il00_remn_units_capacity = 0
where il00_max_inventory_units > 0;
commit;
dbms_output.put_line(''Locations cleaned too !'');
EXCEPTION
WHEN OTHERS THEN -- handles all other errors
dbms_output.put_line(''EXCEPTION occurred !!'');
dbms_output.put_line(SQLERRM);
ROLLBACK;
end;
/
谢谢为了获取信息，我检查了回滚段并且没有返回任何行但是我已经要求DBA将其中一个回滚段设置为最大值并将其余段置于脱机状态，这似乎有效。
id00_status_flag是一个CHAR数据类型。
选择的日期部分运行速度慢，所以我用
和id00_dlm交换了'' 16-SEP-2006''和'16 -NOV-2006''因为我有这么多的记录。虽然即使在这个日期我仍然有更多的记录。
这是我将其更改为： -
创建或替换程序FOL_BACKUP_PURGE_CASES
as
v_date NUMBER：= 25;
游标all_cases是
选择id00_warehouse，id00_case_nbr，id00_status_flag
来自phpick00，chcart00，idcase00
其中ph00_shipto =''Telford''
和ph00_soldto =''Buncrana ''
和ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
和ch00_ref_case_nbr = id00_case_nbr
和id00_warehouse =''KL''
和ph00_warehouse =''KL''
和ch00_warehouse =''KL''
和id00_status_flag> ='''90''
和ph00_pkt_stat_flg =''我'
联盟全部
选择id00_warehouse，id00_case_nbr，id00_status_flag
来自phpick00，chcart00，idcase00
其中ph00_shipto =''Kaisersl''
和ph00_soldto =''Buncrana''
和ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
和ch00_ref_case_nbr = id00_case_nbr
和id00_warehouse =''TE''
和ph00_warehouse =''TE''
和ch00_warehouse =''TE''
和id00_status_flag> ='''90''
和ph00_pkt_stat_flg =''我';
游标all_cases_backup是
选择cases.id00_warehouse，cases.id00_case_nbr
来自
（选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse ，id00_dlm
来自idcase00的
其中id00_warehouse =''TE''
和id00_status_flag =？95？
'20 -OCT-2006''和'21 -OCT-2006'之间的id00_dlm'
）案例，chcart00 b，phpick00 c
其中c.ph00_shipto =''Kaisersl''
和c.ph00_soldto =''Buncrana ''
和c.ph00_pkt_stat_flg =''我'
和b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
和b.ch00_case_nbr = case .id00_reference_carton_nbr
UNION ALL
select cases.id00_warehouse，cases.id00_case_nbr
来自
（选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse，id00_dlm
来自idcase00的
其中id00_warehouse =''KL''
和id00_status_flag =？95？
和id00_dlm在''20 -OCT-2006''和'21 -OCT-2006之间''
）个案，chcart00 b，phpick00 c
其中c.ph00_shipto =''Kaisersl''
和c.ph00_soldto =''Buncrana''
和b.ch00_case_nbr = cases.id00_reference_carton_nbr
和b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
和c.ph00_pkt_stat_flg =''我';
UNION ALL
select cases.id00_warehouse，cases.id00_case_nbr
来自
（选择id00_reference_carton_nbr，id00_case_nbr，id00_warehouse，id00_dlm
来自idcase00
其中id00_warehouse =''KL''
和id00_status_flag ='''95''
和id00_dlm之间'' 20-OCT-2006''和'21 -OCT-2006''
）案例，chcart00 b，phpick00 c
其中c.ph00_shipto =''Telford' '
和c.ph00_soldto =''Buncrana''
和b.ch00_case_nbr = cases.id00_reference_carton_nbr
和b.ch00_pkt_ctl_nbr = c。 ph00_pkt_ctl_nbr
和c.ph00_pkt_stat_flg =''我';
仍然是r unning一天我认为我们需要一些更多的索引，但它正在努力......
Thanks for the information I checked the rolback segment and no rows were return but I have asked the DBA to set one of the rolback segments to the max and put the rest offline and this seems to work.
The id00_status_flag is a CHAR datatype.
The date part of the select is running uch slower so I have exchanged it with
and id00_dlm between ''16-SEP-2006'' and ''16-NOV-2006'' becuase I have so many records. Although even for this date I still have more records.
this is what I have changed it to:-
CREATE OR REPLACE procedure FOL_BACKUP_PURGE_CASES
as
v_date NUMBER :=25;
cursor all_cases is
select id00_warehouse, id00_case_nbr, id00_status_flag
from phpick00, chcart00, idcase00
where ph00_shipto = ''Telford''
and ph00_soldto = ''Buncrana''
and ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
and ch00_ref_case_nbr = id00_case_nbr
and id00_warehouse = ''KL''
and ph00_warehouse = ''KL''
and ch00_warehouse = ''KL''
and id00_status_flag >= ''90''
and ph00_pkt_stat_flg = ''I''
union all
select id00_warehouse, id00_case_nbr, id00_status_flag
from phpick00, chcart00, idcase00
where ph00_shipto = ''Kaisersl''
and ph00_soldto = ''Buncrana''
and ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
and ch00_ref_case_nbr = id00_case_nbr
and id00_warehouse = ''TE''
and ph00_warehouse = ''TE''
and ch00_warehouse = ''TE''
and id00_status_flag >= ''90''
and ph00_pkt_stat_flg = ''I'';
cursor all_cases_backup IS
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''TE''
and id00_status_flag = ?95?
and id00_dlm between ''20-OCT-2006'' and ''21-OCT-2006''
) cases, chcart00 b, phpick00 c
where c.ph00_shipto = ''Kaisersl''
and c.ph00_soldto = ''Buncrana''
and c.ph00_pkt_stat_flg = ''I''
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and b.ch00_case_nbr = cases.id00_reference_carton_nbr
UNION ALL
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''KL''
and id00_status_flag = ?95?
and id00_dlm between ''20-OCT-2006'' and ''21-OCT-2006''
) cases, chcart00 b, phpick00 c
where c.ph00_shipto = ''Kaisersl''
and c.ph00_soldto = ''Buncrana''
and b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_pkt_stat_flg = ''I'';
UNION ALL
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = ''KL''
and id00_status_flag = ''95''
and id00_dlm between ''20-OCT-2006'' and ''21-OCT-2006''
) cases, chcart00 b, phpick00 c
where c.ph00_shipto = ''Telford''
and c.ph00_soldto = ''Buncrana''
and b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_pkt_stat_flg = ''I'';
Still running for one day I think we need some more indexes but its doing my head in....
这篇关于清除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
