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

技术探讨  2016-10-18 11:19   9498 MYSQL 分支 选择 循环 存储过程

640.webp.jpg

# 分支选择语句

# --- 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;


注:转载请注明出处为http://www.sandbean.com/article/23.html。

沙豆网 站长

追求卓越,奋斗不息!

168
文章
9260
点赞

更多文章