MySql学习day05 触发器 trigger 存储过程和游标 事务机制

1. 触发器 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 $$createtriggertr{i}_{i}nser{t}_{c}ours{e}_{b}eforebeforeinsertoncourseforeachrowbeginifnew.u{p}_{l}imitin(60,150,230)thensetnew.u{p}_{l}imit=new.u{p}_{l}imit;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. 存储过程和游标
   2.1 存储过程
   2.1.1 语法
   create procedure 存储过程名(参数列表)
   begin
   存储过程语句块;
   end;

   参数有三种模式：
   in: 输入参数 默认
   out: 输出参数
   in out:输入输出参数

   模式 参数名 参数的数据类型,…

2.2 创建存储过程,实现根据学号查询选择课程数
delimiter $$createprocedurepro{c}_{g}e{t}_{c}ours{e}_{n}o(st{u}_{n}ochar(11),outcntint)beginselectcount(\ast )intocntfromchoosewherestuden{t}_{n}o=st{u}_{n}o;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 $$createprocedurepro{c}_{g}e{t}_{c}ours{e}_{n}o2(inoutnoint)beginselectcount(\ast )intonofromchoosewherestuden{t}_{n}o=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 $$createprocedurepro{c}_{g}e{t}_{n}ame(s_{n}ochar(11),out{s}_{n}amechar(10))begindeclareexithandlerfornotfoundbeginselectconcat{(}^{\prime }没有学号{为}^{\prime },s_{n}o{,}^{\prime }的学{生}^{\prime })错误信息;end;selectstuden{t}_{n}ameinto{s}_{n}amefromstudentwherestuden{t}_{n}o=s_{n}o;end;$$
delimiter ;
– 测试
set @name=’’;
call proc_get_name(‘2017111’,@name);

3)使用MySql的错误代码
创建存储过程，向课程表中插入一行数据，如果教师工号存在，提示错误。
（违反唯一约束：1062）
delimiter $$createprocedurepro{c}_{i}nser{t}_{c}ourse(c_{n}amechar(10),descrivarchar(100),t_{n}ochar(10))begindeclarecontinuehandlerfor1062beginselec{t}^{\prime }违反了唯一约{束}^{\prime };end;insertintocourse(cours{e}_{n}ame,description,teache{r}_{n}o)values(c_{n}ame,descri,t_{n}o);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. 事务机制
   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 $$createprocedurepro{c}_{t}ransfer(fro{m}_{a}ccountint,t{o}_{a}ccountint,moneyint)beginupdateaccountsetbalance=balance+moneywhereaccoun{t}_{n}o=t{o}_{a}ccount;updateaccountsetbalance=balance-moneywhereaccoun{t}_{n}o=fro{m}_{a}ccount;end;$$
   delimiter ;

   4. 测试存储过程
      call proc_transfer(1,2,800);
      1 200
      2 1800

   5. 再次调用存储过程
      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 $$createprocedurepro{c}_{t}ransfer(fro{m}_{a}ccountint,t{o}_{a}ccountint,moneyint)begindeclarecontinuehandlerfor1690beginrollback;end;starttransaction;updateaccountsetbalance=balance+moneywhereaccoun{t}_{n}o=t{o}_{a}ccount;updateaccountsetbalance=balance-moneywhereaccoun{t}_{n}o=fro{m}_{a}ccount;commit;end;$$
delimiter ;

3.6 保存点 savepoint
实现部分提交、部分撤销
– 语法
savepoint A;
rollback to A;

案例：创建一个存储过程，申请两个账号
delimiter $$createprocedurepro{c}_{s}avepoint()begindeclarecontinuehandlerfor1062beginrollbacktoA;end;starttransaction;insertintoaccountvalues(null{,}^{\prime }{丙}^{\prime },1000);savepointA;insertintoaccountvalues(las{t}_{i}nser{t}_{i}d(){,}^{\prime }{丁}^{\prime },1000);commit;end;$$
delimiter ;

– 测试
call proc_savepoint();

3.6 事务的ACID特性
1）原子性(Atomicity)
一个事务中的多条语句是一个不可分割的整体，要么一起成功，要么一起失败
2) 一致性(Consistency)
事务在执行后数据由一种一致性状态转换成另外一种一致性状态
3) 隔离性(Isolation)
一个事务对数据所做的改变，在提交之前对于其他的事务是不可见的
4）持久性(Durability)
事务一旦提交，对数据所做的改变就是永久的