六、MySQL事务与存储过程

JDBC事务控制

事务概述：指逻辑上的一组操作，组成这组操作的各个单元，要么全成功，要么全部不成功。

举例说明：银行中要将A账户中转100元到B账户,这两步操作必须都成功或都不成功

update account set money = money-100 where name='A';
update account set money = money+100 where name='B';

控制事务语法格式

start transaction;//开启事务，在这条语句之后的sql将处在同一事务中，并不会立即影响到数据库
...
...
commit;--提交事务，让这个数据中的sql对数据库的影响立即发生
rollback;--取消事务，对数据库不会产生任何影响

事务的四大特性

1. 原子性：事务是一组不可分割的单位，要么同时成功要么都失败
2. 一致性：事务的前后的数据完整性保持一致
3. 隔离性：多个用户并发访问时，一个用户的事务不被其他用户干扰
4. 持久性：一个事务一旦被提交，它对数据库中的数据的改变是永久性的。不能再回滚

事务的隔离级别

四大隔离级别

脏读：一个事务读到另一个事务未提交的数据。
不可重复读：事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。
虚读：T1读取满足某种搜索条件的一些行，然后T2插入了符合T1的搜索条件的一个新行。如果T1重新执行产生原来那些行的查询，就会得到不同的行。

1. read uncommitted：不作任何隔离，具有脏读，不可重复读，虚读问题
2. read committed：可以防止脏读，不能防止不可重复读，虚读问题
3. Repeatable read：可以防止脏读，不可重复读，不能够防止虚读问题（MySQL默认值）
4. Serializable：数据库运行在串行化模式下，所有问题都防止，但是性能非常低

设置隔离级别

set [session/global] transaction isolation level ...;-- 修改当前隔离级别
select @@tx_isolation;-- 查询当前数据库的隔离级别

存储过程的创建

概述：一条或者多条SQL语句的集合，当数据库进行一系列复杂操作时，存储过程可以将这些复杂操作封装成一个代码块，以便重复使用。

如何创建存储过程

CREATE PROCEDURE sp_name([proc_parameter]) [characteristics...] routine_body;

--proc_parameter为指定存储过程的参数列表，形式如下
[IN/OUT/INOUT]param_name type
--IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出
--param_name表示参数名称，type表示参数类型

--例一：创建一个查看student表的存储过程
select * from student;

--将结束符分号改为双斜杠
DELIMITER //
create procedure Proc()
BEGIN
    select *from student;
END //
DELIMITER ;

使用：
call Proc();

变量的使用

--定义变量：DECLARE关键字,变量的声明一定在存储过程中，作用范围是当前存储过程。
DECLARE var_name[,varname]...date_type[DEFAULT value];

--DEFAULT value为默认值
--修改变量值
方法1：SET var_name = expr[,var_name=expr]...;
方法2：SELECT col_name[...] INTO var_name[...] table_expr;

--例一：定义一个名称为myvariable变量，类型为INT类型，默认值为100
DECLARE myvariable INT DEFAULT 100;

--例二：修改上例中的变量的值
SET myvariable = 200;

--例三：定义变量，保存学生表中查询到的班级和性别信息
DELIMITER //
create procedure proc3()
BEGIN 
    DECLARE s_grade float; 
    DECLARE s_gender char(2); 
    select grade.gender into s_grade,s_gender from student where id=3;
END//
DELIMITER ;

定义条件和处理程序

--定义条件使用DECLARE语句，语法格式如下
DELCARE condition_name CONDITION FOR [condition_type];

--condition_type的两种形式：
[condition_type]：
    SQLSTATE[VALUE] sqlstate_value或者mysql_error_code
 
sqlstate_value和mysql_error_code都可以表示错误MySQL的错误

--定义处理程序
DECLARE handler_type HANDLER FOR condition_value[...] sq_statement;
handler_type:CONTINUE（遇到错误继续运行）
或者EXIT（遇到错误退出）
或者UNDO（遇到错误撤回操作，MySQL不支持）
 
condition_value:
SQLSTATE[VALUE]
或者condition_name
或者SQLWARNING（以01开头的SQLSTATE的错误）
或者NOT FOUND（以02开头的SQLSTATE的错误）
或者SQLEXCEPTION（匹配所有没有被前两个捕获的错误代码）
或者mysql_error_code

--例一：定义ERROR 1148（42000）错误，名称为command_not_allowed的条件
declare command_not_allowed condition for SQLSTATE 42000;
或者
declare command_not_allowed condition for 1148;

--例二：定义上例中的错误的错误程序
declare continue handler for command_not_allowed SET @info='ERROR';

光标(游标)的使用

--声明光标
DECLARE cursor_name CURSOR FOR select_statement;
--为select-statement的查询结果定义一个光标

--光标的使用
OPEN cursor_name
FETCH cursor_name INTO var_name[,varname]...--这里的变量要在游标之前定义好，每次调用均下移一条数据

//关闭游标
CLOSE cursor_name;

流程控制的使用

1. IF语句：满足某种条件，执行相应语句

IF expr_condition THEN statement_list
    [ELSEIF expr_condition THEN statement_list]...
    [ELSE statement_list]
END IF

例一：创建一个存储过程判断变量v的值是否是null，输出对应的提示信息
delimiter //
create procedure proc3()
BEGIN 
    declare v varchar(2); 
    IF v is null then select 'is null'; 
    ELSE select 'not null'; 
    END IF;
END//
delimiter ;

2. CASE语句

--格式一
CASE case_expr
    WHEN when_value THEN statement_list;
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE;
--格式二
CASE
    WHEN expr_condition THEN statement_list;
    [WHEN expr_condition THEN statement_list;]...
    [ELSE statement_list;]
END CASE;

例一：使用CASE流程控制语句的第一中格式，判断var值等于1，等于2，或者两者都不等
delimiter //
create procedure proc4()
BEGIN 
    declare var INT default 1; 
    CASE var WHEN 1 THEN select 'var is 1'; 
    WHEN 2 THEN select 'var is 2'; 
    ELSE select 'var is not 1 or 2'; 
    END CASE;
END//
delimiter ;

3. LOOP语句：用于重复执行某些语句

[loop_labe1:]LOOP
statement_list;
END LOOP [loop_labe1];

例一：使用LOOP语句进行循环操作
declare id INT default 0;
add_loop LOOPSET id=id+1; 
IF id>=10 THEN LEAVE add_loop; 
END IF;
END LOOP add_loop;

4. LEAVE语句：跳出循环，即：java中的break

LEAVE lable;

5. ITERATE语句：重新开始下一轮循环：即：java中的continue

ITERATE lable;

例一：
create procedure doiterate()
BEGIN
    declare p1 INT default 0;
    my_loop:LOOP set p1=p1+1; 
    IF p1<10 THEN ITERATE my_loop; 
    ELSEIF p1>20 THEN LEAVE my_loop; 
    END IF; 
    select 'p1 is between 10 and 20';
    END LOOP my_loop;
END

6. REPEAT语句：创建一个带有判断条件的循环语句

[repeat_lable] REPEAT
statement_list;
UNTIL expr_condition;
END REPEAT[repeat_lable]
--expr_condition为真则退出循环

例一：
declare id INT default 0;
REPEAT set id = id + 1; 
until id>=10;
END REPEAT;

7. WHILE语句

[while_lable:] WHILE expr_condition DO
    Statement_list;
END WHILE [while_lable];

例一：
declare i INT default 0;
WHILE i<10 DO set i=i+1;
END WHILE;

存储过程的使用

调用存储过程：CALL关键字

CALL sp_name([parameter[,...]]);

例一：创建存储过程
deltimiter //
create procedure CountProc1(IN s_gender varchar(50), OUT num INT);
BEGIN 
    select count(*) INTO num from student WHERE gender=s_gender;
END //
delimiter ;

例二：使用存储过程
CALL CountProc1('女',@num);

查看存储过程

1. SHOW STATUS语句查看存储过程的状态

SHOW {PROCEDURE或者FUNCTION} STATUS [LIKE 'pattern'];

例一：
--查看当前所有的存储过程
show procedure status;

--查看以2结尾的存储过程
show procedure status like '%2';

2. SHOW CREATE语句，查看存储过程的状态

SHOW CREATE{PROCEDURE或者FUNCTION} sp_name;

--例一：
show create procedure proc4;

3. 从表information_schema.Routines中查看存储过程信息

select * from information_schema.Routines;

修改存储过程

ALTER {PROCEDURE或者FUNCTION} sp_name[characteristic...];

characteristic：
    CONTAINS SQL 表示子程序包含SQL语句，但不包含或写数据的语句
    NO SQL 表示子程序中不包含SQL语句
    READS SQL DATA 表示子程序中包含写数据的语句
    SQL SWCURITY{DEFINER或者INVOKER} 指明谁有权限来执行
    DEFINER 表示只有定义者自己才能执行
    INVOKER 表示调用者可以执行
    COMMENT string 表示注释信息
注意：MySQL中只能修改存储过程的特性，不能修改代码。

例一：修改存储过程countproc1的定义，将读写权限改为MODIFIES SQL DATA，并指明调用中可执行

alter procedure countproc1 MODIFIES SQL DATASQL SECURLTY INVOKER;

删除存储过程

DROP {PROCEDURE或者FUNCTION} [IF EXISTS] sp_name;

例一：删除存储过程CountProc1
drop procedure CountProc1;