MySQL几种控制结构

-- if 控制结构
delimiter //
CREATE PROCEDURE ex_if(IN x int)    
BEGIN
IF x=1 THEN 
SELECT 1;
ELSEif x=2 THEN 
SELECT 2;
ELSE 
SELECT 3;
END IF;
END //
delimiter ;


--case 控制结构
delimiter //
CREATE PROCEDURE ex_case(IN x int)
BEGIN
CASE x
WHEN 1 THEN SELECT 1;
WHEN 2 THEN SELECT 2;
ELSE
SELECT 3;
END CASE;
END //
delimiter ;


-- while 控制结构
delimiter //
CREATE PROCEDURE ex_while(out sum int)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
WHILE i<=100 do
SET s=s+i;
SET i=i+1;
END while;
SET sum=s;
END //
delimiter ;


-- loop 控制结构
delimiter //
CREATE PROCEDURE ex_loop(out sum int)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
loop_wei:loop
SET s=s+i;
SET i=i+1;
IF i>100 THEN
leave loop_wei;
END if;
END loop;
SET sum=s;
END //
delimiter ;


-- repeat 控制结构
delimiter //
CREATE PROCEDURE ex_repeat(out sum int)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
repeat
SET s=s+i;
SET i=i+1;
until i>100
END repeat;
SET sum=s;
END //
delimiter ;