进阶:流程控制结构
流程控制结构分:
(1)顺序结构
(2)分支结构
(3)循环结构
1、分支结构
1.1 if()函数
语法:
if(条件, 值1, 值2)
功能:实现双分支
应用在begin/end中或外面
如:
SELECT IF(6>0, ‘成立’, ‘不成立’);
问题:有没有if…else if…的写法?
1.2 case结构
语法:
情况1:类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
…
else 语句n;
end
情况2:
case
when 条件1 then 语句1;
when 条件2 then 语句2;
…
else 语句n;
end
应用在begin end 中或外面
*/
1.3 if结构
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;
功能:类似于多重if
只能应用在begin end 中
案例1:创建函数,实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
DROP FUNCTION IF EXISTS fun_myf5;
DELIMITER $
CREATE FUNCTION fun_myf5(score FLOAT) RETURNS VARCHAR(10)
BEGIN
DECLARE degree VARCHAR(20) DEFAULT ‘D’;
# if结构
IF score>=90 THEN SET degree=‘A’;
ELSEIF score>80 THEN SET degree=‘B’;
ELSEIF score>60 THEN SET degree=‘C’;
ELSE SET degree=‘D’;
END IF;
RETURN degree;
END $
DELIMITER ;
SELECT fun_myf5(87);
SELECT fun_myf5(57);
#案例2:创建存储过程,如果工资<2000,则删除,如果5000>工资>2000,则涨工资1000,否则涨工资500
DROP PROCEDURE IF EXISTS fun_myf6;
DELIMITER $
CREATE PROCEDURE fun_myf6(IN sal DOUBLE)
BEGIN
IF sal<2000 THEN DELETE FROM employees
WHERE salary
=sal;
ELSEIF sal>2000 AND sal<5000 THEN UPDATE employees
SET salary
=salary
+1000 WHERE salary
=sal;
ELSE UPDATE employees
SET salary
=salary
+500 WHERE salary
=sal;
END IF ;
END $
DELIMITER ;
#使用存储过程
CALL fun_myf6(3200);
SELECT * FROM employees
WHERE salary
=4200;
2、循环结构
分类:
while、loop、repeat
循环控制:
iterate类似于 continue,继续,结束本次循环,继续下一次
leave 类似于 break,跳出,结束当前所在的循环
*/
2.1 while
语法:
【标签:】while 循环条件 do
循环体;
end while【 标签】;
联想:
while(循环条件){
循环体;
}
*/
2.2 loop
/*
语法:
【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
相当于 while(true)–break
*/
2.3 repeat
/*
语法:
【标签:】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
联想:
do{
循环体;
}while(条件)
*/
#1.没有添加循环控制语句
#案例:批量插入,根据次数插入到admin表中多条记录
USE girls
;
TRUNCATE TABLE admin
;
SELECT * FROM admin
;
DROP PROCEDURE IF EXISTS sp_while1;
DELIMITER $
CREATE PROCEDURE sp_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin
(username
,password
) VALUES (CONCAT(‘Rose’,i),‘6868’);
SET i = i+1;
END WHILE;
END $
DELIMITER ;
#使用
CALL sp_while1(60);
/*
int i=1;
while(i<=insertcount){
//插入
i++;
}
*/
2.4 添加leave语句【break】
#案例:批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
USE girls
;
TRUNCATE TABLE admin
;
SELECT * FROM admin
;
DROP PROCEDURE IF EXISTS sp_while2;
DELIMITER $
CREATE PROCEDURE sp_while2(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
haha: WHILE i<=insertCount DO
INSERT INTO admin
(username
,password
) VALUES (CONCAT(‘Rose’,i),‘6868’);
SET i = i+1;
#添加中断控制语句
IF i>20 THEN LEAVE haha;
END IF;
END WHILE haha ;
END $
DELIMITER ;
#使用
CALL sp_while2(100);
#3.添加iterate语句【continue】
#案例:批量插入,根据次数插入到admin表中多条记录,只插入偶数次
USE girls
;
TRUNCATE TABLE admin
;
SELECT * FROM admin
;
DROP PROCEDURE IF EXISTS sp_while3;
DELIMITER $
CREATE PROCEDURE sp_while3(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
haha: WHILE i<=insertCount DO
SET i = i+1;
# if i%2!=0 then iterate haha; #如果i是奇数,则中止【停止本次继续下一次】
IF MOD(i,2)!=0 THEN ITERATE haha; #mod(x,y)取模函数,结果是x对y取余的值
END IF;
INSERT INTO admin
(username
,password
) VALUES (CONCAT(‘Rose’,i),‘6868’);
END WHILE haha ;
END $
DELIMITER ;
#使用
CALL sp_while3(100);
/*
int i=0;
while(i<=insertCount){
i++;
if(i%2==0){
continue;
}
插入
}
*/
#=3=========
#MySQL存储过程事务transaction
/*
MySQL中,单个Store. Procedure(SP) 不是原子操作,而0racle.则是原子的。
如下的存储过程,即使使语句2失败,语句1仍然会被commit 到数据库中:
create table testproc(
id int(4) primary key,
name varchar (100)
) ;
DELIMITER $
CREATE PROCEDURE test_proc_ins( IN i_id INT,IN i_name VARCHAR(100))
BEGIN
INSERT INT0 testproc VALUES (i_id, i_name); – 语句1
INSERT INTO lestproe VALUES (i_id, i_name): – 语句2 (因为id为PK,此语句将出错)。
END $
DELIMITER
要使整个存储过程成为一个原子操作的办法是:在存储过程主体开始部分,指定开始一个事务。语句2失败,语句1不会:执行
DELIMITER $
CREATE PROCEDURE test_proc_ins (IN i_id INT,IN i_name VARCHAR(100)
BEGIN
start transaction; -整个存储过程指定为一个事务
INSERT INTO testproc VALUES (i_id, i_name) ;
INSERT INTO testproc VALUES (i_id+1, i_name); --这里把id+1,避免主键冲突
commit; --语句1.必须主动提交
END$
DELIMITER
MySQL的回滚事物的操作
在处理事务时,使用SQLException捕获SQL错误,然后处理:
按照这个推论,我们必须在MySQL存储过程中捕获SQL错误
DECLARE t_error INTEGER DEFAULT 0;
#如果出现任何错误(不是NOT FOUND ),设置t_error 为1后继续执行
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1:
*/
USE girls
;
CREATE TABLE testproc(
id INT(4) PRIMARY KEY,
NAME VARCHAR (100)
);
DROP PROCEDURE IF EXISTS test_sp1;
DELIMITER $
CREATE PROCEDURE test_sp1()
BEGIN
DECLARE t_error INT DEFAULT 0; #设置局部变量,标记错误
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
SET autocommit=0; #设置不自动提交事务
START TRANSACTION; #开启事物;
INSERT INTO testproc
VALUES (2,‘1111’);
INSERT INTO testproc
VALUES (1,‘1111’);
IF t_error=1 THEN #出现错误
ROLLBACK; #回滚,都不执行
ELSE #没有错误
COMMIT; #提交,都执行
END IF;
SELECT t_error;
END $
DELIMITER ;
CALL test_sp1();
SELECT * FROM testproc;