Oracle Apps中PLSQL序列生成器：更新行号与自动生成

First, let's fix the existing rows to assign consecutive 1-5 LINE_NUM values. We'll use the ROW_NUMBER() window function to generate the sequence, partitioned by PO_HEADER (so each purchase order gets its own independent sequence) — adjust the ORDER BY clause if you need to preserve a specific order of items:

MERGE INTO your_table t
USING (
    SELECT 
        PO_HEADER,
        ITEM,
        ROW_NUMBER() OVER (PARTITION BY PO_HEADER ORDER BY ITEM) AS new_line_num
    FROM your_table
) src
ON (t.PO_HEADER = src.PO_HEADER AND t.ITEM = src.ITEM)
WHEN MATCHED THEN
    UPDATE SET t.LINE_NUM = src.new_line_num;

COMMIT;

Notes on the Update: #

• Replace your_table with the actual name of your table.
• The ORDER BY ITEM ensures the sequence is based on the item name; if you need a different order (like original insertion order), use a column that tracks that (e.g., a creation timestamp) instead.
• This will reset the LINE_NUM for every PO_HEADER to start at 1 and increment consecutively.

Next, create a reusable PL/SQL function to get the next LINE_NUM when inserting new rows. This function will return the highest existing LINE_NUM for a given PO_HEADER plus 1, or 1 if there are no rows for that PO:

CREATE OR REPLACE FUNCTION get_next_line_num(p_po_header IN your_table.PO_HEADER%TYPE)
RETURN your_table.LINE_NUM%TYPE
IS
    v_max_line_num your_table.LINE_NUM%TYPE;
BEGIN
    SELECT COALESCE(MAX(LINE_NUM), 0) + 1
    INTO v_max_line_num
    FROM your_table
    WHERE PO_HEADER = p_po_header;
    
    RETURN v_max_line_num;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 1;
END get_next_line_num;
/

How to Use the Function: #

When inserting a new row, call the function to get the next sequence number:

INSERT INTO your_table (PO_HEADER, ITEM, LINE_NUM)
VALUES (1, 'C', get_next_line_num(1));

COMMIT;

Concurrency Consideration: #

If multiple users might be inserting rows for the same PO_HEADER at the same time, you could run into race conditions where two sessions get the same LINE_NUM. To avoid this, you can:

• Add a SELECT ... FOR UPDATE in the function to lock the rows for the PO_HEADER before retrieving the max value, or
• Use a sequence per PO_HEADER (though this is less common in practice)
• For Oracle 12c+, consider using an identity column if you don't need per-PO sequences, but that would generate a global sequence across all POs.

This setup ensures your existing rows get the consecutive 1-5 sequence you need, and new rows automatically get the next sequential number without gaps.

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