Loops, Iteration, and Reusable Database Logic in MySQL – Detailed Notes

Course: MySQL Training Course

Topic: Loops, Iteration, and Reusable Database Logic

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Loops, Iteration, and Reusable Database Logic

MySQL provides programming constructs that allow developers to perform repetitive operations, control the flow of execution, and organize database logic into reusable routines. These features are especially useful when working with stored procedures, stored functions, cursors, and database automation tasks.

A loop repeatedly executes a block of statements while a specified condition remains true or until a particular condition is met. Iteration refers to repeating a set of operations, usually by updating variables, processing records, or moving through a collection of results.

Reusable database logic means writing SQL operations once and making them available whenever required. Stored procedures and functions are common ways to reuse logic within MySQL. Instead of repeating the same SQL statements in multiple places, developers can create a routine and call it whenever necessary.

For example, a business application may need to generate invoice numbers, calculate totals, process a batch of records, or update a series of account balances. Loops can handle repetitive steps, while reusable routines help organize the process into maintainable units.

1.1 Key Concepts

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand loops and iteration in MySQL stored programs.
• Explain the purpose of the LOOP, WHILE, and REPEAT statements.
• Use loop-control statements such as LEAVE and ITERATE.
• Declare and update local variables inside stored routines.
• Create reusable stored procedures and functions.
• Understand how cursors can process query results row by row.
• Combine loops with conditional statements to solve database problems.
• Recognize infinite loops and apply safe termination conditions.
• Choose set-based SQL operations when they are more efficient than loops.
• Develop and test a practical database routine that performs repetitive operations.

3. Understanding Loops in MySQL

A loop is a control-flow structure that executes a block of statements multiple times. In MySQL, loop statements are mainly used within stored programs, such as stored procedures, stored functions, and triggers, where the applicable syntax and restrictions permit them.

Unlike a simple SELECT statement, which retrieves data in a single query operation, a loop can repeat calculations, execute conditional logic, update variables, or perform a sequence of database operations.

3.1 Why Are Loops Needed?

• To repeat calculations for a range of values.
• To process records when row-by-row procedural logic is required.
• To execute a series of operations until a condition is satisfied.
• To perform repeated validation or transformation tasks.
• To implement algorithms that require multiple steps.
• To control the execution of reusable database routines.

3.2 Example of a Repetitive Task

Suppose a report needs to display numbers from 1 to 5. Without a loop, each number could be written as a separate query or value. A loop can generate the sequence by updating a counter.

DELIMITER //

CREATE PROCEDURE DisplayNumbers()
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= 5 DO
        SELECT counter AS NumberValue;
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

CALL DisplayNumbers();

This example uses a local variable named counter. The loop displays its value and increases it by one until the counter becomes greater than 5.

Note: This procedure produces a separate result set for each SELECT execution. For a single result set containing a sequence, a recursive common table expression or another set-oriented approach may be more suitable, depending on the MySQL version and task.

4. Types of Loops in MySQL

MySQL stored programs support three principal loop constructs: LOOP, WHILE, and REPEAT. Each construct has a different way of controlling repetition.

5. The LOOP Statement

The LOOP statement repeats its body continuously until control is transferred out of the loop, usually through a LEAVE statement. The loop itself does not have a built-in condition expression.

5.1 Syntax

[loop_label:] LOOP
    -- Statements to repeat
    IF exit_condition THEN
        LEAVE loop_label;
    END IF;
END LOOP [loop_label];

A loop label is an optional identifier that names the loop. Using labels makes it possible to specify exactly which labeled loop should be exited or continued.

5.2 Example: Display Numbers from 1 to 5

DELIMITER //

CREATE PROCEDURE LoopExample()
BEGIN
    DECLARE counter INT DEFAULT 1;

    number_loop: LOOP
        SELECT counter AS NumberValue;

        SET counter = counter + 1;

        IF counter > 5 THEN
            LEAVE number_loop;
        END IF;
    END LOOP number_loop;
END //

DELIMITER ;

CALL LoopExample();

Explanation:

1. The variable counter starts at 1.
2. The labeled loop begins.
3. The current counter value is selected.
4. The counter increases by one.
5. The IF statement checks whether the counter is greater than 5.
6. LEAVE number_loop exits the loop when the condition becomes true.

5.3 When to Use LOOP

Use LOOP when the exit condition is complex or when the loop needs multiple possible exit paths. Because it does not automatically check a condition, the developer must ensure that a valid exit path exists.

6. The WHILE Statement

The WHILE statement repeats a block of statements as long as its condition evaluates to true. MySQL checks the condition before every iteration, so the body might execute zero times if the condition is false initially.

6.1 Syntax

[loop_label:] WHILE condition DO
    -- Statements to repeat
END WHILE [loop_label];

6.2 Example: Calculate the Sum of Numbers from 1 to 10

DELIMITER //

CREATE PROCEDURE CalculateSum()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;

    WHILE counter <= 10 DO
        SET total = total + counter;
        SET counter = counter + 1;
    END WHILE;

    SELECT total AS SumResult;
END //

DELIMITER ;

CALL CalculateSum();

Expected result: 55.

How it works: The counter starts at 1 and the total starts at 0. On each iteration, the counter is added to the total, and then the counter increases by one. When the counter becomes 11, the condition is false and the loop stops.

6.3 WHILE Loop Execution Table

6.4 When to Use WHILE

• When the loop should execute only while a condition is true.
• When a counter or variable controls the number of repetitions.
• When the number of iterations depends on a condition evaluated before each pass.
• When zero iterations are acceptable if the initial condition is false.

7. The REPEAT Statement

The REPEAT statement executes a block of statements and then checks the termination condition. It continues until the condition becomes true. Because the condition is evaluated after the body, the loop executes at least once.

7.1 Syntax

[loop_label:] REPEAT
    -- Statements to repeat
UNTIL termination_condition
END REPEAT [loop_label];

7.2 Example: Display Numbers from 1 to 5

DELIMITER //

CREATE PROCEDURE RepeatExample()
BEGIN
    DECLARE counter INT DEFAULT 1;

    REPEAT
        SELECT counter AS NumberValue;
        SET counter = counter + 1;
    UNTIL counter > 5
    END REPEAT;
END //

DELIMITER ;

CALL RepeatExample();

In this example, the loop displays the counter and increments it. When the counter becomes 6, the UNTIL condition becomes true and the loop ends.

7.3 Example: Validate a Counter Before Exiting

DELIMITER //

CREATE PROCEDURE RepeatCounter()
BEGIN
    DECLARE counter INT DEFAULT 1;

    REPEAT
        SET counter = counter + 1;
    UNTIL counter >= 3
    END REPEAT;

    SELECT counter AS FinalValue;
END //

DELIMITER ;

CALL RepeatCounter();

Expected result: 3.

7.4 When to Use REPEAT

• When the body must run at least once.
• When the exit condition is naturally checked after processing.
• When a task must perform an operation before determining whether to continue.

8. Comparison of LOOP, WHILE, and REPEAT

9. Local Variables in Loops

Local variables store temporary values while a stored routine is executing. They are commonly used as counters, accumulators, flags, or intermediate calculation results in loops.

9.1 Declaring Local Variables

DECLARE variable_name data_type [DEFAULT value];

In MySQL stored programs, local variable declarations must appear at the beginning of the relevant BEGIN ... END block, before executable statements and after any applicable declarations such as cursors and handlers in their required order.

9.2 Example: Counter and Accumulator

DELIMITER //

CREATE PROCEDURE VariableLoopExample()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;

    WHILE counter <= 5 DO
        SET total = total + counter;
        SET counter = counter + 1;
    END WHILE;

    SELECT total AS TotalValue;
END //

DELIMITER ;

The counter variable tracks the current iteration, while total stores the running sum. Both variables exist only within the scope of the stored routine block in which they are declared.

9.3 Common Variable Roles

10. Loop Control with LEAVE

The LEAVE statement exits a labeled loop or a labeled compound statement. It is commonly used when a termination condition is met before the loop would otherwise finish.

10.1 Syntax

LEAVE label_name;

10.2 Example: Stop at a Specific Number

DELIMITER //

CREATE PROCEDURE StopAtFive()
BEGIN
    DECLARE counter INT DEFAULT 1;

    number_loop: LOOP
        IF counter > 5 THEN
            LEAVE number_loop;
        END IF;

        SELECT counter AS NumberValue;
        SET counter = counter + 1;
    END LOOP number_loop;
END //

DELIMITER ;

CALL StopAtFive();

The procedure exits as soon as the counter exceeds 5. The placement of the condition determines whether the boundary value is processed before the loop ends.

10.3 Important Points

• The label referenced by LEAVE must be valid in the current routine scope.
• The label identifies the loop or compound block to exit.
• Every loop should have a clear termination strategy.
• When using nested loops, select the label that corresponds to the intended exit target.

11. Loop Control with ITERATE

The ITERATE statement skips the remaining statements in the current iteration and starts the next iteration of the specified labeled loop. It is similar to the continue statement in many programming languages.

11.1 Syntax

ITERATE label_name;

11.2 Example: Skip Even Numbers

DELIMITER //

CREATE PROCEDURE DisplayOddNumbers()
BEGIN
    DECLARE counter INT DEFAULT 1;

    number_loop: LOOP
        IF counter > 10 THEN
            LEAVE number_loop;
        END IF;

        IF MOD(counter, 2) = 0 THEN
            SET counter = counter + 1;
            ITERATE number_loop;
        END IF;

        SELECT counter AS OddNumber;
        SET counter = counter + 1;
    END LOOP number_loop;
END //

DELIMITER ;

CALL DisplayOddNumbers();

This procedure displays odd numbers from 1 to 9. When the counter is even, it is incremented before ITERATE transfers control to the next iteration. Updating the counter before continuing is important to avoid an infinite loop.

11.3 Difference Between LEAVE and ITERATE

12. Nested Loops in MySQL

A nested loop is a loop placed inside another loop. Nested loops are useful when a task involves two dimensions of repetition, such as processing rows and columns, generating combinations, or performing operations across multiple groups.

12.1 Example: Generate a Multiplication Table

DELIMITER //

CREATE PROCEDURE MultiplicationTable()
BEGIN
    DECLARE outer_counter INT DEFAULT 1;
    DECLARE inner_counter INT;

    WHILE outer_counter <= 3 DO
        SET inner_counter = 1;

        WHILE inner_counter <= 5 DO
            SELECT
                outer_counter AS NumberValue,
                inner_counter AS Multiplier,
                outer_counter * inner_counter AS Product;

            SET inner_counter = inner_counter + 1;
        END WHILE;

        SET outer_counter = outer_counter + 1;
    END WHILE;
END //

DELIMITER ;

CALL MultiplicationTable();

The outer loop selects the number being multiplied, and the inner loop iterates through the multipliers from 1 to 5. The example returns multiple result sets because each iteration executes a separate SELECT.

12.2 Nested Loop Best Practices

• Use meaningful labels when nested loops require LEAVE or ITERATE.
• Initialize inner-loop variables every time the outer loop starts if they need to restart.
• Verify that each loop has a valid exit condition.
• Avoid deeply nested loops when a set-based SQL query can solve the problem more efficiently.

13. Cursors and Row-by-Row Iteration

A cursor allows a stored program to process the rows returned by a query one at a time. Cursors are useful when each row requires procedural logic that cannot be expressed conveniently as a single set-based SQL operation.

Cursor processing usually involves declaring the cursor, opening it, fetching rows into variables, checking for the end of the result set, processing each row, and closing the cursor.

13.1 Main Cursor Operations

13.2 Cursor Declaration Order

Within a MySQL stored-program block, declarations must be placed before executable statements. Variables and named conditions are declared first, followed by cursors, followed by handlers. The exact declarations needed depend on the routine.

13.3 Example: Process Employee Names

Assume an Employees table contains the columns EmployeeID and EmployeeName.

DELIMITER //

CREATE PROCEDURE ListEmployeeNames()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE employee_name VARCHAR(100);

    DECLARE employee_cursor CURSOR FOR
        SELECT EmployeeName
        FROM Employees
        ORDER BY EmployeeID;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET finished = 1;

    OPEN employee_cursor;

    employee_loop: LOOP
        FETCH employee_cursor INTO employee_name;

        IF finished = 1 THEN
            LEAVE employee_loop;
        END IF;

        SELECT employee_name AS EmployeeName;
    END LOOP employee_loop;

    CLOSE employee_cursor;
END //

DELIMITER ;

CALL ListEmployeeNames();

How it works:

1. The cursor is declared for a query that returns employee names.
2. The handler sets finished to 1 when no more rows are available.
3. The cursor is opened.
4. The loop fetches one employee name per iteration.
5. When the result set ends, the handler changes the flag and the loop exits.
6. The cursor is closed after processing.

Important: This example displays each employee name as a separate result set. For ordinary reporting, a single query such as SELECT EmployeeName FROM Employees ORDER BY EmployeeID; is simpler and more efficient. Cursors are most appropriate when row-by-row processing is genuinely needed.

14. Reusable Database Logic with Stored Procedures

A stored procedure is a named database routine that contains one or more SQL statements and can be invoked using the CALL statement. Procedures can accept input parameters, return values through output parameters, modify data, and implement procedural logic.

14.1 Benefits of Stored Procedures

• Reusability: A routine can be called from multiple applications or database sessions.
• Maintainability: Business logic can be kept in a defined database object rather than duplicated across queries.
• Consistency: Centralized logic reduces the chance that different callers implement the same rule differently.
• Access control: Appropriate routine privileges can allow users to perform approved operations without granting unrestricted access to underlying tables.
• Reduced application duplication: Common database operations can be encapsulated in routines.

14.2 Creating a Reusable Procedure

DELIMITER //

CREATE PROCEDURE GetEmployeeCount()
BEGIN
    SELECT COUNT(*) AS TotalEmployees
    FROM Employees;
END //

DELIMITER ;

Call the procedure whenever the employee count is needed:

CALL GetEmployeeCount();

14.3 Procedure With an Input Parameter

DELIMITER //

CREATE PROCEDURE GetEmployeesByDepartment(IN p_department_id INT)
BEGIN
    SELECT EmployeeID, EmployeeName, DepartmentID
    FROM Employees
    WHERE DepartmentID = p_department_id;
END //

DELIMITER ;

CALL GetEmployeesByDepartment(2);

This routine accepts a department ID and returns employees associated with that department. It assumes that the Employees table has the specified columns.

15. Reusable Database Logic with Stored Functions

A stored function is a named routine that returns a value and can be used in expressions where permitted. Functions are useful for reusable calculations and value transformations. They differ from procedures in their invocation and return behavior.

15.1 Example: Calculate a Discount

DELIMITER //

CREATE FUNCTION CalculateDiscount(
    p_amount DECIMAL(10,2),
    p_rate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN p_amount * p_rate / 100;
END //

DELIMITER ;

Call the function in a query:

SELECT CalculateDiscount(1000.00, 10.00) AS DiscountAmount;

Expected result: 100.00.

15.2 Stored Procedure vs Stored Function

16. Combining Loops with Conditional Statements

Loops can be combined with IF statements and CASE expressions to make decisions during each iteration. This allows a routine to apply different actions depending on the current value or database condition.

16.1 Example: Categorize Numbers

DELIMITER //

CREATE PROCEDURE CategorizeNumbers()
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= 5 DO
        IF MOD(counter, 2) = 0 THEN
            SELECT counter AS NumberValue, 'Even' AS Category;
        ELSE
            SELECT counter AS NumberValue, 'Odd' AS Category;
        END IF;

        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

CALL CategorizeNumbers();

This procedure checks whether each number is even or odd and returns the corresponding category. It demonstrates conditional processing inside a loop.

16.2 Practical Uses

• Checking whether an invoice has exceeded a threshold.
• Classifying values into business categories.
• Skipping records that do not satisfy a rule.
• Ending processing when a limit is reached.
• Applying different calculations to different types of records.

17. Iteration and Database Table Processing

Iteration can be used to process records in a table, but a loop is not automatically the best approach for every data-processing task. SQL is designed to operate on sets of rows, so many updates, calculations, and filters are more efficient when written as a single SQL statement.

17.1 Example: Prefer a Set-Based Update

Suppose a table named Products has a Price column and all prices must increase by 5%.

UPDATE Products
SET Price = Price * 1.05;

This statement updates all applicable rows without requiring a procedural loop.

17.2 When Row-by-Row Processing May Be Appropriate

• Each row requires a distinct multi-step workflow.
• Processing depends on a previous row's result.
• Complex procedural validation cannot be conveniently expressed in a set-based query.
• A cursor is required to call a sequence of operations for individual records.

17.3 Important Performance Principle

Prefer a single set-based statement whenever it correctly expresses the requirement. Row-by-row loops and cursors can introduce additional processing overhead, especially when many records are involved.

18. Avoiding Infinite Loops

An infinite loop occurs when a loop continues without reaching its intended termination condition. This often happens when a counter is not updated, the exit condition is incorrect, or a condition can never become true.

18.1 Example of a Problematic Loop

WHILE counter <= 5 DO
    SELECT counter;
    -- Missing counter increment
END WHILE;

If counter starts at 1 and never changes, the condition remains true indefinitely.

18.2 Corrected Example

WHILE counter <= 5 DO
    SELECT counter;
    SET counter = counter + 1;
END WHILE;

18.3 Prevention Checklist

• Initialize counters and flags explicitly.
• Update variables that control loop conditions.
• Check that the termination condition can eventually become true.
• Use LEAVE when a loop requires an explicit exit path.
• Test with small inputs before processing large datasets.
• Avoid executing long-running loops against production data without appropriate safeguards.

19. Error Handling in Loops

Stored programs can use condition handlers to respond to certain database conditions. Handlers are especially useful with cursors, where the NOT FOUND condition commonly indicates that no more rows are available to fetch.

19.1 Cursor End-of-Data Handling

DECLARE finished INT DEFAULT 0;

DECLARE employee_cursor CURSOR FOR
    SELECT EmployeeID FROM Employees;

DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET finished = 1;

The handler sets a flag when a fetch reaches the end of the cursor result set. The loop can check this flag and exit cleanly.

19.2 General Recommendations

• Handle expected conditions explicitly.
• Close cursors after processing.
• Check for invalid values before performing calculations.
• Use transactions where appropriate for multi-step data changes.
• Design routines so that failures do not leave data in an inconsistent state.
• Review handler scope and declaration order when debugging stored programs.

20. Transactions and Reusable Database Workflows

A transaction groups related data changes into a unit of work. When a loop performs multiple modifications, transaction design can help maintain consistency, but transaction boundaries must be selected carefully.

20.1 Basic Transaction Example

START TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

COMMIT;

This example illustrates a two-step transfer pattern. A production implementation should also validate account existence and balances, handle errors, and roll back the transaction when a required operation fails. The storage engine and transaction configuration must support the intended behavior.

20.2 Transactions Inside Loops

When processing multiple records, committing after every row may increase overhead, while holding one transaction open for too long can lock resources and affect concurrency. The appropriate approach depends on data volume, consistency requirements, locking behavior, and recovery strategy.

21. Practical Project: Generate a Number Series and Reuse the Logic

Project Objective: Create a stored procedure that calculates a series of values and displays the final total. This project combines variables, a loop, conditional logic, and reusable database logic.

Step 1: Select a Database

CREATE DATABASE IF NOT EXISTS mysql_iteration_project;
USE mysql_iteration_project;

Step 2: Create the Procedure

DELIMITER //

CREATE PROCEDURE SumNumberSeries(IN p_limit INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE total BIGINT DEFAULT 0;

    IF p_limit < 1 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Limit must be greater than zero';
    END IF;

    WHILE counter <= p_limit DO
        SET total = total + counter;
        SET counter = counter + 1;
    END WHILE;

    SELECT
        p_limit AS RequestedLimit,
        total AS SeriesTotal;
END //

DELIMITER ;

Step 3: Execute the Procedure

CALL SumNumberSeries(10);

Expected result: The total is 55.

Step 4: Test a Different Input

CALL SumNumberSeries(100);

Expected result: The total is 5050.

Step 5: Test Invalid Input

CALL SumNumberSeries(0);

The procedure raises a custom error because the input must be greater than zero. The example demonstrates input validation as well as loop processing.

Project Learning Outcomes

• Create a reusable stored procedure with an input parameter.
• Use a local counter and accumulator variable.
• Repeat operations with a WHILE loop.
• Validate input before beginning the loop.
• Test the routine using different input values.

22. Common Mistakes and Troubleshooting

23. Best Practices for Loops and Reusable Database Logic

• Use the simplest loop construct that matches the problem.
• Prefer set-based SQL for bulk filtering, updates, and calculations whenever possible.
• Give stored procedures and variables meaningful names.
• Initialize counters, flags, and accumulator variables explicitly.
• Ensure every loop has a reliable termination condition.
• Use labels carefully with nested loops.
• Use LEAVE for exiting and ITERATE for skipping to the next iteration.
• Keep reusable logic focused on a clear business responsibility.
• Validate routine input parameters before making changes.
• Test boundary values, empty result sets, and invalid input.
• Use cursors only when row-by-row processing is justified.
• Use appropriate transactions and error handling for multi-step data modifications.
• Document assumptions about table structure and required privileges.
• Monitor performance when routines process large amounts of data.

24. Practice Exercises with Answers

Exercise 1: Print Numbers from 1 to 10

Write a procedure that uses a WHILE loop to display numbers from 1 to 10.

Answer:

DELIMITER //

CREATE PROCEDURE PrintTenNumbers()
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= 10 DO
        SELECT counter AS NumberValue;
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

CALL PrintTenNumbers();

Exercise 2: Calculate the Factorial of a Number

Write a procedure that calculates the factorial of a non-negative integer. For this example, use a value small enough that the result fits within the chosen data type.

Answer:

DELIMITER //

CREATE PROCEDURE CalculateFactorial(IN p_number INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result_value DECIMAL(30,0) DEFAULT 1;

    IF p_number < 0 OR p_number > 25 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Enter a number from 0 to 25';
    END IF;

    WHILE counter <= p_number DO
        SET result_value = result_value * counter;
        SET counter = counter + 1;
    END WHILE;

    SELECT result_value AS FactorialResult;
END //

DELIMITER ;

CALL CalculateFactorial(5);

Expected result: 120. For an input of 0, the procedure returns 1 because the loop does not execute and the initial result remains 1.

Exercise 3: Display Only Multiples of Three

Write a loop that displays multiples of 3 between 1 and 15.

Answer:

DELIMITER //

CREATE PROCEDURE MultiplesOfThree()
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= 15 DO
        IF MOD(counter, 3) = 0 THEN
            SELECT counter AS MultipleOfThree;
        END IF;

        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

CALL MultiplesOfThree();

Exercise 4: Compare WHILE and REPEAT

Explain the difference between WHILE and REPEAT.

Answer: WHILE checks its condition before executing the body and may execute zero times. REPEAT checks its termination condition after executing the body and therefore executes at least once.

Exercise 5: Choose Between a Loop and a Single UPDATE

A table contains thousands of product records, and every product price must increase by 5%. Which approach is generally preferable?

Answer: A single set-based statement such as UPDATE Products SET Price = Price * 1.05; is generally preferable because it avoids unnecessary row-by-row procedural processing.

25. Interview Questions and Answers

Q1. What is a loop in MySQL?

Answer: A loop is a control-flow construct that repeatedly executes statements in a stored program until its termination rules are satisfied.

Q2. What are the main types of loops in MySQL?

Answer: The three main loop constructs are LOOP, WHILE, and REPEAT.

Q3. What is the difference between LOOP and WHILE?

Answer: LOOP repeats until control explicitly exits the loop, usually with LEAVE. WHILE automatically checks a condition before each iteration.

Q4. Why is REPEAT called a post-condition loop?

Answer: Because its termination condition is checked after the body executes, so the body runs at least once.

Q5. What is the purpose of LEAVE?

Answer: LEAVE exits the specified labeled loop or compound statement.

Q6. What is the purpose of ITERATE?

Answer: ITERATE skips the remaining statements in the current iteration and starts the next iteration of the specified labeled loop.

Q7. What is an infinite loop?

Answer: An infinite loop continues without reaching its intended exit condition, often because a control variable is not updated or the condition is incorrect.

Q8. What is a stored procedure?

Answer: A stored procedure is a named database routine containing SQL and procedural statements that can be executed with a CALL statement.

Q9. What is the difference between a procedure and a function?

Answer: A procedure is usually invoked with CALL and can return result sets or output parameters. A function returns a value and can be used in expressions where permitted.

Q10. What is a cursor?

Answer: A cursor is a mechanism in stored programs for retrieving and processing the rows of a query result one at a time.

Q11. Why is a NOT FOUND handler used with a cursor?

Answer: It handles the condition that occurs when a cursor fetch has no more rows to return, allowing the loop to terminate.

Q12. Why are set-based operations often better than loops?

Answer: SQL engines are designed to process sets of rows efficiently. A single set-based operation often requires less procedural overhead than processing each row individually.

Q13. Can loops be used inside stored procedures?

Answer: Yes. MySQL stored procedures support loop constructs such as LOOP, WHILE, and REPEAT.

Q14. What is a nested loop?

Answer: A nested loop is a loop inside another loop, typically used when a task requires repetition across multiple dimensions or groups.

Q15. How can you prevent an infinite loop?

Answer: Initialize variables correctly, update loop-control variables, verify that exit conditions can become true, and test the routine with small inputs.

26. Quick Revision Checklist

• Understand the role of loops in MySQL stored programs.
• Remember that LOOP requires explicit exit logic.
• Remember that WHILE checks its condition before each iteration.
• Remember that REPEAT checks its termination condition after each iteration.
• Use LEAVE to exit a labeled loop.
• Use ITERATE to continue with the next iteration.
• Declare local variables before executable statements in their block.
• Use cursors for row-by-row processing only when necessary.
• Create stored procedures to centralize reusable database operations.
• Use functions for reusable calculations that return values.
• Prefer set-based SQL for bulk operations when possible.
• Test boundary conditions and guard against infinite loops.
• Use appropriate error handling and transaction management for data changes.

27. Conclusion

Loops, iteration, and reusable database logic are important parts of procedural programming in MySQL. The LOOP, WHILE, and REPEAT constructs provide different approaches to repetition, while LEAVE and ITERATE provide additional control over execution.

Local variables, conditional statements, cursors, stored procedures, and stored functions can be combined to create organized and reusable database workflows. However, loops should be used thoughtfully: for many data updates and calculations, set-based SQL is simpler and more efficient than row-by-row processing.

By practicing loop syntax, variable management, termination conditions, error handling, and routine creation, learners can build reliable MySQL programs that are easier to test, maintain, and reuse.

To continue learning database programming and related SQL concepts, visit the JustAcademy MySQL Training Course or register for a course demo.