SQL Server-存储过程

存储过程

存储过程是使用SQL Server所提供的Transact-SQI语言所编写的程序。SQL Server不仅提供了用户自定义的存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。本篇主要介绍用户自定义存储过程的创建、修改、删除、执行的方法。

 

存储过程的类型

SQL Server中的存储过程划分为4类:

• 系统存储过程。
• 扩展存储过程。
• 用户自定义存储过程。
• 临时性存储过程。

常用的存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统提供的存储过程是系统自动创建的，并以sp_为前缀。在SQL Server中，许多管理活动和信息活动都可以使用系统存储过程来执行：用户自定义存储过程是由用户创建并完成某一特定功能的存储过程，存储在所属的数据库中。

存储过程的特点

使用SQL Server中的存储过程而不使用存储在客户计算机本地的 T-SQL程序的原因主要是存储过程具有以下特点:

(1)允许模块化程序设计

存储过程只需创建一次便可作为数据库中的对象之一存储在数据库中，以后各用户即可在程序中调用该过程任意次。

(2)执行速度更快

存储过程只在第一次执行时需要编译且被存储在存储器内，其他次执行就可以不必由数据引擎逐一再翻译，从而提高了执行速度。

(3)减少网络流量

一个需要数百行Transact-SQL代码的操作通过一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。

(4)可作为安全机制使用

对于没有直接执行存储过程中某个(些)语句权限的用户，也可授予他们执行该存储过程的权限。

(5)减轻操作人员和程序设计者的劳动强度

用户通过执行现有的存储过程，并提供存储过程所需的参数就可以得到所需要的结果而不用接触SQL命令。

SQL Server 应用程序

在使用SQL Server 创建应用程序时，Transact-SQL编程语言是应用程序和SQL Server数据库之间的主要编程接口。使用Transact-SQL程序时，可用两种方法存储和执行程序:

(1)在本地(客户端)创建并存储程序，把此程序发送给SQL Server 执行。

(2)在SQL Server中创建存储过程，并将其存储在SQL Server中；然后SQL Server或客户端调用执行此存储过程。

存储过程的功能

SQL Server中的存储过程与其他编程语言中的过程类似：

(1)可以以输人参数的形式引用存储过程以外的参数。

(2)可以以输出参数的形式将多个值返回给调用它的过程或批处理。

(3)存储过程中包含有执行数据库操作的编程语句，也可调用其他存储过程。

(4)用RETURN向调用过程或批处理返回状态值，以表明成功或失败，以及失败原因。

存储过程的权限

(1)执行Create Procedure语句者，必须是系统管理员、数据库拥有者或数据定义语言管理员角色中的一个成员，或被授予Create Procedure权限的人员。

(2)创建过程者一定拥有过程中所引用的所有对象的权限：UPDATE、INSERT、DELETE、SELECT。

(3)若过程拥有者把执行此过程的权限授予某用户，那么此用户就拥有此过程的执行权限。

 

创建并执行存储过程

1.交互式创建并执行一存储过程

打开SQL Server Management Studio。在“对象资源管理器”中，按序展开“数据库”→jxsk→“可编程性”→“存储过程”节点，右击“存储过程”，在打开的快捷菜单中，选择“新建存储过程”命令。打开存储过程编辑窗口，并包含模板语句。

将模板语句修改为如下所示的SQL语句：

set ansi_nulls on
go
set quoted_identifier on
go
create procedure Pro_Qsinf
  @sno_in char(8) = '02', @sname_out char(8) output,
  @sage_out int output, @dept_out char(10) output
as
begin
set nocount on
  -- SET NOCOUNT { ON | OFF }:不返回计数(表示受 Transact-SQL 语句影响的行数)| 返回计数.
select @sname_out = SN, @sage_out = AGE, @dept_out = DEPT
from S
where SNO = @sno_in
end
go

• 当 SET NOCOUNT 为 ON 时，将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft SQL Server 提供的实用工具执行查询时，在 Transact-SQL 语句（如 SELECT、INSERT、 UPDATE 和 DELETE）结束时将不会在查询结果中显示"nn rows affected"。
• 如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。
• SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。

　　我们应该在存储过程的头部加上SET NOCOUNT ON 这样的话，在退出存储过程的时候加上 SET NOCOUNT OFF这样的话，以达到优化存储过程的目的。

 

单击工具栏中的“!”按钮，保存创建的存储过程。

打开查询编辑器窗口。在查询编辑器窗口中输人下列SQL语句，查询并显示默认学号02和学号为04的学生的姓名和年龄。

declare @sno_in char(8),
        @sname_out char(8),
	@sage_out int,
	@sdept_out char(10)
exec Pro_Qsinf default, @sname_out output, @sage_out output, @sdept_out output
print @sname_out
print @sage_out
print @sdept_out
go
declare @sno_in char(8),
        @sname_out char(8),
	@sage_out int,
	@sdept_out char(10)
select @sno_in = '04'
exec Pro_Qsinf @sno_in, @sname_out output, @sage_out output, @sdept_out output
print @sname_out
print @sage_out
print @sdept_out
go

2.用T-SQL创建并执行一存储过程

2.1 查询并显示某位学生的某个课程的成绩。

create procedure Pro_Qscore
  @sname_in char(8), @cname_in char(10), @score_out tinyint output
as
select @score_out = SCORE from S, C, SC
where S.SNO = SC.SNO and C.CNO = SC.CNO
      and SN = @sname_in and CN = @cname_in
go

declare @sname_in char(8),
        @cname_in char(10),
	@score_out tinyint
select @sname_in = '李思'
select @cname_in = '程序设计'
exec Pro_Qscore @sname_in, @cname_in, @score_out output
print rtrim(@sname_in) + ' = ' + ltrim(str(@score_out))
go

　　

2.2 利用学生姓名查询该生选修的课程名、成绩、以及任课教师姓名。

create procedure Pro_SCT
  @sname_in char(10), @cname_out char(10) output, 
  @score_out int output, @tname_out char(10) output
as
select @cname_out = CN, @score_out = SCORE, @tname_out = TN
from S, SC, C, TC, T
where S.SNO = SC.SNO and C.CNO = SC.CNO and
      SC.CNO = TC.CNO and TC.TNO = T.TNO and
      SN = @sname_in
go

declare @sname_in char(10), @cname_out char(10), @score_out int, @tname_out char(10)
select @sname_in = '张三'
exec Pro_SCT @sname_in,@cname_out output,@score_out output, @tname_out output
print @cname_out
print @score_out
print @tname_out
go

　　

2.3 利用课程名查询选修该课程的学生姓名、系别、成绩，并给出“程序设计”课程的该查询信息。

create procedure Pro_SC
  @cname_in char(10)
as
select SN, DEPT, SCORE
from S, SC, C
where S.SNO = SC.SNO and C.CNO = SC.CNO
      and CN = @cname_in
go

exec Pro_SC '程序设计'
go

　　

2.4 利用教师姓名和课程名检索该教师该任课的课程名、课时数、选课人数、平均成绩、最高成绩，并查询教师“张雪”的“微机原理”课程的情况记录。

create procedure Pro_TC
  @tname_in char(10), @cname_in char(10)
as
select CN, Chour, count(SNO) 选课人数, avg(SCORE) 平均分, max(SCORE) 最高分
from C, SC, T, TC
where C.CNO = SC.CNO and C.CNO = TC.CNO and TC.TNO = T.TNO
      and TN = @tname_in
group by CN, Chour
having CN = @cname_in
go

exec Pro_TC '张雪', '程序设计'
go

　　

修改存储过程

1.交互式修改存储过程

打开SQL Server Management Studio。在“对象资源管理器”中，展开“数据库”→jxsk→“可编程性”→“存储过程”节点，右击存储过程Pro_Qsinf，在弹出的快捷菜单中选择“修改”选项，打开编辑窗口。

把输人变量sno_in的长度改为2;把输出变量sage_out的类型改为tinyint。

2.用ALTER PROCEDURE语句修改存储过程

将存储过程Pro_Qsinf的定义修改为根据学号查询姓名﹑性别、系名。设置默认学号为01。

alter procedure Pro_Qsinf
  @sno_in char(2) = '01', @sname_out char(8) output,
  @ssex_out char(2) output, @dept_out char(10) output
as 
select @sname_out = SN, @ssex_out = SEX, @dept_out = DEPT
from S
where SNO = @sno_in
go

　　

删除存储过程

1.交互式删除存储过程

启动SQL Server Management Studio。在“对象资源管理器”中，展开“数据库”→jxsk→“可编程性”→“存储过程”节点，选择存储过程Pro_Qsinf。右击存储过程 Pro_Qsinf，在弹出的快捷菜单中选择“删除”选项。弹出“删除对象”对话框，单击“确定”按钮，存储过程Pro_Qsinf即被删除。

2.用DROP PROCEDURE语句删除存储过程

drop procedure Pro_Qscore
go