Oracle SQL可重复执行的表行插入脚本实现方案问询

Let's break down your problem into two key parts: building a repeatable script that handles inserting/updating the KPI_TYPE row and adjusts the sequence only on first run, and fixing the missing output from your PL/SQL block. Here's a step-by-step solution:

1. Repeatable Script for KPI_TYPE Row and Sequence Update #

Your MERGE statement works for the insert/update logic, but it doesn't give you control to adjust the sequence only when a new row is inserted. A PL/SQL block is a better fit here because it lets you explicitly check for the row's existence and handle the sequence adjustment conditionally.

Here's the complete, repeatable script:

DECLARE
    v_row_exists NUMBER;
BEGIN
    -- First, check if the KPI_TYPE_ID 26 already exists in the table
    SELECT COUNT(*)
    INTO v_row_exists
    FROM KPI_TYPE
    WHERE KPI_TYPE_ID = 26;

    IF v_row_exists = 0 THEN
        -- Insert the new row (we explicitly set KPI_TYPE_ID, so your trigger won't fire the sequence)
        INSERT INTO KPI_TYPE (KPI_TYPE_ID, NAME)
        VALUES (26, 'Web Service Availability');
        
        -- Adjust the KPI_TYPE_SEQ to set LAST_NUMBER to 27
        DECLARE
            v_current_seq_val NUMBER;
        BEGIN
            -- Get the current next value of the sequence
            SELECT KPI_TYPE_SEQ.NEXTVAL INTO v_current_seq_val FROM DUAL;
            
            -- If the sequence hasn't passed 26 yet, increment it to reach 27
            IF v_current_seq_val <= 26 THEN
                -- Calculate how much we need to jump to get to 27
                EXECUTE IMMEDIATE 'ALTER SEQUENCE KPI_TYPE_SEQ INCREMENT BY ' || (27 - v_current_seq_val);
                -- Advance the sequence once to hit 27
                SELECT KPI_TYPE_SEQ.NEXTVAL INTO v_current_seq_val FROM DUAL;
                -- Reset the sequence's increment back to 1 for future use
                EXECUTE IMMEDIATE 'ALTER SEQUENCE KPI_TYPE_SEQ INCREMENT BY 1';
            END IF;
        END;
        
        DBMS_OUTPUT.PUT_LINE('Success: Inserted new KPI_TYPE row and adjusted sequence to start at 27.');
    ELSE
        -- Update only the NAME field for the existing row
        UPDATE KPI_TYPE
        SET NAME = 'Web Service Availability'
        WHERE KPI_TYPE_ID = 26;
        
        DBMS_OUTPUT.PUT_LINE('Success: Updated NAME for existing KPI_TYPE row (ID 26).');
    END IF;
    
    -- Commit the changes (remove if you want to manage transactions manually)
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- Rollback on error and show the error message
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
        RAISE; -- Re-throw the error to notify the caller
END;
/

Key Details: #

• Row Existence Check: We use COUNT(*) to verify if the row is already present, so we know whether to insert or update.
• Sequence Adjustment: We only tweak the sequence if we're inserting the row. The logic adjusts the sequence's increment temporarily to jump to 27, then resets it back to 1 so future NEXTVAL calls work normally.
• Trigger Compatibility: Your B1_KPI_TYPE trigger only fires when KPI_TYPE_ID is NULL. Since we explicitly set the ID to 26 during insertion, the trigger won't interfere with our sequence adjustment.

2. Fixing the Missing PL/SQL Output #

Your original PL/SQL block is logically correct—you just need to enable DBMS_OUTPUT to see the results. Here's how:

For SQL*Plus/SQLcl: #

Run this command before executing your PL/SQL block:

SET SERVEROUTPUT ON;

This enables the output buffer, so DBMS_OUTPUT.PUT_LINE messages will appear in your console.

For GUI Tools (PL/SQL Developer, Toad, etc.): #

• Locate the "DBMS Output" panel (usually in the bottom or side toolbar).
• Click the "Enable" button (or check the "Enable DBMS Output" option) to activate the output buffer.
• Re-run your PL/SQL block, and you'll see the "Is 26" or "Is not 26" message in the panel.

3. Why Your Original MERGE Didn't Handle the Sequence #

MERGE is great for row-level insert/update, but it doesn't provide a way to run additional logic (like sequence adjustments) only in the NOT MATCHED branch. Using a PL/SQL block gives you the conditional control you need for this edge case.

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