寻求替代PL/SQL存储过程IF-ELSE的直接SELECT计数查询

Got it, let's ditch that clunky IF-ELSE logic in your PL/SQL procedure and replace it with a clean, efficient SELECT query using a CASE statement. This will handle all your conditional counting in one go, whether you're working with table rows or individual variables.

Solution for Table Data #

If you need to calculate this count for every row in a table (say, your_table), use this query:

SELECT
    CASE
        -- All three values positive: return 3
        WHEN A > 0 AND B > 0 AND C > 0 THEN 3
        -- Exactly one positive, two negative: return 1
        WHEN (A < 0 AND B < 0 AND C > 0) 
             OR (A < 0 AND B > 0 AND C < 0) 
             OR (A > 0 AND B < 0 AND C < 0) THEN 1
        -- Exactly two positive, one negative: return 2
        WHEN (A > 0 AND B > 0 AND C < 0) 
             OR (A > 0 AND B < 0 AND C > 0) 
             OR (A < 0 AND B > 0 AND C > 0) THEN 2
        -- All other cases: count actual number of positive values (handles zeros, all negatives, etc.)
        ELSE GREATEST(0, SIGN(A) + SIGN(B) + SIGN(C))
    END AS count_val
FROM your_table;

Solution for Individual Variables #

If you're working with bind variables (like in a PL/SQL context where you have :A, :B, :C as inputs), use this version with DUAL:

SELECT
    CASE
        WHEN :A > 0 AND :B > 0 AND :C > 0 THEN 3
        WHEN (:A < 0 AND :B < 0 AND :C > 0) 
             OR (:A < 0 AND :B > 0 AND :C < 0) 
             OR (:A > 0 AND :B < 0 AND :C < 0) THEN 1
        WHEN (:A > 0 AND :B > 0 AND :C < 0) 
             OR (:A > 0 AND :B < 0 AND :C > 0) 
             OR (:A < 0 AND :B > 0 AND :C > 0) THEN 2
        ELSE GREATEST(0, SIGN(:A) + SIGN(:B) + SIGN(:C))
    END AS count_val
FROM DUAL;

How It Works #

Let's break down the logic to make sure it matches your requirements:

• The first WHEN clause directly checks for all positive values, returning 3 as specified.
• The second clause catches every combination where exactly one value is positive (and the other two are negative).
• The third clause covers all scenarios with exactly two positive values (and one negative).
• For the ELSE case (which includes zeros, all negatives, or mixes of positives/negatives/zeros), we use the SIGN() function:
  • SIGN(x) returns 1 if x is positive, -1 if negative, 0 if zero.
  • Adding these together gives a sum that represents the net positive count (zeros contribute nothing, negatives subtract).
  • GREATEST(0, sum) ensures we never return a negative number (e.g., if all values are negative, the sum is -3, so we return 0 instead).

This approach is far more concise than nested IF-ELSE blocks and works seamlessly in both ad-hoc queries and PL/SQL contexts.

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