In this article, we will discuss if else in pl/SQL and case in Oracle PL/SQL. You will learn how to use the PL/SQL IF statement to either execute or pass over a sequence of words depending upon a specified condition. Also, use of CASE Statements in conditional control structure in pl/SQL.
Please also read the previous articles:
|What’s inside: |
PL/SQL IF THEN statement
IF THEN ELSE statement
IF THEN ELSIF statement
Decision-making statements are those in charge of executing an idea out of multiple given reports based on some condition. The condition will return either true or false. Based on what the condition returns and the associated statement is executed.
The decision making statements in PL/SQL are of two types:
- If Else statements
- Case statement
Let’s see them all one by one with examples:
If Else in PL/SQL
The IF statement allows you to either execute or pass over a sequence of statements, depending on a condition. If the condition is true, then execute the statement, if false, then goto else statement.
The IF a statement has three forms:
IF THEN ELSE
IF THEN ELSIF
PL/SQL IF THEN statement
The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition estimate (evaluate) to TRUE, the
statements after the
THEN execute. and if the condition is false or NULL, then the IF statement does nothing
IF condition THEN statements; END IF;
PL/SQL IF THEN ELSE statement
The second form of
IF statement adds the keyword
ELSE, followed by an alternative sequence of statements. If the condition is not satisfied with the IF clause, then the value will go in else block.
IF condition THEN statements; ELSE else_statements; END IF;
Example of if-then-else statement
DECLARE sales NUMBER(8,2) := 13100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 1200; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; ELSE bonus := 50; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /
PL/SQL IF THEN ELSIF
If the first condition is FALSE or NULL, the ELSIF clause tests another state. An IF statement can have numbers of ELSIF clauses; the final ELSE clause is optional. The Conditions evaluates one by one from top to bottom. If any condition is proper, then its associated sequence of statements is executed, and control passes to the next statement. If all conditions are false or NULL, the ELSE clause’s sequence is executed and exited to end if. We can say that It allows you to choose between several alternatives.
IF condition_1 THEN statements_1 ELSIF condition_2 THEN statements_2 [ ELSIF condition_3 THEN statements_3 ] ... [ ELSE else_statements ] END IF;
Example Using the IF-THEN-ELSEIF Statemen
DECLARE sales NUMBER(8,2) := 23000; bonus NUMBER(6,2); emp_id NUMBER(6) := 130; BEGIN IF sales > 50000 THEN bonus := 1500; ELSIF sales > 35000 THEN bonus := 500; ELSE bonus := 100; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /
IF statement, the
CASE statement selects one sequence of statements to execute. It works almost the same as if-then-elsif statements.
- CASE statement uses a “selector” rather than a Boolean expression to choose the sequence. A selector is an expression in which the value of which uses to select one of several alternatives.
- The expression could be of any type (arithmetic, variables, etc.)
- Each alternative is assigned with a specific pre-defined value (selector), and the option with selector value that matches the conditional expression value will be executed.
- Unlike IF-THEN-ELSIF, the CASE statement can also be used in SQL statements.
- ELSE block in the CASE statement holds the sequence that needs to be executed when none of the alternatives got selected.
The syntax for the case statement in PL/SQL is −
CASE selector WHEN 'value1' THEN a1; WHEN 'value2' THEN S2; WHEN 'value3' THEN S3; ... ELSE Sn; -- default case END CASE;
Explanation of syntax:
- The conditions in the
WHENclauses are evaluated in order, from top to bottom.
- The sequence of statements associated with the
WHENclause whose condition evaluates to TRUE is executed. If more than one condition evaluates to TRUE, only the first one executes.
- If no condition evaluates to TRUE, the
ELSEclause executes. And If you skip the
ELSEclause and no expressions are TRUE, a
CASE_NOT_FOUNDan exception is raised.
Example of CASE statements :
DECLARE grade char(1) := 'A'; BEGIN CASE grade when 'A' then dbms_output.put_line('Excellent'); when 'B' then dbms_output.put_line('Very good'); when 'C' then dbms_output.put_line('Well done'); when 'D' then dbms_output.put_line('You passed'); when 'F' then dbms_output.put_line('Better try again'); else dbms_output.put_line('No such grade'); END CASE; END; /
1.What are the two types of exceptions.
Answer: Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.
2. What is the importance of SQLCODE and SQLERRM?
Answer: SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.
3. Explain TTITLE and BTITLE
Answer: TTITLE and BTITLE commands that control report headers and footers.
This is all about the conditional control structure in pl/sql. In the coming article, we will discuss the loops in pl/sql. So, please stay with us.
THANK YOU 🙂