iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A PL/SQL CASE expression returns a value; a CASE statement chooses and runs one or more statements. Use an expression when a branch should produce a value for an assignment or larger expression. Use a statement when branches should perform different actions. Both can test one selector against alternatives (simple CASE) or evaluate conditions in order (searched CASE), but their branch contents and behavior when nothing matches are different.
How do a CASE expression and CASE statement differ?
| Question | CASE expression | CASE statement |
|---|---|---|
| Purpose | Returns a value. | Selects and executes a statement or statements. |
| Typical use | Supply a value in an assignment or another larger expression. | Choose procedural actions, such as calling a procedure or assigning several variables. |
| Branch contents | A result value for each matching alternative. | PL/SQL statement or statements for each matching alternative. |
| Closing syntax | END, as part of the enclosing expression. |
END CASE; |
| No matching alternative and no ELSE | Returns NULL. |
Raises the predefined CASE_NOT_FOUND exception. |
Oracle describes a CASE expression as a value-producing expression that can form part of a larger statement, while a CASE statement is a control-flow construct. See Oracle’s PL/SQL expressions reference and CASE statement reference.
When should you use each form?
Use a CASE expression to choose a value
Choose an expression when each branch supplies one result, such as a label to assign to a variable. This illustrative expression handles a null status before checking the other values:
status_label := CASE
WHEN status_code IS NULL THEN 'Missing'
WHEN status_code = 'A' THEN 'Active'
ELSE 'Other'
END;Use a CASE statement to choose an action
Choose a statement when different branches need to perform different actions. The illustrative statement calls a different procedure for each status:
#1 Best Overall
CASE status_code
WHEN 'A' THEN activate_account;
WHEN 'S' THEN suspend_account;
ELSE log_unrecognized_status;
END CASE;These snippets illustrate the distinction; they are not complete PL/SQL blocks. Declarations and procedure definitions depend on the surrounding program.
What are simple and searched CASE?
Both expressions and statements support two ways to describe alternatives:
- Simple CASE: Evaluate one selector and compare it with alternative values.
- Searched CASE: Evaluate Boolean conditions in order and use the first condition that is true.
Simple CASE suits a decision based on one value. Searched CASE is needed for conditions such as ranges or null checks. Oracle documents these forms in its PL/SQL CASE statement reference and PL/SQL control statements reference.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →What happens when no WHEN clause matches?
The result depends on whether you wrote an expression or a statement. For either form, an explicit ELSE supplies the fallback. Without one, a CASE expression returns NULL; a CASE statement raises CASE_NOT_FOUND. Decide whether a null result is acceptable for the expression, and whether the statement’s exception should be handled in the surrounding PL/SQL code. Oracle explains these behaviors in its CASE statement reference and expressions reference.
Does WHEN NULL match a NULL selector?
No. In a simple CASE, a null selector does not match WHEN NULL. Use searched CASE and test nullness explicitly, for example CASE WHEN status_code IS NULL THEN .... Use that condition with an expression payload when returning a value, or with a statement payload when performing an action. Oracle describes this behavior in its PL/SQL control statements reference.
How does Oracle evaluate alternatives?
Alternatives are checked in order. Once a match is found, Oracle uses that alternative and does not evaluate later alternatives. In a searched CASE, order therefore matters when conditions overlap: put the intended higher-priority condition first. This first-match behavior is documented for PL/SQL in Oracle’s CASE statement reference and expressions reference.
Rank #4
Are SQL CASE expression rules the same as PL/SQL CASE rules?
Do not apply SQL expression-specific rules automatically to every PL/SQL CASE statement. Oracle’s SQL Language Reference for Database 12.2 says SQL CASE expressions require compatible return types (with numeric precedence rules for numeric types), can have collation-sensitive character comparisons, and allow at most 65,535 arguments. Those are SQL CASE expression rules documented for that release, not universal rules for PL/SQL CASE statements. Check the SQL reference for the Oracle Database release and SQL context you use: Oracle Database 12.2 SQL CASE Expressions.
Recommended Free Tools
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
