Control structures in Oracle PL/SQL let you control the flow of your program: making decisions, looping through data, and exiting loops. These are essential for writing flexible, efficient, and readable code. Below, we’ll explore the main types of control structures:
- Conditional Statements –
IF-THEN
,IF-THEN-ELSE
,CASE
- Looping Constructs –
LOOP
,WHILE
,FOR
,CONTINUE
- EXIT and EXIT WHEN statements
1. Conditional Statements
A. IF-THEN
Use IF-THEN
when you want code to execute only if a condition is true. If it’s false, PL/SQL simply skips the block.
Syntax:
IF condition THEN
statements;
END IF;
Example: Check if a salary exceeds a threshold.
DECLARE
v_salary NUMBER := 60000;
BEGIN
IF v_salary > 50000 THEN
DBMS_OUTPUT.PUT_LINE('High salary: ' || v_salary);
END IF;
END;
/
B. IF-THEN-ELSE
Adds an alternative block if the condition is false.
Syntax:
IF condition THEN
statements_if_true;
ELSE
statements_if_false;
END IF;
Example: Categorizing employees.
DECLARE
v_experience NUMBER := 3;
BEGIN
IF v_experience >= 5 THEN
DBMS_OUTPUT.PUT_LINE('Senior employee');
ELSE
DBMS_OUTPUT.PUT_LINE('Junior employee');
END IF;
END;
/
C. CASE Statement
Use CASE
for multiple conditions. It’s cleaner than chaining multiple IF-ELSE blocks.
Syntax (searched CASE):
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_default
END;
Example: Grade evaluation.
DECLARE
v_score NUMBER := 85;
v_grade VARCHAR2(2);
BEGIN
v_grade := CASE
WHEN v_score >= 90 THEN 'A'
WHEN v_score >= 80 THEN 'B'
WHEN v_score >= 70 THEN 'C'
ELSE 'F'
END;
DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
END;
/
2. Looping Constructs
Loops execute a block of code multiple times until a condition is met. Oracle provides several types of loops.
A. Basic LOOP
The simplest form. You must explicitly EXIT
or it runs infinitely.
Syntax:
LOOP
statements;
EXIT WHEN condition;
END LOOP;
Example: Print numbers from 1 to 5.
DECLARE
i NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Value: ' || i);
i := i + 1;
EXIT WHEN i > 5;
END LOOP;
END;
/
B. WHILE Loop
Checks the condition before each iteration.
Syntax:
WHILE condition LOOP
statements;
END LOOP;
Example: Countdown.
DECLARE
counter NUMBER := 5;
BEGIN
WHILE counter > 0 LOOP
DBMS_OUTPUT.PUT_LINE('Countdown: ' || counter);
counter := counter - 1;
END LOOP;
END;
/
C. FOR Loop
Automatically handles the loop counter and increments. You can loop forward or backward using REVERSE
.
Syntax:
FOR counter IN [REVERSE] lower_bound..upper_bound LOOP
statements;
END LOOP;
Example: Print even numbers between 2 and 10.
BEGIN
FOR i IN 2..10 LOOP
IF MOD(i,2)=0 THEN
DBMS_OUTPUT.PUT_LINE('Even: ' || i);
END IF;
END LOOP;
END;
/
Reverse Example:
BEGIN
FOR i IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Reverse: ' || i);
END LOOP;
END;
/
D. CONTINUE Statement
Skips the current loop iteration and moves to the next one (available in Oracle 11g+).
Example: Skip odd numbers.
BEGIN
FOR i IN 1..6 LOOP
CONTINUE WHEN MOD(i,2) = 1;
DBMS_OUTPUT.PUT_LINE('Even number: ' || i);
END LOOP;
END;
/
3. EXIT and EXIT WHEN Statements
EXIT
Exits the loop immediately.
Example: Stop processing early.
DECLARE
v_num NUMBER := 0;
BEGIN
LOOP
v_num := v_num + 1;
DBMS_OUTPUT.PUT_LINE('Number: ' || v_num);
IF v_num = 3 THEN
EXIT; -- Leave the loop at 3
END IF;
END LOOP;
END;
/
EXIT WHEN
Combines EXIT
with a condition in one line—cleaner syntax.
Example: Exit after reaching 5.
DECLARE
v_counter NUMBER := 0;
BEGIN
LOOP
v_counter := v_counter + 1;
DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
EXIT WHEN v_counter >= 5;
END LOOP;
END;
/
Best Practices
- Use CASE instead of multiple
IF-THEN-ELSE
for readability. - Use FOR loops when you know the range in advance.
- Use EXIT WHEN to simplify loop exit conditions.
- Always include an EXIT condition in
LOOP
to avoid infinite loops. - Use CONTINUE to skip unnecessary iterations and improve performance.
Summary Table
Control Structure | When to Use | Example Use Case |
---|---|---|
IF-THEN | Simple, single condition | Check if salary > 50000 |
IF-THEN-ELSE | Binary choice | Categorize employee levels |
CASE | Multiple conditions | Grade evaluation |
LOOP | Custom loops, flexible exit | Print values with dynamic limits |
WHILE | Pre-check condition before looping | Countdown timer |
FOR | Fixed range looping | Process rows from 1 to 100 |
CONTINUE | Skip iteration without exiting | Skip odd numbers |
EXIT / EXIT WHEN | Break out of loops | Stop processing early |
Final Takeaway
Oracle control structures are the decision-making and repetition backbone of PL/SQL. They make your programs smarter—able to choose paths (IF
, CASE
), repeat tasks (LOOP
, WHILE
, FOR
), and terminate loops safely (EXIT
, EXIT WHEN
). By combining these tools, you can handle everything from payroll calculations to automated data migrations with clean, efficient code.