将某INSERT移出循环后PL/SQL存储过程执行卡顿求助

Hey there! Let's break down what's causing that infinite hang when you moved the Submission INSERT out of your cursor loop, and get your procedure working as intended.

Why the Hang Happens #

When you had the Submission INSERT inside the loop, each iteration was inserting a new Submission record (which is why you ended up with multiple entries instead of one) and likely avoiding some lock or resource issue that's now popping up. The most common culprits for the hang after moving it outside are:

• Incorrect primary key retrieval: If you're using SELECT MAX(id) FROM Submission to get the new submission ID, this can cause table locks (especially if the table is large or has concurrent writes) that block subsequent inserts.
• Cursor mismanagement: Failing to properly open, fetch, or close your explicit cursor can leave the session holding onto resources indefinitely.
• Uncommitted transactions: Without a COMMIT at the end, locks from the initial Submission insert might stay active, blocking further writes to Value or Submission_Value.

The Correct Approach #

Here's a clean, efficient version of your procedure that ensures only one Submission record is created, then links all your cursor values to it—without the hang:

CREATE OR REPLACE PROCEDURE create_single_submission_with_values IS
    v_submission_id NUMBER; -- Stores the ID of the single Submission we create
    -- Define your cursor to fetch the values you need
    CURSOR c_source_data IS
        SELECT your_value_column, other_needed_column 
        FROM your_source_table; -- Replace with your actual source table
    v_source_row c_source_data%ROWTYPE;
BEGIN
    -- Step 1: Insert ONE record into Submission, get its ID immediately
    INSERT INTO Submission (submission_col1, submission_col2) -- Replace with your actual columns
    VALUES ('default_val1', 'default_val2') -- Replace with your actual values
    RETURNING id INTO v_submission_id; -- Critical: Uses RETURNING to get the ID without a separate query

    -- Step 2: Loop through the cursor to create Value and Submission_Value records
    OPEN c_source_data;
    LOOP
        FETCH c_source_data INTO v_source_row;
        EXIT WHEN c_source_data%NOTFOUND; -- Exit loop when no more data

        -- Insert into Value table, get its ID
        DECLARE
            v_value_id NUMBER;
        BEGIN
            INSERT INTO Value (value_column) -- Replace with your Value table columns
            VALUES (v_source_row.your_value_column)
            RETURNING id INTO v_value_id;

            -- Link the Value to the Submission
            INSERT INTO Submission_Value (submission_id, value_id)
            VALUES (v_submission_id, v_value_id);
        END;
    END LOOP;
    CLOSE c_source_data; -- Always close explicit cursors

    COMMIT; -- Commit all changes at once to release locks
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- Undo all changes if something goes wrong
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); -- Print error for debugging
        RAISE; -- Re-throw the exception to alert the caller
END;
/

Key Fixes Explained #

1. RETURNING clause for ID retrieval: Instead of querying for the max ID (which is slow and risky), we get the new submission ID directly from the INSERT statement. This avoids lock contention and ensures we get the correct ID every time.
2. Proper cursor handling: We explicitly open, fetch, and close the cursor, with a clear exit condition when there's no more data. This prevents the cursor from hanging open.
3. Single transaction with commit: All inserts are wrapped in a single transaction, and we commit at the end to release any locks held during the process. If an error occurs, we rollback everything to keep data consistent.

Debugging Tips If It Still Hangs #

• Use DBMS_OUTPUT.PUT_LINE to print messages at each step (e.g., "Inserted Submission with ID: " || v_submission_id) to see exactly where the procedure gets stuck.
• Check for lock waits in your database with a query like:

  SELECT l.session_id, s.serial#, o.object_name, l.lmode, l.request
  FROM v$lock l
  JOIN v$session s ON l.session_id = s.sid
  JOIN dba_objects o ON l.id1 = o.object_id
  WHERE l.request > 0;
  

  This will show if your procedure is waiting on a lock held by another session.

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