EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PL/SQL else if

Home » Data Science » Data Science Tutorials » Database Management Tutorial » PL/SQL else if

PL_SQL else if

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.
Note: You can extend the else if statements up to any number of times as you want as per the need of the program. For each of the else if conditions a different block of code should be specified containing different statements to be executed when its corresponding condition in else if evaluates to true.

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:

PL/SQL else if 1

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;

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,882 ratings)
Course Price

View Course

Related Courses
PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

After running and compiling you can see it in your DB as shown the below output.

Output:

PL/SQL else if 2

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:

PLSQL else if 3

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:

The sales of the branch are going great!

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 –

  1. Triggers in PL/SQL
  2. PL/SQL Collections
  3. PL/SQL Data Types
  4. Loops in PL/SQL

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Database Management Tutorial
  • PL/SQL
    • What is PL/SQL?
    • Careers in PL/SQL
    • PLSQL procedure
    • PL/SQL Exception
    • PL/SQL LIKE
    • PL/SQL Raise Exception
    • PLSQL rowtype
    • PLSQL? bind variables
    • PL/SQL Record
    • PL/SQL WITH
    • PL/SQL bulk collect
    • PL/SQL Block Structure
    • PL/SQL else if
    • PL/SQL nvl2
    • PL/SQL Package
    • PL/SQL exists
    • PL/SQL instr
    • PL/SQL listagg
    • PL/ SQL Formatter
    • PLSQLlength
    • PL/SQL Commands
    • PL/SQL Data Types
    • CASE statement in PL/SQL
    • PL/SQL IF Statement
    • Loops in PL/SQL
    • PL/SQL Add Column
    • For Loop in PLSQL
    • PL/SQL Cursor Loop
    • PLSQL Array
    • Cursors in PL/SQL
    • PL/SQL FOR Loop Cursor
    • PL/SQL Queries
    • PL/SQL SELECT INTO
    • PL/SQL TO_CHAR
    • PL/SQL UNION
    • PL/SQL NOT EQUAL
    • PL/SQL varray
    • PL/SQL Concatenate
    • PL/SQL UPDATE
    • PL/SQL TRIM
    • PL/SQL GROUP BY
    • PL/SQL GOTO
    • PL/SQL Date Functions
    • PL/ SQL having
    • PL/SQL to_DATE
    • PL/SQL NVL
    • PLSQL format date
    • PLSQL mod
    • PLSQL round
    • PL/SQL Boolean
    • PL/SQL exit
    • PL/SQL DECODE
    • PL/SQL ROWNUM
    • PLSQL?pivot
    • PLSQL string functions
    • PL/SQL Block
    • PL/SQL Function
    • PL/SQL Unwrapper
    • PL/SQL Table
    • PL/SQL ALTER TABLE
    • PLSQL execute immediate
    • Triggers in PL/SQL
    • PL/SQL Collections
    • PL/SQL stored procedure
    • PL/SQL Anonymous Block
    • PLSQL Interview Questions
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Data Administrator
    • Database Administrator
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • What is DB2?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Checkpoint in DBMS
    • Teradata Create Table
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • MariaDB
    • MariaDB Versions
    • MariaDB?list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB?boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL
  • SQLite
    • What is SQLite
    • SQLite Commands
    • SQLite Data Types
    • SQLite COUNT
    • SQLite Boolean
    • SQLite autoincrement
    • SQLite select
    • SQLite? Bulk Insert
    • SQLite? add column
    • SQLite? concat
    • SQLite BETWEEN
    • SQLite group by
    • SQLite CASE
    • SQLite group_concat
    • SQLite array
    • SQLite? enum
    • SQLite sum
    • SQLite create table
    • SQLite Alter Table
    • SQLite Create Database
    • SQLite Delete
    • SQLite connection string
    • SQLite Database
    • SQLite Describe Table
    • SQLite Show Tables
    • SQLite exit
    • SQLite create index
    • SQLite foreign key
    • SQLite Stored Procedures
    • SQLite Extension
  • DB2
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
    • View Serializability in DBMS
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • Dataset Normalization
    • MariaDB Max Connections
    • jdbc connection
    • MariaDB GUI
  • DBMS
    • Introduction To DBMS
    • DBMS ER Diagram
    • What is DBMS?
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • Netezza Database
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - SQL Certification Course Learn More