Control flow dictates the order in which statements are executed within a PL/SQL program. Understanding and effectively utilizing control flow constructs is fundamental for writing efficient, logical, and maintainable Oracle PL/SQL code. This section covers various techniques for managing the flow of execution, from conditional branching to iterative processing.

Conditional Logic

Conditional logic allows your PL/SQL programs to make decisions and execute different blocks of code based on whether specific conditions are met. This is a cornerstone of dynamic and responsive applications.

IF-THEN-ELSE Statements

The IF-THEN-ELSE statement is the most common and basic form of conditional control in Oracle PL/SQL. It allows you to execute a sequence of statements only if a specified condition is true. You can also provide alternative actions if the condition is false. This is essential for implementing business rules and data validation.

Note: Oracle PL/SQL IF statement, conditional branching, THEN ELSE, ELSIF, PL/SQL decision making

Example 1: Basic IF-THEN Statement

DECLARE
    v_score NUMBER := 85; -- Declare and initialize a variable for a student's score
BEGIN
    -- Check if the score is greater than or equal to 70 for a passing grade
    IF v_score >= 70 THEN
        DBMS_OUTPUT.PUT_LINE('Congratulations! You passed the exam.'); -- Output message if condition is true
    END IF;
END;
/

Explanation

This example demonstrates a simple IF-THEN statement. It declares a numeric variable v_score and initializes it. The IF v_score >= 70 THEN condition checks if the score is 70 or higher. If this condition evaluates to TRUE, the message "Congratulations! You passed the exam." is displayed using DBMS_OUTPUT.PUT_LINE. If the condition is FALSE, the code within the IF block is skipped.

Example 2: IF-THEN-ELSE Statement

DECLARE
    v_age NUMBER := 17; -- Declare and initialize a variable for a person's age
BEGIN
    -- Check if the age is 18 or older to determine eligibility
    IF v_age >= 18 THEN
        DBMS_OUTPUT.PUT_LINE('You are old enough to vote.'); -- Message for eligible voters
    ELSE
        DBMS_OUTPUT.PUT_LINE('You are not yet old enough to vote.'); -- Message for ineligible voters
    END IF;
END;
/

Explanation

This example showcases an IF-THEN-ELSE statement. It checks if v_age is 18 or greater. If the condition is TRUE, the message "You are old enough to vote." is printed. If the condition is FALSE (meaning v_age is less than 18), the code within the ELSE block is executed, printing "You are not yet old enough to vote." This allows for two distinct execution paths based on the condition.

Example 3: IF-ELSIF-ELSE Statement for Multiple Conditions

DECLARE
    v_grade_percentage NUMBER := 92; -- Student's percentage grade
    v_letter_grade VARCHAR2(1); -- Variable to store the letter grade
BEGIN
    -- Determine the letter grade based on percentage
    IF v_grade_percentage >= 90 THEN
        v_letter_grade := 'A'; -- Assign 'A' for 90% and above
    ELSIF v_grade_percentage >= 80 THEN
        v_letter_grade := 'B'; -- Assign 'B' for 80-89%
    ELSIF v_grade_percentage >= 70 THEN
        v_letter_grade := 'C'; -- Assign 'C' for 70-79%
    ELSIF v_grade_percentage >= 60 THEN
        v_letter_grade := 'D'; -- Assign 'D' for 60-69%
    ELSE
        v_letter_grade := 'F'; -- Assign 'F' for below 60%
    END IF;

    DBMS_OUTPUT.PUT_LINE('Your letter grade is: ' || v_letter_grade); -- Display the calculated letter grade
END;
/

Explanation

This example demonstrates the use of ELSIF for handling multiple conditions. The code sequentially checks the v_grade_percentage against various thresholds. As soon as a condition evaluates to TRUE, the corresponding THEN block is executed, v_letter_grade is assigned, and the rest of the ELSIF and ELSE conditions are skipped. If none of the IF or ELSIF conditions are met, the ELSE block is executed, assigning 'F'. This is ideal for scenarios with several possible outcomes.

Example 4: Nested IF Statements

DECLARE
    v_is_senior BOOLEAN := TRUE; -- Flag for senior citizen status
    v_has_discount_card BOOLEAN := TRUE; -- Flag for discount card
BEGIN
    -- Check for senior discount eligibility
    IF v_is_senior THEN
        DBMS_OUTPUT.PUT_LINE('Eligible for senior discount.');
        -- Further check for additional discount if they also have a discount card
        IF v_has_discount_card THEN
            DBMS_OUTPUT.PUT_LINE('Also eligible for an additional discount with discount card.');
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Not eligible for senior discount.');
    END IF;
END;
/

Explanation

This example illustrates nested IF statements, where one IF statement is contained within another. The outer IF checks for v_is_senior. If true, it prints a message and then proceeds to the inner IF to check v_has_discount_card. This allows for more granular decision-making based on a hierarchy of conditions.

Example 5: IF with NULL Values

DECLARE
    v_value_a NUMBER := 10;
    v_value_b NUMBER := NULL; -- A variable with a NULL value
BEGIN
    -- Check conditions involving NULL
    IF v_value_a IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('v_value_a is not NULL.');
    END IF;

    IF v_value_b IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('v_value_b is NULL.');
    END IF;

    -- Important: NULL comparisons result in UNKNOWN, not TRUE or FALSE
    IF v_value_a = v_value_b THEN -- This condition will be UNKNOWN
        DBMS_OUTPUT.PUT_LINE('This line will not be executed (v_value_a = v_value_b is UNKNOWN).');
    END IF;

    IF v_value_a != v_value_b THEN -- This condition will also be UNKNOWN
        DBMS_OUTPUT.PUT_LINE('This line will also not be executed (v_value_a != v_value_b is UNKNOWN).');
    END IF;
END;
/

Explanation

This example demonstrates how IF statements interact with NULL values. It's crucial to use IS NULL or IS NOT NULL to check for the presence or absence of a value, as direct comparisons (= or !=) involving NULL always result in UNKNOWN, preventing the IF condition from evaluating to TRUE. The example highlights that UNKNOWN results in the IF block being skipped.

CASE Statements and Expressions

The CASE statement and CASE expression provide a more structured and readable way to handle multiple conditional branches, especially when you are testing a single value against several possible options. They are often a cleaner alternative to complex IF-ELSIF-ELSE constructs.

Note: Oracle PL/SQL CASE statement, CASE expression, simple CASE, searched CASE, multi-way branching, SQL CASE

Example 1: Simple CASE Statement

DECLARE
    v_day_number NUMBER := 3; -- Represents a day of the week (1=Monday, 2=Tuesday, etc.)
    v_day_name VARCHAR2(10); -- To store the corresponding day name
BEGIN
    -- Determine the day name based on the day number
    CASE v_day_number
        WHEN 1 THEN
            v_day_name := 'Monday';
        WHEN 2 THEN
            v_day_name := 'Tuesday';
        WHEN 3 THEN
            v_day_name := 'Wednesday';
        WHEN 4 THEN
            v_day_name := 'Thursday';
        WHEN 5 THEN
            v_day_name := 'Friday';
        WHEN 6 THEN
            v_day_name := 'Saturday';
        WHEN 7 THEN
            v_day_name := 'Sunday';
        ELSE
            v_day_name := 'Invalid Day'; -- Handle cases where v_day_number is not 1-7
    END CASE;
    DBMS_OUTPUT.PUT_LINE('Today is: ' || v_day_name); -- Display the determined day name
END;
/

Explanation

This example uses a simple CASE statement to map a numeric v_day_number to its corresponding v_day_name. The CASE statement evaluates v_day_number and executes the WHEN clause whose value matches v_day_number. If no WHEN clause matches, the ELSE clause is executed. This provides a clean way to handle a fixed set of possibilities.

Example 2: Searched CASE Statement

DECLARE
    v_temperature NUMBER := 25; -- Current temperature in Celsius
    v_weather_outlook VARCHAR2(20); -- Description of the weather
BEGIN
    -- Determine weather outlook based on temperature ranges
    CASE
        WHEN v_temperature < 0 THEN
            v_weather_outlook := 'Freezing Cold';
        WHEN v_temperature BETWEEN 0 AND 10 THEN
            v_weather_outlook := 'Very Cold';
        WHEN v_temperature BETWEEN 11 AND 20 THEN
            v_weather_outlook := 'Cool';
        WHEN v_temperature BETWEEN 21 AND 30 THEN
            v_weather_outlook := 'Pleasant';
        WHEN v_temperature > 30 THEN
            v_weather_outlook := 'Hot';
        ELSE
            v_weather_outlook := 'Unknown Temperature';
    END CASE;
    DBMS_OUTPUT.PUT_LINE('Weather outlook: ' || v_weather_outlook);
END;
/

Explanation

This example demonstrates a searched CASE statement, which is more flexible than the simple CASE. Instead of comparing a single expression, each WHEN clause has its own boolean condition. The first WHEN clause whose condition evaluates to TRUE is executed. This is particularly useful for evaluating different ranges or complex conditions.

Example 3: CASE Expression (within a SELECT statement)

SELECT
    employee_id,
    first_name,
    salary,
    CASE
        WHEN salary < 5000 THEN 'Low Salary'
        WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium Salary'
        WHEN salary > 10000 THEN 'High Salary'
        ELSE 'Undefined'
    END AS salary_category -- The CASE expression becomes a new column
FROM
    employees
WHERE
    ROWNUM <= 5; -- Limiting for example purposes

Explanation

This example shows a CASE expression used within a SELECT statement in SQL (which is often used within PL/SQL blocks when querying data). A CASE expression returns a single value based on the conditions. Here, it creates a new column salary_category that categorizes each employee's salary into "Low Salary", "Medium Salary", or "High Salary". This is incredibly powerful for data categorization and reporting.

Example 4: CASE Statement with NULL Handling

DECLARE
    v_user_status VARCHAR2(10) := NULL; -- User status can be 'ACTIVE', 'INACTIVE', or NULL
    v_message VARCHAR2(50);
BEGIN
    -- Handle user status, including NULL
    CASE v_user_status
        WHEN 'ACTIVE' THEN
            v_message := 'User is currently active.';
        WHEN 'INACTIVE' THEN
            v_message := 'User is currently inactive.';
        WHEN NULL THEN -- Explicitly handle NULL for simple CASE
            v_message := 'User status is unknown.';
        ELSE
            v_message := 'Invalid user status encountered.';
    END CASE;
    DBMS_OUTPUT.PUT_LINE(v_message);

    v_user_status := 'ACTIVE'; -- Change status for another test
    CASE v_user_status
        WHEN 'ACTIVE' THEN
            v_message := 'User is currently active.';
        WHEN 'INACTIVE' THEN
            v_message := 'User is currently inactive.';
        WHEN NULL THEN
            v_message := 'User status is unknown.';
        ELSE
            v_message := 'Invalid user status encountered.';
    END CASE;
    DBMS_OUTPUT.PUT_LINE(v_message);

    v_user_status := 'SUSPENDED'; -- Test an 'ELSE' case
    CASE v_user_status
        WHEN 'ACTIVE' THEN
            v_message := 'User is currently active.';
        WHEN 'INACTIVE' THEN
            v_message := 'User is currently inactive.';
        WHEN NULL THEN
            v_message := 'User status is unknown.';
        ELSE
            v_message := 'Invalid user status encountered.';
    END CASE;
    DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

Explanation

This example highlights how to handle NULL values within a simple CASE statement. Unlike IF statements where NULL comparisons result in UNKNOWN, a WHEN NULL clause in a simple CASE will explicitly match if the expression being evaluated is NULL. This provides clear handling for missing or undefined data points.

Example 5: CASE Statement with No ELSE Clause (and potential NO_DATA_FOUND)

DECLARE
    v_product_category VARCHAR2(20) := 'Electronics';
    v_delivery_time VARCHAR2(20);
BEGIN
    -- If no WHEN clause matches and there's no ELSE, a CASE_NOT_FOUND exception is raised
    BEGIN
        CASE v_product_category
            WHEN 'Books' THEN
                v_delivery_time := '3-5 business days';
            WHEN 'Food' THEN
                v_delivery_time := '1-2 business days';
            WHEN 'Apparel' THEN
                v_delivery_time := '5-7 business days';
            -- No ELSE clause intentionally to demonstrate exception
        END CASE;
        DBMS_OUTPUT.PUT_LINE('Delivery time for ' || v_product_category || ': ' || v_delivery_time);
    EXCEPTION
        WHEN CASE_NOT_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Error: Product category "' || v_product_category || '" not found in delivery logic.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
    END;

    v_product_category := 'Food'; -- Test a matching case
    BEGIN
        CASE v_product_category
            WHEN 'Books' THEN
                v_delivery_time := '3-5 business days';
            WHEN 'Food' THEN
                v_delivery_time := '1-2 business days';
            WHEN 'Apparel' THEN
                v_delivery_time := '5-7 business days';
        END CASE;
        DBMS_OUTPUT.PUT_LINE('Delivery time for ' || v_product_category || ': ' || v_delivery_time);
    EXCEPTION
        WHEN CASE_NOT_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Error: Product category "' || v_product_category || '" not found in delivery logic.');
    END;
END;
/

Explanation

This example demonstrates the behavior of a CASE statement when no ELSE clause is provided and none of the WHEN conditions match. In such a scenario, Oracle PL/SQL raises a CASE_NOT_FOUND exception. It's crucial to either include an ELSE clause to handle all possible values or implement exception handling (EXCEPTION WHEN CASE_NOT_FOUND) to gracefully manage unmatched cases and prevent runtime errors.

Loops

Loops are fundamental control structures that allow a block of code to be executed repeatedly. This is essential for iterating over collections, processing sets of data, or repeating actions until a certain condition is met. Oracle PL/SQL provides several types of loops to cater to different iteration requirements.

Note: Oracle PL/SQL loops, iterative processing, Basic LOOP, WHILE LOOP, FOR LOOP, REVERSE FOR LOOP, loop control, EXIT, CONTINUE

Basic Loop

The LOOP...END LOOP construct is the simplest form of loop in Oracle PL/SQL. It creates an indefinite loop that will execute indefinitely unless an EXIT statement is encountered. This makes it suitable for situations where the exit condition is determined dynamically within the loop's body.

Note: PL/SQL infinite loop, EXIT statement, LOOP END LOOP, basic loop structure

Example 1: Basic Loop with EXIT WHEN

DECLARE
    v_counter NUMBER := 1; -- Initialize a counter variable
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting basic loop...');
    LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter); -- Display the current iteration
        v_counter := v_counter + 1; -- Increment the counter
        EXIT WHEN v_counter > 5; -- Exit the loop when counter exceeds 5
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Basic loop finished.');
END;
/

Explanation

This example demonstrates a basic LOOP with an EXIT WHEN clause. The loop continuously increments v_counter and prints its value. The EXIT WHEN v_counter > 5 statement acts as the termination condition; when v_counter becomes 6, the loop is exited, and execution continues after END LOOP.

Example 2: Basic Loop with Conditional EXIT

DECLARE
    v_balance NUMBER := 100; -- Initial balance
    v_withdrawal_amount NUMBER := 20; -- Amount to withdraw in each iteration
BEGIN
    DBMS_OUTPUT.PUT_LINE('Initial Balance: $' || v_balance);
    LOOP
        IF v_balance < v_withdrawal_amount THEN
            DBMS_OUTPUT.PUT_LINE('Insufficient funds to withdraw $' || v_withdrawal_amount || '.');
            EXIT; -- Exit if balance is too low
        END IF;

        v_balance := v_balance - v_withdrawal_amount; -- Deduct withdrawal amount
        DBMS_OUTPUT.PUT_LINE('Withdrew $' || v_withdrawal_amount || '. Remaining Balance: $' || v_balance);
        EXIT WHEN v_balance <= 0; -- Exit if balance drops to 0 or below
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Final Balance: $' || v_balance);
END;
/

Explanation

This example shows a basic LOOP with an IF statement inside to check for an exit condition. It simulates withdrawals. The loop exits if v_balance falls below v_withdrawal_amount (due to IF v_balance < v_withdrawal_amount THEN EXIT;) or if the v_balance drops to zero or less (due to EXIT WHEN v_balance <= 0). This demonstrates flexible exit conditions.

Example 3: Basic Loop with Nested Loop and Label

DECLARE
    i NUMBER := 1;
    j NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting nested loops:');
    <<outer_loop>> -- Label for the outer loop
    LOOP
        DBMS_OUTPUT.PUT_LINE('  Outer Loop Iteration: ' || i);
        j := 1;
        <<inner_loop>> -- Label for the inner loop
        LOOP
            DBMS_OUTPUT.PUT_LINE('    Inner Loop Iteration: ' || j);
            j := j + 1;
            EXIT inner_loop WHEN j > 3; -- Exit the inner loop
        END LOOP inner_loop; -- Referencing the inner loop label explicitly
        i := i + 1;
        EXIT outer_loop WHEN i > 2; -- Exit the outer loop
    END LOOP outer_loop; -- Referencing the outer loop label explicitly
    DBMS_OUTPUT.PUT_LINE('Nested loops finished.');
END;
/

Explanation

This example demonstrates nested basic loops and the use of loop labels. Labels (e.g., <<outer_loop>>, <<inner_loop>>) are used to uniquely identify loops, especially useful for EXIT statements to specify which loop to exit. EXIT inner_loop WHEN j > 3; exits only the inner loop, while EXIT outer_loop WHEN i > 2; exits the entire outer loop.

Example 4: Basic Loop Processing a Cursor (FETCH loop)

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE ROWNUM <= 3; -- Limiting for example
    v_emp_rec emp_cursor%ROWTYPE; -- Record type to hold fetched row
BEGIN
    DBMS_OUTPUT.PUT_LINE('Fetching employee data:');
    OPEN emp_cursor; -- Open the cursor to fetch data
    LOOP
        FETCH emp_cursor INTO v_emp_rec; -- Fetch one row into the record variable
        EXIT WHEN emp_cursor%NOTFOUND; -- Exit when no more rows are found

        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_rec.employee_id ||
                             ', Name: ' || v_emp_rec.first_name || ' ' || v_emp_rec.last_name);
    END LOOP;
    CLOSE emp_cursor; -- Close the cursor
    DBMS_OUTPUT.PUT_LINE('Finished fetching employee data.');
END;
/

Explanation

This is a common and practical use of a basic loop: iterating over a result set from a cursor. The LOOP continuously FETCHes rows from emp_cursor into v_emp_rec. The EXIT WHEN emp_cursor%NOTFOUND; condition checks if the last FETCH operation failed to retrieve a row, indicating that all rows have been processed, and then exits the loop. This pattern is fundamental for row-by-row processing in PL/SQL.

Example 5: Basic Loop with CONTINUE

DECLARE
    v_number NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Demonstrating CONTINUE:');
    LOOP
        v_number := v_number + 1;
        IF MOD(v_number, 2) = 0 THEN -- Check if the number is even
            DBMS_OUTPUT.PUT_LINE('  Skipping even number: ' || v_number);
            CONTINUE; -- Skip the rest of the current iteration and go to the next
        END IF;

        DBMS_OUTPUT.PUT_LINE('  Processing odd number: ' || v_number);
        EXIT WHEN v_number >= 7; -- Exit when number reaches 7 (after processing)
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Loop with CONTINUE finished.');
END;
/

Explanation

This example illustrates the CONTINUE statement within a basic loop. When v_number is even, CONTINUE is executed. This immediately skips the remaining statements in the current iteration of the loop (i.e., DBMS_OUTPUT.PUT_LINE(' Processing odd number: ' || v_number); is skipped for even numbers) and proceeds to the next iteration. This is useful when you want to bypass certain operations based on a condition within the loop.

WHILE Loop

A WHILE loop in Oracle PL/SQL executes a sequence of statements repeatedly as long as a specified condition remains true. The condition is evaluated at the beginning of each iteration, meaning the loop body might not execute at all if the condition is initially false. This is ideal when the number of iterations is not known in advance but depends on a dynamic condition.

Note: PL/SQL WHILE loop, loop with condition, pre-test loop, iterative processing, condition at start

Example 1: Basic WHILE Loop

DECLARE
    v_counter NUMBER := 1; -- Initialize loop counter
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting WHILE loop...');
    WHILE v_counter <= 5 LOOP -- Loop continues as long as v_counter is less than or equal to 5
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
        v_counter := v_counter + 1; -- Increment the counter
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('WHILE loop finished.');
END;
/

Explanation

This example demonstrates a basic WHILE loop. The loop's execution is controlled by the condition v_counter <= 5. The DBMS_OUTPUT.PUT_LINE statement and v_counter increment only execute if the condition is true. Once v_counter becomes 6, the condition v_counter <= 5 evaluates to FALSE, and the loop terminates.

Example 2: WHILE Loop with User Input (Simulated)

DECLARE
    v_user_input VARCHAR2(10) := 'yes'; -- Simulate initial user input
    v_continue BOOLEAN := TRUE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Simulating user interaction:');
    WHILE v_continue LOOP
        DBMS_OUTPUT.PUT_LINE('Do you want to continue? (yes/no)');
        -- In a real application, you would get input here, e.g., from a user interface or another source.
        -- For this example, we'll manually change v_user_input for demonstration.
        IF v_user_input = 'yes' THEN
            DBMS_OUTPUT.PUT_LINE('Continuing...');
            -- Simulate some processing
            -- For the next iteration, let's change input to 'no' to stop
            v_user_input := 'no'; -- This would be dynamic in a real scenario
        ELSIF v_user_input = 'no' THEN
            v_continue := FALSE; -- Set flag to FALSE to exit loop
            DBMS_OUTPUT.PUT_LINE('Stopping as requested.');
        ELSE
            v_continue := FALSE; -- Set flag to FALSE for invalid input
            DBMS_OUTPUT.PUT_LINE('Invalid input. Stopping.');
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('WHILE loop based on user input finished.');
END;
/

Explanation

This example simulates a WHILE loop that continues based on user input. The WHILE v_continue condition controls the loop. Inside the loop, the v_user_input is checked. If it's 'yes', the loop continues. If 'no' or invalid, the v_continue flag is set to FALSE, causing the loop to terminate in the next iteration. This highlights how WHILE loops are effective when the termination condition is dynamic and changes based on internal logic or external factors.

Example 3: WHILE Loop for Factorial Calculation

DECLARE
    v_number NUMBER := 5; -- Number for which to calculate factorial
    v_factorial NUMBER := 1; -- Initialize factorial result
    v_temp_num NUMBER;
BEGIN
    IF v_number < 0 THEN
        DBMS_OUTPUT.PUT_LINE('Factorial is not defined for negative numbers.');
        RETURN; -- Exit the block
    END IF;

    v_temp_num := v_number; -- Use a temporary variable for the loop

    WHILE v_temp_num > 0 LOOP
        v_factorial := v_factorial * v_temp_num; -- Multiply factorial by current number
        v_temp_num := v_temp_num - 1; -- Decrement number
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_number || ' is: ' || v_factorial);
END;
/

Explanation

This example demonstrates using a WHILE loop to calculate the factorial of a number. The loop continues as long as v_temp_num is greater than 0. In each iteration, v_factorial is updated, and v_temp_num is decremented. This is a classic example of using a WHILE loop where the number of iterations is determined by the initial value and the condition.

Example 4: WHILE Loop with EXIT (similar to basic loop, but emphasizes initial condition)

DECLARE
    v_product_price NUMBER := 10;
    v_total_cost NUMBER := 0;
    v_quantity NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Calculating total cost with WHILE loop:');
    WHILE v_total_cost < 50 LOOP -- Loop as long as total cost is less than 50
        v_quantity := v_quantity + 1;
        v_total_cost := v_total_cost + v_product_price;
        DBMS_OUTPUT.PUT_LINE('Quantity: ' || v_quantity || ', Current Total: $' || v_total_cost);
        -- An internal EXIT can still be used, though less common in pure WHILE loops
        IF v_quantity >= 10 THEN
            DBMS_OUTPUT.PUT_LINE('Maximum quantity reached, exiting.');
            EXIT; -- Force exit even if v_total_cost < 50
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Final Total Cost: $' || v_total_cost || ', Final Quantity: ' || v_quantity);
END;
/

Explanation

While WHILE loops primarily rely on their initial condition, this example shows that an EXIT statement can still be used within a WHILE loop. Here, the loop normally runs as long as v_total_cost is less than 50. However, an additional EXIT condition is introduced: if v_quantity reaches 10, the loop terminates prematurely, demonstrating the flexibility of combining WHILE with EXIT.

Example 5: Infinite WHILE Loop (and how to avoid/handle)

DECLARE
    v_loop_control BOOLEAN := TRUE;
    v_count NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Demonstrating (and stopping) a potential infinite WHILE loop:');
    WHILE v_loop_control LOOP -- Condition is always TRUE initially
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('Looping... Count: ' || v_count);
        IF v_count >= 3 THEN
            DBMS_OUTPUT.PUT_LINE('Setting control to FALSE to exit.');
            v_loop_control := FALSE; -- Essential to change the condition to FALSE
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('WHILE loop successfully terminated.');
END;
/

Explanation

This example demonstrates a common pitfall: an "infinite" WHILE loop if the loop's controlling condition never becomes false. Initially, v_loop_control is TRUE, making the WHILE condition always true. To prevent an actual infinite loop, an IF statement inside the loop explicitly sets v_loop_control to FALSE after v_count reaches 3, ensuring termination. This highlights the importance of ensuring that the WHILE condition will eventually evaluate to FALSE.

FOR Loop (including reverse)

The FOR loop is specifically designed for iterating over a predefined range of integers. It is particularly useful when you know in advance how many times you need to execute a block of code. The FOR loop automatically manages the loop counter, incrementing it with each iteration. It also supports iterating in reverse order.

Note: Oracle PL/SQL FOR loop, integer loop, fixed iterations, REVERSE FOR loop, loop counter, iteration range

Example 1: Basic FOR Loop

BEGIN
    DBMS_OUTPUT.PUT_LINE('Counting up with FOR loop:');
    FOR i IN 1..5 LOOP -- Loop from 1 to 5 (inclusive)
        DBMS_OUTPUT.PUT_LINE('Current value of i: ' || i);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('FOR loop finished.');
END;
/

Explanation

This example demonstrates a basic FOR loop. The loop variable i is implicitly declared as a NUMBER within the loop's scope. It automatically iterates from 1 up to 5, incrementing by 1 in each step. The loop terminates once i exceeds 5. This is the simplest and most common use of a FOR loop when the number of iterations is fixed.

Example 2: FOR Loop with REVERSE

BEGIN
    DBMS_OUTPUT.PUT_LINE('Counting down with REVERSE FOR loop:');
    FOR j IN REVERSE 1..5 LOOP -- Loop from 5 down to 1
        DBMS_OUTPUT.PUT_LINE('Current value of j: ' || j);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('REVERSE FOR loop finished.');
END;
/

Explanation

This example showcases the REVERSE keyword with a FOR loop. Instead of incrementing, the loop variable j now decrements from the upper bound (5) down to the lower bound (1). This is very convenient when you need to process items in reverse order without manually managing the decrementing counter.

Example 3: FOR Loop Iterating Over a Cursor (Implicit Cursor)

DECLARE
    -- No explicit cursor declaration needed for implicit FOR loop cursor
BEGIN
    DBMS_OUTPUT.PUT_LINE('Fetching employee names using implicit FOR loop cursor:');
    -- The FOR loop implicitly declares a record variable for each row fetched
    FOR emp_rec IN (SELECT first_name, last_name FROM employees WHERE ROWNUM <= 3 ORDER BY employee_id) LOOP
        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Implicit FOR loop cursor finished.');
END;
/

Explanation

This is a highly recommended and common way to process data fetched from a database query. The FOR emp_rec IN (SELECT ...) syntax uses an implicit cursor. Oracle automatically opens the cursor, fetches each row into the emp_rec record (which is implicitly declared with the rowtype of the query), and then closes the cursor when all rows are processed or the loop exits. This greatly simplifies cursor management compared to explicit OPEN, FETCH, and CLOSE statements.

Example 4: FOR Loop with Nested Loops

BEGIN
    DBMS_OUTPUT.PUT_LINE('Demonstrating nested FOR loops:');
    FOR i IN 1..3 LOOP -- Outer loop
        DBMS_OUTPUT.PUT_LINE('Outer Iteration (i): ' || i);
        FOR j IN 1..2 LOOP -- Inner loop
            DBMS_OUTPUT.PUT_LINE('  Inner Iteration (j): ' || j);
        END LOOP;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Nested FOR loops finished.');
END;
/

Explanation

This example shows how FOR loops can be nested. The inner loop completes all its iterations for each single iteration of the outer loop. This structure is common for tasks like generating combinations, iterating over matrices, or any scenario where you need to perform operations for every pair of elements from two sets.

Example 5: FOR Loop with CONTINUE and EXIT (less common, but possible)

BEGIN
    DBMS_OUTPUT.PUT_LINE('FOR loop with CONTINUE and EXIT:');
    FOR k IN 1..10 LOOP
        IF k = 3 THEN
            DBMS_OUTPUT.PUT_LINE('  Skipping number ' || k);
            CONTINUE; -- Skip the rest of this iteration for k=3
        END IF;

        DBMS_OUTPUT.PUT_LINE('  Processing number ' || k);

        IF k = 7 THEN
            DBMS_OUTPUT.PUT_LINE('  Reached 7, exiting loop.');
            EXIT; -- Exit the loop entirely when k=7
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('FOR loop with CONTINUE and EXIT finished.');
END;
/

Explanation

While FOR loops are typically meant for fixed iterations, CONTINUE and EXIT can still be used. CONTINUE will skip the remaining statements in the current iteration and proceed to the next value of the loop counter (e.g., skips printing for k=3). EXIT will terminate the loop entirely, regardless of the remaining range (e.g., stops the loop when k reaches 7). While valid, overusing EXIT in a FOR loop can sometimes make the code less readable as it deviates from the loop's defined iteration range.

GOTO Statement (and when to avoid it)

The GOTO statement in Oracle PL/SQL allows for unconditional branching to a specific labeled statement within the same block or subprogram. While it provides a way to jump to different parts of your code, its use is generally discouraged in modern programming practices due to its potential to create "spaghetti code" that is difficult to read, debug, and maintain. Structured control flow statements like IF-THEN-ELSE, CASE, and various loops are almost always preferred.

Note: Oracle PL/SQL GOTO, unconditional jump, code readability, spaghetti code, best practices, structured programming, when to avoid GOTO

Example 1: Basic GOTO to a Label

DECLARE
    v_counter NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting GOTO demonstration:');
    <<start_point>> -- Define a label
    v_counter := v_counter + 1;
    DBMS_OUTPUT.PUT_LINE('Current counter: ' || v_counter);

    IF v_counter < 3 THEN
        GOTO start_point; -- Jump back to the label
    END IF;

    DBMS_OUTPUT.PUT_LINE('GOTO demonstration finished.');
END;
/

Explanation

This example demonstrates a basic GOTO statement. The <<start_point>> defines a label. The GOTO start_point; statement causes the program execution to immediately jump back to the line immediately following the start_point label. This effectively creates a loop-like behavior, but it's less structured and harder to follow than a dedicated loop.

Example 2: GOTO for Error Handling (Less Recommended)

DECLARE
    v_value NUMBER := -5;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Checking value:');
    IF v_value < 0 THEN
        DBMS_OUTPUT.PUT_LINE('Error: Value cannot be negative.');
        GOTO error_handler; -- Jump to error handling section
    END IF;

    DBMS_OUTPUT.PUT_LINE('Processing value: ' || v_value);
    -- More processing logic here

    GOTO end_of_program; -- Jump to the end after successful processing

    <<error_handler>> -- Label for error handling
    DBMS_OUTPUT.PUT_LINE('Performing error cleanup and logging...');
    -- Real error handling logic (e.g., log, rollback)

    <<end_of_program>> -- Label for program exit
    DBMS_OUTPUT.PUT_LINE('Program finished.');
END;
/

Explanation

This example shows a potential (though generally discouraged) use of GOTO for error handling. If v_value is negative, execution jumps directly to the <<error_handler>> label, bypassing normal processing. After successful processing, it jumps to <<end_of_program>>. While this might seem convenient, PL/SQL's EXCEPTION handling block is the correct and structured way to manage errors.

Example 3: GOTO to Exit Nested Loops (Better alternatives exist)

DECLARE
    found_match BOOLEAN := FALSE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Searching for match with GOTO:');
    FOR i IN 1..3 LOOP
        FOR j IN 1..3 LOOP
            DBMS_OUTPUT.PUT_LINE('Checking (' || i || ',' || j || ')');
            IF i = 2 AND j = 2 THEN
                DBMS_OUTPUT.PUT_LINE('Match found at (' || i || ',' || j || ')! Exiting loops with GOTO.');
                found_match := TRUE;
                GOTO end_of_search; -- Jump out of both loops
            END IF;
        END LOOP;
    END LOOP;

    <<end_of_search>> -- Label to jump to
    IF found_match THEN
        DBMS_OUTPUT.PUT_LINE('Search completed due to match.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Search completed, no match found.');
    END IF;
END;
/

Explanation

This example uses GOTO to exit multiple nested loops prematurely once a condition is met. While EXIT can only exit the immediate loop, GOTO can jump completely out of any number of nested loops to a specified label. However, using a flag variable and checking it in outer loops (as demonstrated in Basic Loop Example 3 with labels) or refactoring into a procedure/function with RETURN is a more structured and readable approach for this scenario.

Example 4: GOTO to Skip Code Block (Generally Bad Practice)

DECLARE
    v_process_flag BOOLEAN := FALSE; -- Set to TRUE to process the block
BEGIN
    DBMS_OUTPUT.PUT_LINE('Demonstrating GOTO for skipping:');
    IF NOT v_process_flag THEN
        GOTO skip_block; -- Unconditionally skip to the label
    END IF;

    DBMS_OUTPUT.PUT_LINE('This block will be processed.');
    -- Extensive processing logic here
    DBMS_OUTPUT.PUT_LINE('Block processed.');

    <<skip_block>> -- Label to skip to
    DBMS_OUTPUT.PUT_LINE('Finished GOTO skip demonstration.');
END;
/

Explanation

This example illustrates using GOTO to skip a block of code based on a condition. If v_process_flag is false, the GOTO statement immediately jumps past the "processed" block. This is almost always better achieved with an IF-THEN statement where the "skipped" code is simply placed inside the THEN block (or ELSE block) that is only executed when the condition is met. GOTO in this context makes the flow non-linear and harder to reason about.

Example 5: GOTO Crossing Block Boundaries (Invalid)

-- This example demonstrates an INVALID GOTO usage. It will result in a compilation error.
-- You cannot GOTO into or out of a nested block, function, or procedure.

/*
DECLARE
    v_data NUMBER := 10;
BEGIN
    <<outer_label>>
    DBMS_OUTPUT.PUT_LINE('In outer block.');

    DECLARE -- Start of inner block
        v_inner_data NUMBER := 20;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('In inner block.');
        -- INVALID: Cannot GOTO to a label in an enclosing block
        -- GOTO outer_label;
    END; -- End of inner block

    -- INVALID: Cannot GOTO to a label inside a nested block
    -- GOTO inner_label_here; -- (if inner_label_here existed inside the inner block)

    DBMS_OUTPUT.PUT_LINE('Back in outer block.');
END;
/
*/
-- To make this a valid, compilable example (but still illustrates the point about avoidance):
-- We will show an example of what GOTO is allowed to do,
-- and then comment on the limitations.

DECLARE
    v_count NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Demonstrating GOTO within the same block:');
    <<loop_start>>
    v_count := v_count + 1;
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
    IF v_count < 3 THEN
        GOTO loop_start;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Finished GOTO within same block.');
    -- It is IMPORTANT to remember that GOTO cannot jump into or out of compound statements
    -- like IF statements, loops, or exception handlers, or different subprograms.
    -- It can only jump within the same declarative or executable section of a single block.
END;
/

Explanation

This example (initially commented out to prevent compilation error) is crucial because it highlights a major restriction of GOTO: it cannot transfer control into or out of different PL/SQL blocks, subprograms (functions, procedures), IF statements, or loops. It can only jump to a label within the same executable section of the current block. The working part of the example simply reiterates Example 1 to show valid usage while the comments emphasize the strong limitations, which further underscore why GOTO is rarely the right tool. Structured control flow mechanisms are designed precisely to handle these block transitions cleanly and safely.