Oracle表插入新行后COL_LOC字段自动按Excel列规则移位的实现

First, let's fix a key issue with your existing column number calculation: it doesn't correctly map Excel-style column identifiers (like APS, APT) to their numeric equivalents. Excel uses a base-26 numbering system (A=1, Z=26, AA=27, AB=28, etc.), so we'll start with proper conversion logic, then build the full automation.

Step 1: Create a Reusable Column Identifier Conversion Function #

We need a function to turn numeric column positions back into Excel-style labels (e.g., 27 → AA, 53 → BA). This is way more scalable than hardcoding values for each character:

CREATE OR REPLACE FUNCTION num_to_excel_col(p_num IN NUMBER) RETURN VARCHAR2 IS
    v_result VARCHAR2(10) := '';
    v_num NUMBER := p_num;
BEGIN
    WHILE v_num > 0 LOOP
        v_result := CHR(MOD(v_num - 1, 26) + ASCII('A')) || v_result;
        v_num := TRUNC((v_num - 1) / 26);
    END LOOP;
    RETURN v_result;
END;
/

Step 2: Correct Column Number Calculation #

To accurately get the numeric position of any COL_LOC value, use this query (it properly handles Excel's base-26 logic):

SELECT 
    MXP_ID, 
    MX_ID, 
    TB_SRC, 
    "DESC", 
    COL_LOC,
    SUM( (ASCII(SUBSTR(COL_LOC, LEVEL, 1)) - ASCII('A') + 1) * POWER(26, LENGTH(COL_LOC) - LEVEL) ) AS COLUMN_NUMBER
FROM MyTable
CONNECT BY LEVEL <= LENGTH(COL_LOC)
GROUP BY MXP_ID, MX_ID, TB_SRC, "DESC", COL_LOC
ORDER BY COLUMN_NUMBER;

Step 3: Automate Insert & Shift with a Stored Procedure #

Wrap the logic to insert a new row and shift subsequent COL_LOC values into a stored procedure for easy, repeatable use. This takes the column to insert after, the new MX_ID, and new description as inputs:

CREATE OR REPLACE PROCEDURE insert_and_shift_col(
    p_insert_after_col IN VARCHAR2, 
    p_new_mx_id IN VARCHAR2, 
    p_new_desc IN VARCHAR2
) IS
    v_insert_col_num NUMBER;
    v_new_col_loc VARCHAR2(10);
BEGIN
    -- Get the numeric position of the column we're inserting after
    SELECT 
        SUM( (ASCII(SUBSTR(COL_LOC, LEVEL, 1)) - ASCII('A') + 1) * POWER(26, LENGTH(COL_LOC) - LEVEL) )
    INTO v_insert_col_num
    FROM MyTable
    WHERE COL_LOC = p_insert_after_col
    CONNECT BY LEVEL <= LENGTH(COL_LOC)
    GROUP BY COL_LOC;
    
    -- Calculate the column identifier for the new row
    v_new_col_loc := num_to_excel_col(v_insert_col_num + 1);
    
    -- Update all rows with a higher column number: shift their COL_LOC to the next position
    UPDATE MyTable t1
    SET COL_LOC = (
        SELECT num_to_excel_col(t2.COLUMN_NUMBER + 1)
        FROM (
            SELECT 
                MXP_ID,
                SUM( (ASCII(SUBSTR(COL_LOC, LEVEL, 1)) - ASCII('A') + 1) * POWER(26, LENGTH(COL_LOC) - LEVEL) ) AS COLUMN_NUMBER
            FROM MyTable
            CONNECT BY LEVEL <= LENGTH(COL_LOC)
            GROUP BY MXP_ID, COL_LOC
        ) t2
        WHERE t2.MXP_ID = t1.MXP_ID
    )
    WHERE EXISTS (
        SELECT 1
        FROM (
            SELECT 
                MXP_ID,
                SUM( (ASCII(SUBSTR(COL_LOC, LEVEL, 1)) - ASCII('A') + 1) * POWER(26, LENGTH(COL_LOC) - LEVEL) ) AS COLUMN_NUMBER
            FROM MyTable
            CONNECT BY LEVEL <= LENGTH(COL_LOC)
            GROUP BY MXP_ID, COL_LOC
        ) t3
        WHERE t3.MXP_ID = t1.MXP_ID
        AND t3.COLUMN_NUMBER > v_insert_col_num
    );
    
    -- Insert the new row with the calculated column identifier
    INSERT INTO MyTable (MXP_ID, MX_ID, TB_SRC, "DESC", COL_LOC)
    VALUES (1, p_new_mx_id, 'MB_SHEET_ROW', p_new_desc, v_new_col_loc);
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Column ' || p_insert_after_col || ' does not exist in MyTable.');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/

Step 4: Run the Automation #

Call the stored procedure with your specific values to replicate your example scenario:

-- Insert MX_NEW after APT, matching your requested outcome
EXEC insert_and_shift_col('APT', 'MX_NEW', 'NEW_ENTRY');

This will:

1. Locate the numeric position of APT
2. Shift all rows with a higher column number to their next Excel-style identifier (e.g., APU → APV, APV → APW, etc.)
3. Insert the new MX_NEW row with the APU column identifier

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