LEAVE的使用
DELIMITER //
CREATE PROCEDURE leave_begin (IN num INT)
begin_label:BEGIN
IF num <= 0
THEN LEAVE begin_label;
ELSEIF num=1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num=2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
#查询总人数
SELECT COUNT(*) FROM employees;
END //
DELIMITER;
#调用
CALL leave_begin(2);
ITERATE的使用
/*
举例:定义局部变量num,初始值为0.循环结构中执行num+1操作。
-如果num<10,则继续执行循环;
-如果num>15,则退出循环结构;
*/
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
loop_label:LOOP
#赋值
SET num = num +1;
IF num < 10
THEN ITERATE loop_label;
ELSEIF num >15
THEN LEAVE loop_label;
END IF ;
SELECT 'sql';
END LOOP;
END //
DELIMITER ;
CALL test_iterate();