第一个
DECLARE
TYPE T_Endorsement_No IS TABLE OF Auto_Active_Endorse_Snapshot.Endorsement_No %TYPE;
TYPE T_Policy_No IS TABLE OF Auto_Active_Policy_Snapshot.Policy_No %TYPE;
c_Endorsement_No T_Endorsement_No := T_Endorsement_No();
c_Policy_No
T_Policy_No := T_Policy_No();
CURSOR cur_Delete_Data IS
SELECT a.Policy_No, a.Endorsement_No
FROM Auto_Agreement_Request_I a
WHERE a.Branch_Company_Code = 7050100
AND a.Planned_End_Date >= a.Requested_Date
AND a.Signature_Date >= TO_DATE('2006-01-01', 'YYYY-MM-DD');
BEGIN
OPEN cur_Delete_Data;
FETCH cur_Delete_Data BULK COLLECT
INTO c_Policy_No, c_Endorsement_No;
FORALL i IN c_Policy_No.FIRST .. c_Policy_No.LAST
DELETE FROM Auto_Active_Endorse_Snapshot
WHERE Branch_Company_Code = 7050100
AND Policy_No = c_Policy_No(i)
AND Endorsement_No = c_Endorsement_No(i);
CLOSE cur_Delete_Data;
c_Policy_No.DELETE();
c_Endorsement_No.DELETE();
END;
第二个
DELETE /*+ Parallel(t 4) */ AUTO_ACTIVE_ENDORSE_SNAPSHOT T WHERE EXISTS (SELECT1
FROM AUTO_AGREEMENT_REQUEST_I A WHERE A.BRANCH_COMPANY_CODE = 7050100 AND A.PLANNED_END_DATE >= A.REQUESTED_DATE AND A.SIGNATURE_DATE >= TO_DATE('2006-01-01', 'YYYY-MM-DD') AND T.POLICY_NO = A.POLICY_NO AND T.ENDORSEMENT_NO = A.ENDORSEMENT_NO AND T.BRANCH_COMPANY_CODE = 7050100)
