流程控制
流程控制结构可以分为:顺序结构、分支结构、循环结构。
分支结构
if函数
语法:if(条件,值1,值2)
功能:实现双分支
应用在begin end中或外面
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 中或外面
多重if
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能:类似于多重if
只能应用在begin end 中
#创建函数,实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END $
SELECT test_if(87)$
#创建函数,实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;
RETURN ch;
END $
SELECT test_case(56)$
循环结构
分类:while、loop、repeat。
循环控制:
-
iterate类似于 continue,继续,结束本次循环,继续下一次。
-
leave 类似于 break,跳出,结束当前所在的循环。
while
语法:
【标签:】while 循环条件 do
循环体;
end while【标签】;
联想:
while(循环条件){
循环体;
}
loop
语法:
【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
repeat
语法:
【标签:】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
#1.没有添加循环控制语句
#案例:批量插入,根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
SET i=i+1;
END WHILE;
END $
CALL pro_while1(100)$
/*
int i=1;
while(i<=insertcount){
//插入
i++;
}
*/
#2.添加leave语句
#案例:批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while1(100)$
#3.添加iterate语句
#案例:批量插入,根据次数插入到admin表中多条记录,只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
CALL test_while1(100)$
/*
int i=0;
while(i<=insertCount){
i++;
if(i%2==0){
continue;
}
插入
}
*/
游标/光标
游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE,其语法分别如下:
-- 声明光标
DECLARE cursor_name CURSOR FOR select_statement;
-- OPEN光标
OPEN cursor_name;
-- FETCH光标 使用游标(从游标中取得数据)
FETCH cursor_name INTO var_name [,var_name]...
-- CLOSE光标
CLOSE cursor_name;
示例:
-- 查询emp表中的数据,并逐行获取进行展示
create procedure pro_test()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare has_data int default 1;
declare emp_result cursor for select * from emp;
declare exit handler for not found set has_data = 0;
open emp_result;
repeat
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id,',name=',e_name,',age=',e_age,',薪资为:',e_salary);
until has_data = 0
end repeat;
close emp_result;
end$
触发器
在实际开发中,我们经常会遇到这样的情况:有 2 个或者多个相互关联的表,如商品信息和库存信息分别存放在 2 个不同的数据表中,我们在添加一条新商品记录的时候,为了保证数据的完整性,必须同时在库存表中添加一条库存记录。
这样一来,我们就必须把这两个关联的操作步骤写到程序里面,而且要用事务包裹起来,确保这两个操作成为一个原子操作,要么全部执行,要么全部不执行。要是遇到特殊情况,可能还需要对数据进行手动维护,这样就很 容易忘记其中的一步 ,导致数据缺失。
这个时候,咱们可以使用触发器。你可以创建一个触发器,让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来,就不用担心因为忘记添加库存数据而导致的数据缺失了。
介绍
MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样,都是嵌入到MySQL服务器的一段程序。
触发器是由事件来触发某个操作,这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序,当数据库执行这些语句时候,就相当于事件发生了,就会 自动 激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作,需要自动执行一些数据库逻辑时,可以使用触发器来实现。
触发器是与表有关的数据库对象,指在insert/update/delete之前或之后,触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据效验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其它的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。
触发器类型 | NEW和OLD的使用 |
---|---|
INSERT型触发器 | NEW表示将要或者已经新增的数据 |
UPDATE型触发器 | OLD表示修改之前的数据,NEW表示将要或修改后的数据 |
DELETE型触发器 | OLD表示将要或者已经删除的数据 |
创建触发器
create trigger trigger_name
{before|after} {insert|update|delete} on 表名
[for each row] -- 行级触发器
触发器执行的语句块;
表名 :表示触发器监控的对象。
before|after :表示触发的时间。before表示在事件之前触发;after表示在事件之后触发
insert|update|delete:表示触发的事件。
insert表示插入记录时触发;
update表示更新记录时触发;
delete表示删除记录时触发。
触发器执行的语句块:可以是单条SQL语句,也可以是由BEGIN…END结构组成的复合语句块。
示例:通过触发器记录emp表的数据变更日志
-- 首先创建一张日志表
create table emp_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型,insert/update/delete',
operate_time datetime not null comment '操作时间',
operate_id int(11) not null comment '操作表的ID',
operate_params varchar(500) comment '操作参数',
primary key(id)
)engine=innodb default charset=utf8;
-- 创建insert型触发器,完成插入数据时的日志记录
create trigger emp_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs(id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat(
'插入后id:',new.id,'name:',new.name,',age:',new.age,',salary',new.salary));
end$
删除触发器
-- 如果没有指定schema_name,默认为当前数据库
drop trigger if exists [schema_name.]trigger_name;
查看触发器
查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。
# 方式1:查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G
# 方式2:查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
# 方式3:从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
评论区