The 7 July quiz tested your knowledge of using CASE expressions to implement conditional logic.
One player wrote: "I just marked all of the answers wrong because the CASE statements didn't end with END CASE (the SQL one I reckoned was wrong anyway). Is this an error in quiz, or is it deliberate, or am I wrong about CASE statements needing to end with 'END CASE'? I would have marked at least one answer as correct if it wasn't for that."
Unfortunately, this player was wrong, but the confusion is very understandable.
When you use CASE in PL/SQL, you can write either a CASE statement or a CASE expression.
A CASE statement requires an "END CASE" for termination.
A CASE expression is terminated only with an "END".
I don't really know why Oracle took these different approaches. Any thoughts out there?
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
Hello All,
ReplyDeleteThe END CASE reminds me of the old good SQL*FORMS of my Oracle youth ...
I'd just add that I asked myself the same very question and, what is more annoying, is that, except the documentation itself that of course contains the correct syntax, usually this difference IS NOT pointed out in different presentations, sessions, classes that introduced
CASE as a new feature (if I remember correctly, it was only partially available in Oracle8i,
and fully in Oracle9i).
So, unfortunately, the "usual" way of learn about it is "to fall into the trap", as it happened to that player, and probably to many of us in the past ...
As to the issue itself, one of my suppositions is that, if I remember correctly, the CASE expression was introduced in SQL somewhat before
the PL/SQL, and thus also before the CASE statement.
In SQL it is unusual to "END" expressions,
but, however, regarding the CASE, Oracle authors probably felt that it is mandatory to end it, so they added the "small" END.
Otherwise, they could have defined it as a function and use paranthesis.
Later on, when the CASE statement came, they probably decided to use a pattern similar to other PL/SQL statements that always end with a similar construct as their start, like END IF, END LOOP, a.s.o. and thus I suppose that the END CASE was born, but the CASE expression, probably also for backward compatibility reasons, it remained simply END.
Just an assumption ...
But, as a personal taste, both constructs are "nicely closed" ... something that always bothers me for example when those "open" constructs like the different TIMESTAMP and INTERVAL types are used, where it is not very clear where do they end.
Thanks & Best Regards,
Iudith
The difference in syntax between CASE expressions and CASE statements is due to their heritage. CASE expressions are part of the ANSI-92 SQL standard with which Oracle is mostly compliant. The syntax of PL/SQL is modeled after that of the Ada programming language which generally closes blocks with an END specific to the type of block being closed (for example, END CASE, END LOOP, and END IF).
ReplyDeleteThis difference in heritage also explains why CASE expressions don't raise exceptions when none of the conditions are met, but CASE statements do.