MySQL中存储过程/触发器/事务

1. 存储过程

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

优点：
1允许模块化程序设计（创建一次多次使用）
2允许更快执行
3减少网络流量
4更好的安全机制

创建存储过程格式：

DELIMITER //
CREATE PROCEDURE 储存名([ IN ,OUT ,INOUT ]?参数名?数据类形...)
BEGIN
SQL语句
END //
DELIMITER ；

 

调用过程:

用call 过程名（ ）

查看所有的存储过程show procedure status;
查看创建的存储过程show create procedure 过程名;
删除过程 drop procedure 过程名

 

In 表示参数从外部传入到里面使用（过程内部使用）
Out 表示参数从过程里边把数据保存到变量中，交给外部使用，所有传入的必须是变量 如果说传入的out变量本身在外部有数据，那么在进入过程之后，第一件事就是被清空,设为null
Inout 数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返回给外部

-------------------------------创建存储过程---------------------------
DELIMITER //
CREATE PROCEDURE addUser
(IN uCode VARCHAR(50),IN uName VARCHAR(20),IN uRole INT,IN sex INT,IN tel VARCHAR(30))
BEGIN
INSERT INTO smbms_user (userCode,userName,userRole,gender,phone)
VALUES(uCode,uName,uRole,sex,tel);
END//
DELIMITER //

查看存储过程 show procedure status;

<insert id="saveUser">
CALL addUser(#{userCode},#{userName},#{userRole},#{gender},#{phone})
</insert>

public int saveUser(
@Param("userCode") String userCode,
@Param("userName") String userName,
@Param("userRole") Integer userRole,
@Param("gender") Integer gender,
@Param("phone") String phone);

public List<User> findUserListPage(String queryUserName,
Integer queryUserRole,
Integer currentPageNo, Integer pageSzie);

public boolean saveUser(String userCode, String userName, Integer userRole,
Integer gender, String phone) {
SqlSession sqlSession = null;
int row = 0; // 受影响的行数
try {
sqlSession = MyBatisUtil.createSqlSession();
row = sqlSession.getMapper(UserMapper.class).saveUser(userCode, userName, userRole, gender, phone);
// 提交事务
sqlSession.commit();
} catch (Exception e) {
if (sqlSession != null) {
sqlSession.rollback();
}
row = 0;
e.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
if (row > 0) {
return true;
}
return false;
}

userService.saveUser("maoge", "毛哥", 1, 2, "123456");

2.触发器

触发器是一种特殊类型的存储过程，它又不同于存储过程，
触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。

触发器是一种与表操作有关的数据库对象，当创建了触发器所在的表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。感觉类似于对表注册的各种回调（6种）函数。

作用：
1.可在写入数据表前，强制检验或转换数据
2.触发器发生错误时，异动的结果会被撤销

格式
DELIMITER //
Create trigger 触发器名字 触发时机 触发事件 on 表 for each
row
Begin
操作的内容
End //
DELIMITER ;

触发对象 ：on 表 for each row 触发器绑定实质是表中的所有行，因此当每一行发生改变的时候，就会触发触发器
触发时机：每张表中对应的行都会有不同的状态，当SQL 指令发生的时候，
都会令行中的数据发生改变，每一行总会有两个状态。操作数据之前(before)，操作数据(after)之后
触发事件:
Mysql中触发器针对的目标是数据发生改变，对应的操作只有（增，删，改）查询不发生数据的改变，
所以查询没有触发事件
注意事项:
一张表中，每一个触发器绑定的触发事件对应的触发器类型只能有一个；
一张表中只能有一个after insert 触发器 因此，一张表中最多的触发器只能有六个

 

创建触发器

在MySQL中，创建触发器语法如下：

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中：

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

 

创建两张表
create table my_goods(
id int primary key auto_increment,
name varchar(20) not null,
inv int
)

create table my_orders(
id int primary key auto_increment,
goods_id int not null,
goods_num int not null)

insert into my_goods values(null,'手机',1000),(null,'电脑',500),(null,'游戏机',100);

DELIMITER //
CREATE TRIGGER a_i_o_t AFTER INSERT ON my_orders FOR EACH ROW
BEGIN
UPDATE my_goods SET inv =inv -new.goods_num WHERE id=new.goods_id;
END
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER b_i_o_t BEFORE INSERT ON my_orders FOR EACH ROW
BEGIN
SELECT inv FROM my_goods WHERE id=new.goods_id INTO @inv;
IF @inv <new.goods_num THEN
INSERT INTO xxx VALUES('xx');
END IF;
END
//
DELIMITER //

 

测试 insert into my_orders values(null,3,5);

3.事务

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin
 transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

事务是保存在项目里的由用户显示设定，存储过程是保存在数据库里的由用户直接调用；

事务的编程语言（java，C++等），存储过程中只有SQL语言。

事务的四大特征(ACID)

• 原子性(A)：事务是最小单位，不可再分，整个事务的所有操作要么全部执行完成，要么失败回滚；
• 一致性(C)：数据库总是从一个一致性状态转为另一个一致性状态；
• 隔离性(I)：事务A和事务B之间具有隔离性，一个事务对数据库所做的修改在最终提交成功之前对其它事务是不可见的；
• 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)，一旦事务提交，其对数据库所做修改将永远保持在数据库之中。