Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
The conditional statements are used to test certain conditions and execute the statements based on the outcome of that "test." There are two types of conditional control statements in PL/SQL:
IF statements.
CASE statements.
The IF statements are further classified into three types:
IF THEN
IF THEN ELSE
IF THEN ELSE IF
The CASE statement is classified into two types:
Simple CASE statements
Searched CASE statements
We shall discuss each of the above conditional statements and also see an example of it. The topic is very simple, and the only prerequisite is that the reader must be aware of the basic syntax of PL/SQL. However, everything has been explained in this article.
The IF statement is used to perform or execute certain statements if a given condition is true. The IF statement in PL/SQL acts similarly to that of statements in any other programming language. We use the IF statement to test a particular condition. The result or the output depends on the outcome of that condition. If the condition is true, then the statements for which the condition is true are executed otherwise, not.
We shall now discuss the various types of IF statements.
IF THEN Statement
This is the most simple IF statement used in PL/SQL.
Syntax:
IF condition THEN
execute the statements;
END IF;
The IF then statement is used to check a single condition, and if the condition is fulfilled or true, then the set of statements is executed. Here we do not specify what to do if the condition fails. Consider a simple example below:
DECLARE
age NUMBER :=18;
BEGIN
IF age>=18 THEN
DBMS_OUTPUT.PUT_LINE(‘You are an eligible voter’);
END IF;
END;
In the above example, we declared a variable age and assigned a value 18 to it. In the IF block, we have a condition (age>=18), the age must be greater than or equal to 18 to be eligible to vote. Since the age value is 18, it will display " You are an eligible voter." in the output. It can be observed that we, however, did not specify what is to be displayed if the age value is less than 18. The statements inside the IF block are only executed if the condition is true.
IF THEN ELSE Statement
In the above example, we saw that nothing could be displayed if the IF statement is false. The IF THEN ELSE statement is an extension to the IF statement. The IF THEN ELSE statement gives us the flexibility to execute the set of statements in case the IF condition is not true.
Syntax:
IF condition THEN
execute the statements when the condition is true;
ELSE
execute the statements when the condition is not true;
END IF;
First, the condition is checked. If the condition is true, then the statements inside the IF block are executed; otherwise, the statements inside the ELSE block for which the condition is true are executed.
Example:
The output of the below PL/SQL code is "Sorry, You cannot vote!"
DECLARE
age NUMBER :=16;
BEGIN
IF age>=18 THEN
DBMS_OUTPUT.PUT_LINE(‘You are an eligible voter’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Sorry, You cannot vote!’);
END IF;
END;
The problem with the IF-THEN and IF THEN ELSE statements is that we can check one condition at a time. However, the IF-THEN ELSIF statement allows us to evaluate multiple conditional statements at a time.
Firstly, the IF statement is checked. If it is true, then the statements for which the condition is true are executed. In case the condition is false, we move to the next condition, which is present in the ELSIF statement. If the condition is true, then the statement inside the ELSIF block is executed, and further conditions will not be evaluated. Otherwise, the other conditions will be checked.
The IF-THEN ELSIF statement may or may not have an ELSE statement.
Syntax:
IF condition1 THEN
execute the statements;
ELSIF condition2 THEN
execute the statements when condition2 is true;
ELSIF condition3 THEN
execute the statements when condition3 is true;
…
ELSIF conditionN THEN
Execute the statements when the nth condition is true;
ELSE
execute the else statement;
END IF;
Example:
DECLARE
capital VARCHAR(20) := ‘New Delhi’;
BEGIN
IF capital = ‘Buenos Aires’ THEN
DBMS_OUTPUT.PUT_LINE(‘ It is capital of Argentina’);
ELSIF capital = ‘Washington DC’ THEN
DBMS_OUTPUT.PUT_LINE(‘ It is capital of the USA’);
ELSIF capital = ‘London’ THEN
DBMS_OUTPUT.PUT_LINE(‘ It is capital of the United Kingdom’);
ELSIF capital = ‘New Delhi’ THEN
DBMS_OUTPUT.PUT_LINE(‘ It is capital of India’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘ Please enter the capital name correctly’);
END IF;
END;
In the above example, we have declared a variable capital and assigned New Delhi as the value. Each condition is checked if the capital is equal to the value that is given in the condition. For the first three conditions, the value does not match. However, in the fourth condition, the value is equal to 'New Delhi'. Therefore the output is displayed as "It is capital of India." The else block is not checked after that, and the execution terminates.
The CASE Statements
The CASE statement is very similar to the IF-THEN ELSIF statement. Just as the IF-THEN ELSIF statement evaluates the conditions, the CASE statement chooses the correct alternative from a set of conditions. The key difference is that the CASE statement uses a selector instead of a boolean expression to select an alternative. The CASE statement is similar to the "switch-case" functionality available in various programming languages.
The CASE statement is of two types:
Simple CASE statements
Searched CASE statements
Simple CASE statements
Syntax:
CASE selector
WHEN selector_value_1 THEN execute the statement 1
WHEN selector_value_2 THEN execute the statement 2
...
WHEN selector_value_n THEN execute the statement n
ELSE else_statements
END CASE;
A selector is a variable that is already defined. Whenever a selector value matches with the value of the selector, the statements for that case are executed. Consider the following example for clarification.
Example:
DECLARE
name VARCHAR(20) := 'VIRAT';
BEGIN
CASE name
WHEN 'ABD' THEN DBMS_OUTPUT.PUT_LINE('Your team is RCB');
WHEN 'DHONI' THEN DBMS_OUTPUT.PUT_LINE('Your team is CSK');
WHEN 'VIRAT' THEN DBMS_OUTPUT.PUT_LINE('Your team is RCB');
WHEN 'ROHIT' THEN DBMS_OUTPUT.PUT_LINE('Your team is MI');
ELSE DBMS_OUTPUT.PUT_LINE('You are not playing this year');
END CASE;
END;
In the above example, the selector names. There are various players however, we are interested in such a case where the name is 'VIRAT'. The CASE statement begins with the keyword CASE, followed by the name of the selector. In the lines that follow are the cases (or situations). Each such case is compared with the selector value. If they match, the statement for that case is executed. In the above example, in the third case, when the name is 'VIRAT', it is the same as the selector value "name." Therefore in the output, it will reflect as "Your team is RCB'. It is not necessary to include the ELSE case at the end; however, it is used in case none of the cases match the selector value.
The searched CASE statement does not use a selector. Rather it contains an expression defined with a when clause, which when turns out to be true, and then the corresponding statement is executed. In the simple CASE statement, we use the selector, but in the searched CASE statement, the selector is not used. In place of a selector, we have a condition.
Syntax:
CASE
WHEN condition_1 THEN execute the statements_1
WHEN condition_2 THEN execute the statements_2
...
WHEN condition_n THEN execute the statements_n
ELSE execute the else_statements
END CASE;
Example:
DECLARE
name VARCHAR(20) := 'VIRAT';
BEGIN
CASE
WHEN name= 'ABD' THEN DBMS_OUTPUT.PUT_LINE('Your team is RCB');
WHEN name= 'DHONI' THEN DBMS_OUTPUT.PUT_LINE('Your team is CSK');
WHEN name= 'VIRAT' THEN DBMS_OUTPUT.PUT_LINE('Your team is RCB');
WHEN name= 'ROHIT' THEN DBMS_OUTPUT.PUT_LINE('Your team is MI');
ELSE DBMS_OUTPUT.PUT_LINE('You are not playing this year');
END CASE;
END;
In the above example, we do not use the name selector. Whenever the condition name='VIRAT' evaluates to true, then the corresponding output is displayed. Once the condition matches, the control skips the other remaining conditions. In case none of the conditions matches, a statement in the ELSE block is displayed.