清除记录 | trunc

本文介绍了清除记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述我正在尝试使用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; begindbms_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 illocn00set il00_max_inventory_units = 0,il00_remn_units_capacity = 0where 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 messageConnected to:Oracle8i Enterprise Edition Release 8.1.7.4.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - ProductionStarting procedure FOL_PURGE_CASESCleaning locationsLocations cleaned!-----******-----No putaway locations bug workaround!No putaway locations bug workaround - cleaned!Starting main loopall_cases cusor completeEXCEPTION 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?推荐答案这篇关于清除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！