MySql数据库之（五）事务与存储过程

五、事务与存储过程

5.1 事务管理

• 事务的概念
  所谓事务就是针对数据库的一组操作，它可以由一条或多条SQL语句组成，同一个事务的操作具备同步的特点，即事务中的语句要么都执行，要么都不执行。

• 事务的定义特性
  1、原子性（Atomicity）
  原子性是指一个事务必须被视为一个不可分割的最小的工作单元，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功。

  2、一致性（Consistency）
  一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

  3、隔离性（Isolation）
  隔离性还可以称为并发控制、可串行化、锁等，当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

  4、持久性（Durability）
  持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

• 事务的提交
  事务中的操作语句都需要使用commit语句手动提交，只有事务提交后其中的操作才会生效。

  commit;
  

• 事务的回滚
  如果不想提交当前事务，可以使用rollback语句取消当前事务。
  rollback语句只能针对未提交的事务执行回滚操作，已提交的事务是不能回滚的。

  rollback;
  

#开启事务
start transaction;
#执行SQL语句
SQL语句;
#提交事务
commit;
#取消事务（回滚）
rollback;

• 事务的隔离级别
  1. 未提交读（Read uncommitted）
  ①定义：就是一个事务读取到其他事务未提交的数据，是级别最低的隔离机制。
  ②缺点：会产生脏读、不可重复读、幻读。
  2. 提交读（Read committed)
  ①定义：就是一个事务读取到其他事务提交后的数据。Oracle默认隔离级别。
  ②缺点：会产生不可重复读、幻读。
  3. 可重复读（Repeatable read）
  ①定义：就是一个事务对同一份数据读取到的相同，不在乎其他事务对数据的修改。MySQL默认的隔离级别。
  ②缺点：会产生幻读。
  4. 序列化（Serializable）
  ①定义：事务串行化执行，隔离级别最高，牺牲了系统的并发性。
  ②缺点：可以解决并发事务的所有问题。但是效率地下，消耗数据库性能，一般不使用。

  事务隔离级别	脏读	不可重复读	幻读
  读未提交（read-uncommitted）	是	是	是
  不可重复读（read-committed）	否	是	是
  可重复读（repeatable-read）	否	否	是
  串行化（serializable）	否	否	否

5.2 存储过程的创建

存储过程就是一条或多条SQL语句的集合，当对数据库进行一系列复杂操作时，存储过程可以将这些复杂操作封装成一个代码块，以便重复使用，大大减少数据库开发人员的工作量。

• 创建存储过程

  create procedure proc_name([proc_parameter])
  begin
  	sql语句;
  end;
  

  create procedure p1()
  begin 
  	select now();
  end
  
  call p1
  

• 变量的使用
  变量可以在子程序中声明，用于保存数据处理过程中的值，这些变量的作用范围在begin…end程序中。

  #定义变量
  declare var_name1 data_type [default value];
  #使用set语句为变量赋值
  set var_name=expr[,var_name=expr...];
  #使用select...into为一个或多个变量赋值
  select col_name[...] into var_name[...] table_expr;
  
  #定义一个名为myvariable的变量，类型为int，默认值为100
  declare myvariable int default 100;
  

  create procedure p2(in m int,in n int)
  begin
   declare a,b int;
   set a=m,b=n;
   select a+b;
  end
  
  call p2(1,2)
  

  注意：
  default value子句给变量提供一个默认值，该值可以被声明为一个常数或一个表达式，如果没有default子句，变量的初始值为null。

• 定义条件和处理程序

  • 定义条件是指事先定义程序执行过程中遇到的问题，使用declare语句。

  #定义条件
  declare condition_name condition for [condition_type];
  #mysql的错误
  sqlstate[value] sqlstate_value|mysql_error_code
  

  condition_type的两种形式：

  1. sqlstate[value] sqlstate_value：是长度为5的字符串类型错误代码；
  2. mysql_error_code：为数值类型的错误代码。

  • 定义处理程序：定义了在程序执行过程中遇到问题时应当采取的处理方式，并且保证存储过程在遇到警告或错误时能继续执行处理过程使用declare语句定义。

  #处理程序
  declare handler_type handler for condition_value[,...] sp_statament handler_type;
  

  handler_type：为错误处理方式，参数取值有三个：continue、exit、undo。
  continue：表示遇到错误不处理，继续执行；
  exit：遇到错误马上退出；
  undo：表示遇到错误后撤回之前的操作，mysql暂时不支持这样的操作。
  sp_statement：参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程。
  condition_value：表示错误类型。

• 光标的使用
  在编写存储过程时，查询语句可能会返回多条记录，如果数据量非常大，则需要使用光标来逐条读取查询结果集中的记录。光标是一种用于轻松处理多行数据的机制。

  #光标的声明
  declare cursor_name cursor for select_statement;
  #光标的使用
  open cursor_name;
  #光标的关闭
  close cursor_name;
  

• 流程控制的使用
  控制流程语句用于将多个SQL语句划分或组合成符合业务逻辑的代码块，MySql中的流程控制语句有7个：

  • IF语句

  if expr_condition then statement_list
  	[elseif expr_condition then statement_list]
  	...
  end if;
  

  create procedure p4(a int,b int)
  	begin
  		if a>b then 
  		select 'a大于b';
  		elseif a=b then
  			select '=';
  			else 
  			select '<';
  		end if;
  	end
  
  call p4(2,1)
  

  • CASE语句

  case case_expr when when_value then statement_list
  	[when when_value then statement_list]
  	...
  	[else statement_list]
  end case;
  

  case id when '' then
  case when id>1 then 
  

  • LOOP语句

  loop statement_list
  end loop;
  

  • LEAVE语句

  leave lable;
  

  • ITERATE语句
    结束当前循环，进行下一次循环。

  iterate lable;
  

  create procedure p5(out result int)
  begin
  	declare i int default 0;
  	set result=0;
  		aa: LOOP
  		if i>3 then
  	 leave aa;
  	 end if;
  		set i=i+1;
  	IF mod(i,2)=0 THEN
  		iterate aa;##continue 结束当次循环，直接进入下一次
  	END IF; 
  		set result= result+i;
  	
  END LOOP aa;
  end;
  
  call p5(@r);
  select @r
  

• REPEAT语句

  repeat statement_list
  untill expr_condition
  end repeat[repeat lable];
  

  create procedure p6(out r int)
  begin
  	declare i,sum int default 0;
  		a1:repeat
  			set i=i+1;
  		until i>10
  		end repeat a1;
  		set sum=i;
  		set r=sum;
  end
  
  call p6(@r);
  select @r;
  

  • WHILE语句

  while expr_condition do
  statement_list
  end while [while_lable];
  

  create procedure p7(out r int)
  begin
  	declare i int default 0;
  	while i<10 do
  		set i=i+1;
  	end while;
  	set r=i;
  end
  
  call p7(@r);
  select @r;
  

5.3 存储过程的使用

• 调用存储过程

  call proc_name([parameter[,...]]);
  

  call：为调用存储过程的关键字；
  proc_name：为存储过程的名称；
  parameter：为存储过程的参数。

• 查看存储过程

  show create{procedure|function} ap_name;
  

• 修改存储过程

  alter {procedure|function} sp_name[characteristic...];
  

  sp_name：表示存储过程或函数的名称；
  characteristic：表示要修改存储过程的哪个部分，取值分为8部分。

• 删除存储过程

  drop {procedure|function} [if exists] sp_name;