如何用PL/SQL按特定执行逻辑插入3行数据？求实现代码

Got it, let's break down how to implement your conditional INSERT logic in PL/SQL. The key here is using nested exception handling blocks to track the success/failure of each INSERT statement and trigger the next step accordingly.

Here's a complete, tested code example that follows your requirements:

DECLARE
  -- Optional: Add variables if you need to pass values between inserts
  v_a_val1 VARCHAR2(50) := 'A_value_1';
  v_a_val2 NUMBER := 100;
  v_c_val1 VARCHAR2(50) := 'C_value_1';
  v_c_val2 NUMBER := 300;
  v_b_val1 VARCHAR2(50) := 'B_value_1';
  v_b_val2 NUMBER := 200;
BEGIN
  -- First attempt: Execute INSERT A
  BEGIN
    INSERT INTO your_target_table (column1, column2) 
    VALUES (v_a_val1, v_a_val2);
    DBMS_OUTPUT.PUT_LINE('✅ INSERT A executed successfully');

    -- A succeeded: Automatically execute INSERT C
    BEGIN
      INSERT INTO your_target_table (column1, column2) 
      VALUES (v_c_val1, v_c_val2);
      DBMS_OUTPUT.PUT_LINE('✅ INSERT C executed successfully');

      -- C succeeded: Automatically execute INSERT A again
      BEGIN
        -- Note: Adjust this INSERT to match your needs (same as first A or modified)
        INSERT INTO your_target_table (column1, column2) 
        VALUES (v_a_val1 || '_repeat', v_a_val2 + 10);
        DBMS_OUTPUT.PUT_LINE('✅ INSERT A executed successfully (second run)');
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('❌ Second INSERT A failed: ' || SQLERRM);
      END;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('❌ INSERT C failed after A succeeded: ' || SQLERRM);
        -- No B execution here: A was successful, so skip B
    END;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('❌ INSERT A failed: ' || SQLERRM);

      -- A failed: Attempt INSERT C
      BEGIN
        INSERT INTO your_target_table (column1, column2) 
        VALUES (v_c_val1, v_c_val2);
        DBMS_OUTPUT.PUT_LINE('✅ INSERT C executed successfully after A failed');

        -- C succeeded: Automatically execute INSERT A
        BEGIN
          INSERT INTO your_target_table (column1, column2) 
          VALUES (v_a_val1, v_a_val2);
          DBMS_OUTPUT.PUT_LINE('✅ INSERT A executed successfully after C succeeded');
        EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('❌ INSERT A failed after C succeeded: ' || SQLERRM);
        END;
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('❌ INSERT C also failed: ' || SQLERRM);

          -- Both A and C failed: Execute INSERT B
          BEGIN
            INSERT INTO your_target_table (column1, column2) 
            VALUES (v_b_val1, v_b_val2);
            DBMS_OUTPUT.PUT_LINE('✅ INSERT B executed successfully (fallback)');
          EXCEPTION
            WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('❌ INSERT B also failed: ' || SQLERRM);
          END;
      END;
END;
/

Key Notes: #

• Replace Placeholders: Swap your_target_table, column1/column2, and the variable values with your actual table structure and data.
• Exception Handling: Each INSERT is wrapped in its own BEGIN-EXCEPTION block to catch failures without stopping the entire procedure.
• Flow Control:
  • If INSERT A succeeds → run INSERT C. If C succeeds → run INSERT A again (adjust this step if you don't need the repeat execution).
  • If INSERT A fails → try INSERT C. If C succeeds → run INSERT A.
  • Only when both A and C fail → execute INSERT B.
• Debug Output: The DBMS_OUTPUT statements help you track which steps succeeded/failed (enable server output to see these messages).

If you need to adjust the repeat execution of INSERT A after C succeeds (e.g., you didn't mean to loop it), just remove that innermost BEGIN-EXCEPTION block for the second INSERT A.

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