PL/SQL IF Statement
PL/SQL provides robust support for conditional statements like IF-THEN,
IF-THEN-ELSE, IF-THEN-ELSIF, and IF-THEN-ELSIF-ELSE. These constructs allow
you to make decisions in your code based on specific conditions. Let's
explore the syntax and usage:
Example: Using Student table
-- Create a sample STUDENTS table using sql commands
CREATE TABLE STUDENTS (
STUDENT_ID NUMBER PRIMARY KEY,
STUDENT_NAME VARCHAR2(50),
MARKS NUMBER
);
-- Insert some sample data
INSERT INTO STUDENTS VALUES (1, 'Alice Smith', 85);
INSERT INTO STUDENTS VALUES (2, 'Bob Johnson', 65);
INSERT INTO STUDENTS VALUES (3, 'Charlie Brown', 95);
COMMIT;
1. IF-THEN Statement:
Use the IF-THEN statement when you want to execute statements only when a
condition is TRUE.
Syntax:
IF condition
THEN
-- Statements to execute when the condition is
TRUE
END IF;
PL/SQL program with IF-THEN statement
DECLARE
student_marks NUMBER;
student_id NUMBER := 2; -- Change this to the desired
student ID
BEGIN
-- Retrieve the marks of the student with the specified
ID
SELECT MARKS INTO student_marks FROM STUDENTS WHERE
STUDENT_ID = student_id;
-- Check the marks using IF-THEN statement
IF student_marks >= 80 THEN
dbms_output.put_line('Student with ID ' ||
student_id || ' has excellent marks.');
END IF;
END;
Output:
Student with ID 2 has excellent marks.
2. IF-THEN-ELSE Statement:
The IF-THEN-ELSE statement lets you execute one set of statements when a
condition is TRUE and another set when it's FALSE.
Syntax:
IF condition
THEN
-- Statements to execute when the condition is
TRUE
ELSE
-- Statements to execute when the condition is
FALSE
END IF;
PL/SQL program with IF-THEN-ELSE statement
DECLARE
student_marks NUMBER;
student_id NUMBER := 2; -- Change this to the desired
student ID
BEGIN
-- Retrieve the marks of the student with the specified
ID
SELECT MARKS INTO student_marks FROM STUDENTS WHERE
STUDENT_ID = student_id;
-- Check the marks using IF-THEN-ELSE statement
IF student_marks >= 80 THEN
dbms_output.put_line('Student with ID ' ||
student_id || ' has excellent marks.');
ELSE
dbms_output.put_line('Student with ID ' ||
student_id || ' has average marks.');
END IF;
END;
/
Output:
Student with ID 2 has average marks.
3. IF-THEN-ELSIF Statement:
Use the IF-THEN-ELSIF statement when you want to execute different
sets of statements based on multiple conditions.
Syntax:
IF condition1
THEN
-- Statements to execute when condition1 is
TRUE
ELSIF condition2
THEN
-- Statements to execute when condition2 is
TRUE
END IF;
PL/SQL program with IF-THEN-ELSIF statement
DECLARE
student_marks NUMBER;
student_id NUMBER := 3; -- Change this to the desired
student ID
BEGIN
-- Retrieve the marks of the student with the
specified ID
SELECT MARKS INTO student_marks FROM STUDENTS WHERE
STUDENT_ID = student_id;
-- Check the marks using IF-THEN-ELSIF statement
IF student_marks >= 90 THEN
dbms_output.put_line('Student with ID ' ||
student_id || ' has outstanding marks.');
ELSIF student_marks >= 80 THEN
dbms_output.put_line('Student with ID ' ||
student_id || ' has excellent marks.');
ELSIF student_marks >= 70 THEN
dbms_output.put_line('Student with ID ' ||
student_id || ' has good marks.');
ELSE
dbms_output.put_line('Student with ID ' ||
student_id || ' has average marks.');
END IF;
END;
/
Output:
Student with ID 3 has outstanding marks.
4. IF-THEN-ELSIF-ELSE Statement:
The IF-THEN-ELSIF-ELSE statement is suitable when you have
multiple conditions, and you want to handle each scenario
differently.
Syntax:
IF condition1
THEN
-- Statements to execute when condition1 is
TRUE
ELSIF condition2
THEN
-- Statements to execute when condition2 is
TRUE
ELSE
-- Statements to execute when both conditions are
FALSE
END IF;
PL/SQL program with IF-THEN-ELSIF-ELSE statement
DECLARE
student_marks NUMBER;
student_id NUMBER := 1; -- Change this to the
desired student ID
BEGIN
-- Retrieve the marks of the student with the
specified ID
SELECT MARKS INTO student_marks FROM STUDENTS
WHERE STUDENT_ID = student_id;
-- Check the marks using IF-THEN-ELSIF-ELSE
statement
IF student_marks >= 90 THEN
dbms_output.put_line('Student with ID '
|| student_id || ' has outstanding marks.');
ELSIF student_marks >= 80 THEN
dbms_output.put_line('Student with ID '
|| student_id || ' has excellent marks.');
ELSIF student_marks >= 70 THEN
dbms_output.put_line('Student with ID '
|| student_id || ' has good marks.');
ELSE
dbms_output.put_line('Student with ID '
|| student_id || ' has average marks.');
END IF;
END;
/
Output:
Student with ID 1 has excellent marks.
More topic in PL/SQL
