SQL之恋(5)：走近存储过程
软件世界
存储过程(Stored Procedures)是存储在数据库服务器中并且被编译执行的SQL批处理过程。可以说存储过程是关系型数据库的命根子。
一、为什么使用存储过程
把业务逻辑封装在存储过程中起码有如下好处：
●使反映业务逻辑的代码模块化、集中化，使维护管理工作更简易；
●允许不同的客户端应用程序访问存储过程，共享业务逻辑；
●在存储过程执行之前SQL Server会对它进行“伪编译”，执行效率高；
●存储过程一旦运行，就被装入高速缓冲区，以备下次调用，使用存储过程可以减少代码从磁盘装载的过程，提高服务器的工作效率；
●客户端调用存储过程只需要极少的语句，能够有效地降低网络流量；
●在安全方面，存储过程的优势体现在以下两个方面：其一，存储过程是对付来自Web的“SQL注入式”攻击的有效手段；其二，SQL Server允许在存储过程上设定执行权限。
例如，我们不希望车间主任直接查看(SELECT)工资表，但需要车间主任根据所辖员工的工资基数(来自工资表)来计算每月奖金，这时我们可以封杀车间主任对工资表的SELECT权限，同时编写一个存储过程来处理奖金的计算(这个存储过程中有对工资表的SELECT 操作)，并把这个存储过程的执行权限向车间主任开放。
二、另一种声音
有些开发人员对存储过程“不屑一顾”，他们的观点是：由于存储过程中不可避免地使用了大量SQL“方言”，因此使用存储过程会导致数据库在不同的DBMS间迁移时发生困难！
笔者以为，相对于存储过程在系统构架、系统维护、系统效率和系统安全方面的卓越贡献，所谓的“跨DBMS”的问题是不值得深究的，因为绝大多数系统没有跨DBMS的现实需求；即使需要跨DBMS，大型系统依然无法抵挡存储过程的诱惑，开发人员宁愿维护多个DBMS上的存储过程。所以笔者奉劝初学者切不可因噎废食！
三、创建存储过程
SQL Server支持六种存储过程：用户定义存储过程、系统存储过程、扩展存储过程、临时存储过程、全局临时存储过程和远程存储过程，本文只讨论用户定义存储过程的创建和执行。
打开SQL Server的“查询分析器”，登录到SQL Server。输入如下代码，并按F5键执行，就可以在示例数据库中创建存储过程SalesByCategory_TEST，它的作用是：查询1996到1998年中任一年份按产品类别汇总的销售情况。
--第一部分：设定当前数据库
USE NorthWind
GO
--第二部分：删除NorthWind 数据库中的同名存储过程
IF EXISTS(SELECT * FROM sysobjects WHERE ID=OBJECT_ID('SalesByCategory_TEST') AND OBJECTPROPERTY(ID,'IsProcedure')=1)
BEGIN
DROP PROCEDURE SalesByCategory_TEST
END
GO
--第三部分：创建存储过程
CREATE PROCEDURE SalesByCategory_TEST
@CategoryName nvarchar(15), @OrdYear int= 1998，@Msg nvarchar(50) OUTPUT
AS
IF @OrdYear != 1996 AND @OrdYear != 1997 AND @OrdYear != 1998
BEGIN
SELECT @OrdYear = 1998
END
SELECT ProductName，TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND DATEPART(YEAR,O.OrderDate) = @Or dYear
GROUP BY ProductName ORDER BY Product Name
SELECT @Msg=CAST(@OrdYear AS CHAR(4))+' 年度,'+@CategoryName+' 类产品的销售信息汇总。'
RETURN @@ROWCOUNT
GO
四、代码分析
以上代码分为三个部分。
1.第一部分是设定当前数据库为示例数据库NorthWind；
2.第二部分的作用是保证NorthWind 数据库中不存在名为SalesByCatego ry_TEST存储过程。
3.代码的第三部分是创建存储过程，详细分析见上表。
五、调用存储过程
在SQL Server里调用存储过程很简单，主要通过 EXEC 命令。在“查询分析器”里打开新窗口，输入如下代码即可。
DECLARE @COUNT INT
DECLARE @YEAR INT
DECLARE @MSG nvarchar(50)
SELECT @YEAR=1997
--下面四条语句任选一句输入
--提示：注意怎样从存储过程读取“返回值”；怎样使用“输入输出参数”。
EXEC @COUNT=SalesByCategory_TEST 'Condi ments'，@YEAR，@MSG OUTPUT
--提示：DEFAULT关键字让存储过程启用参数的默认值。
EXEC @COUNT=SalesByCategory_TEST 'Condi ments'，DEFAULT，@MSG OUTPUT
--提示：忽略具有默认值的参数，表示让存储过程启用参数的默认值。
EXEC SalesByCategory_TEST 'Condiments'，@YEAR，@MSG OUTPUT
--提示：调用存储过程时指定参数名称，就可以颠倒参数的顺序。
EXEC @COUNT=SalesByCategory_TEST @MSG=@MSG OUTPUT，@CategoryName='Condiments'，@Or dYear=@YEAR
PRINT @COUNT
PRINT @MSG
GO
六、存储过程与INSERT语句结合
在SQL Server里存储过程可以被简单调用，也可以和INSERT 语句结合。上文的调用过程属于前者，下面我们看看怎样实现后者。
--建立临时表 #Temp
CREATE TABLE #TEMP(ProductName nvarchar(40),TotalPurchase money)
--把存储过程返回的结果集插入到临时表中
INSERT INTO #TEMP (ProductName ，TotalPur chase) EXEC SalesByCategory 'Condiments',DE FAULT,''
--显示临时表
SELECT * FROM #TEMP
--删除临时表
DROP TABLE #TEMP
GO
读到这里，朋友们或许会产生几个疑问，存储过程能不能与其他SQL 语句(例如SELECT和UPDATE)结合？SQL Server是否提供事件机制，自动触发存储过程？这些问题的答案你将在下期文章中找到，解决方法就是存储过程的“变体”──用户定义函数和触发器。