PL/SQL中三个INSERT语句的条件执行及异常处理代码编写需求

Let's break down your requirements first to avoid any ambiguity:

• If insert_1 succeeds, automatically run insert_3
• If insert_3 succeeds, automatically run insert_1
• Only run insert_2 both insert_1 and insert_3 have failed

Wait a second—this logic creates a potential infinite loop if both insert_1 and insert_3 keep succeeding. For example: insert_1 runs successfully → trigger insert_3 → insert_3 runs successfully → trigger insert_1 again, and this repeats forever. So we need to add a guard against that, like limiting the number of execution cycles or tracking which inserts have already run.

Below is a PL/SQL block that implements your requirements while preventing infinite loops by tracking execution attempts:

DECLARE
    v_insert1_success BOOLEAN := FALSE;
    v_insert3_success BOOLEAN := FALSE;
    v_max_attempts NUMBER := 2; -- Prevent infinite loops, adjust as needed
    v_current_attempt NUMBER := 0;
BEGIN
    -- First attempt: run insert_1
    BEGIN
        -- Replace with your actual insert_1 logic
        INSERT INTO your_table (col1, col2) VALUES ('val1_1', 'val1_2');
        v_insert1_success := TRUE;
        DBMS_OUTPUT.PUT_LINE('insert_1 executed successfully');
    EXCEPTION
        WHEN OTHERS THEN
            v_insert1_success := FALSE;
            DBMS_OUTPUT.PUT_LINE('insert_1 failed: ' || SQLERRM);
    END;

    -- If insert_1 succeeded, run insert_3 and check its status
    IF v_insert1_success THEN
        v_current_attempt := v_current_attempt + 1;
        WHILE v_current_attempt <= v_max_attempts AND v_insert3_success = FALSE LOOP
            BEGIN
                -- Replace with your actual insert_3 logic
                INSERT INTO your_table (col1, col2) VALUES ('val3_1', 'val3_2');
                v_insert3_success := TRUE;
                DBMS_OUTPUT.PUT_LINE('insert_3 executed successfully');
                
                -- If insert_3 succeeded, run insert_1 again (as per requirement)
                IF v_insert3_success AND v_current_attempt < v_max_attempts THEN
                    BEGIN
                        INSERT INTO your_table (col1, col2) VALUES ('val1_1', 'val1_2');
                        DBMS_OUTPUT.PUT_LINE('insert_1 executed again successfully (triggered by insert_3 success)');
                    EXCEPTION
                        WHEN OTHERS THEN
                            DBMS_OUTPUT.PUT_LINE('insert_1 failed on retry: ' || SQLERRM);
                    END;
                END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    v_insert3_success := FALSE;
                    DBMS_OUTPUT.PUT_LINE('insert_3 failed: ' || SQLERRM);
            END;
            v_current_attempt := v_current_attempt + 1;
        END LOOP;
    ELSE
        -- insert_1 failed, try insert_3
        BEGIN
            INSERT INTO your_table (col1, col2) VALUES ('val3_1', 'val3_2');
            v_insert3_success := TRUE;
            DBMS_OUTPUT.PUT_LINE('insert_3 executed successfully (after insert_1 failure)');
            
            -- If insert_3 succeeded, run insert_1 again
            BEGIN
                INSERT INTO your_table (col1, col2) VALUES ('val1_1', 'val1_2');
                DBMS_OUTPUT.PUT_LINE('insert_1 executed successfully (triggered by insert_3 success after initial failure)');
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('insert_1 failed after insert_3 success: ' || SQLERRM);
            END;
        EXCEPTION
            WHEN OTHERS THEN
                v_insert3_success := FALSE;
                DBMS_OUTPUT.PUT_LINE('insert_3 failed after insert_1 failure: ' || SQLERRM);
        END;
    END IF;

    -- Only run insert_2 if both insert_1 and insert_3 failed in all attempts
    IF NOT v_insert1_success AND NOT v_insert3_success THEN
        BEGIN
            -- Replace with your actual insert_2 logic
            INSERT INTO your_table (col1, col2) VALUES ('val2_1', 'val2_2');
            DBMS_OUTPUT.PUT_LINE('insert_2 executed successfully (both insert_1 and insert_3 failed)');
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('insert_2 failed: ' || SQLERRM);
        END;
    END IF;

    COMMIT; -- Adjust if you need to handle transactions differently
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

Key Notes: #

• Infinite Loop Prevention: We added v_max_attempts to stop the cycle of insert_1 → insert_3 → insert_1 from running forever. You can adjust this number based on your actual needs.
• Exception Handling: Each INSERT is wrapped in its own exception block to catch failures and track success status accurately.
• Transaction Control: The block ends with a COMMIT (and ROLLBACK on unexpected errors) — adjust this if your use case requires different transaction behavior (e.g., individual commits per insert).
• Customization: Replace the sample INSERT statements with your actual insert_1, insert_2, insert_3 logic (including table names, columns, and values).

If your original intention was to avoid the infinite loop (maybe a misstatement in requirements, like "run insert_3 if insert_1 succeeds, and only run insert_2 if both insert_1 and insert_3 fail on first attempt"), feel free to tweak the logic or ask for an adjusted version!

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