CASE expression

We have seen how to use simple and searched CASE expression in PL/SQL, but this is not the complete story. We can even use a CASE (both simple and searched) in expression context. The CASE expression returns the value accordingly to the selected branch. If no branch is actually selected it returns NULL.

As en example, we see the usage of a local function in an anonymous PL/SQL block that converts the boolean it gets in input in a varchar2 that represents it. A function like that makes sense since the put_line() function in the DBMS_OUTPUT packages does not know how to manage boolean values:

declare
  function boolean_to_varchar2(flag in boolean) return varchar2 is
  begin
    return case flag
      when true then 'true'
      when false then 'false'
      else 'NULL' -- 1.
    end;
  end;
begin
  dbms_output.put_line(boolean_to_varchar2(true));
  dbms_output.put_line(boolean_to_varchar2(false));
  dbms_output.put_line(boolean_to_varchar2(NULL));
end;

1. if you comment this line, when the input boolean to this function is not true nor false, it won't throw an exception, but it will return NULL, and so the put_line() would just print an empty line.

More information on CASE in the chapter 4 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein, that is about conditional and sequential control.