如何创建PL/SQL存储过程及定时任务实现两表关联删除

Alright, let's break this down into two clear parts: building a robust PL/SQL procedure to handle the cross-table deletions safely, and setting up a scheduled job to run it on your desired frequency. Here's how to implement both:

This procedure will handle deleting matching records from both tables while ensuring transactional integrity (so either both deletions succeed, or neither does if something goes wrong).

CREATE OR REPLACE PROCEDURE delete_matching_records
IS
    v_table1_deleted_count NUMBER;
    v_table2_deleted_count NUMBER;
BEGIN
    -- First, delete matching records from table2
    DELETE FROM table2 t2
    WHERE t2.P = 90
      AND EXISTS (
          SELECT 1 
          FROM table1 t1
          WHERE t1.S = t2.Z
            AND t1.O = 90
      );
    v_table2_deleted_count := SQL%ROWCOUNT;

    -- Then delete corresponding records from table1
    DELETE FROM table1 t1
    WHERE t1.O = 90
      AND EXISTS (
          SELECT 1 
          FROM table2 t2
          WHERE t2.Z = t1.S
            AND t2.P = 90
      );
    v_table1_deleted_count := SQL%ROWCOUNT;

    -- Commit the transaction if all goes well
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('删除完成: table1删除' || v_table1_deleted_count || '条记录, table2删除' || v_table2_deleted_count || '条记录');
EXCEPTION
    WHEN OTHERS THEN
        -- Rollback everything if an error occurs
        ROLLBACK;
        -- Optionally log the error to a table (create an error_log table first if needed)
        -- INSERT INTO error_log (log_date, error_message, procedure_name)
        -- VALUES (SYSDATE, 'Error: ' || SQLERRM, 'delete_matching_records');
        RAISE_APPLICATION_ERROR(-20001, '删除操作失败: ' || SQLERRM);
END delete_matching_records;
/

Key Notes for the Procedure: #

• Transactional Safety: The COMMIT and ROLLBACK ensure that if either deletion fails, no changes are persisted to the database.
• Row Count Tracking: We capture the number of deleted rows for each table, which is helpful for debugging and logging.
• Error Handling: The exception block catches any issues, rolls back changes, and raises a custom error message for easier troubleshooting. You can uncomment the INSERT line to log errors to a dedicated table (just create error_log first with appropriate columns).

Oracle's DBMS_SCHEDULER is the modern, preferred way to schedule jobs (replacing the older DBMS_JOB). Below is an example to run the procedure daily at 2 AM. Adjust the frequency as needed.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'DELETE_MATCHING_RECORDS_JOB', -- Unique job name
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'delete_matching_records', -- Name of your procedure
        start_date      => SYSTIMESTAMP, -- Start immediately
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0', -- Daily at 2 AM
        enabled         => TRUE, -- Enable the job right away
        comments        => 'Scheduled job to delete matching records from table1 and table2'
    );
END;
/

Customizing the Schedule: #

• To run every hour: 'FREQ=HOURLY; INTERVAL=1'
• To run every Monday at 8 AM: 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=8; BYMINUTE=0'
• For more complex schedules, refer to Oracle's DBMS_SCHEDULER interval syntax documentation.

Useful Commands to Manage the Job: #

• Check job status: SELECT job_name, status FROM user_scheduler_jobs;
• View job run history: SELECT log_date, status, error# FROM user_scheduler_job_run_details WHERE job_name = 'DELETE_MATCHING_RECORDS_JOB';
• Disable the job: BEGIN DBMS_SCHEDULER.DISABLE('DELETE_MATCHING_RECORDS_JOB'); END; /
• Enable the job: BEGIN DBMS_SCHEDULER.ENABLE('DELETE_MATCHING_RECORDS_JOB'); END; /

Prerequisites: #

• Ensure the user creating the procedure and job has DELETE privileges on table1 and table2.
• The user needs CREATE JOB system privilege to create scheduled jobs (ask your DBA if you don't have this).

内容的提问来源于stack exchange，提问作者LND