Writing Conditional Logic in PL/SQL
Writing Conditional Logic in PL/SQL
Get a review of various variations of the "if this, then that" conditional logic statements that you can use in your PL/SQL code.
Join the DZone community and get the full member experience.Join For Free
Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate
PL/SQL offers a number of options when it comes to writing conditional logic, as in variations on "if this, then that." This post reviews them and provides examples.
You can choose from the following:
- IF statement: IF, IF-THEN, IF-ELSE, IF-ELSIF-ELSE
- CASE statement: Simple and searched CASE
- CASE expression: The expression "version" of the statement (and one of my favorite elements of PL/SQL)
It's hard to imagine a programming language without a version of IF, and PL/SQL is no exception.
The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement can take one of three forms, but they all have this in common: they start with "IF" and then end with "END IF;".
The simplest IF statement: If the boolean expression evaluates to TRUE, then execute the statements between THEN and END IF. Example:
BEGIN IF l_hire_date < SYSDATE THEN send_survey_request (l_employee_id); END IF; END;
- You can put the IF expression inside parentheses, but you do not have to.
- If you've got a really complex, multi-part expression, consider assigning it to a variable first, as I show below. That way, you can more easily trace and test what that expression evaluates to.
So instead of this (an example from the code base of the Oracle Dev Gym):
BEGIN IF l_is_competitive = qdb_config.c_yes AND l_ce_status = qdb_competition_mgr.c_compev_ranked AND ( qdb_comp_event_mgr.is_closed (comp_event_id_in) OR comp_event_id IS NULL) THEN get_answer_info; END IF; END; /
...consider writing your code as follows:
BEGIN l_ok_to_see_answer := l_is_competitive = qdb_config.c_yes AND l_ce_status = qdb_competition_mgr.c_compev_ranked AND ( qdb_comp_event_mgr.is_closed (comp_event_id_in) OR comp_event_id IS NULL); qdb_utilities.trace_activity ('get_answer check', l_ok_to_see_answer); IF l_ok_to_see_answer THEN get_answer_info; END IF; END;
Of course, you will need to call your own trace utility, or better yet use the open-source Logger.
IF THEN ELSE
Offer an alternative action if your boolean expression does not evaluate to TRUE.
BEGIN IF l_hire_date < SYSDATE THEN send_survey_request (l_employee_id); ELSE send_invite_to_new_hire_meetup (l_employee_id); END IF; END;
- Same tip as with IF for moving complex expressions into a variable.
- There is a big difference between "does not evaluate to TRUE" and "evaluates to FALSE". That expression could evaluate to NULL.In the above IF-ELSE, an invitation will be sent to that "employee" even if the hire_date is NULL. Probably not what you want to do.
- If you need different actions for FALSE and NULL conditions, then you need to use ELSIF (below).
IF THEN ELSIF
When the logic gets tough, the tough start using ELSIFs.
Sometimes it's not an either-or situation. It's more of a this-or that-or the other-or wait a minute something else! Or as noted above, maybe you just need a separate clause for NULL, as in:
BEGIN IF l_hire_date < SYSDATE THEN send_survey_request (l_employee_id); ELSIF l_hire_date >= SYSDATE THEN send_invite_to_new_hire_meetup (l_employee_id); ELSE remove_from_employee_table (l_employee_id); END IF; END;
And of course, there's nothing stopping you from having lots of ELSIF classes:
BEGIN IF l_hire_date < ADD_MONTHS (SYSDATE, -12) THEN send_survey_request (l_employee_id); ELSIF l_hire_date BETWEEN ADD_MONTHS (SYSDATE, -12) AND SYSDATE THEN send_newhire_survey (l_employee_id); ELSIF EXTRACT (YEAR FROM l_hire_date) = EXTRACT (YEAR FROM SYSDATE) THEN send_invite_to_new_hire_meetup (l_employee_id); ELSIF EXTRACT (YEAR FROM l_hire_date) = EXTRACT (YEAR FROM SYSDATE) + 1 THEN add_to_waitlist (l_employee_id); ELSE remove_from_employee_table (l_employee_id); END IF; END;
- Avoid repeated evaluations of the same expressions in multiple IF-ELSIF clauses. I extract the year number for hire date and SYSDATE twice in the block above. These are fast operations, so it's no big deal, but if you call an "expensive" application function multiple times, assign the call to a variable and then reference the variable multiple times in the IF statement.
- If you start building a long list of ELSIF clauses, consider switching to a CASE statement. It's cleaner and easier to read/manage.
OK, so much for the kinda boring IF statement. Let's have fun with CASE!
CASE was added to PL/SQL in Oracle Database 9i, back in 2001 or so. How do I know? Because I looked through the various editions of Oracle PL/SQL Programming until I found its first appearance in the index! I was very happy when CASE was added, especially CASE expressions (which can be used in both SQL and PL/SQL, by the way).
A CASE statement is logically equivalent to an IF-ELSIF statement, but a CASE statement "presents" better (easier to read, often involving less code).
The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. , and has these forms:
- Simple, which evaluates a single expression and compares it to several potential values.
- Searched, which evaluates multiple conditions and chooses the first one that is true.
Here's a simple CASE statement:
FUNCTION grade_translator (grade_in IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2 (100); BEGIN CASE grade_in WHEN 'A' THEN retval := 'Excellent'; WHEN 'B' THEN retval := 'Very Good'; WHEN 'C' THEN retval := 'Good'; WHEN 'D' THEN retval := 'Fair'; WHEN 'F' THEN retval := 'Poor'; END CASE; RETURN retval; END;
Here's a searched CASE statement version of that same logic:
FUNCTION grade_translator (grade_in IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2 (100); BEGIN CASE WHEN grade_in = 'A' THEN retval := 'Excellent'; WHEN grade_in = 'B' THEN retval := 'Very Good'; WHEN grade_in = 'C' THEN retval := 'Good'; WHEN grade_in = 'D' THEN retval := 'Fair'; WHEN grade_in = 'F' THEN retval := 'Poor'; ELSE retval := 'No such grade'; END CASE; RETURN retval; END;
Hopefully, it's clear to you that using a searched CASE statement in which each WHEN clause has the same "variable = value" format is a poor choice. That's a perfect use case for a simple CASE.
Also, if all you are doing inside each WHEN clause is assigning a value to the same variable, that's a piece of code that is crying out for a CASE expression (next section).
Here's a more interesting example of a searched CASE statement, taken from the workout manager package for the Oracle Dev Gym:
FUNCTION can_change_workout (workout_id_in IN INTEGER) RETURN BOOLEAN IS l_count INTEGER; l_start DATE; l_end DATE; BEGIN SELECT wo.start_date, wo.end_date INTO l_start, l_end FROM dg_workouts wo WHERE workout_id = workout_id_in; CASE WHEN l_end <= SYSDATE THEN RETURN FALSE; WHEN l_start > SYSDATE THEN RETURN TRUE; ELSE /* The week has started. Have you done anything yet? */ SELECT COUNT (*) INTO l_count FROM dg_user_wo_results uwor, dg_user_workouts uwo WHERE uwor.completed_date IS NOT NULL AND uwo.workout_id = workout_id_in; RETURN l_count = 0; END CASE; END;
The CASE statement is appropriate when a different action is to be taken for each alternative. The CASE expression is great for when you need to evaluate or return a different value for each alternative. I love how CASE expressions de-clutter my code. Let's take a look.
A CASE expression "liberates" CASE from being its own statement. As an expression, CASE can be part of a statement:
- The right-hand side of an assignment
- Passed as an actual argument to the formal parameter of a subprogram
- Part of a larger expression
It's one of my favorite parts of PL/SQL because it lets me write more concise code. To give you a sense of that, suppose that I need to write a function that implements this silly requirement:
- Put a "-" between all three numbers.
- If s1 is like 'T%' then uppercase the string.
- If s2 is NULL, there should be just one - between s1 and s2.
- If s3's length > 6, then lowercase the string.
Here's the function using the IF statement:
FUNCTION plch_full_string ( s1 IN VARCHAR2 , s2 IN VARCHAR2 , s3 IN VARCHAR2) RETURN VARCHAR2 IS l_return VARCHAR (32767); BEGIN IF s1 LIKE 'T%' THEN l_return := UPPER (s1); ELSE l_return := s1; END IF; IF s2 IS NOT NULL THEN l_return := l_return || '-' || s2; END IF; IF LENGTH (s3) > 6 THEN l_return := l_return || '-' || LOWER (s3); ELSE l_return := l_return || '-' || s3; END IF; RETURN l_return; END;
And now with a CASE expression:
FUNCTION plch_full_string ( s1 IN VARCHAR2 , s2 IN VARCHAR2 , s3 IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN CASE WHEN s1 LIKE 'T%' THEN UPPER (s1) ELSE s1 END || CASE WHEN s2 IS NULL THEN NULL ELSE '-' END || s2 || '-' || CASE WHEN LENGTH (s3) > 6 THEN LOWER (s3) ELSE s3 END; END;
Right? Isn't that so much better? Here's another example of shrinking your code with CASE expressions, a progression from CASE statement to CASE expression to CASE expression directly inside call to DBMS_OUTPUT.PUT_LINE.
PROCEDURE show_pig_species_v1 (feature_in IN VARCHAR2) IS l_guinea_pig_species VARCHAR2 (100); BEGIN CASE feature_in WHEN 'curly' THEN l_guinea_pig_species := 'Texel'; WHEN 'long' THEN l_guinea_pig_species := 'Peruvian'; WHEN 'rosettes' THEN l_guinea_pig_species := 'Abyssinian'; END CASE; DBMS_OUTPUT.put_line ('Species=' || l_guinea_pig_species); END; PROCEDURE show_pig_species_v2 (feature_in IN VARCHAR2) IS l_guinea_pig_species VARCHAR2 (100); BEGIN l_guinea_pig_species := CASE feature_in WHEN 'curly' THEN 'Texel' WHEN 'long' THEN 'Peruvian' WHEN 'rosettes' THEN 'Abyssinian' END; DBMS_OUTPUT.put_line ('Species=' || l_guinea_pig_species); END; PROCEDURE show_pig_species_v3 (feature_in IN VARCHAR2) IS BEGIN DBMS_OUTPUT.put_line ( 'Species=' || CASE feature_in WHEN 'curly' THEN 'Texel' WHEN 'long' THEN 'Peruvian' WHEN 'rosettes' THEN 'Abyssinian' END); END;
Tips on CASE:
- If a CASE statement fails to find a match in any WHEN clauses and there is no ELSE clause, then an error is raised: ORA-06592: CASE not found while executing CASE statement.
- If a CASE expression fails to find a match in any WHEN clauses and there is no ELSE clause, then it returns NULL.
- You can use the CASE expression inside SQL. You can't use IF and CASE statements inside a SQL statement, because, well, it's already its own statement, and statements cannot have within them other statements.
Hey, What About DECODE?
And then there is DECODE. If you've used DECODE, that means that either you've been working with Oracle Database for a long time, or you should have used CASE but instead somehow latched onto this anachronism.
DECODE offers CASE-like functionality, but can only be used inside SQL (either outside of a PL/SQL block, or from within PL/SQL). It's been a part of SQL long before CASE was added to SQL. Haven't used it yet? Don't bother. You should use CASE instead. Have it in your existing SQL statements? Consider converting it to CASE the next time you need to work on those SQL statements.
I won't even bother showing you examples, but here is a link to appropriately aged doc on DECODE.
When you need to write conditional logic in your PL/SQL blocks (and who doesn't, now and again?), you have a nice range of offerings. Whether you go with the "traditional" IF statement or opt for CASE, you'll be able to get the job done with a minimum of fuss.
I simply suggest that you follow a few handy guidelines:
- Avoid redundancies in your code ("WHEN x = y" over and over in your CASE clauses or repeated execution of the same union in ELSIF clauses).
- Look for opportunities to use the CASE expression to cut down on code volume and improve readability.
- Assign complex Boolean expressions to variables or constants, rather than "embedding" those expressions inside your conditional statements. That way you can examine/trace the results of those expressions more easily, and debug your code more quickly.
Finally, visit the Oracle Dev Gym to take lots of quizzes about these PL/SQL features!
Published at DZone with permission of Steven Feuerstein , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.