Control Flow: Variables, Branches & Loops SQL is natively a declarative set-based language. However, inside MySQL Stored Programs (procedures, functions, and triggers), you can write full imperative procedural logic using Variables , Conditional Branches , and Iterative Loops .
1. Declaring and Setting Local Variables Local variables exist only within the BEGIN ... END block where they are declared.
1 -- Syntax: DECLARE var_name datatype [DEFAULT default_val];
2 DECLARE user_count INT DEFAULT 0 ;
3 DECLARE discount_rate DECIMAL ( 4 , 2 ) ;
4
5 -- Assigning values with SET
6 SET discount_rate = 0.15 ;
7
8 -- Assigning values from query with SELECT ... INTO
9 SELECT COUNT ( * ) INTO user_count FROM users WHERE is_active = 1 ;
Rule: All DECLARE statements must appear at the very beginning of the BEGIN ... END block before any operational SQL statements!
2. Conditional Branching: IF ... ELSEIF ... ELSE 1 DELIMITER //
2
3 CREATE PROCEDURE CheckCreditRisk (
4 IN p_credit_score INT ,
5 OUT p_risk_category VARCHAR ( 20 )
6 )
7 BEGIN
8 IF p_credit_score >= 750 THEN
9 SET p_risk_category = 'EXCELLENT' ;
10 ELSEIF p_credit_score >= 650 THEN
11 SET p_risk_category = 'MODERATE' ;
12 ELSEIF p_credit_score >= 500 THEN
13 SET p_risk_category = 'HIGH RISK' ;
14 ELSE
15 SET p_risk_category = 'REJECTED' ;
16 END IF ;
17 END //
18
19 DELIMITER ;
3. Iterative Loops in Stored Procedures MySQL supports three primary loop constructs:
A. The WHILE Loop (Pre-Condition Check) Evaluates the condition before entering the loop body:
1 DECLARE counter INT DEFAULT 1 ;
2
3 WHILE counter <= 5 DO
4 INSERT INTO batch_log ( run_step ) VALUES ( counter ) ;
5 SET counter = counter + 1 ;
6 END WHILE ;
B. The REPEAT ... UNTIL Loop (Post-Condition Check) Executes the loop body at least once, evaluating the condition at the end :
1 DECLARE counter INT DEFAULT 1 ;
2
3 REPEAT
4 INSERT INTO batch_log ( run_step ) VALUES ( counter ) ;
5 SET counter = counter + 1 ;
6 UNTIL counter > 5 END REPEAT ;
Note: Do not put a semicolon after the UNTIL condition!
C. The LOOP with LEAVE (Break) and ITERATE (Continue) 1 DECLARE counter INT DEFAULT 0 ;
2
3 my_loop: LOOP
4 SET counter = counter + 1 ;
5
6 -- Equivalent to 'continue' in languages like C/Java
7 IF counter = 3 THEN
8 ITERATE my_loop ;
9 END IF ;
10
11 -- Equivalent to 'break'
12 IF counter >= 5 THEN
13 LEAVE my_loop ;
14 END IF ;
15
16 INSERT INTO batch_log ( run_step ) VALUES ( counter ) ;
17 END LOOP my_loop ;
Interactive Quiz • Knowledge Check
Multiple Choice Questions Where must DECLARE statements be positioned within a stored procedure BEGIN...END block?
A Anywhere inside the block
B At the very top before any executable SQL statements
C At the very end before END
D Inside the loop condition
How is an IF block properly closed in MySQL stored procedures?
A FI
B ENDIF
C END IF;
D STOP IF
What is the key operational difference between WHILE and REPEAT loops?
A WHILE evaluates condition at start; REPEAT evaluates condition at end (guaranteeing at least 1 execution)
B REPEAT can only execute 10 times
C WHILE loops cannot be nested
D REPEAT only works with floating point numbers
Which keyword serves as a "break" statement to exit a labeled LOOP construct?
A BREAK
B EXIT
C LEAVE
D TERMINATE
What statement is used to assign the result of a single-row SELECT query into a local variable?
A FETCH INTO
B SELECT ... INTO
C PULL TO
D EXTRACT INTO