9. Exercise on MySQL : control structures, and connecting tables.
Control Structures:
IF
CASE
WHILE
REPEAT
LOOP
1. IF Statement
DELIMITER $$
CREATE PROCEDURE sp_if_demo(IN p_num INT)
BEGIN
IF p_num > 0 THEN
SELECT 'Positive Number' AS result;
ELSEIF p_num < 0 THEN
SELECT 'Negative Number' AS result;
ELSE
SELECT 'Zero' AS result;
END IF;
END $$
DELIMITER ;
Call:
CALL sp_if_demo(5);
2. CASE Statement
DELIMITER $$
CREATE PROCEDURE sp_case_demo(IN p_day INT)
BEGIN
SELECT
CASE p_day
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
ELSE 'Invalid Day'
END AS day_name;
END $$
DELIMITER ;
Call:
CALL sp_case_demo(2);
3. WHILE Loop
(Sums numbers from 1 to n)
DELIMITER $$
CREATE PROCEDURE sp_while_demo(IN p_n INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
WHILE i <= p_n DO
SET total = total + i;
SET i = i + 1;
END WHILE;
SELECT total AS sum;
END $$
DELIMITER ;
Call:
CALL sp_while_demo(5);
4. REPEAT … UNTIL Loop
(Add numbers until total reaches target)
DELIMITER $$
CREATE PROCEDURE sp_repeat_demo(IN p_target INT)
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE n INT DEFAULT 0;
REPEAT
SET n = n + 1;
SET total = total + n;
UNTIL total >= p_target
END REPEAT;
SELECT total AS final_total, n AS steps;
END $$
DELIMITER ;
Call:
CALL sp_repeat_demo(20);
5. LOOP With LEAVE
(Counts from 1 to n)
DELIMITER $$
CREATE PROCEDURE sp_loop_demo(IN p_n INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
count_loop: LOOP
SET i = i + 1;
SET total = total + i;
IF i >= p_n THEN
LEAVE count_loop;
END IF;
END LOOP;
SELECT total AS sum;
END $$
DELIMITER ;
Call:
CALL sp_loop_demo(5);
Connecting Tables: JOIN Procedure With Input Parameter
DELIMITER $$
CREATE PROCEDURE sp_join_simple(IN p_dept INT)
BEGIN
-- Return employees belonging to the given department
SELECT e.emp_id, [Link], [Link], d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id = p_dept;
END $$
DELIMITER ;
Call
CALL sp_join_simple(2);