mysql的学习笔记-控制与循环结构

1、分界符delimiter+procedure+begin end

BEGIN ... END复合语句：包含多个语句。statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。

[begin_label:] BEGIN
语句群
END [end_label]

默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，
那么回车后，mysql将会执行该命令。如输入下面的语句
mysql> select * from test;
然后回车，那么MySQL将立即执行该语句。
但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号。

默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。
因为mysql一遇到分号，它就要自动执行。
即，在语句RETURN ”;时，mysql解释器就要执行了。
这种情况下，就需要事先把delimiter换成其它符号，如//或$$。

drop PROCEDURE if exists test_if; -- 删除
delimiter // -- 更改分界符  
create procedure test_if(in x int) 

BEGIN
INSERT INTO emp VALUES (11,'研发中心1',10,3000);
INSERT INTO emp VALUES (12,'研发中心2',20,7000);
INSERT INTO emp VALUES (13,'研发中心3',30,8000);
SELECT * FROM emp;
END;
//
delimiter ;  -- 还原分节符为；
call test_if(0); -- 调用存储过程

2、CASE分支

case语法格式：

CASE [条件]
WHEN 条件1 THEN 语句1
WHEN 条件2 THEN 语句2

......
ELSE 语句n
END CASE

执行案例：

SELECT CASE sal WHEN '3000' THEN '低工资' WHEN  '8000' THEN '高工资' ELSE '普通工资' END AS 薪水 FROM emp;  -- case后面加sal，与下面的不同，注意比较

select ename '姓名', deptno '部门编号' ,case -- case后面没有加sal，加了sal直接会走else语句
 when sal<'4000'   then  '低工资'   
 when sal<'7000'   then  '普通工资' 
 when sal<'8000'   then  '中等工资' 
ELSE '高工资'
end  '薪水'  
from emp;

3、变量

SELECT @A:=SUM(deptno) FROM emp;
UPDATE emp SET deptno=@A WHERE deptno=10;

4、if .. then .. elseif .. then.. else..end if

if语法格式：

-- if语句格式
if condition then
...
[elseif condition then]
...
[else]
...
end if

执行案例：

drop procedure if exists testtt_if;
delimiter // -- 分界符
create procedure testtt_if(in x int)
begin
if x=1 then
INSERT INTO emp VALUES (16,'研发中心1',10,3000);
elseif x=0 then
INSERT INTO emp VALUES (17,'研发中心2',20,7000);
else 
INSERT INTO emp VALUES (18,'研发中心3',30,80000);
end if;
end
//
delimiter ;
call testtt_if(2);

-- if(expr1,expr2,expr3)
-- 如果expr1是True(expr1<>0 and expr1<>NULL),则IF()的返回值为expr2,否则返回值则为expr3.

-- if(expr1,expr2,expr3) 
-- 如果expr1是True(expr1<>0 and expr1<>NULL),则IF()的返回值为expr2,否则返回值则为expr3.
select ename '姓名', deptno '部门编号' ,`IF`(sal<'4000','低工资',`IF`(sal<'7000','普通工资' ,`IF`(sal<'8000','中等工资', '高工资'))) from emp;

select ename '姓名', deptno '部门编号' ,`IF`(sal<'4000','低工资','高工资') from emp;

报[Err] 1370 - execute command denied to user '***'@'****' for routine 'test.IF' 错误

解决方法：https://stackoverflow.com/questions/6434573/mysql-execute-command-denied-to-user-localhost-for-routine-error

root登录赋值权限，使权限生效

log in as root
GRANT EXECUTE ON PROCEDURE TestMediaControl.monthavrage TO '***'@'****'
flush privileges

5、while .. do ..end while

语法格式：

while condition do
...
end while

执行案例：

DROP PROCEDURE IF EXISTS test_while;
delimiter //  -- 无分号
CREATE PROCEDURE test_while (OUT s INT)  -- 无分号
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE sum INT DEFAULT 8000;

WHILE i <10 DO
SET i = i +1; -- 有分号
SET sum = sum + 100*i;
INSERT INTO emp VALUES (i,'研发中心2',20,sum);
END WHILE; -- 有分号


SET s=sum; 
END; 
//
delimiter ;
call test_while(@s);

6、loop循环

loop语法：该循环没有内置循环条件，但可以通过leave 语句退出循环。如下：

loop_lable:loop
if 条件 then leave loop_lable;
end if;
end loop;

执行案例：

DROP PROCEDURE IF EXISTS test_loop;
delimiter //
CREATE PROCEDURE test_loop(OUT s INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE sum INT DEFAULT 8001;

loop_lable:LOOP
SET i=i+1;
SET sum = sum + 100*i;
INSERT INTO emp VALUES (i,'研发中心5',20,sum);
if i>10 THEN LEAVE  loop_lable;
END IF;
END LOOP;

SET s = sum;
END;
//
delimiter ;
CALL test_loop(@sum);

7、repeat循环

该语句执行一次循环体，之后判断condition条件是否为真，为真则退出循环，否则继续执行循环体。repeat语句的表示形式如下。

repeat
...
until condition
end repeat

DROP PROCEDURE IF EXISTS test_repeat;
delimiter //
CREATE PROCEDURE test_repeat(OUT s INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE sum INT DEFAULT 8002;

REPEAT 
SET i = i+1;
SET sum = sum + 100*i;
INSERT INTO emp VALUES (i,'研发中心5',20,sum);
UNTIL i >10  -- 此处不能有分号
END REPEAT;

SET s= sum;
END;

//
delimiter ;
CALL test_repeat(@s);

8、ITERATE 再次循环与 LEAVE

ITERATE 语句: ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。” 会再次回到label开始位置

ITERATE 标签

LEAVE语句：这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用。像其他语言中的break
LEAVE 标签