自定义函数
执行时机:调用执行
定义函数语法:
create function 函数名(参数列表)returns 类型 begin return null; end;
讯享网
函数的主体内容:
也就是begin到end中的逻辑代码,可以使用变量,也能写逻辑控制语句
变量的使用
变量分为全局变量和局部变量
全局变量:依赖于一次有效的数据库连接
使用步骤:1.赋初值,使用 set @变量名=数据值; @符号必须存在,这是全局变量的标识符,如果不为全局变量赋初值,默认值为null
2.使用,直接通过@变量名即可访问,并且在函数、存储过程、触发器中均可访问
局部变量:只能在存储过程、函数或触发器的begin~end之间使用
使用步骤:1.声明,声明格式:
讯享网declare 变量名 数据类型;
2.赋值:set 变量名=值;
3.使用:通过变量名访问
注意:所有局部变量的声明必须集中放置在所属结构的最前方!
逻辑控制语句
逻辑控制语句分为条件结构和循环结构
条件结构语句
关于条件结构语句这里只列举两个:
嵌套条件结构:
示例代码:
if(条件表达式) then if(条件表达式) then 条件成立时的操作 else 不成立时的操作 end if; else 不成立时的操作 end if;
多重条件结构
示例代码:
讯享网if(条件1) then elseif(条件2) then elseif(条件3) then else end if;
循环结构语句
在mysql有多种循环结构,这里只介绍while循环(因为其他不会)
定义while循环语法:
while(循环条件表达式) do end while;
示例代码:
讯享网drop function if exists fun_add; create function fun_add(n int)returns int begin declare i int; declare sum int; set i=1; set sum=0; while(i<=n) do set sum=sum+i;#累加操作 set i=i+1;#循环变量自增 end while; return sum; end;
存储过程
执行时机:调用执行
优点:1.执行速度更快
2.允许模块化程序设计(一个存储过程可以包含多条SQL指令,完成一段连续的逻辑操作)
3.提高系统安全性
4.减少网络流通量
创建存储过程语法:
create procedure 存储过程名称(参数列表) begin 逻辑代码主体 end;
示例代码:
讯享网create procedure pro_addEmp(eId int, eName varchar(20), ePosition varchar(20), eHrId int, eSalary float, eBonus float, dId int) begin #查找 declare c int; set c=(select count(empId) from employee where empId=eId); if(c=0) then insert into employee(empId,empName,empPosition,empHrId,empSalary,empBonus,deptId) values(eId,eName,ePosition,eHrId,eSalary,eBonus,dId); else signal sqlstate 'TX001' set message_Text='工号已经存在'; end if; end;
与函数的区别
1.存储过程的主体代码可以包含查询语句 返回结果集
2.参数:输入参数:in修饰,可以省略,默认为输入参数
输出参数(也就是函数中的返回值):out修饰
示例代码:
drop procedure if exists pro_sum; create procedure pro_sum(in n int,out s int,out c int) begin declare i int; set i=1; set s=0; set c=1; while(i<=n) do set s=s+i; set c=c*i; set i=i+1; end while; end; call pro_sum(10,@mysum,@myc);
3.存储过程没有返回值
4.调用存储过程 需要使用 call 存储过程名(参数)进行调用
触发器
执行时机:预设执行时机
创建触发器语法:
讯享网create trigger tri_emp_add before/after insert/update/delete on 表名 for each row begin 触发后的操作代码段 end;
示例代码:
create trigger tri_emp_add before insert on employee for each row begin if(new.empHrId is not null and not exists(select * from employee where empId=new.empHrId)) then #不能新增 找不到对应的上级 #阻止当前正在进行的新增操作 signal sqlstate 'TX001' set message_text ='上级工号设置有误'; end if; end;
上述代码在新增数据之前对上级工号进行判断是否不为空并且没有此上级工号时,会激活此触发器,抛出一个错误阻止插入操作
手动制造一个错误,用于终止正在执行的操作:
讯享网signal sqlstate 'TX001' set message_text ='消息文本';
注意:每张表的每一个触发时机只能绑定一个触发器,新增操作只能用new表,删除操作只能用old表,修改操作既可以用new表也可以用old表
触发器的级联操作
级联删除部门和对应部门员工的触发器:
create trigger tri_dept_remove before delete on department for each row begin #先删除对应部门的员工 delete from employee where deptId=old.deptId; end;
级联删除操作一定要谨慎使用 ! ! !
级联更新触发器
讯享网create trigger tri_sellInfo_add before insert on sellinfo for each row begin #验证本次添加的销售记录中销售的商品数量是否 超过 对应商品的库存数量 declare wCount int; set wCount=(select wareCount from inventinfo where id=new.inventId); if(new.sellCount>wCount) then signal sqlstate 'TX001' set message_text ='商品库存不足'; else #数量充足 把库存数量减少 update inventinfo set wareCount=wareCount-new.sellCount where id=new.inventId; end if; end;
上述代码在对销售表进行新增操作的时候会激活此构造器,首先对库存数量进行判断,库存不足会抛出错误,阻止此次新增,如果库存充足,会将库存数量减少,再对销售表进行新增
例题:
#创建学生表和成绩表 create table t_Student ( stuName varchar(20) not null, stuNo varchar(100) primary key, stuSex varchar(10) check ( stuSex = '男' or stuSex = '女'), stuAge int not null, stuSeat int unique not null, stuAddress varchar(100) default '地址不详' ); create table t_Score ( examNo varchar(20) not null comment '科目号', stuNo varchar(100) not null comment '学号', writtenExam int not null comment '笔试成绩', labExam int not null comment '机试成绩', foreign key (stuNo) references t_Student (stuNo) );
关于数据:笔者直接用图形化工具添加的(因为懒),所以没有插入语句,随便填一下就好啦(记得先填主表t_Student的数据!)
题目及其解决过程:
讯享网#循环提分过程,要求比较平均机试成绩和笔试成绩,对较低者进行提分,每次提一分,直到有人的成绩达到97分为止。计算通过率 create procedure pro_addScore() begin declare avgWrite double; declare avgLab double; set avgWrite = (select avg(writtenExam) from t_score); #平均笔试成绩 set avgLab = (select avg(labExam) from t_score); #平均机试成绩 if (avgLab > avgWrite) then #对笔试成绩提分 #循环 while(not exists(select writtenExam from t_score where writtenExam = 97)) do #每次提1分 update t_score set writtenExam=writtenExam + 1; end while; else #对机试成绩提分 #循环 while(not exists(select labExam from t_score where labExam = 97)) do #每次提1分 update t_score set labExam=t_Score.labExam + 1; end while; end if; end; #调用存储过程 call pro_addScore(); #查询 select stuName 姓名, stu.stuNo 学号, ifnull(writtenExam, '缺考') 笔试成绩, ifnull(labExam, '缺考') 机试成绩, (if(writtenExam >= 60 and labExam >=60, '是', '否')) 是否通过 from t_Student stu left join t_Score ts on stu.stuNo = ts.stuNo; #计算通过率 select count(stu.stuNo) 总人数, count(case when writtenExam>=60 and labExam >=60 then stu.stuNo end ) 通过人数, concat(format(count(case when writtenExam>=60 and labExam >=60 then stu.stuNo end )/count(stu.stuNo)*100,0),'%') 通过率 from t_score ts right join t_Student stu on stu.stuNo = ts.stuNo;

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/81241.html