PL/SQL逗号分隔字段校验与更新存储过程开发求助

Alright, let's fix that comma-separated CONCIL field handling in your SP_CANCEL procedure. I get that you need to strip out the specified P_CONCIL value—whether there are exactly 2 entries or up to 3—while keeping the remaining entries in their original order. Here's a clean, scalable approach that avoids messy conditional branches:

Core Approach #

Instead of writing separate logic for 2 vs 3 entries, we can:

1. Split the comma-separated CONCIL string into individual elements
2. Filter out the element matching P_CONCIL (with safeguards for accidental spaces)
3. Reassemble the remaining elements back into a comma-separated string

Updated PL/SQL Procedure Code #

Here's how to integrate this logic into your procedure:

CREATE OR REPLACE PROCEDURE SP_CANCEL (
    P_CONCIL IN VARCHAR2,
    -- Add any other required parameters here
) AS
BEGIN
    -- Your existing pre-processing logic goes here...

    -- Update the CONCIL field by removing the target value
    UPDATE your_target_table t  -- Replace with your actual table name
    SET t.CONCIL = (
        -- Reassemble remaining elements, default to empty string if none left
        SELECT NVL(LISTAGG(TRIM(val), ',') WITHIN GROUP (ORDER BY pos), '')
        FROM (
            -- Split CONCIL into individual elements, preserve original order
            SELECT REGEXP_SUBSTR(t.CONCIL, '[^,]+', 1, LEVEL) AS val,
                   LEVEL AS pos
            FROM dual
            CONNECT BY REGEXP_SUBSTR(t.CONCIL, '[^,]+', 1, LEVEL) IS NOT NULL
        ) split_vals
        -- Exclude the target value, use TRIM to handle accidental spaces
        WHERE TRIM(val) != TRIM(P_CONCIL)
    )
    -- Only update records that actually contain the target value (performance boost)
    WHERE t.CONCIL LIKE '%' || TRIM(P_CONCIL) || '%';

    -- Commit changes (adjust based on your transaction requirements)
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;  -- Re-throw the error for upstream handling
END SP_CANCEL;
/

Test Scripts to Validate #

You can test this logic with manual executions to confirm it works for all cases:

-- 1. Create test table and sample data
CREATE TABLE test_concil (id NUMBER, concil VARCHAR2(100));
INSERT INTO test_concil VALUES (1, 'A,B');
INSERT INTO test_concil VALUES (2, 'X,Y,Z');
INSERT INTO test_concil VALUES (3, 'P, Q, R');  -- Includes spaces to test TRIM
INSERT INTO test_concil VALUES (4, 'CONCIL_ONE,CONCIL_TWO');

-- 2. Test case 1: Remove from 2-element string
EXEC SP_CANCEL('A');
SELECT concil FROM test_concil WHERE id = 1;  -- Should return 'B'

-- 3. Test case 2: Remove from 3-element string
EXEC SP_CANCEL('Y');
SELECT concil FROM test_concil WHERE id = 2;  -- Should return 'X,Z'

-- 4. Test case 3: Remove value with surrounding spaces
EXEC SP_CANCEL('Q');
SELECT concil FROM test_concil WHERE id = 3;  -- Should return 'P,R'

Key Notes #

• TRIM Usage: Ensures we handle cases where elements have accidental leading/trailing spaces (e.g., 'P, Q').
• Order Preservation: The ORDER BY pos in LISTAGG keeps the remaining elements in their original order, which is important if sequence matters for your business logic.
• Performance: The WHERE clause filters only records containing P_CONCIL, avoiding unnecessary updates.
• Edge Case Handling: NVL ensures the field doesn't end up as NULL if all elements are removed (adjust this to match your business rules if needed).

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