存储过程
在 SQL Server数据库系统中，存储过程具有很重要的作用，存储过程是 T-SQL语句的集合，它提供了一种高效和安全的访问数据库的方法，经常被用来访问数据和管理被修改的数据，SQL Server 2000不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。本章重点介绍存储过程的概念、使用企业管理器和 T-SQL语句创建和管理存储过程，并介绍存储过程的一些应用技巧。
8.1 存储过程简介
存储过程存放在服务器端数据库中，是经编译过的能完成特定功能的 T-SQL语句的集合，是作为一个单元来处理的。
存储过程在第一次执行时进行语法检查和编译，执行后它的执行计划就驻留在高速缓存中，用于后续调用。存储过程可以接收和输出参数、返回执行存储过程的状态值，还可以嵌套调用。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。
8.1.1 存储过程的优点
存储过程是一种数据库对象，使用存储过程优点如下。
（1）执行速度快，改善系统性能。存储过程在服务器端运行，可以利用服务器强大的计算能力和速度，执行速度快。而且存储过程是预编译的，第一次执行后的存储过程会驻留在高速缓存中，以后直接调用，执行速度很快，如果某个操作需要大量的 T-SQL语句或重复执行，那么使用存储过程比直接使用 T-SQL语句执行得更快。
（2）减少网络流量。用户可以通过发送一条执行存储过程的语句实现一个复杂的操作，而不需要在网络上发送几百条 T-SQL语句，这样可以减少在服务器和客户端之间传递语句的数量，减轻了服务器的负担。
（3）增强代码的重用性和共享性。存储过程在被创建后，可以在程序中被多次调用，而不必重新编写。所有的客户端都可以使用相同的存储过程来确保数据访问和修改的一致性。而且存储过程可以独立于应用程序而进行修改，大大提高了程序的可移植性。
（4）提供了安全机制。如果存储过程支持用户需要执行的所有业务功能， SQL Server可以不授予用户直接访问表、视图的权限，而是授权用户执行该存储过程，这样，可以防止把数据库中表的细节暴露给用户，保证表中数据的安全性。
8.1.2 存储过程的类别
SQL Server支持下列 5种类型的存储过程。
（1）系统存储过程
系统存储过程主要从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。存储在 master数据库中，可以在其他数据库中任意进行调用，由前缀 “sp_”标识。
（2）本地存储过程指在用户数据库中创建的存储过程。
（3）临时存储过程
临时存储过程属于本地存储过程。名字以单个“ #”开始的是局部临时存储过程；名字以两个“##”开始的，是全部临时存储过程。局部临时存储过程只能在单个用户会话中使用，全局临时存储过程可以在所有用户会话中使用。
（4）远程存储过程
指从远程服务器上调用的存储过程。是 SQL Server早期的功能，现在，分布式查询支持该功能。
（5）扩展存储过程
扩展存储过程以在 SQL Server环执行的动态链接库来实现。由前缀“xp_”标识。使用的时候要加载到 SQL Server系统中，使用与存储过程相似的方法来执行。
8.2 创建存储过程
8.2.1 使用企业管理器创建存储过程
1．使用向导创建存储过程在企业管理器中，选择“工具”菜单中的“向导”选项，展开“数据库”文件夹，选择“创建存储过程向导”，如图 8.1所示。
单击“确定”按钮，出现“创建存储过程向导”对话框，如图 8.2所示。在创建存储过程向导的帮助下，可以完成选择存储过程所属数据库；根据存储过程的操作特性，可以生成插入、删除或更新的存储过程；编辑创建存储过程属性的 Transact-SQL语句。
2．在企业管理器中直接创建存储过程在企业管理器中创建存储过程的步骤如下。
（1）启动 SQL Server企业管理器。在控制台树中，依次展开 Microsoft SQL Server、SQL Server组和用户的服务器。
（2）展开“数据库”文件夹，展开要存放存储过程的数据库，然后右击“存储过程”节点，在弹出菜单中选择“新建存储过程”命令，如图 8.3所示。
（3）之后出现“存储过程属性—新建存储过程”对话框，如图 8.4所示。在对话框中将文本框中的[OWNER]和[PROCEDURE NAME]分别替换为存储过程所有者名称和存储过程名称。如果不指定所有者，则默认为 dbo。
图 8.1 “选择向导”对话框图
8.2 “创建存储过程向导”对话框
图 8.3 选择“新建存储过程”的命令图
8.4 “存储过程属性—新建存储过程”对话框
（sql存储过程实例4）在文本框中第二行开始输入存储过程的 T-SQL语句。
（5）可以在存储过程文本输入完后，单击“检查语法”按钮检查用于创建存储过程的 T-SQL语法脚本。如果脚本没有语法错误，将会弹出“语法检查成功！”的消息框。也可以单击“另存为
模板”按钮将所创建的存储过程脚本保存为模板。
（6）单击“确定”按钮，保存存储过程的定义，关闭属性对话框。
8.2.2 使用 Transact-SQL语言创建存储过程
上节在企业管理器中创建存储过程其实仍是基于命令的， SQL Server使用 CREATE PROCEDURE语句用于创建存储过程，语法格式如下：
CREATE PROC[EDURE] [所有者.]存储过程名[;整数]
[{@参数数据类型 }[VARYING][= 默认值][OUTPUT][,...n]
[WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS
SQL语句[,...n]
【说明】
•存储过程名：必须符合标识符规则，且对于数据库及其所有者必须惟一。要创建局部临时存储过程，可以在存储过程名前面加一个“#”字符（ #存储过程名）；要创建全局临时存储过程，可以在存储过程名前面加“##”字符（ ##存储过程名）。完整的名称（包括 #或##）不能超过 128个字符。可以选择是否指定过程所有者的名称。
•;整数：是可选的整数，用来对同名的存储过程分组，以便用一条 DROP PROCEDURE语句即可将同组的过程一起除去。例如：名为 orders的应用程序使用的过程可以命名为 orderproc;1、orderproc;2等。此时 DROP PROCEDURE orderproc语句将可以除去整个组。
•@参数数据类型 [VARYING][= 默认值][OUTPUT]：用于定义存储过程参数的类型和参数的属性。指定的参数除游标参数不限制个数外，其他昀多可以指定 2100个，其中 VARYING指定作为输出参数支持的结果集（仅是适用于游标参数），OUTPUT用来指定该参数是可以返回的，可将信息返回给调用过程。 Text、ntext和 image参数可用做 OUTPUT参数。
•{RECOMPILE |ENCRYPTION | RECOMPILE, ENCRYPTION}：用于定义存储过程的处理方式。RECOMPILE指定每执行一次存储过程都要重新编译，虽然可能降低了执行速度，但也可能有助于数据的昀后处理；ENCRYPTION表示 SQL Server加密 syscomments系统表中包含 CREATE PROCEDUDE的语句。
•FOR REPLICATION：表示该存储过程只能在数据复制时使用。该选项不能与 WITH RECOMPILE一起使用。
8.3 执行存储过程
1．通过存储过程自身执行存储过程
创建存储过程后，可以使用 EXECUTE语句来执行这个存储过程。语法格式如下： [EXEC[UTE]]
{[@整型变量=]存储过程名[;分组标识号]|@存储过程变量 }
[[@参数=]{参量值 |@变量 [OUTPUT]|[DEFAULT]}][,...n]
[WITH RECOMPLILE]
【说明】
•@整型变量：为整型局部变量，用于保存存储过程的返回状态。使用 EXECUTE语句之前，这个变量必须在批处理、存储过程或函数中声明过。
• ;分组标识号：当执行与同名存储过程同组的存储过程时，就要指定该存储过程的分组标识号。
• @存储过程变量：是局部定义的变量，表示存储过程名称。
• @参数:是在创建存储过程时定义的过程参数。调用者向存储过程所传递的参数值由参量值或@变量提供，或者使用 DEFAULT关键字指定使用该参数的默认值。 OUTPUT参数说明指定参数为返回参数。
•WITH RECOMPILE：指定在实行存储过程时重新编译执行计划。
【例 8.1】本例创建一个简单的无参数的存储过程：在 Sales数据库中，创建存储过程 proc_
Employees，查询所有的员工信息。
创建和执行存储过程的脚本内容如下：
USE Sales
GO
CREATE PROC proc_Employees
AS
SELECT * FROM Employees
--执行存储过程
EXEC proc_Employees
【例 8.2】创建一个带有输入参数的存储过程 proc_goods，查询指定员工所进商品信息。创建和执行存储过程的脚本内容如下：
USE Sales
GO
CREATE PROC proc_goods
@员工编号 char(6)='1001'
AS SELECT商品编号 ,商品名称 ,生产厂商,进货价 ,零售价,数量,进货时间
FROM Goods
WHERE 进货员工编号=@员工编号
--执行存储过程，查询 1001号员工所进的商品的信息
EXEC proc_goods @员工编号=default
--或
EXEC proc_goods @员工编号='1001'
【例 8.3】创建一个带有输入和输出参数的存储过程 proc_GNO，查询指定厂商指定名称的商品所对应的商品编号。
USE Sales
GO
CREATE PROC proc_GNO
@商品名称 varchar(20),@生产厂商 varchar(30),
@商品编号 int OUTPUT
AS
SELECT @商品编号 =商品编号
FROM Goods
WHERE 商品名称=@商品名称 AND 生产厂商=@生产厂商
--执行存储过程，查询惠普公司打印机商品编号
DECLARE @商品编号 int
EXEC proc_GNO '打印机', '惠普公司 ',@商品编号 OUTPUT
PRINT '该商品编号为： '+CAST(@商品编号 AS char(6))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。