数据库技术及应用第9章 存储过程

➢ 扩展存储过程
➢ 扩展存储过程允许您使用编程语言（例如 C） 创建自己的外部例程。扩展存储过程是指 Microsoft SQL Server 的实例可以动态加载 和运行的 DLL。扩展存储过程直接在 SQL Server 的实例的地址空间中运行，可以使用 SQL Server 扩展存储过程 API 完成编程。
➢ 在对象资源管理器中，依次展开数据库 |Northwind|可编程性，选中存储过程点击鼠 标右键，选择新建存储过程
➢ 系统将在查询编辑器中打开存储过程模版。 在模版中输入存储过程的名称，设置相应的 参数。也可以通过菜单“查询”|“指定模版 参数的值”进行设置
2020/10/27
SQL Server 2005
2020/10/27
SQL Server 2005
19
执行存储过程
➢ 使用EXECUTE语句时应注意以下几点：
➢ EXECUTE语句可以用于执行系统存储过程用户定义 存储过程或扩展存储过程，同时支持Transact-SQL 批处理内的字符串的执行。
➢ 如果EXECUTE语句是批处理今的第一条语句，那么 省略EXECUTE关键字也可以执行该存储过程
➢ CLR
➢ CLR 存储过程是指对 Microsoft .NET Framework 公 共语言运行时 (CLR) 方法的引用，可以接受和返回 用户提供的参数。它们在 .NET Framework 程序集 中是作为类的公共静态方法实现的。
2020/10/27
SQL Server 2005
6
存储过程分类
ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
2020/10/27
SQL Server 2005
15
➢ 只返回单一记录集的存储过程。 ➢ 查询表Categories的内容的存储过程
USE Northwind； GO CREATE PROC GETCATEGORIES AS SELECT * FROM Categories
CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH{ RECOMPILE | ENCRYPTION | RECOMPILE ,
➢ 减少网络流量。用户可以通过发送一个单独的 语句实现一个复杂的操作，而不需要在网络上 发送几百个Transact-SQL代码，这样减少了在 服务器和客户机之间传递的请求的数量
2020/10/27
SQL Server 2005
9
创建存储过程
➢ 在图形界面下创建存储过程
➢ 打开Microsoft SQL Server Manager Studio， 并连接数据库。
ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate Between @Beginning_Date And
@Ending_Date； GO
2020/10/27
SQL Server 2005
18
执行存储过程
➢ 建立一个存储过程以后，可以使用EXECUTE语 句来执行这个存储过程。EXECUTE语句的语法 如下：
London
2020/10/27
SQL Server 2005
23
➢ 执行简单存储过程 ➢ 执行存储过程GETCATEGORIES。
USE Northwind； GO EXECUTE dbo.GETCATEGORIES; GO
10
2020/10/27
SQL Server 2005
11
2020/10/27
SQL Server 2005
12
➢ 指定模版参数的值窗口的前三行分别是创建人、创建时 间、描述，是对存储过程进行注释。从第四行开始，分 别指定存储过程名称、参数名称、数据类型、参数的缺 省值。
2020/10/27
SQL Server 2005
第9章 存储过程
存储过程概述
➢ 在大型数据库系统中，存储过程具有很重 要的作用。存储过程是SQL 语句和流程 控制语句的集合。存储过程在运算时生成 执行方式，所以，以后对其再运行时其执 行速度很快。SQL Server 2005不仅提供 了用户自定义存储过程的功能，而且也提 供了许多可作为工具使用的系统存储过 程。 。
FROM [Order Details] WHERE (OrderID = @orderID) END
➢ 点击工具栏上的执行按钮来创建存储过程，如没有错误， 消息框中则显示“命令已成功完成”。
2020/10/27
SQL Server 2005
14
用SQL语句创建存储过程
➢ 使用CREATE PROCEDURE语句可以创建存储过 程。其语法如下：
3
存储过程分类
➢ Transact-SQL
➢ Transact-SQL 存储过程是指保存的 Transact-SQL 语 句集合，可以接受和返回用户提供的参数。例如， 存储过程中可能包含根据客户端应用程序提供的信 息在一个或多个表中插入新行所需的语句。存储过 程也可能从数据库向客户端应用程序返回数据。例 如，电子商务 Web 应用程序可能使用存储过程根据 联机用户指定的搜索条件返回有关特定产品的信息。
2020/10/27
SQL Server 2005
22
➢ 执行带有通配符参数的存储过程 ➢ 执行创建的存储过程usp_GetEmployees。不指
定参数，则使用默认参数值执行。
USE Northwind;
GO
EXECUTE usp_GetEmployees
GO
结果如下：
LastName FirstName Title
2020/10/27
SQL Server 2005
20
执行存储过程
➢ 虽然可以省略已提供默认值的参数，但只能截断参 数列表。例如，如果—个存储过程有五个参数，可 以省略第四个和第五个参数，但不能跳过第四个参 数而仍然包含第五个参数，除非以“@参数=值”形 式提供参数。
➢ 如果在建立存储过程时定义了参数的默认值，那么 下列情况下将使用默认值：执行存储过程时未指定 该参数的值；将Default关键字指定为该参数的值。
➢ 提供了安全机制。即使是没有访问存储过程引 用的表或视图的权限的用户，也可以被授权执 行该存储过程
2020/10/27
SQL Server 2005
8
存储过程的优点
➢ 改进性能。如果某一操作包含大量的 Transaction-SQL 代码或分别被多次执行，那么 存储过程要比批处理的执行速度快很多。因为 存储过程是预编译的，在首次运行一个存储过 程时，查询优化器对其进行分析、优化，并给 出最终被存在系统表中的执行计划。而批处理 的Transaction- SQL 语句在每次运行时都要进 行编译和优化，因此速度相对要慢一些。
[ { EXEC | EXECUTE } ] { [ @return_status = ] { procedure_name [ ;number ] | @procedure_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ]|[ DEFAULT ]}] [ ,...n ] [ WITH RECOMPILE ] }
@lastname varchar(40) = 'D%', @firstname varchar(20) = '%' AS SELECT LastName, FirstName, Title, Address,City FROM dbo.Employees WHERE FirstName LIKE @firstname
RAISERROR('NULL values are not allowed',14,1) RETURN END SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal,
DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals"
AND LastName LIKE @lastname; GO
2020/10/27
SQL Serveபைடு நூலகம் 2005
17
在存储过程中使用输入参数 创建名为SalesbyYear的存储过程，返回在两个指定日期之间的所有销售额。 CREATE procedure SalesbyYear
@Beginning_Date DateTime, @Ending_Date DateTime AS IF @Beginning_Date IS NULL OR @Ending_Date IS NULL BEGIN
2020/10/27
SQL Server 2005
16
➢ 使用带有通配符参数的简单过程 ➢ 以下存储过程只从视图中返回指定的一些雇员（提供名和姓）及
其职务和部门名称。此存储过程模式与所传递的参数相匹配；或 者，如果未提供参数，则使用预设的默认值（以字母 D 打头的 姓）。
USE Northwind; GO CREATE PROCEDURE usp_GetEmployees
2020/10/27
SQL Server 2005
7
存储过程的优点
➢ 与其他应用程序共享应用程序逻辑，因而确保 了数据访问和修改的一致性。存储过程可以封 装业务功能，在存储过程中可以在同一位置改 变封装的业务规则和策略，所有的客户端可以 使用相同的存储过程来确保数据访问和修改的 一致性
➢ 防止把数据库中表的细节暴露给用户。如果一 组存储过程支持用户需要执行的所有业务功能， 用户就不必直接访问表
➢ 向存储过程传递参数时，如果使用“@参数＝值” 的形式，则可以按任何顺序来提供参数，还可以省 略那些已经提供默认值的参数。一旦以“@参数＝ 值”形式提供了一个参数，就必须按这种形式提供 后面所有的参数。如果不是以“@参数＝值”形式 来提供参数，则必须按照CREATE PROCEDURE语句 中结出的顺序提供参数。
13
➢ 删除掉参数@p2,并编写相应的SQL语句。SQL语句如下：
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT [Order Details].*
2020/10/27
SQL Server 2005
2
存储过程的基本概念
➢ 存储过程（Stored Procedure）是一组为 了完成特定功能的SQL 语句集，经编译 后存储在数据库中，用户通过指定存储过 程的名字并给出参数（如果该存储过程带 有参数）来执行它。
2020/10/27
SQL Server 2005
➢ 如果在存储过程中使用了带Like关键字的参数名称， 则提供的默认值必须是常量，并且可以包含%、_、 []、[^]通配符。
2020/10/27
SQL Server 2005
21
➢ 通过参数名传递值 ➢ 执行创建的存储过程SalesbyYear。
USE Northwind; GO EXECUTE dbo.SalesbyYear @Beginning_Date='1998-5-1', @Ending_Date='1998-5-6'; GO 通过定位传递值 EXECUTE dbo.SalesbyYear '1998-5-1','1998-5-6';
Address
City
--------- --------- -------------------- ----------------- -------
Davolio Nancy Sales Representative 507-20th Ave.E.Apt.2A Seattle
Dodsworth Anne Sales Representative Houndstooth Rd.