第10章 存储过程

存 储 过 程

练习题10及参考答案

1．什么是存储过程存储过程分为哪几类使用存储过程有什么好处

答：存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL 代码集，它与特定的数据库相关联，存储在SQL Server 服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。

存储过程分为3类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。使用存储过程有如下好处如下：

（1）存储过程提供了处理复杂任务的能力：存储过程提供了许多标准SQL 语言所没有的高级特性，通过传递参数和执行逻辑表达式，能够处理复杂任务。

（2）增强代码的重用性和共享性：每一个存储过程可以在系统中重复地调用，可以被多个有访问权限的用户访问。存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，提高开发的质量和效率。

（3）减少网络数据流量：存储过程是存放在服务器中并在服务器上运行的，应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。所以，使用存储过程可以减少网络中的数据流量。

（4）加快系统运行速度：第一次执行后的存储过程会驻留在内存中，以后可以直接运行，从而加快应用系统的处理速度。

（5）加强系统安全性：SQL Server 可以不授予用户某些表、视图的访问权限，但授第 章

10

予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。从而保证表中数据的安全性。

2．修改存储过程有哪几种方法假设有一个存储过程需要修改但又不希望影响现有的权限，应使用哪个语句来进行修改

答：修改存储过程有两种方法：一种方法是把旧的存储过程删除，然后再重新建立该存储过程。另一种方法是用单个的步骤更改该存储过程。使用前一种方法修改存储过程，所有与该存储过程相关联的权限都将丢失。而使用后一种方法可以更改过程或参数定义，但为该存储过程定义的权限将保留。所以要修改一个存储过程但又不希望影响现有的权限可使用后一种方法。使用的语句为ALTER PROCEDURE。

上机实验题9及操作过程

在上机实验题8的factory数据库上，使用T-SQL语句完成如下各题：

（1）创建一个为worker表添加职工记录的存储过程Addworker。

（2）创建一个存储过程Delworker删除worker表中指定职工号的记录。

（3）显示存储过程Delworker。

（4）删除存储过程Addworker和Delworker。

操作过程

（1）建立存储过程的程序如下：

USE factory

GO

CREATE PROCEDURE Addworker

@no int=NULL,

@name char(10)=NULL,

@sex char(2)=NULL,

@birthday datetime=NULL,

@na char(2)=NULL,

@wtime datetime=NULL,

@depno int=NULL

AS

IF @no IS NULL OR @name IS NULL OR @sex IS NULL OR

@birthday IS NULL OR @depno IS NULL

BEGIN

PRINT '请重新输入该职工信息！'

PRINT '你必须提供职工号、姓名、性别、出生日期、部门号' RETURN

END

BEGIN TRANSACTION

INSERT INTO worker

VALUES(@no,@name,@sex,@birthday,@na,@wtime,@depno) IF @@error<>0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRANSACTION

PRINT '职工'+@name+'的信息成功添加到表worker中'

执行下列语句，可验证存储过程的正确性：

USE factory

GO

Addworker 20,'陈立','女','55/03/08','否','75/10/10',4 GO

SELECT 职工号,姓名,性别,党员否 FROM worker

GO

执行结果如下：

职工陈立的信息成功添加到表worker中职工号姓名性别党员否

---------- ------- ---- ----

1 孙华男是

10 陈涛男否

11 刘欣男否

12 李涵男是

13 王小燕女否

14 李艺女否

15 魏君女否

2 孙天奇女否

20 陈立女否

3 陈明男否

4 李华男否

5 余慧男是

6 欧阳少兵男否

7 程西女是

8 张旗男否

9 刘夫文男是

（2）建立存储过程的程序如下：

USE factory

GO

CREATE PROCEDURE Delworker

@no int=NULL

AS

IF @no IS NULL

BEGIN

PRINT '必须输入职工号！'

RETURN

END

BEGIN TRANSACTION

DELETE FROM worker WHERE 职工号=@no

IF @@error<>0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRANSACTION

PRINT '成功删除职工号为'+CAST(@no AS CHAR(2))+'的职工记录'执行下列语句，可验证存储过程的正确性：

USE factory

GO

Delworker 20

GO

SELECT 职工号,姓名,性别,党员否 FROM worker

GO

执行结果如下：

成功删除职工号为的职工记录

职工号姓名性别党员否

---------- ------- ---- ----

1 孙华男是

10 陈涛男否

11 刘欣男否

12 李涵男是

13 王小燕女否

14 李艺女否

15 魏君女否