Hi,
Last blog we visited the IF-THEN-ELSE conditional syntax. This blog, I’d like to convert those examples using the newer CASE syntax. RapidSQL has templates for this syntax as well!
Using the ctrl + space bar, you can see the templates available in the code workbench of RapidSQL.
You can also type in ‘case’ then ctrl + spacebar…if you know the shortcut name for the templates, saves pulling up the whole list.
Like the IF-THEN-ELSE, these too can be nested…the WHEN clause can have another CASE statement…and like the IF-THEN-ELSE…these too can be nested about 250 deep or so…but as I said in the last blog, I just don’t see a need to go more than about 3 levels deep. The code gets confusing and there has to be a better way to code the solution!
Lets convert the grade example using CASE.
Again, RapidSQL will prompt for the bind variable on line 3.
The same performance issue is realized with CASE that I mentioned with the IF-THEN-ELSE. IF the student is given a ‘C’ most of the time…put the ‘C’ check at the top of the list. Just like the IF-THEN-ELSE…when the condition is met, the remaining checks are not performed.
Which is better? IF-THEN-ELSE or CASE? Hard to say. I’d leave it up to the developer which statement they prefer. Another line of thought is to use the same coding techniques as other programs in the same series. I’ve always promoted this in the past…make all the programs in a series look the same…same coding style.
Just an FYI…internally when Oracle9+ has the optimizing compiler option set to 3…CASE is converted back to IF-THEN-ELSE statements. This tells me that IF-THEN-ELSE might be a bit more efficient…but in the long run…I don’t think it matters that much. Use which ever you are more comfortable with.
Till next time,
Dan Hotka
Oracle ACE Director
Instructor/Author/CEO