如何判断单条记录多列非空并设置v_ethn_code变量？

Problem Context #

You have an Oracle table users with columns id, ethn_1, ethn_2, ethn_3, ethn_4 (the latter four are varchar2(3)). Your requirements are:

• When exactly one of the four ethn_* columns has a value, set v_ethn_code to that ethnic code
• When multiple columns are non-null, set v_ethn_code to 'Unknown'

Your table structure and sample data:

CREATE TABLE users ( 
    id number(4) NOT NULL, 
    ethn_1 varchar2(3), 
    ethn_2 varchar2(3), 
    ethn_3 varchar2(3), 
    ethn_4 varchar2(3)
); 

INSERT INTO users (id, ethn_1, ethn_2, ethn_3, ethn_4) VALUES (1,'AS',NULL,NULL,NULL); 
INSERT INTO users (id, ethn_1, ethn_2, ethn_3, ethn_4) VALUES (2,NULL,NULL,'WH',NULL); 
INSERT INTO users (id, ethn_1, ethn_2, ethn_3, ethn_4) VALUES (3,NULL,'BL',NULL,NULL); 
INSERT INTO users (id, ethn_1, ethn_2, ethn_3, ethn_4) VALUES (4,'AS','BL',NULL,NULL); 
INSERT INTO users (id, ethn_1, ethn_2, ethn_3, ethn_4) VALUES (5,NULL,NULL,NULL,'HO'); 
INSERT INTO users (id, ethn_1, ethn_2, ethn_3, ethn_4) VALUES (6,NULL,NULL,'WH','HO'); 
INSERT INTO users (id, ethn_1, ethn_2, ethn_3, ethn_4) VALUES (7,NULL,'BL',NULL,NULL);

You tried nested conditionals but ran into logical issues and redundant code—let's fix that with a cleaner, more efficient approach.

Solution 1: Calculate in a SQL Query #

Leverage Oracle's NVL2 function to count non-empty columns, then use a CASE statement with COALESCE to get the result in a single query.

SELECT 
    id,
    CASE 
        WHEN non_empty_count = 1 THEN COALESCE(ethn_1, ethn_2, ethn_3, ethn_4)
        ELSE 'Unknown'
    END AS v_ethn_code
FROM (
    SELECT 
        id,
        ethn_1,
        ethn_2,
        ethn_3,
        ethn_4,
        -- Sum 1 for each non-null ethnic column
        NVL2(ethn_1, 1, 0) + 
        NVL2(ethn_2, 1, 0) + 
        NVL2(ethn_3, 1, 0) + 
        NVL2(ethn_4, 1, 0) AS non_empty_count
    FROM users
) subquery;

How this works:

• NVL2(col, 1, 0) returns 1 if col is non-null, 0 otherwise—summing these gives the total number of non-empty ethnic columns
• If the count is exactly 1, COALESCE picks the only non-null value (order doesn't matter here since only one exists)
• For any other count (0 or ≥2), we return 'Unknown'

Solution 2: Assign to a Variable in PL/SQL #

If you need to set a PL/SQL variable (e.g., in a stored procedure), use the same counting logic but fetch values into variables first:

DECLARE
    v_ethn_code VARCHAR2(10); -- Adjust length as needed
    v_non_empty_count NUMBER;
    v_single_ethn_val VARCHAR2(3);
    v_target_id NUMBER := 4; -- Replace with your target user ID
BEGIN
    -- Fetch count of non-empty columns and the single non-null value (if exists)
    SELECT 
        NVL2(ethn_1, 1, 0) + NVL2(ethn_2, 1, 0) + NVL2(ethn_3, 1, 0) + NVL2(ethn_4, 1, 0),
        COALESCE(ethn_1, ethn_2, ethn_3, ethn_4)
    INTO v_non_empty_count, v_single_ethn_val
    FROM users
    WHERE id = v_target_id;

    -- Assign the correct value to v_ethn_code
    IF v_non_empty_count = 1 THEN
        v_ethn_code := v_single_ethn_val;
    ELSE
        v_ethn_code := 'Unknown';
    END IF;

    -- Optional: Verify the result
    DBMS_OUTPUT.PUT_LINE('User ID ' || v_target_id || ' Ethnic Code: ' || v_ethn_code);
END;
/

Why This Is Better Than Nested Conditionals #

• Cleaner Logic: No messy layers of IF-ELSE checks for every possible column combination
• Maintainable: If you add/remove ethnic columns later, you only need to update the NVL2 sum and COALESCE list
• Efficient: Only requires a single pass over the data (either in the query or PL/SQL fetch)

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