存储过程

要点：

 存储过程的概念

 建立和执行存储过程  存储过程的管理和维护 

存储过程中参数的使用

一、存储过程的概念

存储过程是SQL Server应用中最广泛、最灵活的技术。

在开发SQL Serve应用程序中，应用程序与SQL Server数据库交互操作有两种方法 a) 一种是存储在本地的应用程序向SQL Server发送T-SQL命令，并对返回的数据

进行处理

b) 另一种是在SQL Server上定义某个过程。其中包括一系列的操作，那么以后每

次应用程序只需调用该存储过程即可完成指定操作，这种在SQL Serve中定义

的过程称为存储过程。

二、存储过程的特点

 提高数据库的执行速度：存储过程只在创建时进行编译，以后每次执行存储过

程都不需要重新编译。

 

能实现模块化程序设计：可重复调用并于程序源代码而进行修改和扩展。 减少网络流量：一个需要数百行T-SQL代码的操作，如果将其创建成存储过程，那么一个存储过程的调用语句就可完成，这样可避免在网络上发送数百行代码，从而减少网络负荷 提高系统安全性：管理员可不授予用户访问存储过程中涉及的表的权限，而只授予执行过程的权限，这样用户通过存储过程操纵数据库中的的数据，但对表



所能进行的操作是有的，从而保证了表中数据的安全性

三、存储过程的类型

a) 系统存储过程（system stored procedure）

SQL Server自带的（以sp_开头），具有执行系统存储过程权限的用户可直接调用系统存储过程。当新建一个数据库时，一些系统存储过程会在新建的数据库

中自动创建。

常用系统存储过程如下： i. Sp_help: 用于报告有关数据库对象（master数据中的sysobjects表中列出

的任何对象）、用户定义数据类型或SQL Server所提供的数据类型的信息

ii. Sp_helptext: 用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本

列出有关sysobjects中每个对象的信息

USE master EXEC sp_help

显示employee_insupd触发器的SQL语句文本，该触发器在数据库pubs中

USE pubs

EXEC sp_helptext 'employee_insupd'

b) 扩展存储过程(Extended stored procedure)

即外挂程序，用于扩展SQL Server功能，是可以动态装载并执行的动态链接库（DDL）。扩展存储过程直接在SQL Server的地址空间运行，并使用SQL Server开放式数据服务（ODS）API编程。编写好扩展存储过程后，固定服务器角色sysadmin的成员即可在SQL Server中注册该扩展存储过程，然后授予其他用户执行该过程的权限。

扩展存储过程只能添加到Master数据库中，并在Master数据库中使用，以sp_或xp_开头。

使用系统存储过程xp_cmdshell

Xp_cmdshell扩展存储过程可以让系统管理员以操作系统命令行解释器的方式执行特定的命令，并以文本行方式返回结果集，是一个功能非常强大的扩展存储过程。

默认情况下，只有sysadmin固定服务器角色的成员才能执行这个存储过程。因此，为避免滥用这个存储过程，需要及时设置并保护好sysadmin固定服务器角色的成员口令。 在查询分析器中，执行以下语句

use master

exec xp_cmdshell 'dir c:'

该语句是通过xp_cmdshell扩展存储过程，执行操作系统命令”dir c:”，列出当前操作系统所在C盘上的文件和文件夹清单。

c)

用户定义的存储过程（User-defined stored procedure）

由用户根据实际问题的需要所创建的存储过程，在命令时不要以sp_和xp_开头。

存储过程参数的使用

存储过程通过参数来调用它的程序通信。在程序中调用存储过程时，可以通过输入参数将数据传给存储过程，存储过程可以通过输出参数和返回值将数据返回给调用它的程序 一、 带参数的存储过程

存储过程的参数在创建时应在CREATE PROCEDURE 和AS关键字之间定义，每个参数都

要指定参数名和数据类型。参数名必须以@符号为前缀。用户可以为参数指定默认值，如果调用存储过程时，不为参数传入数据，系统就会将默认值传给输入参数。如果是输出参数应用OUTPUT关键字描述，各个参数定义之间用逗号隔开。具体语法如下： @parameter_name data_type[=default][OUTPUT] 1、 使用输入参数

根据读者的编号，查询读者的当前借录 CREATE PROCEDURE BorrowInfo @ReaderID int AS

SELECT Borrow.图书编号, Books.图书名, Borrow.借阅日期, Borrow.应还日期 FROM Borrow INNER JOIN

Books ON Borrow. 图书编号= Books.图书编号 WHERE Borrow. 读者编号=@ReaderID

调用存储过程的方法： 1） 通过位置传递

这种方法是在执行存储过程的语句中直接给出参数的值。当有多个参数时，给出的参数值的顺序与创建存储过程的语句中的参数顺序一致，即参数传递的顺序就是参数定义的顺序。例如，查询读者编号为2的当前借书信息执行如下： EXECUTE BorrowInfo 2. 2） 通过参数名传递

这种方法是在执行存储过程的语句中，用“参数名=参数值”的形式给出参数值。通过

参数名传递参数的好处是，参数可以按任意顺序给出。 语句如下EXECUTE BorrowInfo 2 @ReaderID=2.

2、 使用输出参数 通过定义输出参数，可以从存储过程中返回一个或多个值，可以从存储过程中返回一个或多个值。定义输出参数需要在参数定义后加OUTPUT。具体语法为： @parameter_name data_type[=default]OUTPUT 根据读者的编号，查询读者到期的图书个数 CREATE PROC GetOverDateNum

@ReaderID int,@BookNum int OUTPUT AS

SELECT @BookNum=COUNT(*) FROM Borrow

Where 读者编号=@ReaderID

AND datediff(day,应还日期,getdate())>0

* datediff 返回跨两个指定日期的日期和时间边界数。 select datediff(day,'2004-09-01','2004-09-18') --返回：17

在程序中调用存储过程，SQL 提供了两种传递参数的方法 1） 通过位置传递参数

USE Library GO

DECLARE @num int

EXEC GetOverDateNum 2,@num OUTPUT SELECT'到期删数是',@num 2) 通过参数名传递 USE Library GO

DECLARE @num int

EXEC GetOverDateNum @ReaderID=1,@BookNum=@num OUTPUT SELECT'到期删数是：',@num

二、 通过RETURE返回参数

在存储过程中除了可以返回输出参数以外，还可以有返回值，用来显示存储过程的执行情况。

根据指定图书编号，查找其是否借出，将其值返回。 CREATE PROCEDURE IsBorrowed @BookID int AS

DECLARE @BookState bit

SELECT @BookState=是否借出 FROM books

WHERE 图书编号 = @BookID RETURN @BookState

调用存储过程并获得返回值: DECLARE @IsBorrowed bit

EXEC @IsBorrowed=IsBorrowed 1 IF @IsBorrowed=0 PRINT '未借出' ELSE

PRINT '已借出'

存储过程的管理和维护

一、 查看存储过程的定义信息

1、 使用企业管理器查看

企业管理器/数据库/存储过程/选择已有的用户存储过程/右键/属性…

2、 使用查询分析器查看

查询分析器/数据库/存储过程/选择用户存储过程/右键/编辑…

二、 修改存储过程

语法：

ALTER PROCEDURE 已创建的存储过程名 AS 修改后的存储过程语句体

修改上例中定义的存储过程GetBookCategory

USE LIBRARY

GO

ALTER PROCEDURE GetBookCategory AS

SELECT 类编号, 类名称 FROM BookCategory ORDER BY 类编号 GO

三、

删除存储过程

1、 用DROP PROC命令删除 USE Library

GO

DROP PROC GetBookCategory GO

2、 用企业管理器删除

企业管理器/数据库/存储过程/选择用户存储过程/右键/删除/“除去对象” 存储过程上机练习

一、创建借书存储过程

1） 根据输入的“读者编号”和“图书编号”判断该书是否可借（图书是否已借出、借

出数量是否超过读者的借出数量上限、该读者是否有过期未还图书的情况） 2） 若读者可借该图书，则办理借书业务-----在Borrow表中保存读者借书信息（借书人、

所借图书、借阅日期和应还日期） 1） 将该图书的状态改为“借出”状态。可由上例中的触发器实现。 3）

1.创建借书存储过程BorrowBook。 CREATE PROC borrowbook @BookID int,@ReaderID int AS

DECLARE @BorrowNum int,@BorrowDureNum int,@borrowDureDays int --根据读者编号，查出该读者当前借书的数量 SELECT @BorrowNum=COUNT(*) FROM Borrow

WHERE 读者编号=@ReaderID

--根据读者编号，得到该读者能借的图书数量和借书期限

SELECT @BorrowDureNum=借书数量,@borrowduredays=借书期限 FROM readcategory,readers

WHERE ReadCategory.种类编号=Readers.种类编号 AND Readers.读者编号=@ReaderID --若书已借出，不能借

IF (SELECT 是否借出 FROM Books WHERE 图书编号=@BookID)=1 BEGIN

PRINT ‘图书已借出，不能借’ RETURN 1 END

--若超过借书数量，不能借

IF @BorrowNum>=@BorrowDurenum BEGIN

PRINT ‘借书数量已满，不能再借’ RETURN 2 END

--若有过期图书，不能借

IF EXISTS(SELECT * FROM borrow WHERE datediff(day,应还日期,getdate())>0 AND 读者编号=@ReaderID)

BEGIN

PRINT ‘有过期未还图书，不能借’ RETURN 3 END

--保存借书信息

INSERT INTO Borrow

VALUES(@BookID,@ReaderID,getdate(),dateadd(day,@borrowduredays,getdate())) IF @@ERROR=0 BEGIN

PRINT ‘图书借阅成功’ RETURN 0 END ELSE BEGIN

PRINT ‘图书借阅失败！’ RETURN 4 END

二、创建还书存储过程

2） 在表BorrowHistory中保存还书信息（图书编号、读者编号、借出日期、应还日期、

还书日期、支付罚金、备注） 3） 从Borrow表中删除此借书信息

4） 将图书的状态改为“流通”状态，可由上例中的触发器实现。

2.创建还书存储过程ReturnBook。 CREATE PROC ReturnBook

@BookID int,@ReaderID int,@ReturnDate datetime,@Fine money=null,@memo text=null AS

--查找有无该借录，若不存在，返回

DECLARE @BorrowDate dateTime,@DueDate datetime

IF NOT EXISTS(SELECT * FROM Borrow WHERE 图书编号=@BookID and 读者编号=@ReaderID) BEGIN

PRINT '无此借录' RETURN 1 END

SELECT @BorrowDate=借阅日期,@DueDate=应还日期 FROM Borrow

WHERE 图书编号=@BookID and 读者编号=@ReaderID --开始事务

BEGIN TRANSACTION

--在数据表BorrowHistory中保存还书信息

INSERT INTO BorrowHistory(图书编号,读者编号,借出日期,应还日期,还书日期,支付罚金,备注) VALUES(@BookID, @ReaderID, @BorrowDate,@DueDate,@ReturnDate,@Fine,@memo) IF @@ERROR<>0 BEGIN

ROLLBACK TRAN PRINT '还书失败' RETURN 1 END

--将该借录从借书表Borrow中删除

DELETE FROM BORROW

WHERE 图书编号=@BookID and 读者编号=@ReaderID IF @@ERROR<>0 BEGIN

ROLLBACK TRAN PRINT '还书失败' RETURN 1 END COMMIT TRAN PRINT '借书成功' RETURN 0