MySQL必知必会 | 存储过程、游标、触发器

使用存储过程

存储过程
简单来说就是为了以后的使用而保存的一条或多条MySQL语句的集合。
我觉得就是封装了一组sql语句

为什么需要存储过程（简单来说就是，简单、安全、高性能

• 通过把处理封装在容易使用的单元中，简化复杂操作
• 所有开发人员和应用程序都是用同一存储过i成，则使用的sql语句都是一样的，保证了数据的完整性（防止数据库中存在不正确的数据
• 简化对变动的管理，保证了数据库的安全性（防止数据库中存在不正确的数据
• 提高了性能
• 增加了代码功能的灵活性

执行存储过程

CALL procedure_name(参数);

创建存储过程

CREATE POCEDURE procedure_name()
BEGIN
	SQL语句
END

MYSQL命令行客户机的分隔符
默认的MYSQL语句分割符为 ;
如果希望自定义分隔符，可以使用 DELIMITER关键字

DELIMITER //
CREATE PROCEDURE procedure_name
BEGIN
	SQL 语句
END //

DELIMITER ;

存储过程实际上是一种函数，所以存储过程名后边需要有 ()

删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直到被删除

DROP PROCEDURE procedure_name;

注意，只需要给出存储过程名，不需要加 ()

使用参数

变量
内存中的一个特定的位置，用来临时存储数据

参数的数据类型
存储过程的参数允许的数据类型与表中使用的数据类型相同

变量名
MYSQL中所有变量都必须以@开始

游标

为什么需要游标
MySQL检索操作，返回一组成为结果集的行，有时，需要在检索出来的行中前进或者后退几行，这就需要游标

游标的定义
游标是一个存储在MySQL服务器上的数据库查询，它是被语句查询出来的结果集
在存储了游标以后，应用程序可以根据需要滚动或浏览器中的数据
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改

使用游标

• 使用游标前，需要声明游标，这个过程实际上没有检索数据，只是定义要使用的select语句
• 声明游标以后，需要将它打开来使用，这个过程会使用上一步定义的select语句把数据实际检索出来
• 对于填有数据的游标，根据需要取出各行
• 在结束游标使用时，必须关闭游标

创建游标

CREATE PROCEDURE procedure_name
BEGIN
	DECLARE vernier_name CURSOR
	FOR
	SELECT语句
END

打开和关闭游标

OPEN vernier_name

CLOSE vernier_name

在一个游标关闭后，如果没有重新打开，则不能使用他
但是，声明过的游标，关闭后不需要再次声明

隐含关闭，如果你不明确关闭游标，MySQL会在 END语句使，自动关闭游标

使用游标数据

当一个游标被打开后，可以使用FETCH来访问他的每一行
FETCH指定检索什么数据，检索出来的数据存储在什么地方，还可以向前移动游标中的内部行指针，使下一条FETCH语句检索下一行

DECLARE vernier_name CURSOR
FOR
SELECT语句

OPEN vernier_name

DECLARE O INT
REPEAT
	FETCH vernier_name INTO O
UNTIL condition END REPEAT

CLOSE vernier_name

DECLARE 语句的次序
局部变量 > 游标 > 句柄

触发器

为什么需要触发器
有时需要，某个表发生改动时，自动处理，这种情况下，就需要触发器

MySQL中可以触发触发器的语句
DELETE\INSERT\UPDATE

创建触发器

触发器需要在每个表中唯一命名，但不需要在每个数据库中唯一，即数据库中的两个表可具有相同名字的触发器

CREATE TRIGGER trigger_name AFTER/BEFORE operation ON table_name
FOR EACH ROW 

只有表才支持触发器，视图不支持
触发器按每个表每个事件每次定义，每个表每个事件每次只允许一个触发器
每个表最多支持6个触发器（3种操作 * ( before || after ) = 6
单一触发器不能与多个事件或多个表关联

删除触发器

触发器的类型

• INSERT
  • 在INSERT触发器代码内，可以引用一个名为NEW的虚拟表，访问被插入的行，
  • 在before insert 触发器中， new的值也可以被更新，
  • 对于auto_increment列，new在insert执行前包含0，在INSERT执行后包含新的自动生成值
• DELET
  • 在DELET触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行
  • OLD中的值全部都是只读的，不能更新
• UPDATE
  • 在UPDATE触发器代码中，可以引用OLD虚拟表访问UPDATE前的值，引用NEW虚拟表，访问新更新的值
  • 在BEFORE UPDATE触发器中，NEW中的值可能也被更新
  • OLD中的值全部都是只读的，不能更新

触发器的使用要点

• 创建触发器可能需要特殊的安全访问权限
• 应该用触发器来保重数据的一致性
• 触发器的一种非常有意义的使用是创建审计和跟踪
• 触发器中不能调用存储过程

管理事务处理

COMMIT&ROLLBACK

事务管理

并非所有的引擎都支持事务处理，MyISAM不支持事务处理，InnoDB支持事务处理

事务处理
用于维护数据库的完整性，保证成批的MySQL操作要么完全执行，要么完全不执行

事务：指一组SQL语句
回退：被撤销指定SQL语句的过程
提交：将未存储的SQL语句结果写入数据库
保留点：事务处理中设置的临时占位符，可以对他发布回退

控制事务处理

管理事务处理的关键在于，将SQL语句组分解为逻辑块，并明确规定数据和是应该回退，何时不应该回退

SELECT * FROM table_name;
START TRANSACTION;
DELETE FROM table_name;
SELECT * FROM table_name;
ROLLBACK;
SELECT * FROM order_name;

使用COMMIT

隐含提交：一般的MySQL语句都是直接针对数据库表执行和编写的，即提交操作是自动进行的
隐含事务关闭：当COOMIT和ROLLBACK语句执行后，是误会自动关闭

使用保留点

为了支持回退部分事务处理，可以在事务处理块中的合适位置防止占位符，如果需要回退，可以回退到某个占位符

保留点越多越好：可以在MySQL代码中设置任意多的保留点，保留点越多，你就越能按自己的意愿灵活的进行回退

释放保留点：保留点在事务处理完成后自动释放，也可以使用RELEASE SAVEPOINT明确的释放保留点

更改默认的提交行为

SET autocommit=0;

autocommit标志决定是否自动提交更改，不管有没有COMMIT语句
autocommit针对每个连接，而不是服务器