Introduction to PL/SQL else if
The following article provides an outline for PL/SQL else if. PL/SQL else if statement is used along with the if clause above it to skip or execute a certain block of code based on certain conditions. Usually, while there is a requirement to check the only one time the condition and then decide the code to execute, we only make the use of the if statement. But when we need to check multiple conditions which are similar in nature with little variation and all of them are dependent then we make the use of else if statements. This else if ladder can be extended up to multiple times as per our need and the conditions that we want to put for executing a different block of code on suffice of a different condition.
Syntax of PL/SQL else if
The syntax of the if statement when used along with else if also referred to as ELSIF in short as a keyword of PL/SQL is as shown below:
IF Boolean expression 1 THEN
block of code 1
ELSIF Boolean expression 2 THEN
block of code 2
[ ELSIF Boolean expression 3 THEN
block of code 3
]
...
[ ELSE
block of code for else
]
END IF;
In the above syntax, the terminologies that we have used are as given below one by one:
- Boolean Expression: Expression or statement that will evaluate a Boolean value. This can be any conditional statement involving a comparison of two values or the logical operators such as AND, OR, and NOT by using which multiple comparisons are combined to return a single Boolean value as a resultant. The Boolean value resulted here becomes the deciding factor for considering whether the further mentioned block of code specified after this condition should be executed or not.
- Block of Code: For each of the corresponding Boolean expressions or conditions, if we want to execute a certain block of code when that condition becomes true then all those statements are included inside the block of code. For each of the individual Boolean expressions, we will specify different statements inside the block of code to be executed.
- Block of Code for else: The use of the ELSE statement itself is optional in nature when using the else if ladder. If you want to specify that if none of the mentioned expressions inside if or elseif statements evaluates to true then you need to execute certain statements, then this should be specified in the block of code for else inside the else statement.
Working of else if Statements
- The flow of execution for the above-mentioned syntax is as given here. If the Boolean expression 1 evaluates to true then the block of code 1 will be executed and the if-else, if ladder flow will stop there itself and control, will be transferred to the line which is immediately after this whole else if blocks specification.
- If the Boolean expression 1 condition evaluates to false, then the flow of execution will go to the ELSIF block and then the Boolean expression 2 will be evaluated thereafter the same process will be repeated for all the else if blocks specified hence further.
- In case, if none of the Boolean expression conditions evaluates to be true for if or else if statements then the block of code specified inside the else statement will be executed.
Examples of PL/SQL else if
Given below are the examples mentioned:
Let us see the implementation of the else if statement in PL/SQL along with the help of certain examples. Let us see about the sample data firstly which we will consider for demonstrating the use of select into a statement. We have one existing table in our PL/SQL database whose name is customer_details.
To check the existing content of the table you can fire the following query statement.
Code:
SELECT * FROM customer_details;
Output:
Example #1
Let us create a function that will calculate the total bill amount for a particular store id that is passed as parameter to it.
This function is created as shown below.
Code:
CREATE OR REPLACE FUNCTION retrieve_total_billAmt(
store_id_value PLS_VARCHAR
)
RETURN NUMBER
IS
total_bill_amt NUMBER := 0;
BEGIN
-- retrieve total bill amount
SELECT SUM(bill_amount)
INTO total_bill_amt
FROM customers_details
GROUP BY store_id
HAVING store_id = store_id_value;
-- return the total bill amount
RETURN total_bill_amt;
END;
4.5 (7,743 ratings)
View Course
After running and compiling you can see it in your DB as shown the below output.
Output:
Now, we will call the function for the electronics store whose total bill amount that will be retrieved will be 39600. If we try to use the below if else if conditions in our program, then study the output.
Code:
BEGIN
IF retrieve_total_billAmt('ELECTRONICS') > 30000 THEN
DBMS_OUTPUT.PUT_LINE('Total Bill Amount for Electronic store is above target');
ELSIF retrieve_total_billAmt('ELECTRONICS') <= 300000 AND retrieve_total_billAmt('ELECTRONICS') > 50000 THEN
DBMS_OUTPUT.PUT_LINE( 'Total Bill Amount for Electronic store is extremely poor! Needs high attention!' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'Total Bill Amount for Electronic store is at lowest! Store in loss! Needs to shutdown!' );
END IF;
END;
Output:
Example #2
Code:
DECLARE
total_sales NUMBER := 650000;
BEGIN
IF total_sales > 500000 THEN
DBMS_OUTPUT.PUT_LINE( 'The sales of the branch are going great!' );
ELSIF total_sales <= 500000 AND total_sales > 300000 THEN
DBMS_OUTPUT.PUT_LINE( 'The sales of the branch is average and needs to improve!' );
ELSIF total_sales <= 300000 AND total_sales > 50000 THEN
DBMS_OUTPUT.PUT_LINE( 'The sales of the branch is exteremely poor! Needs high attention!' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'The sales of the branch are at lowest! Branch in loss! Needs to shutdown!' );
END IF;
END;
Output:
Conclusion
The use of else if condition in PL/SQL program is required when we want to execute or skip a certain part of the code on fulfillment of some condition. If the condition becomes true then the corresponding block is executed else a different else if block is checked.
Recommended Articles
This is a guide to PL/SQL else if. Here we discuss the introduction, working of else if statements, and examples respectively. You may also have a look at the following articles to learn more –
