PL/SQL provides control statements to control the flow of execution and loops to execute a block of statements repeatedly. In this blog, we will cover IF statements, nested conditions, and looping mechanisms with examples.
1. IF THEN Statement
The IF THEN statement allows execution of a block only if a condition is true.
Syntax
IF (condition) THEN
statement;
END IF;
Example
DECLARE
no INTEGER(2) := 14;
BEGIN
IF (no = 14) THEN
DBMS_OUTPUT.PUT_LINE('Condition true');
END IF;
END;
/
Result
Condition true
PL/SQL procedure successfully completed.
2. IF THEN ELSE Statement
The IF THEN ELSE statement allows execution of one block if the condition is true, otherwise another block is executed.
Syntax
IF (condition) THEN
statement;
ELSE
statement;
END IF;
Example
DECLARE
no INTEGER(2) := 14;
BEGIN
IF (no = 11) THEN
DBMS_OUTPUT.PUT_LINE(no || ' is same');
ELSE
DBMS_OUTPUT.PUT_LINE(no || ' is not same');
END IF;
END;
/
Result
14 is not same
PL/SQL procedure successfully completed.
3. IF THEN ELSIF Statement
The IF THEN ELSIF statement allows multiple conditions to be checked sequentially.
Syntax
IF (condition-1) THEN
statement-1;
ELSIF (condition-2) THEN
statement-2;
ELSIF (condition-3) THEN
statement-3;
ELSE
statement;
END IF;
Example: (Student grade example)
DECLARE
result CHAR(20) := 'second';
BEGIN
IF (result = 'distinction') THEN
DBMS_OUTPUT.PUT_LINE('First Class with Distinction');
ELSIF (result = 'first') THEN
DBMS_OUTPUT.PUT_LINE('First Class');
ELSIF (result = 'second') THEN
DBMS_OUTPUT.PUT_LINE('Second Class');
ELSIF (result = 'third') THEN
DBMS_OUTPUT.PUT_LINE('Third Class');
ELSE
DBMS_OUTPUT.PUT_LINE('Fail');
END IF;
END;
/
Result
Second Class
PL/SQL procedure successfully completed.
4. Nested IF THEN ELSE Statement
Nested IF THEN ELSE statements are logically similar to IF THEN ELSIF. Each condition is checked inside the else block.
Syntax
IF (condition-1) THEN
statement-1;
ELSE
IF (condition-2) THEN
statement-2;
ELSE
IF (condition-3) THEN
statement-3;
END IF;
END IF;
END IF;
Example: (Gender and grade check)
DECLARE
gender CHAR(20) := 'female';
result CHAR(20) := 'second';
BEGIN
IF (gender = 'male') THEN
DBMS_OUTPUT.PUT_LINE('Gender Male Record Skip!');
ELSE
IF (result = 'distinction') THEN
DBMS_OUTPUT.PUT_LINE('First Class with Distinction');
ELSIF (result = 'first') THEN
DBMS_OUTPUT.PUT_LINE('First Class');
ELSIF (result = 'second') THEN
DBMS_OUTPUT.PUT_LINE('Second Class');
ELSIF (result = 'third') THEN
DBMS_OUTPUT.PUT_LINE('Third Class');
ELSE
DBMS_OUTPUT.PUT_LINE('Fail');
END IF;
END IF;
END;
/
Result
Second Class
PL/SQL procedure successfully completed.
Note: Once a condition evaluates TRUE, the corresponding block executes and the rest are skipped.
PL/SQL Loops
Loops in PL/SQL allow you to repeat a block of statements. There are three types of loops:
Basic LOOP
WHILE LOOP
FOR LOOP
Oracle recommends using labels for loops to improve readability (optional).
1. Basic LOOP
Syntax
[ label_name ] LOOP
statements;
END LOOP [ label_name ];
Example
DECLARE
no NUMBER := 5;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Inside value: no = ' || no);
no := no - 1;
IF no = 0 THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Outside loop end');
END;
/
Result
Inside value: no = 5
Inside value: no = 4
Inside value: no = 3
Inside value: no = 2
Inside value: no = 1
Outside loop end
PL/SQL procedure successfully completed.
2. WHILE LOOP
Syntax
[ label_name ] WHILE condition LOOP
statements;
END LOOP [ label_name ];
Example
DECLARE
no NUMBER := 0;
BEGIN
WHILE no < 10 LOOP
no := no + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum :' || no);
END;
/
Result
Sum : 10
PL/SQL procedure successfully completed.
3. FOR LOOP
Syntax
[ label_name ] FOR current_value IN [ REVERSE ] lower_value..upper_value LOOP
statements;
END LOOP [ label_name ];
Example
BEGIN
FOR no IN 1 .. 5 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration : ' || no);
END LOOP;
END;
/
Result
Iteration : 1
Iteration : 2
Iteration : 3
Iteration : 4
Iteration : 5
PL/SQL procedure successfully completed.
✅ Conclusion
IF statements help control flow based on conditions.
Nested IF and ELSIF statements allow multiple checks.
Loops (BASIC, WHILE, FOR) help repeat statements efficiently.
Labels are optional but improve readability.
This forms the foundation of control flow and looping in Oracle PL/SQL.