MYSQL存储过程分支、选择、循环使用笔记

# 分支选择语句

# --- IF ---

IF  condition THEN

ELSE IF condition THEN

END IF;

EG:

DECLARE a INT;

DECLARE b INT;

IF a>b THEN

SELECT 'a>b';

ELSE IF b>a THEN

SELECT 'b>a';

END IF;

# --- CASE ---

CASE xxx

WHEN x THEN sql_statement;

WHEN x THEN sql_statement;

WHEN x THEN sql_statement;

...

ELSE sql_statement;

ENS CASE;

#循环语句

#--- WHILE ---

WHILE xxx DO

sql_statement;

...

END WHILE;

[label:] WHILE  expression DO

statements

END  WHILE [label];

#--- REPEAT UNTIL ---

REPEAT

sql_statement;

...

UNTIL xxx END REPEAT;

[label:] REPEAT

statements

UNTIL expression

END  REPEAT  [label];

#--- LOOP ---

[label:] LOOP

statements

END LOOP [label];

## REPAEAT 实例

CREATE PROCEDURE p_repeat ()

BEGIN

DECLARE v INT;

SET v = 0;

REPEAT

INSERT INTO userInfo VALUES(NULL,'王五','123456');

SET v = v + 1;

UNTIL v >= 5 END REPEAT;

END;

##注释:这是一个REPEAT循环的例子，功能和WHILE循环一样。

##区别在于它在执行后检查结果，而WHILE则是执行前检查,可能等同于DO WHILE吧

######调用存储过程

CALL p_repeat();

## WHILE 实例

CREATE PROCEDURE pro_while()

BEGIN

DECLARE cout INT;

SET cout=10;

WHILE cout>0 DO

INSERT INTO userInfo VALUES(NULL,'王五','123456');

SET cout=cout-1;

END WHILE;

END;

##注释:这是一个while循环的例子，功能和LOOP循环一样。

## 区别在于它在执行前检查结果，而LOOP则是执行后检查,可能等同于WHILE

CALL pro_while();

## LOOP 实例

CREATE PROCEDURE p_loop ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

INSERT INTO userInfo VALUES(NULL,'王五','123456');

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END;

CALL p_loop();

SELECT COUNT(*) FROM userInfo;