MySql——过程的创建和使用——六月十六

过程

过程（又称存储过程）是事先编译好存储在数据库中的一组 SQL 的集合，调用过程可以简化应用程序开发人员的工作，减少与数据库服务器之间的通信，对于提升数据操作的性能也是有帮助的。其实迄今为止，我们使用的 SQL 语句都是针对一个或多个表的单条语句，但在实际开发中经常会遇到某个操作需要多条 SQL 语句才能完成的情况。例如，电商网站在受理用户订单时，需要做以下一系列的处理。

1. 通过查询来核对库存中是否有对应的物品以及库存是否充足。
2. 如果库存有物品，需要锁定库存以确保这些物品不再卖给别人， 并且要减少可用的物品数量以反映正确的库存量。
3. 如果库存不足，可能需要进一步与供应商进行交互或者至少产生一条系统提示消息。
4. 不管受理订单是否成功，都需要产生流水记录，而且需要给对应的用户产生一条通知信息。

我们可以通过过程将复杂的操作封装起来，这样不仅有助于保证数据的一致性，而且将来如果业务发生了变动，只需要调整和修改过程即可。对于调用过程的用户来说，过程并没有暴露数据表的细节，而且执行过程比一条条的执行一组 SQL 要快得多。

下面的过程实现了查询某门课程的最高分、最低分和平均分。

drop procedure if exists sp_score_stat;
--使用 `DROP PROCEDURE IF EXISTS` 语句检查并删除已存在的同名存储过程（如果存在）。
delimiter $$

create procedure sp_score_stat(
--使用 `CREATE PROCEDURE` 语句创建存储过程，并指定参数和返回值。
	courseId int, 
	out maxScore decimal(4,1), 
	--输出参数 maxScore，其数据类型为 decimal(4,1)。
	--decimal(4,1) 表示一个带有小数的固定精度数字，具体含义如下：
    --4 表示该数字的总位数，包括小数位和整数位。
    --1 表示小数位的位数。
	out minScore decimal(4,1),
	out avgScore decimal(4,1)
)
begin
	select max(score) into maxScore from tb_record where cou_id=courseId;
	/*在存储过程的定义中，输出参数可以通过 `INTO` 关键字将查询结果赋值给该参数，
	然后在调用存储过程时，可以通过传入相应的变量来接收输出参数的值。*/
	select min(score) into minScore from tb_record where cou_id=courseId;
	select avg(score) into avgScore from tb_record where cou_id=courseId;
end $$

delimiter ;

说明：在定义过程时，因为可能需要书写多条 SQL，而分隔这些 SQL 需要使用分号作为分隔符，如果这个时候，仍然用分号表示整段代码结束，那么定义过程的 SQL 就会出现错误，所以上面我们用delimiter $$将整段代码结束的标记定义为$$，那么代码中的分号将不再表示整段代码的结束，整段代码只会在遇到end $$时才会执行。在定义完过程后，通过delimiter ;将结束符重新改回成分号（恢复现场）。

以上代码是一个示例的存储过程，名为 sp_score_stat，用于统计指定课程的最高分、最低分和平均分。

首先，使用 DROP PROCEDURE IF EXISTS 语句检查并删除已存在的同名存储过程（如果存在）。

然后，使用 DELIMITER 命令将定界符设置为 $$，以便在存储过程体中使用分号 (;)。

接着，使用 CREATE PROCEDURE 语句创建存储过程，并指定参数和返回值。

在存储过程体中，使用三个独立的 SELECT 语句来计算最高分、最低分和平均分，将结果分别存储到 maxScore、minScore 和 avgScore 的输出参数中。

最后，使用 DELIMITER 命令将定界符恢复为原来的值 ;。

这样，存储过程 sp_score_stat 就被创建了。通过传入课程ID参数，可以调用存储过程并获取最高分、最低分和平均分的统计结果。

要调用这个存储过程并获取结果，可以使用以下语句：

SET @maxScore = 0;
SET @minScore = 0;
SET @avgScore = 0;
CALL sp_score_stat(1, @maxScore, @minScore, @avgScore);
SELECT @maxScore, @minScore, @avgScore;

这个示例中，假设我们传入课程ID为1，然后通过调用存储过程 sp_score_stat，将结果存储在变量 @maxScore、@minScore 和 @avgScore 中。最后，使用 SELECT 语句显示这些变量的值。

请注意，存储过程中的参数和变量名前面需要使用 @ 符号进行声明和引用，以便与普通的 SQL 语句中的标识符区分开来。