This week I’ll take a look at IF-THEN-ELSE-ELSEIF syntax. Next blog I’ll take a look at the similar CASE syntax and compare/contrast the two. This will help document what I’ll cover in a live demo using Rapid SQL.
Lets look at the IF-THEN-ELSE syntax. Rapid SQL has a nice template in its code workbench. Use the ctrl+space bar to signal these!
Lets select the IF_ENDIF first.
IF THEN ELSE allows you to interrogate a variable then select a path for code execution. The END IF ends the IF statement. The ELSE is optional.
The THEN can contain another IF statement. This is called nesting IF statements. Oracle allows you to nest these like 250+ IF statements deep! This is just plain silly. I recommend not nesting beyond about 3 IF statements deep as the code gets confusing and hard to follow. This is useful when there are more questions to ask of a data item such as the below example.
Notice I indented the nested IF statement at line 10. I do this to make the flow of nesting easy to follow.
You can right-click on the workspace and select ‘Format SQL’ once your code is entered.
The other Rapid SQL IF statement template uses the IF – ELSIF syntax. This is more efficient when interrogating the same column for multiple values. Also, the IF statements should be in the order that they are likely to hit via the incoming data. Code execution continues past the END IF after a hit is made with one of the IF statements.
Yes, the ELSIF is misspelled!
This code shows a more efficient way of asking the same column for different data items. This is more efficient than a series of IF-THEN-END IF statements.
Notice I changed the variable assignment to a ‘:grade’ on line 3.
This allows RapidSQL to prompt me for the data. I submitted a ‘B’ in the above panel.
IF the typical grade is a ‘C’…then the check for C should be first in this list of IF-ELSIF.
I do not think there is a limit to the IF-ELSIF combination. There is a limit to the nesting of IF statements. There is a limit of like 32K for the size of the PL/SQL routine though…
Good luck out there with your Oracle development using Rapid SQL.
Dan Hotka
Oracle ACE Director
Instructor/Author/CEO