mysql 存储过程数据 mysql8.0存储过程

8.1 存储程序

存储程序：
预先在数据库服务器端存储SQL命令/语句，并且过后能在数据库服务器端被执行的数据库对象。

存储程序的主体：
存储程序定义的主体除了常规的SQL语句外，通常还使用变量声明、选择、循环和复合语句等。

使用存储程序：
利用CALL语句等方式使用存储程序。

存储程序的分类：
存储例程：和一个数据库相关，可以根据名字调用
触发器：和一个表相关，并在该表产生特殊事件时被触发
事件：和一个数据库相关，代表了由MySQL服务器的事件调度器在特定时刻调度执行的任务

8.1.1 存储例程

存储例程：存储例程是存储在服务器端的SQL语句集合，能够用存储例程名字复用相应的代码
经常用于提高效率和安全性。

1. 减少在服务器和客户端之间的数据传输。
2. 对储存例程的授权管理更易于结合应用系统安全性
3. 存储例程很适合记录日志

存储过程PROCEDURE与存储函数FUNCTION
区别：过程没有返回值，通过CALL调用；函数通过return返回值

但是存储过程也能返回“值”甚至结果集。

1. 允许使用输出类型的参数来传递值
2. 允许使用select语句，这样可以返回结果集。

8.1.2 触发器

触发器是数据库的命名对象，与一个表相关联，并且在该表的INSERT，UPDATE，DELETE等更改操作前后被触发。

定义触发器：
where 在哪张表
when 在什么时候，即由什么操作触发
what 被触发时执行什么SQL语句。

触发器的典型应用：

1. 实现自定义完整性约束
   例如一位教师在一个学期最多只能承担三门课程
2. 用于值的计算
   例如订单明细发生改变时，重新计算订单金额并更新相关表中的相关数据
3. 日志或副本记录
   可确保系统跟踪并审计“时变”数据

8.1.3 事件

和一个数据库相关，代表了由MySQL服务器的事件调度器在特定时刻调度执行的任务。

定义事件的要素：

1. 事件的时刻属性：
   在某时刻仅执行一次
   按照时间间隔周期性执行多次
2. 事件的任务属性：
   要执行的SQL语句

事件的典型应用：

1. 更新汇总报告
2. 清理过期失效的数据
3. 归档、备份数据

8.2 创建和调用存储过程

使用CREATE PROCEDURE语句创建存储过程。
如，创建一个存储过程，用于备份表记录到备份表中。

CREATE PROCEDURE backup()
INSERT INTO t_bak SELECT * FROM t;

用CALL调用存储过程。

mysql>CALL backup();
Query OK,3 rows affected(0.00 sec)

存储过程的处理需要多条语句的：
使用BEGIN-END
如，归档：备份表记录到备份表中后，删除原表记录。

CREATE PROCEDURE backup1()
BEGIN
	INSERT INTO t_bak SELECT * FROM t;
	DELETE FROM t;
END

使用DELIMITER语句界定符

mysql> DELIMITER //
mysql> CREATE PROCEDURE backup1()
    -> BEGIN
    -> INSERT INTO t_bak SELECT * FROM t;
    -> DELETE FROM t;
    -> END//
Query OK, 0 rows affected (0.01 sec)

8.2.1 存储过程的参数模式

存储过程的参数类型可以是MySQL的有效数据类型，参数有IN、OUT、INOUT三种。

存储过程的IN参数
例如，要求存储过程备份那些主键字段值小于给定值的记录。

CREATE PROCEDURE backup3(n int)
BEGIN
	INSERT INTO t_bak SELECT * FROM t WHERE id<=n;
	DELETE FROM t WHERE id<=n;
END

存储过程的OUT参数
例如修改backup3，使之传回本次备份的数据数

CREATE PROCEDURE backup3(n int, OUT record_count INT)
BEGIN
	INSERT INTO t_bak SELECT * FROM t WHERE id<=n;
	SELECT COUNT(*) INTO record_count FROM t WHERE id<=n;
	# 返回存储过程的结果集
	DELETE FROM t WHERE id<=n;
END

8.2.2 存储过程的安全上下文

有权执行某个存储过程的用户在执行存储过程时，存储过程中的SQL语句的执行，按定义者（默认）或调用者的权限进行检查。

定义者：默认是执行CREATE PROCEDURE 语句的用户，也可以用DEFINER子句指定另外的用户名
调用者：执行CALL语句的用户

8.3 创建和调用存储函数

用CREATE FUNCTION 来创建存储函数

CREATE FUNCTION sp([func_parameter[,...]]) RETURNS type
// 存储函数必须说明返回值的类型
routine_body //存储函数必须有return 返回值

func_parameter：parameter name //参数视作in参数

函数调用：
直接使用函数名+参数调用，类似SQL内置函数，存储函数的调用可以出现在很多位置。

实例：

CREATE FUNCTION backup3(n int) RETURNS int
BEGIN
	DECLARE record_count int;
	INSERT INTO t_bak SELECT * FROM t WHERE id<=n;
	SELECT COUNT(*) INTO record_count FROM t WHERE id<=n;
	# 返回存储过程的结果集
	DELETE FROM t WHERE id<=n;
	RETURN record_count;
END

8.4 存储过程和存储函数的共性

存储例程特性的说明：

characteristic:
| [NOT] DETERMINISTIC
| {NO SQL| CONTAINS SQL| READS SQL DATA| MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}

存储例程的确定性：
[NOT] DETERMINISTIC，确定性，即给定同样的输入参数是否总能得到相同的输出。例如内置函数NOW()就是不确定性的。

默认选项是NOT DETERMINISTIC，该选项会对优化产生影响，因为如果是确定性的，那么MySQL可能会使用缓存等优化手段。

存储例程的数据访问特性：

| {NO SQL| CONTAINS SQL| READS SQL DATA| MODIFIES SQL DATA}，默认为CONTAINS SQL

NO SQL：存储例程不包含SQL语句
CONTAINS SQL：存储例程不包含读或写的SQL语句
READS SQL DATA：存储例程仅读取数据，例如SELECT，但是不能写数据
MODIFIES SQL DATA：存储例程可以写数据，如INSERT

8.5 存储例程的维护管理

8.5.1 查看存储例程

SHOW PROCEDURE STATUS [like_or_where]
SHOW FUNCTION STATUS [like_or_where]

例如：

mysql>SHOW PROCEDURE STATUS LIKE 'backup'\G
// 不使用分号结尾而是反斜杠+G，使结果以纵向方式输出方便查看

mysql>SHOW CREATE PROCEDURE 'backup'\G
// 与上文的区别在于会多返回创建存储例程时的语句。

8.5.2 删除存储例程

DROP PROCEDURE [IF EXISTS] proc_name;
DROP FUNCTION [IF EXISTS] func_name;

存储例程删除后不可恢复，删除不存在的存储例程会报错。

8.5.3 修改存储例程

如何修改存储例程的定义？先删除已创建的存储例程，然后重新定义新的存储例程。

如何修改存储例程的特性：使用ALTER语句修改

ALTER PROCEDURE proc_name [characteristic...];
ALTER FUNCTION func_name [characteristic...]

characteristic:
COMMENT 'string'
| LANGUAGE SQL
| {NO SQL| CONTAINS SQL| READS SQL DATA| MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}

授权执行存储例程：
用GRANT语句授权用户执行存储例程。
用户执行存储例程需要存储例程对象上的EXECUTE权限

GRANT EXECUTE ON [{PROCEDURE | FUNCTION }]
	{*.*| db_name.* |db_name.routine_name} TO user

db_name.routine_name:授权指定的数据库对象.存储例程名给指定对象
db_name.* ：使用通配符，在数据库层面将数据库内所有存储例程都授权给用户
*.* :全局权限，用户可以执行服务器上的所有数据库的所有存储例程。

8.5.4 在存储程序中使用游标

存储程序中对结果集每行记录依次处理，需要使用游标（CURSOR）。

游标的作用
• 在存储程序中编程访问SELECT所返回结果集
• 方便逐行访问并对每行记录完成相应的处理

游标的使用

1. 先声明：DECLARE CURSOR和DECLARE HANDLER
2. 后使用
   OPEN，使用游标必须先显式打开游标
   FETCH，提取当前行记录字段值
   CLOSE，最后关闭游标

8.5.5 在存储过程中使用事务

MySQL默认在每一条SQL语句执行后都自动提交（事务），也允许在存储过程中使用显式地事务控制。

事务控制原则
• 根据需要手工启动事务
• 根据处理情况（成功时）提交事务或（失败时）回滚事务

常用的事务控制语句
• START TRANSACTION ——用于启动事务
• COMMIT ——用于提交事务
• ROLLBACK ——用于回滚事务

实例：

用存储过程transfer实现转账功能，其中包括事务处理。

CREATE PROCEDURE transfer(account_from INT, account_to INT, amount INT, OUT status INT)
MODIFIES SQL DATA
BEGIN
	DECLARE account_from_balance INT;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET status=-1; END;
	START TRANSACTION;
	UPDATE bank_account SET Balance = Balance + amount WHERE Account_Id = account_to;
	SELECT balance INTO account_from_balance FROM bank_account WHERE Account_Id = account_from;
	IF account_from_balance < amount THEN
		ROLLBACK; SET status = -1;
	ELSE
		UPDATE bank_account SET Balance = Balance - amount WHERE Account_Id = account_from;
		COMMIT; SET status = 0;
	END IF;
END

8.6 触发器相关

触发器是数据库的命名对象，与一个表相关联，并且在该表的INSERT，UPDATE，DELETE等更改操作前后被触发。

使得适合让表的增删改操作接受一定的强制性规则。

8.6.1 创建触发器

CREATE TRIGGER trigger_name 
// 创建触发器
{ BEFORE | AFTER }{INSERT | UPDATE| DELETE}
// 触发的时机与事件
ON tb_name FOR EACH ROW
// 触发器关联的表
trigger_body
//触发器主体定义

触发器示例1：将无效成绩“舍入”到有效成绩

CREATE TRIGGER valid_score_before_update_choose
BEFORE UPDATE
ON choose FOR EACH ROW
BEGIN
	IF New.Score < 0 THEN SET New.Score = 0;
	ELSEIF New.Score > 100 THEN SET New.Score = 100;
	END IF;
END

触发器示例2：拒绝无效成绩

CREATE TRIGGER valid_score_before_update_choose
BEFORE UPDATE
ON choose FOR EACH ROW
BEGIN
	IF New.Score < 0 Or New.Score > 100 THEN
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT ='Score must be in [0, 100]';
	END IF;
END

触发器示例3：使用触发器保证每位教师最多开设3门课程

CREATE TRIGGER teacher_courses_constraint
BEFORE INSERT
ON course FOR EACH ROW
BEGIN
	IF (SELECT COUNT(*) FROM course WHERE Teacher_id = New.Teacher_id) >= 3 THEN
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'at most 3 courses for a teacher';
	END IF;
END

8.6.2 查看触发器的定义

查看触发器列表
SHOW TRIGGERS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
Trigger: valid_score_before_update_choose
Event: UPDATE
Table: choose
Statement: BEGIN
IF New.Score < 0 Or New.Score > 100 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Score must be in [0, 100]';
END IF;
END
Timing: BEFORE
......

查看定义存储例程的CREATE语句

mysql> SHOW CREATE TRIGGER valid_score_before_insert_choose\G
*************************** 1. row ***************************
Trigger: valid_score_before_update_choose
......
SQL Original Statement: CREATE DEFINER=`root`@`localhost`
TRIGGER valid_score_before_insert_choose
BEFORE UPDATE
ON choose FOR EACH ROW
BEGIN
IF New.Score < 0 Or New.Score > 100 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Score must be in [0, 100]';
END IF;
END
......

8.6.3 删除触发器

使用DROP TRIGGER语句删除触发器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

如：DROP TRIGGER IF EXISTS valid_score_before_update_choose;

注意：
修改表名后，该表上的触发器仍然有效；
删除表后，该表上创建的触发器会自动被删除。

8.7 事件相关

事件是指在MySQL事件调度器的调度下，在特定的时刻所执行的任务，因此也称为调度事件。

事件调度器配置：
全局变量event_scheduler代表事件调度器的状态

SHOW VARIABLES LIKE 'event_scheduler';//查看事件调度器
//其值可以为ON、OFF或DISABLED，代表启动、停止和禁用。

可使用命令行参数或my.ini配置’event_scheduler’=DISABLED，这样事件调度器将被禁用且运行时不可改变状态

而未配置为DISABLED 时，可以通过：

SET GLOBAL event_scheduler = ON（或OFF）

在运行时启动或停止事件调度器。

8.7.1 事件的概念和基本属性

事件也是一种存储程序，是事件调度器按照时间和间隔为依据，调度执行事件的任务代码。
事件也有和其它存储程序相似的属性：

存储程序的共性属性：
名称、属于哪个数据库、要执行的SQL语句。

和触发器相似的属性：
定义者（类似于触发器，有定义者，没有调用者）

事件所特有的属性：
调度的时间和周期（类似于触发器的触发事件）

调度的时间和周期：

1. 在什么时间调度
   • 仅调度一次的任务在什么时间
   • 重复调度的事件，首次调度在什么时间
2. 每隔多长时间重复调度
   是否需要在某个时间后就不再重复调度
3. 过期的事件是否要自动删除

8.7.2 创建事件

使用CREATE EVENT语句创建事件

CREATE EVENT event_name
// 创建事件
ON SCHEDULE
// 调度事件的时机
{ AT time_spec 
// 一次性事件的时刻
| EVERY interval [STARTS time_spec] [ENDS time_spec] }
// 重复事件的周期和始终
[ ON COMPLETION [NOT] PRESERVE ]
// 完成后是否保留
[ ENABLE | DISABLE ]
// 创建时启用还是禁用
DO event_body;
// 事件要执行的SQL

示例1：定义一个一次性事件，在一分钟后备份表。

CREATE EVENT event_backup
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO INSERT INTO t_bak SELECT * FROM t;

解释：
• 在默认数据库中创建一个事件，名为event_backup
• 事件是一个一次性事件（使用了AT子句）
• 事件在当前时间（CREATE EVENT语句执行时）后1分钟被调度
• 事件接受两个默认选项：创建后启用，完成后不予保留
• 事件完成的任务是DO后面的一条语句

示例2：定义一个重复性事件，在每天1点定时备份表。

CREATE EVENT daily_backup
ON SCHEDULE
EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 HOUR
DO INSERT INTO t_bak SELECT * FROM t;

解释：
• 在默认数据库中创建一个事件，名为daily_backup
• 事件是一个重复性事件（使用了EVERY子句）
• 事件在的运行周期为1天（EVERY 1 DAY）
• 起始时间是当天1点（STARTS CURRENT_DATE + INTERVAL 1 HOUR）
• 事件将持续循环（没有使用ENDS指定终止时间）

8.7.3 查看、修改与删除事件

查看事件列表
mysql> SHOW EVENTS\G

查看定义事件的CREATE语句
mysql> SHOW CREATE EVENT daily_backup\G

使用ALTER EVENT语句
• 不需要先删除后重新创建
• 语句中的成分和CREATE EVENT非常相似
• 增加了RENAME TO子句，用于修改事件名称

举例：
ALTER EVENT daily_backup
ON SCHEDULE
EVERY 1 WEEK STARTS CURRENT_DATE + INTERVAL 2 HOUR
RENAME TO weekly_backup

使用DROP EVENT语句删除事件
• DROP EVENT [IF EXISTS] [schema_name.]event_name

举例：
DROP EVENT IF EXISTS weekly_backup;