-
触发器 trigger
DML触发器
1.1 创建触发器的语法
create trigger 触发器名 触发时间 触发事件 on 表名 for each row
begin
触发程序;
end;
其中:
触发时间:before after
触发事件:insert update delete
for each row: 行级触发器列标识符: old(原值) new(新值)
insert
update update
delete
1.2 示例:创建触发器,实现检查约束
课程表中的选课人数上限up_limit字段,值必须在(60,150,230)中取值
delimiter createtriggertriinsertcoursebeforebeforeinsertoncourseforeachrowbeginifnew.uplimitin(60,150,230)thensetnew.uplimit=new.uplimit;elseinsertintoAAAvalues(null);−−出错的语句endif;end; create trigger tri_insert_course_before before insert on course for each row begin if new.up_limit in(60,150,230) then set new.up_limit = new.up_limit; else insert into AAA values(null);-- 出错的语句 end if; end; createtriggertriinsertcoursebeforebeforeinsertoncourseforeachrowbeginifnew.uplimitin(60,150,230)thensetnew.uplimit=new.uplimit;elseinsertintoAAAvalues(null);−−出错的语句endif;end;
delimiter ;insert into course values(null,'HTML',100,'暂无',default,'005'); insert into course values(null,'HTML',150,'暂无',default,'005'); update course set up_limit=150 where course_no=7; delimiter $$
create trigger tri_update_course_before before update on course for each row
begin
if new.up_limit not in(60,150,230) then
set new.up_limit = old.up_limit;
else
set new.up_limit=old.up_limit;
end if;
end;
$$
delimiter ;1.3 查看触发器
show triggers\G
1.4 删除触发器
drop trigger 触发器名; -
存储过程和游标
2.1 存储过程
2.1.1 语法
create procedure 存储过程名(参数列表)
begin
存储过程语句块;
end;参数有三种模式:
in: 输入参数 默认
out: 输出参数
in out:输入输出参数模式 参数名 参数的数据类型,…
2.2 创建存储过程,实现根据学号查询选择课程数
delimiter createprocedureprocgetcourseno(stunochar(11),outcntint)beginselectcount(∗)intocntfromchoosewherestudentno=stuno;end;
create procedure proc_get_course_no(stu_no char(11),out cnt int)
begin
select count(*) into cnt from choose where student_no=stu_no;
end;
createprocedureprocgetcourseno(stunochar(11),outcntint)beginselectcount(∗)intocntfromchoosewherestudentno=stuno;end;
delimiter ;
2.1.3 调用
-- 语法
call 存储过程名(参数列表)
-- 示例
set @course_no=0;
set @stu_no='2017001';
call proc_get_course_no(@stu_no,@course_no);
select @course_no;
call proc_get_course_no('2017003',@course_no);
select @course_no;
2.1.4 使用inout的参数
delimiter createprocedureprocgetcourseno2(inoutnoint)beginselectcount(∗)intonofromchoosewherestudentno=no;end;
create procedure proc_get_course_no2(inout no int)
begin
select count(*) into no from choose where student_no=no;
end;
createprocedureprocgetcourseno2(inoutnoint)beginselectcount(∗)intonofromchoosewherestudentno=no;end;
delimiter ;
set @num='2017001';
call proc_get_course_no2(@num);
select @num;
2.1.5 查看存储过程
– 所有存储过程的定义
show procedure status\G
-- 查看指定存储过程
show create procedure 过程名\G
-- mysql.proc
select 字段列表 from mysql.proc where db='数据库名' and type='procedure';
2.1.6 删除存储过程
drop procedure 存储过程名;
2.1.7 函数和存储过程的区别
1) 函数必须要返回类型和返回值,存储过程可以使用out或inout的参数作为返回值。
2)函数可以直接在sql或mysql语句中使用,存储过程一般需要单独调用
3)函数内部可以select …into语句为变量赋值,但是不能使用有返回结果集的select语句。存储过程没有这个限制。
4)函数的函数体中不能使用事务控制语句
2.2 错误触发条件和错误处理
insert into choose values(null,‘2017111’,1,null,now());
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`choose`.`choose`, CONSTRAINT `choose_student_fk` FOREIGN KEY (`student_no`) REFERENCES `student` (`student_no`))
1452:MySql错误代码
23000:ANSI标准错误代码
2.2.1 自定义错误处理程序
1)语法
declare 错误处理类型 handler for 错误触发条件 自定义错误处理程序;
其中:
错误处理类型:continue exit
错误触发条件:
预定义:sqlexception、sqlwarning、not found
MySql的错误代码
ANSI标准错误代码
2)使用预定义错误触发条件
创建存储过程,根据给定的学号查询姓名。如果学号不存在,提示错误
delimiter createprocedureprocgetname(snochar(11),outsnamechar(10))begindeclareexithandlerfornotfoundbeginselectconcat(′没有学号为′,sno,′的学生′)错误信息;end;selectstudentnameintosnamefromstudentwherestudentno=sno;end;
create procedure proc_get_name(s_no char(11),out s_name char(10))
begin
declare exit handler for not found
begin
select concat('没有学号为',s_no,'的学生') 错误信息;
end;
select student_name into s_name from student where student_no=s_no;
end;
createprocedureprocgetname(snochar(11),outsnamechar(10))begindeclareexithandlerfornotfoundbeginselectconcat(′没有学号为′,sno,′的学生′)错误信息;end;selectstudentnameintosnamefromstudentwherestudentno=sno;end;
delimiter ;
– 测试
set @name=’’;
call proc_get_name(‘2017111’,@name);
3)使用MySql的错误代码
创建存储过程,向课程表中插入一行数据,如果教师工号存在,提示错误。
(违反唯一约束:1062)
delimiter createprocedureprocinsertcourse(cnamechar(10),descrivarchar(100),tnochar(10))begindeclarecontinuehandlerfor1062beginselect′违反了唯一约束′;end;insertintocourse(coursename,description,teacherno)values(cname,descri,tno);end;
create procedure proc_insert_course(c_name char(10),descri varchar(100),t_no char(10))
begin
declare continue handler for 1062
begin
select '违反了唯一约束';
end;
insert into course(course_name,description,teacher_no) values(c_name,descri,t_no);
end;
createprocedureprocinsertcourse(cnamechar(10),descrivarchar(100),tnochar(10))begindeclarecontinuehandlerfor1062beginselect′违反了唯一约束′;end;insertintocourse(coursename,description,teacherno)values(cname,descri,tno);end;
– 测试
call proc_insert_course(‘CSS’,‘暂无’,‘001’);
4)使用ANSI标准错误代码
declare continue handler for 1062
替换成
declare continue handler for sqlstate ‘23000’
2.2.2 自定义错误触发条件
1) 语法
declare 错误触发条件 condition for MySql错误代码或ANSI标准错误代码;
2) 示例
delimiter $$
create procedure proc_insert_course2(c_name char(10),descri varchar(100),t_no char(10))
begin
declare unique_err condition for 1062;
declare continue handler for unique_err
begin
select '违反了唯一约束' 错误;
end;
declare continue handler for 1452
begin
select '违反了外键约束' 错误;
end;
insert into course(course_name,description,teacher_no) values(c_name,descri,t_no);
end;
$$
-- 测试
call proc_insert_course2('CSS','暂无','001');//违反了唯一约束
call proc_insert_course2('CSS','暂无','007');//违反了外键约束
2.3 游标 cursor
2.3.1 使用游标的步骤
1)声明游标
– 语法
declare 游标名 cursor for select语句;
声明游标名一级对应的select语句(select语句不执行)
2)打开游标
– 语法
– open 游标名;
执行select语句,把结果集检索到内存中
游标指针指向结果集中第一行的数据
3)提取数据
– 语法
fetch 游标名 into 变量列表;
其中,变量列表中变量的数量和数据类型必须和select的字段列表匹配
把游标指针指向的的当前数据提取出来,保存在变量列表中
游标指针自动向下移动一行
4)关闭游标
– 语法
close 游标名;
释放游标打开时的结果集
2.3.2 案例:成绩进行修正,每个人的成绩加5分
delimiter $$
create procedure proc_update_score(c_no int)
begin
-- 声明局部变量
declare stu_no char(11);-- 学号
declare grade int; -- 成绩
declare flag char(10); -- 循环是否继续的标识
-- 声明游标
declare score_cursor cursor for
select student_no,score from choose where course_no=c_no;
-- 声明错误处理程序
declare continue handler for not found set flag='error';
-- 打开游标
open score_cursor;
-- 循环提取数据
update_score:loop
fetch score_cursor into stu_no,grade;
if flag='error' then
leave update_score;
end if;
set grade=grade+5;
if grade>100 then
set grade=100;
end if;
if grade between 55 and 59 then
set grade=60;
end if;
update choose set score=grade where student_no=stu_no and course_no=c_no;
end loop update_score;
-- 关闭游标
close score_cursor;
end;
$$
delimiter ;
-- 测试
call proc_update_score(1);
-
事务机制
3.1 事务机制的必要性
银行转账
1)创建账户表
create table account(
account_no int auto_increment primary key,
account_name char(10) not null,
balance int unsigned
);
2)插入测试数据
insert into account values(null,‘甲’,1000);
insert into account values(null,‘乙’,1000);3)创建存储过程,完成转账
delimiter createprocedureproctransfer(fromaccountint,toaccountint,moneyint)beginupdateaccountsetbalance=balance+moneywhereaccountno=toaccount;updateaccountsetbalance=balance−moneywhereaccountno=fromaccount;end; create procedure proc_transfer(from_account int, to_account int,money int) begin update account set balance=balance+money where account_no=to_account; update account set balance=balance-money where account_no=from_account; end; createprocedureproctransfer(fromaccountint,toaccountint,moneyint)beginupdateaccountsetbalance=balance+moneywhereaccountno=toaccount;updateaccountsetbalance=balance−moneywhereaccountno=fromaccount;end;
delimiter ;-
测试存储过程
call proc_transfer(1,2,800);
1 200
2 1800 -
再次调用存储过程
call proc_transfer(1,2,800);
1 200
2 2600
结果 数据不是一致的
-
3.2 关闭MySql自动提交
1) 显示关闭
– 查看自动提交的状态
show variables like ‘autocommit’;
– 关闭自动提交
set autocommit=0;
2) 隐式关闭
-- 开始事务
start transaction;
-- 隐式的关闭自动提交,但不改变@@autocommit的值。
3.3 回滚 rollback
作用:撤销当前事务中没有提交的dml操作
set autocommit=0;
update account set balance=balance+800 where account_no=2;
select * from account;
1 200
2 3400
rollback;
select * from account;
1 200
2 2600
3.4 提交 commit
作用:提交当前事务中所有没有提交的dml操作
1)显示提交
commit;
set autocommit=0;
update account set balance=balance+800 where account_no=2;
commit;
2)隐式提交
begin,set autocommit=1,start transaction
ddl语句
dcl语句
锁语句
update account set balance=balance+800 where account_no=2;
3.5 事务
drop procedure proc_transfer;
delimiter createprocedureproctransfer(fromaccountint,toaccountint,moneyint)begindeclarecontinuehandlerfor1690beginrollback;end;starttransaction;updateaccountsetbalance=balance+moneywhereaccountno=toaccount;updateaccountsetbalance=balance−moneywhereaccountno=fromaccount;commit;end;
create procedure proc_transfer(from_account int,to_account int,money int)
begin
declare continue handler for 1690
begin
rollback;
end;
start transaction;
update account set balance=balance+money where account_no= to_account;
update account set balance=balance-money where account_no= from_account;
commit;
end;
createprocedureproctransfer(fromaccountint,toaccountint,moneyint)begindeclarecontinuehandlerfor1690beginrollback;end;starttransaction;updateaccountsetbalance=balance+moneywhereaccountno=toaccount;updateaccountsetbalance=balance−moneywhereaccountno=fromaccount;commit;end;
delimiter ;
3.6 保存点 savepoint
实现部分提交、部分撤销
– 语法
savepoint A;
rollback to A;
案例:创建一个存储过程,申请两个账号
delimiter createprocedureprocsavepoint()begindeclarecontinuehandlerfor1062beginrollbacktoA;end;starttransaction;insertintoaccountvalues(null,′丙′,1000);savepointA;insertintoaccountvalues(lastinsertid(),′丁′,1000);commit;end;
create procedure proc_savepoint()
begin
declare continue handler for 1062
begin
rollback to A;
end;
start transaction;
insert into account values(null,'丙',1000);
savepoint A;
insert into account values(last_insert_id(),'丁',1000);
commit;
end;
createprocedureprocsavepoint()begindeclarecontinuehandlerfor1062beginrollbacktoA;end;starttransaction;insertintoaccountvalues(null,′丙′,1000);savepointA;insertintoaccountvalues(lastinsertid(),′丁′,1000);commit;end;
delimiter ;
– 测试
call proc_savepoint();
3.6 事务的ACID特性
1)原子性(Atomicity)
一个事务中的多条语句是一个不可分割的整体,要么一起成功,要么一起失败
2) 一致性(Consistency)
事务在执行后数据由一种一致性状态转换成另外一种一致性状态
3) 隔离性(Isolation)
一个事务对数据所做的改变,在提交之前对于其他的事务是不可见的
4)持久性(Durability)
事务一旦提交,对数据所做的改变就是永久的