Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

• Lecture:
  • Flow Control Statements (LOOP, WHILE, and REPEAT)
  • Using Flow Control Statements in Stored Procedures/Functions
  • Conditional Statements (IF, IF-ELSE-THEN, SWITCH CASE)
  • Example of Each Type of Statement
• Lab:
  • Use of Flow Control Statements in Stored Procedures
  • Use of Conditional Statements in Stored Procedures

Lecture Notes (In-Depth)

1. Flow Control Statements (LOOP, WHILE, and REPEAT)

MySQL stored procedures support flow control statements to manage the execution flow, enabling loops and iterative processing.

• LOOP:

  • A basic looping construct that continues until explicitly terminated with LEAVE or RETURN.

  • Syntax:

    [label]: LOOP
        -- Statements
        IF condition THEN
            LEAVE [label];
        END IF;
    END LOOP [label];
    
    

  • Example: Iterate to insert numbers 1 to 5 into a table.

    CREATE PROCEDURE InsertNumbers()
    BEGIN
        DECLARE counter INT DEFAULT 1;
        my_loop: LOOP
            INSERT INTO numbers (value) VALUES (counter);
            SET counter = counter + 1;
            IF counter > 5 THEN
                LEAVE my_loop;
            END IF;
        END LOOP my_loop;
    END;
    
    

  • Key Notes:

    • Requires a LEAVE statement to avoid infinite loops.
    • Labels are optional but useful for nested loops.
• WHILE:

  • Executes a block of statements as long as a condition is true, checked before each iteration.

  • Syntax:

    WHILE condition DO
        -- Statements
    END WHILE;
    
    

  • Example: Insert even numbers up to 10.

    CREATE PROCEDURE InsertEvenNumbers()
    BEGIN
        DECLARE num INT DEFAULT 2;
        WHILE num <= 10 DO
            INSERT INTO numbers (value) VALUES (num);
            SET num = num + 2;
        END WHILE;
    END;
    
    

  • Key Notes:

    • Condition is evaluated before the loop starts.
    • Suitable when the number of iterations depends on a condition.
• REPEAT:

  • Executes statements until a condition becomes true, checked after each iteration (at least one iteration is guaranteed).

  • Syntax:

    REPEAT
        -- Statements
    UNTIL condition
    END REPEAT;
    
    

  • Example: Insert odd numbers up to 9.

    CREATE PROCEDURE InsertOddNumbers()
    BEGIN
        DECLARE num INT DEFAULT 1;
        REPEAT
            INSERT INTO numbers (value) VALUES (num);
            SET num = num + 2;
        UNTIL num > 9
        END REPEAT;
    END;
    
    

  • Key Notes:

    • Ensures at least one execution, unlike WHILE.
    • Condition is checked at the end of the loop.

2. Using Flow Control Statements in Stored Procedures/Functions

• Stored Procedures:
  • Flow control statements are used to iterate over data, perform repetitive tasks, or process records dynamically.
  • Example: Update employee salaries in a loop based on department.
• Functions:
  • Functions can also use flow control, but they return a single value and cannot modify database state (e.g., no INSERT/UPDATE).
  • Example: Calculate a cumulative sum using a WHILE loop.
• Key Notes:
  • Flow control is often combined with cursors (covered in Session 12) to process rows individually.
  • Use variables to track state within loops (e.g., counters, accumulators).

3. Conditional Statements (IF, IF-ELSE-THEN, SWITCH CASE)

Conditional statements control the execution path based on conditions.

• IF:

  • Executes a block of code if a condition is true.

  • Syntax:

    IF condition THEN
        -- Statements
    END IF;
    
    

  • Example: Increase salary if above a threshold.

    IF salary > 50000 THEN
        SET salary = salary * 1.1;
    END IF;
    
    

• IF-ELSE-THEN:

  • Executes one block if the condition is true, another if false.

  • Syntax:

    IF condition THEN
        -- Statements for true
    ELSE
        -- Statements for false
    END IF;
    
    

  • Example: Adjust salary based on department.

    IF dept_id = 1 THEN
        SET salary = salary * 1.1;
    ELSE
        SET salary = salary * 1.05;
    END IF;
    
    

• SWITCH CASE:

  • Evaluates a value and executes the corresponding block.

  • Syntax:

    CASE expression
        WHEN value1 THEN
            -- Statements
        WHEN value2 THEN
            -- Statements
        ELSE
            -- Default statements
    END CASE;
    
    

  • Alternative Syntax (condition-based):

    CASE
        WHEN condition1 THEN
            -- Statements
        WHEN condition2 THEN
            -- Statements
        ELSE
            -- Default statements
    END CASE;
    
    

  • Example: Assign bonus based on department.

    CASE dept_id
        WHEN 1 THEN SET bonus = 1000;
        WHEN 2 THEN SET bonus = 500;
        ELSE SET bonus = 200;
    END CASE;