Twitter Linkedin RSS
Close
Oracle

23ai PL/SQL Dangling Predicates in CASE expressions

Sean D. StuberLeave a comment

23ai introduces dangling predicates as a new feature for CASE statements. Per the Oracle documentation they extend the simple case structure, allowing you to match WHEN clauses for conditions other than single values.

That is, prior to 23ai, you could write a CASE statement like this:

CASE p_value
   WHEN 1 then 'White'
   WHEN 2 then 'Green'
   WHEN 3 then 'Yellow'
   ELSE 'Red'
END;

But, if you wanted to check for ranges of values, then you needed to use a searched format CASE:

CASE
   WHEN  p_value < 41 THEN 'White'
   WHEN  p_value < 71 THEN 'Green'
   WHEN  p_value < 95 THEN 'Yellow'
   ELSE 'Red'
END;

With 23ai though, you can now have conditions in your simple case like the searched case, but without specifying the left operand.

CASE p_value
   WHEN < 41 THEN 'White'
   WHEN < 71 THEN 'Green'
   WHEN < 95 THEN 'Yellow'
   ELSE 'Red'
END;

This format saves a few keystrokes, but more importantly (in my opinion) it is an improved, self-documenting feature. I prefer to use the simple format (with selector values or dangling predicates) because it defines scope at the beginning of the construct. In the examples above, you know each CASE statement uses p_value. While I could reference other variables, constants, or functions in each of the WHEN clauses, we know up front the intent or purpose of the CASE expression is to evaluate p_value. It is the driver of the statement, at least that's how I find it most helpful to think about when writing and reading these in actual code.

A complete use case of the above might look like this:

CREATE FUNCTION color_code(p_value IN PLS_INTEGER)
    RETURN VARCHAR2
IS
BEGIN
    RETURN CASE p_value
             WHEN < 41 THEN 'White'
             WHEN < 71 THEN 'Green'
             WHEN < 95 THEN 'Yellow'
             ELSE 'Red'
           END;
END;

This CASE format can also be used for procedural flow instead of as a resolved expression.

CREATE PROCEDURE color_code(p_value IN PLS_INTEGER)
IS
BEGIN
   CASE p_value
      WHEN < 41 THEN dbms_output.put_line('White');
      WHEN < 71 THEN dbms_output.put_line('Green');
      WHEN < 95 THEN dbms_output.put_line('Yellow');
      ELSE dbms_output.put_line('Red');
   END CASE;
END;

Unfortunately this syntax is only available in PL/SQL at this time. Hopefully it will be extended to SQL as well in future releases. Until that happens you can use WITH clause pl/sql declarations and/or create functions (including sql macro functions) to implement in pl/sql syntax but expose it for SQL usage.

For more information, see the Database PL/SQL Language Reference: https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/plsql-language-fundamentals.html#GUID-F52A70F5-87E1-4AAE-9388-FE56E1ED5598


Leave a Reply