MySQL存储过程 事务 简介及两者之间的关系

存储过程 Stored Procedure

定义

MySQL中存储过程与存储对象等的关系

概要定义

红框翻译：

This chapter discusses stored database objects that are defined in terms of SQL code that is stored on the server for later execution.
本章讨论在SQL模式中定义的数据库存储对象，存储对象存储在服务器上，用于后续执行。

Stored procedure: An object created with CREATE PROCEDURE and invoked using the CALL statement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the client program.
存储过程： 使用CREATE PROCEDURE创建，使用CALL语句调用。存储过程没有返回值，但是调用者可通过修改参数以用于后续检查。存储过程也可以返回结果集给客户端程序。

注意：

存储过程没有返回值， 可理解为:虽然没有 return 语句

存储过程也可以返回结果集给客户，可理解为:仍然会有数据返回给调用者（客户端）

测试：

执行之后的结果：

一般定义

红框翻译:

Each stored program contains a body that consists of an SQL statement. This statement may be a compound statement made up of several statements separated by semicolon (😉 characters. For example, the following stored procedure has a body made up of a BEGIN … END block that contains a SET statement and a REPEAT loop that itself contains another SET statement:
每个存储程序都包含一个有SQL语句组成的主体。这些语句可以是组合语句，组合语句之间用分号（；)分割。例如，下面的存储过程包含了一个由BEGIN…END语句块组成的主体，里面有SET语句，也有REPEAT语句。

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

事务 Transaction

定义

transaction
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
Database transactions, as implemented by InnoDB, have properties that are collectively known by the acronym ACID, for atomicity, consistency, isolation, and durability.
See Also ACID, commit, isolation level, lock, rollback.

以上摘自MySQL8官方手册—>MySQL Glossary

翻译：
事务是一项任务的原子单元，可以被提交或者回滚。当一个事务对数据库进行了多处改变，当事务提交时，所有的改变都要成功，事务回滚时，所有的改变都会撤销。
数据库事务由InnoDB实现，事务具有ACID属性，原子性，一致性，隔离性，和持久性。

相关关键词

• START TRANSACTION / BEGIN 开启一个新事务

注意：这里的BEGIN和存储过程中的BEGIN…END块不一样的

演示

测试表数据

START TRANSACTION 启动事务

START TRANSACTION;
-- BEGIN;
INSERT INTO dept_inf(NAME,REMARK) VALUES('BEGIN','...END');

INSERT INTO dept_inf(NAME2,REMARK2) VALUES('BEGIN','...END');

SELECT * FROM dept_inf;
COMMIT;

验证：查看数据库表，是否有数据插入？

使用BEGIN开启一个新事务，与START TRANSACTION一致，演示略。。。

存储过程与事务的关系

• 存储过程与事务本质上是相互独立的两个东西，不是一个维度的（参见上面的定义）

• 事务中开启事务的关键字BEGIN与存储过程中的BEGIN…END块，不能混淆，各是各的

• 当然可以在存储过程中，添加一个事务进去,见下面演示：

演示

表中初始数据，如下：

带事务的存储过程：

CREATE DEFINER=`root`@`%` PROCEDURE `my_proced`()
BEGIN

START TRANSACTION;
INSERT INTO dept_inf(NAME,REMARK) VALUES('测试BEGIN','...END');

INSERT INTO dept_inf(NAME2,REMARK2) VALUES('测试BEGIN','...END');

SELECT * FROM dept_inf WHERE ID=1;

COMMIT;

SELECT * FROM dept_inf WHERE ID=2;

END

执行存储过程

遗留问题

事务

START TRANSACTION;
-- BEGIN;
INSERT INTO dept_inf(NAME,REMARK) VALUES('BEGIN','...END');

INSERT INTO dept_inf(NAME2,REMARK2) VALUES('BEGIN','...END');

SELECT * FROM dept_inf;
COMMIT;

连续执行2次或2次以上，只有第一次，数据库表才不会插入数据。第2次及以后都会插入数据。

关闭后，重新打开，有会出现同样的情况，第一次不插入数据，后面的插入数据

待解决上面遗留问题