存儲過程和函數中可以使用流程控制來控制語句的執(zhí)行。MySQL中可以使用IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句來進行流程控制。
LOOP語句可以使某些特定的語句重復執(zhí)行，實現一個簡單的循環(huán)。但是LOOP語句本身沒有停止循環(huán)的語句，必須是遇到LEAVE語句等才能停止循環(huán)。
ITERATE語句也是用來跳出循環(huán)的語句。但是，ITERATE語句是跳出本次循環(huán)，然后直接進入下一次循環(huán)。
REPEAT語句是有條件控制的循環(huán)語句。當滿足特定條件時，就會跳出循環(huán)語句。
DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
ELSE
? INSERT INTO t_user VALUES(NULL,'2312312','2321312');
END IF ;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
CASE @num
? WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
? WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312');
? ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312');
END CASE ;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
BEGIN
? aaa:LOOP
? ? SET totalNum=totalNum-1;
? ? IF totalNum=0 THEN LEAVE aaa ;
? ? ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
? ? END IF ;
? END LOOP aaa ;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user8(IN totalNum INT)
BEGIN
? aaa:LOOP
? ? SET totalNum=totalNum-1;
? ? IF totalNum=0 THEN LEAVE aaa ;
? ? ELSEIF totalNum=3 THEN ITERATE aaa ;
? ? END IF ;
? ? INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
? END LOOP aaa ;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
BEGIN
? REPEAT
? ? SET totalNum=totalNum-1;
? ? INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
? ? UNTIL totalNum=1
? END REPEAT;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
BEGIN
WHILE totalNum>0 DO
? INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
? SET totalNum=totalNum-1;
END WHILE ;
END
&&
DELIMITER ;
CALL pro_user();
CALL pro_user2();
CALL pro_user3();
CALL pro_user4();
CALL pro_user5(5);
CALL pro_user6(6);
CALL pro_user7(11);
CALL pro_user8(11);
CALL pro_user9(11);
CALL pro_user10(10);
DELETE FROM t_user;
