好处就是程序代码可以写的更少。
执行速度更快。
另外看上去好像很有趣。可以出错回滚。create or replace procedure CleanDD is
--pragma autonomous_transaction; NULLERR EXCEPTION;
begin
SET TRANSACTION READ WRITE;
delete from files where FILE_ID not in (select FILE_ID from PACKAGEFILES) and FILE_ID not in (select FILE_ID from person_files);
delete from files where file_ID in
(select file_id from PACKAGEFILES where package_ID in
(
select package_id from PACKAGE where package_id not in(
select package_id from PACKAGE where package_id in
(
select package_id from CLAIM
union
select package_id from LAWCASE
union
select package_id from MEETINGS
union
select package_id from WORK_PLAN_SUM
union
select package_id from WORK_REPORT
union
select package_id from ORG_PDUTY
union
select package_id from ORGANIZE
union
select package_id from FILE_ASSIGN
))
)
);
delete from PACKAGEFILES where package_ID in
(
select package_id from PACKAGE where package_id not in(
select package_id from PACKAGE where package_id in
(
select package_id from CLAIM
union
select package_id from LAWCASE
union
select package_id from MEETINGS
union
select package_id from WORK_PLAN_SUM
union
select package_id from WORK_REPORT
union
select package_id from ORG_PDUTY
union
select package_id from ORGANIZE
union
select package_id from FILE_ASSIGN
))
);
delete from PACKAGE where package_ID in
(
select package_id from PACKAGE where package_id not in(
select package_id from PACKAGE where package_id in
(
select package_id from CLAIM
union
select package_id from LAWCASE
union
select package_id from MEETINGS
union
select package_id from WORK_PLAN_SUM
union
select package_id from WORK_REPORT
union
select package_id from ORG_PDUTY
union
select package_id from ORGANIZE
union
select package_id from FILE_ASSIGN
))
);
delete from person;
delete from bbs where bbs_board_id in (select bbs_board_id from bbs_board where sts='D');
delete from bbs_board where sts='D';
commit;
EXCEPTION
WHEN NULLERR THEN
DBMS_OUTPUT.put_line('值为空或零');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLCODE);
DBMS_OUTPUT.put_line(SQLERRM);
rollback;
end CleanDD;
执行速度更快。
另外看上去好像很有趣。可以出错回滚。create or replace procedure CleanDD is
--pragma autonomous_transaction; NULLERR EXCEPTION;
begin
SET TRANSACTION READ WRITE;
delete from files where FILE_ID not in (select FILE_ID from PACKAGEFILES) and FILE_ID not in (select FILE_ID from person_files);
delete from files where file_ID in
(select file_id from PACKAGEFILES where package_ID in
(
select package_id from PACKAGE where package_id not in(
select package_id from PACKAGE where package_id in
(
select package_id from CLAIM
union
select package_id from LAWCASE
union
select package_id from MEETINGS
union
select package_id from WORK_PLAN_SUM
union
select package_id from WORK_REPORT
union
select package_id from ORG_PDUTY
union
select package_id from ORGANIZE
union
select package_id from FILE_ASSIGN
))
)
);
delete from PACKAGEFILES where package_ID in
(
select package_id from PACKAGE where package_id not in(
select package_id from PACKAGE where package_id in
(
select package_id from CLAIM
union
select package_id from LAWCASE
union
select package_id from MEETINGS
union
select package_id from WORK_PLAN_SUM
union
select package_id from WORK_REPORT
union
select package_id from ORG_PDUTY
union
select package_id from ORGANIZE
union
select package_id from FILE_ASSIGN
))
);
delete from PACKAGE where package_ID in
(
select package_id from PACKAGE where package_id not in(
select package_id from PACKAGE where package_id in
(
select package_id from CLAIM
union
select package_id from LAWCASE
union
select package_id from MEETINGS
union
select package_id from WORK_PLAN_SUM
union
select package_id from WORK_REPORT
union
select package_id from ORG_PDUTY
union
select package_id from ORGANIZE
union
select package_id from FILE_ASSIGN
))
);
delete from person;
delete from bbs where bbs_board_id in (select bbs_board_id from bbs_board where sts='D');
delete from bbs_board where sts='D';
commit;
EXCEPTION
WHEN NULLERR THEN
DBMS_OUTPUT.put_line('值为空或零');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLCODE);
DBMS_OUTPUT.put_line(SQLERRM);
rollback;
end CleanDD;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货