如何编写异常处理：插入数据遇约束冲突时跳过并继续执行

Hey, let's tackle this problem step by step. The core issue here is handling uniqueness constraint violations when inserting into the respondent table—you want to skip duplicate cycle_samples entries without deleting existing records (especially critical since there are parent/child tables dependent on them). Here are two robust approaches tailored to your scenario:

Approach 1: Filter Duplicates Before Insert (Recommended for Performance) #

Instead of letting exceptions fire, we'll pre-filter out any cycle_samples entries that already exist in respondent. This is way more efficient for bulk inserts, as it avoids unnecessary error handling overhead.

Here's a sample procedure (assuming you're using Oracle, since your code starts with CREATE OR REPLACE PROCEDURE):

CREATE OR REPLACE PROCEDURE RESPONDENT_INSERT(
    p_cycle_samples IN SYS_REFCURSOR -- Pass in your dataset of cycle_samples to insert
) AS
    -- Define a record type matching your respondent table structure
    TYPE t_respondent_rec IS RECORD (
        cycle_sample_id NUMBER,
        respondent_name VARCHAR2(100),
        -- Add all other respondent table fields here
    );
    TYPE t_respondent_tab IS TABLE OF t_respondent_rec;
    v_new_records t_respondent_tab;
BEGIN
    -- Fetch only records that don't already exist in respondent
    FETCH p_cycle_samples
        BULK COLLECT INTO v_new_records
        WHERE NOT EXISTS (
            SELECT 1 
            FROM respondent r
            WHERE r.cycle_sample_id = cycle_sample_id -- Adjust this to match your unique constraint column(s)
        );

    -- Insert the filtered records if there are any
    IF v_new_records.COUNT > 0 THEN
        FORALL i IN v_new_records.FIRST..v_new_records.LAST
            INSERT INTO respondent (cycle_sample_id, respondent_name /* Add other fields */)
            VALUES (v_new_records(i).cycle_sample_id, v_new_records(i).respondent_name /* Match values */);
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Successfully inserted ' || v_new_records.COUNT || ' new records');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No new cycle_samples records to insert');
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Insert failed with error: ' || SQLERRM);
        RAISE; -- Remove this line if you don't want to propagate the error upward
END RESPONDENT_INSERT;
/

Approach 2: Catch Duplicate Exceptions (For Granular Control) #

If you need to explicitly log which records are duplicates and continue processing the rest, you can use a loop with per-record exception handling. This is useful when you need visibility into exactly which entries were skipped.

CREATE OR REPLACE PROCEDURE RESPONDENT_INSERT(
    p_cycle_samples IN SYS_REFCURSOR
) AS
    -- Declare variables matching your input cursor columns
    v_cycle_sample_id NUMBER;
    v_respondent_name VARCHAR2(100);
    -- Add other fields here
    v_success_count NUMBER := 0;
    v_skip_count NUMBER := 0;
BEGIN
    LOOP
        FETCH p_cycle_samples INTO v_cycle_sample_id, v_respondent_name /* Match cursor columns */;
        EXIT WHEN p_cycle_samples%NOTFOUND;

        -- Try inserting the single record, catch duplicates
        BEGIN
            INSERT INTO respondent (cycle_sample_id, respondent_name /* Add other fields */)
            VALUES (v_cycle_sample_id, v_respondent_name /* Match values */);
            v_success_count := v_success_count + 1;
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN -- Oracle's specific exception for unique constraint violations
                v_skip_count := v_skip_count + 1;
                DBMS_OUTPUT.PUT_LINE('Skipped duplicate record: cycle_sample_id = ' || v_cycle_sample_id);
            WHEN OTHERS THEN
                ROLLBACK;
                DBMS_OUTPUT.PUT_LINE('Error processing record cycle_sample_id=' || v_cycle_sample_id || ': ' || SQLERRM);
                RAISE; -- Propagate unexpected errors
        END;
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Insert completed: ' || v_success_count || ' successful, ' || v_skip_count || ' duplicates skipped');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Procedure failed with error: ' || SQLERRM);
        RAISE;
END RESPONDENT_INSERT;
/

Key Notes: #

• Database-Specific Exceptions: DUP_VAL_ON_INDEX is Oracle-specific. If you're using another database (like PostgreSQL), replace it with the appropriate exception (e.g., UNIQUE_VIOLATION for PostgreSQL) or error code.
• Performance: Approach 1 is far better for large datasets because it minimizes exception handling. Approach 2 is better when you need detailed logging of duplicates.
• Preserving Existing Data: Both approaches leave your existing respondent records (and their dependent parent/child tables) completely untouched—only new, non-duplicate records are inserted.

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