一:触发器
目录
触发器插入数据时修改同一张表某个字段的值(场景临时放开某个字段的值)
new old
insert,update操作的数据是new
delete操作的数据是old
业务修改表数据后需要更新另外表的字段数据
BEGIN
SET @organizationId = (SELECT organization_id FROM `bn_organization_user` WHERE user_id = NEW.user_id);
SET @totalStudent = (SELECT COUNT(0) FROM bn_organization_user WHERE organization_id = @organizationId);
SET @studiedCount = (SELECT COUNT(0) FROM bn_user_progress WHERE user_id IN (SELECT user_id FROM bn_organization_user WHERE organization_id = @organizationId) AND course_id = NEW.course_id);
UPDATE bn_class_course SET studied_count=@studiedCount,total_student=@totalStudent WHERE course_id = NEW.course_id AND organization_id = @organizationId;
END
语句解释:
NEW是update操作的数据体,拿到修改数据中的user_id,course_id,查询出organization_id并赋值给@organizationId,在通过@organizationId条件查询所需数据,更新到对应的bn_class_course表中
业务修改表数据后需要向另外表插入数据
begin
set @t_id= new.ID;
set @t_name= 'P_Booth';
set @t_time=NOW();
INSERT INTO `L_UpdateLogs` (`Counts`, `TablesName`,`OperateTime`) VALUES (@t_id,@t_name,@t_time);
end
触发器插入数据时修改同一张表某个字段的值(场景临时放开某个字段的值)
CREATE TRIGGER updatestatus
BEFORE insert
ON bn_user_device
FOR EACH ROW
BEGIN
set new.status = 1;
END
二:函数
创建函数
delimiter // # delimiter特殊开始标识, //是必须的
create FUNCTION f1(num INT) # f1函数名,int 是入参
RETURNS VARCHAR(20) # 返回参数类型
BEGIN
declare str VARCHAR(20) DEFAULT '哈哈哈'; # 定义变量str并默认值
if num >= 180 THEN #条件判断
SET str = '比较高'; #满足条件设置值
elseif num >= 170 and num < 180 THEN
SET str = '中等';
else
SET str = '一般';
end if; # end是结束if判断块
return str; # 返回数据
END // # end结束函数,//是必须的
delimiter; # 结束标识
函数调用
# 调用函数测试
SELECT f1(172);