巧解如何编写可重用的MySQL查询(1)(3)
四、存储过程
我们知道，存储过程能够起到代码模块化和集中化的作用。然而，将SQL代码放入存储过程本身并不意味着就能提高通用性或者可重用性。举例来说，下面的语句将生成一份反映各员工去年奖金总数的报告：
SELECT e.name,
e.salary,
COUNT(b.bonus_id) AS "Total Bonuses"
FROM employees e
LEFT OUTER JOIN
(SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = 2010) AS b
ON e.id = b.emp_id
GROUP BY e.id;
下面我们将其转变成一个存储过程：
CREATE PROCEDURE `p_2010_bonuses_lst`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
SELECT e.name,
e.salary,
COUNT(b.bonus_id) AS "Total Bonuses"
FROM employees e
LEFT OUTER JOIN
(SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = 2010) AS b
ON e.id = b.emp_id
GROUP BY e.id;
END;
现在，其他人或程序就可以方便的使用这个过程了，不过这里有个时间限制，就是只能在明年之前使用。但是，我们为什么要创建这种有限制的东西呢?因为，我们每年都可能需要生成相似的报告，所以下面我们要去掉这个时间限制。
为此，我们将该过程中的硬编码的日期删除掉，如下所示：
CREATE PROCEDURE `p_yearly_bonuses_lst`(IN `@year` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
SELECT e.name,
e.salary,
COUNT(b.bonus_id) AS "Total Bonuses"
FROM employees e
LEFT OUTER JOIN
(SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = @year) AS b
ON e.id = b.emp_id
GROUP BY e.id;
END;
作为一名有上进心的开发人员，我们会自问是否可以做得更好呢?客户程序也许对起始日期和结束日期方面有更高的灵活性要求，比如他们可能要求日期范围与财政年度一致。从这方面考虑，不管客户程序是否要求，我们的都必须提供一个起始日期和终止日期参数。 MySQL有一个非常不错的BETWEEN运算符，可以用来处理某个范围内的值。
下面我们就将其用于起始日期和终止日期：
CREATE PROCEDURE `p_bonuses_lst`( IN `@StartDate` DATETIME,
IN `@EndDate` DATETIME )
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
SELECT e.name,
e.salary,
COUNT(b.bonus_id) AS "Total Bonuses"
FROM employees e
LEFT OUTER JOIN
(SELECT emp_id,
bonus_id
FROM bonuses
WHERE award_date Between @StartDate AND @EndDate) AS b
ON e.id = b.emp_id
GROUP BY e.id;
END;
五、小结
在本文中，我们讨论了如何利用视图、内建函数和用户定义函数以及存储过程来提高SELECT查询的通用性和可重用性。为了便于理解，我们还给出了一些实例代码，以便帮助读者理解本文讲到的内容。根据局部性原理，现在执行的操作，近期内很可能会再次执行该操作，所以提高可重用性是非常有帮助的。希望大家通过这篇文章的阅读，能够掌握如何编写可重用的MySQL查询的方法，为以后的工作能有所帮助。
