资源简介

(共52张PPT)
网站数据库应用技术SQL Server 2008
（项目教程）
项目十一 存储过程
创建存储过程
任务一
任务二
项目十一 存储过程
创建带参数的存储过程
任务1 创建存储过程
11.1.1 相关知识
在SQL Server 2008中存储过程和触发器是两个重要的数据库对象。使用存储过程，可以将Transact-SQL语句和控制流语句预编译到集合并保存到服务器端，它使得管理数据库、显示关于数据库及其用户信息的工作更为容易。
Transact-SQL语句是应用程序与SQL Server数据库之间的主要编程接口，大量的时间将花费在Transact-SQL语句和应用程序代码上。在很多情况下，许多代码被重复使用多次，每次都输入相同的代码不但繁琐，更由于在客户机上的大量命令语句逐条向SQL Server发送将降低系统运行效率。因此，SQL Server提供了一种方法，它将一些固定的操作集中起来由SQL Server数据库服务器来完成，应用程序只需调用它的名称，将可实现某个特定的任务，这种方法就是存储过程。
1. 存储过程概述
SQL Server中T-SQL语言为了实现特定任务而将一些需要多次调用的固定的操作编写成子程序并集中以一个存储单元的形式存储在服务器上，由SQL Server数据库服务器通过子程序名来调用它们，这些子程序就是存储过程。
存储过程是一种数据库对象，存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行，具有很强的编程功能。存储过程可以使用EXECUTE语句来运行。
加快系统运行速度存储程序只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。
封装复杂操作当对数据库进行复杂操作时（如对多个表进行更新，删除时），可用存储过程将此复杂操作封装起来与数据库提供的事务处理结合一起使用。
实现代码重用可以实现模块化程序设计，存储过程一旦创建，以后即可在程序中调用任意多次，这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。
增强安全性可设定特定用户具有对指定存储过程的执行权限而不具备直接对存储过程中引用的对象具有权限。可以强制应用程序的安全性，参数化存储过程有助于保护应用程序不受SQL注入式攻击。
减少网络流量因为存储过程存储在服务器上，并在服务器上运行。一个需要数百行T-SQL代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码，这样就可以减少网络流量。
使用存储过程好处：
2. 存储过程的分类
(1)系统存储过程
在SQL Server
2008中内置了许多存储过程，它们有时也被称为系统存储过程。
系统存储过程被放在master数据库中，但是仍可以在其它数据库中对其进行调用
类型 描述
活动目录存储过程 用于在Windows的活动目录中注册SQL Server实例和SQL Server数据库
目录访问存储过程 用于实现ODBC数据字典功能，并且隔离ODBC应用程序，使之不受基础系统表更改的影响
游标过程存储 用于实现游标变量功能
数据库引擎存储过程 用于SQL Server数据库引擎的常规维护
数据库邮件SQLMail存储过程 用于从SQL Server实例内执行电子邮件操作
数据库维护计划存储过程 用于设置管理数据库性能所需的核心维护任务
分布式查询存储过程 用于实现和管理分布式查询
全文搜索存储过程 用于实现和查询全文索引
日志传送存储过程 用于配置、修改和监视日志传送配置
自动化存储过程 用于在Transact-SQL批处理中使用OLE自动化对象
通知服务存储过程 用于管理Microsoft SQL Server 2008系统通知服务
复制存储过程 用于管理复制操作
安全性存储过程 用于管理安全性
Porfile存储过程 在SQL Server代理用于管理计划的活动和事件驱动活动
Web任务存储过程 用于创建网页
XML存储过程 用于XML文本管理
(2)用户存储过程
用户存储过程是指用户根据自身需要，为完成某一特定功能，在用户数据库中创建的存储过程
存储过程可以接受输入参数、向客户端返回表格或者标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML），然后返回输出参数。在SQL Server 2008中，用户定义的存储过程有两种类型：Transact-SQL或者CLR
(3)扩展存储过程
扩展存储过程以在SQL Server环境外执行的动态链接库（DLL，Dynamic-Link Libraries）来实现。扩展存储过程通过前缀“xp_”来标识，它们以与存储过程相似的方式来执行。
扩展存储过程就是保存在动态链接库（DLL）中从动态链接中执行的C++代码。在多数扩展存储过程与其他系统存储过程一起执行，因此它们很少单独使用，下面列出了２个可以单独使用的扩展存储过程：
3. 创建存储过程语法
（1）创建存储过程的规则
在设计和创建存储过程时，应该满足一定的约束和规则。只有满足了这些约束和规则才能创建有效的存储过程。
可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。
可以在存储过程内引用临时表。
如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失。
如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。
如果执行对远程SQL Server 2008实例进行更改的远程存储过程，则不能回滚这些更改，而且远程存储过程不参与事务处理。
存储过程中的参数的最大数目为2100。
存储过程中的局部变量的最大数目仅受可用内存的限制。
根据可用内存的不同，存储过程最大可达128MB。
（2）存储过程的语法
使用CREATE PROCEDURE语句创建存储过程的语法如下。
CREATE PROCDURE procedure_name[;number]
[{@parameter data_type}
[VARYING][=default][OUTPUT]][,…n]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement[…n]
Procedure_name 新存储过程的名称。过程名称在架构中必须唯一，可在procedure_name前面使用一个数字符号“#”来创建局部临时过程，使用两个数字符号“#”来创建全局临时过程。对于CLR存储过程，不能指定临时名称。
number 是可选的整数，用来对同名的过程分组。使用一个DROP PROCEDURE语句可将这些分组过程一起删除。如果名称中包含分隔标识符，则数字不应该包含在标识符中;只应在procedure_name前使用分隔符。
@parameter 过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值，如果指定了FOR REPLICATION，则无法声明参数。
参数含义：
Data_type 参数的数据类型。所有数据类型均可以用作存储过程的参数。不过cursor数据类型只能用于OUTPUT参数。如果指定的数据类型为cursor，则还必须指定VARYING和OUTPUT关键字。对于CLR存储过程，不能指定char,varchar,text,next,image,cursor和table作为参数。如果参数的数据类型为CLR用户定义类型，则必须对此类型有EXECUTE权限。
Default参数的默认值。如果定义了dafault值，则无须指定此参数的值即可执行过程。默认值必须是常量或NULL。如果过程使用带like关键字的参数，则可包含下列通配符：%、_、[]、[^]。
Output 指示参数是输出参数。此选项的值可以返回给调用EXECUTE的语句。使用OUTPUT参数将值返回给过程的调用方。除非是CLR过程，否则text,ntext和image参数不能用作OUTPUT参数。OUTPUT关键字的输出参数可以为游标占位符，CLR过程除外，要包含在过程中的一个或多个T-SQL语句中
参数含义：
4．执行存储过程
在需要执行存储过程时，可以使用T-SQL语句EXECUTE。如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程，EXECUTE语法格式如下：
[ { EXEC | EXECUTE } ]
{
[ @return_status= ]
{ procedure_name [;number] | @procedure_name_var }
@parameter = [ { value | @variable [ OUTPUT ] | [ DEFAULT ] } ]
[,…n]
[ WITH RECOMPILE ]
参数说明：
@return_status 是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于EXECUTE语句前，必须在批处理、存储过程或函数中声明过。
Procedure_name 要调用的存储过程名称。
number 是可选的整数，用于将相同名称的过程进行组合，使得它们可以用一句DROP PROCEDURE 语句删除。在”Student”中使用的过程可以Reader_proc;1、proc_GetCountsBook；２等来命名。DROP PROCEDURE Reader_proc语句将除去整个组。在对过程分组后，不能删除组中的单个过程。例如，DROP PROCEDURE proc_GetCountsBook;２是不允许的。
参数说明：
@procedure_name_var 是局部定义变量名，代表存储过程名称。
@parameter 是过程参数，在CREATE PROCEDURE语句中定义。参数名称前必须加上符号“@”。
Value 是过程中参数的值。如果参数名称没有指定，参数值必须以CREATE PROCEDURE语句中定义的顺序给出。
@variable 是用来保存参数或者返回参数的变量。
OUTPUT 指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字OUTPUT创建。使用游标变量作参数时使用该关键字。
5．管理存储过程
（1）查看存储过程信息
如果希望查看存储过程的定义信息，可以使用sys.sql_modules目录视图、OBJECT_DEFINITION系统函数、sp_helptext系统存储过程等。例如，下面代码使用OBJECT_DEFINITION系统函数查看proc_GetReaderBookscount存储过程的定义内容
SELECT OBJECT_DEFINITION (OBJECT_ID(N’proc_GetReaderBookscount’))
在创建存储过程时使用了WITH ENCRYPTION子句，则将隐藏存储过程定义文本的信息，上面将不能查看到具体的文本信息
（2）修改存储过程语法
ALTER PROCEDURE procedure_name[;number]
[{@parameter data_type}
[VARYING][=default][OUTPUT]]
[,…n]
[WITH
{ RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS
sql_statement[…n]
修改存储过程的语法中的各参数与创建存储过程语法中的各参数相同，这里就不在重复介绍。
修改存储过程注意事项：
在使用ALTER PROCEDURE语句时，考虑以下方面的事项：
如果要修改具有任何选项的存储过程，例如WITH ENCRYPTION选项，必须在ALTER PROCEDURE语句中包括该选项以保留该选项提供的功能。
ALTER PROCEDURE语句只能修改一个单一的过程，如果过程调用了其他存储过程，嵌套的存储过程不受影响。
在默认状态下，允许该语句的执行者是存储过程最初的创建者、sysadmin服务器角色成员和db_owner与db_ddladmin固定的数据库角色成员，用户不能授权执行ALTER PROCEDURE语句。
建议不要直接修改系统存储过程
（3）删除存储过程
使用DROP PROCEDURE语句来从当前的数据库中删除用户定义的存储过程。删除存储过程的基本语法如下所示。
DROP PROCEDURE {procedure}[,…n]
11.1.2任务实施
1．创建简单的存储过程
方法1：命令创建存储过程的示例
例如，在SQL Server 2008的示例数据库“Student”中创建一个名为Reader_student的存储过程，它将从表中返回所有学生的学号、姓名、性别、出生日期、政治面貌、班级编号、家庭住址、邮编等信息。使用CREATE PROCEDURE语句如下：
Use Student
Go
CREATE PROCEDURE Reader_Student
As
SELECT 学号,姓名,性别,出生日期,政治面貌,班级编号,家庭住址,邮编
FROM 学生表
下面的存储过程proc_GetCountsStudent获取了“Student”数据库中学生的总人数，具体语句如下所示：
Use Student
Go
CREATE PROCEDURE proc_GetCountsStudent
As
SELECT count(*) AS 学生总数 FROM 学生表
以上两个存储过程示例都是从单个表中提取数据，在第二个示例中使用了简单的表达式。
下面使用SELECT语句链接多个表，最终返回了借书人的简明信息。存储过程名称是proc_Get_cj,创建语句如下：
Use Student
Go
CREATE PROCEDURE proc_Get_cj
As
select a.学号,b.课程名称,a.成绩
from dbo.成绩表 a,dbo.课程表 b
where a.课程编号 = b.课程编号
方法2：使用图形工具创建
除了直接编写T-SQL创建外，SQL Server 2008还提供了一种简便的方法，使用SQL Server Management Studio工具。操作步骤如下：
步骤1:打开SQL Server Management Studio窗口，连接到“Student”数据库。
步骤2:依次展开“服务器”｜“数据库”｜“Student”|“可编程性”节点。
步骤3:从列表中右击“存储过程”节点选择“新建存储过程”命令，然后将出现如图11-3所示的显示CREATE PROCEDURE语句的模板，可以修改要创建的存储过程的名称，然后加入存储过程所包含的SQL语句。
步骤4:修改完后，单击“执行”按钮即可创建一个存储过程。
如下图：
2．执行存储过程
我们通过EXECUTE语句来依次执行上文创建的3个存储过程。首先是Reader_student存储过程，它位于”Student”数据库中，使用语句如下：
Use Student
Go
EXECUTE Reader_student
如下图：
然后再使用同样的方法，执行“Student”数据库中的两个存储过程，结果分别如图11-5和图11-6所示
说明：运行EXECUTE语句无须权限，但是需要对EXECUTE字符串内引用的对象的权限。例如，如果字符串包含INSERT语句，则EXECUTE语句的调用方对目标表必须具有INSERT权限。
除使用EXECUTE直接执行外，还可以将存储过程嵌入到INSERT语句中执行。这样操作时，INSERT语句将把本地或远程存储过程返回的结果集加入到一个本地表中。SQL Server 2008会将存储过程中的SELECT语句返回的数据载入表中，前提是表必须存在并且数据类型必须匹配。
3．管理存储过程
（1）查看存储过程信息
用户可以通过系统存储过程sp_helptext查看存储过程的定义，通过sp_text查看存储过程的参数，通过sp_depends查看存储过程的相关性。
运行如下SQL语句：
EXECUTE sp_helptext Reader_student
EXECUTE sp_help proc_GetCountsStudent
EXECUTE sp_depends proc_Get_cj
执行结果如图11-7所示。
（2）修改存储过程
上例proc_Get_cj中，若想在存储过程执行后获得学生姓名信息，则需要修改原存储过程，代码如下：
Use Student
Go
ALTER PROCEDURE proc_Get_cj
As
select a.学号,c.姓名,b.课程名称,a.成绩
from dbo.成绩表 a,dbo.课程表 b,学生表 c
where a.课程编号 = b.课程编号
and a.学号 = c.学号
执行结果如图11-8所示。
（3）删除存储过程
下面的语句将删除proc_GetCountsStudent存储过程：
DROP PROC proc_GetCountsStudent
如果另一个存储过程调用某个已被删除的存储过程，SQL Server 2008将在执行调用进程时显示一条错误消息。但是，如果定义了具有相同名称和参数的新存储过程来替换已被删除的存储过程，那么引用该过程的其他过程仍能成功执行
任务2 创建带参数的存储过程
11.2.1 相关知识
存储过程的优势不仅在于存储在服务器端、运行速度快、还有重要的一点就是存储过程可完成的功能非常强大，特别是在SQL Server 2008中。本节将学习如何在存储过程使用参数，包括输入参数和输出参数，以及参数的默认值等。
SQL Server 2008的存储过程可以使用两种类型的参数：输入参数和输出参数。参数用于在存储过程以及应用程序之间交换数据，其中：
输入参数允许用户将数据值传递到存储过程或函数。
输出参数允许存储过程将数据值或游标变量传递给用户。
每个存储过程向用户返回一个整数代码，如果存储过程没有显式设置返回代码的值，则返回代码为0。
存储过程的参数在创建时应在CREATE PROCEDURE和AS关键字之间定义，每个参数都要指定参数名和数据类型，参数名必须以@符号为前缀，可以为参数指定默认值；如果是输出参数，则应用OUTPUT关键描述。各个参数定义之间用逗号隔开，具体语法如下：
@parameter_name data_type [ =default ] [ OUTPUT ]
11.2.2任务实施
1．存储过程的输入参数
输入参数，即指在存储过程中有一个条件，在执行存储过程时为这个条件指定值，通过存储过程返回相应的信息。使用输入参数可以向同一存储过程多次查找数据库。例如，可以创建一个存储过程用于返回”Student”数据库上某条成绩中包含的课程名称。通过为同一存储过程指定不同的学号，来返回不同的课程成绩
在上文创建的存储过程proc_Get_cj只能对成绩表进行特定的查询，返回整个表的数据，缺乏针对性。若要使这个存储过程更加通用化、灵活且能够查询某个学生的成绩信息，那么学生信息中的学号就应该是可变的，这样的存储过程才能返回某个学生的参加数据。
在这个存储过程上将一个学生的学号作为参数来实现，名称为proc_GetcjByCode,其代码如下：
Use Student
Go
CREATE PROCEDURE proc_GetcjByCode
@code varchar(12)
As
select a.学号,b.课程名称,a.成绩
from dbo.成绩表 a,dbo.课程表 b
where a.课程编号 = b.课程编号
and a.学号 = @code
以上代码，创建一个名为proc_GetcjByCode的存储过程，使用一个字符串型的参数@ code来执行。
执行带有输入参数的存储过程时，SQL Server 2008提供了如下两种传递参数的方式。
按位置传递这种方式是在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出的参数的顺序与创建存储过程的语句中的参数的顺序一致，即参数传递的顺序就是参数定义的顺序。使用这种方式执行proc_GetcjByCode存储过程的代码为：
EXEC proc_GetcjByCode ‘01201401002’
这种方式是在执行存储过程的语句中，使用“参数名=参数值”的形式给出参数值。通过参数名传递参数的好处是，参数可以以任意顺序给出。用这种方式执行proc_GetcjByCode存储过程的代码如下。
EXEC proc_GetcjByCode@code = '01201401002'
执行效果：
2．使用默认参数值
执行存储过程proc_GetcjByCode时，如果没有指定参数，则系统运行就会出错；如果希望不给出参数时也能够正确运行，则可以给参数设置默认值来实现。
因此，如果要将proc_GetcjByCode存储过程修改为默认值使用学号为[00000000000]的proc_GetcjByCode，则可以运行下列代码：
Use Student
Go
CREATE PROCEDURE proc_GetcjByCode
@code varchar(12) = '00000000000'
As
select a.学号,b.课程名称,a.成绩
from dbo.成绩表 a,dbo.课程表 b
where a.课程编号 = b.课程编号
and a.学号 = @code
此时，执行该存储过程不指定学号也不会出错，代码如下：
Exec proc_GetcjByCode
3．存储过程的输出参数
通过定义输出参数，可以从存储过程中返回一个或多个值。为了使用输出参数，必须在CREATE PROCEDURE语句和EXECUTE语句中指定关键字OUTPUT。
在执行存储过程时，如果忽略OUTPUT关键字，存储过程仍会执行但不返回值。
USE Student
GO
CREATE PROCEDURE proc_GetCountsTuanyuan
@zzmm varchar(10)='团员',
@TuanyuanCounts int OUTPUT
As
SELECT @TuanyuanCounts=COUNT(学号)
FROM 学生表
where 政治面貌 = @zzmm
以上代码创建一个名为proc_GetCountsTuanyuan的存储过程，它使用两个参数：@ zzmm为输入参数，用于指定要查询的学生政治面貌，默认参数值为团员；@TuanyuanCounts为输出参数，用来返回学生表中的团员总数量。
为了接收某一存储过程的返回值，需要一个变量来存放返回参数的值，在该存储过程的调用语句中，必须为这个变量加上OUTPUT关键字来声明。下面的代码显示了如何调用proc_GetCountsTuanyuan，并将得到的结果返回到@TuanyuanCounts中。
USE Student
GO
DECLARE @TuanyuanCountsint
EXEC proc_GetCountsTuanyuan ‘团员’,@TuanyuanCountsOUTPUT
SELECT'学生团员数量为：'+STR(@TuanyuanCounts)+'人'
GO
其运行结果如图11-10所示
运行效果：
4．存储过程的返回值
存储过程在执行后都会返回一个整形值。如果执行成功，则返回0；否则返回-1到-99之间的随机数，也可以使用RETURN语句来指定一个存储过程的返回值。
例如，下面创建一个名为aAndb的存储过程，用以计算出两个参数的和。本例使用SET语句，但是也可以使用SELECT语句来组织一个字符串，语句如下：
CREATE PROC aANDb
@a int=0,@b int=0,@c int=0 OUTPUT
AS
Set @c=@a+@b
Return @c
@c参数由OUTPUT关键字指定。在执行这个存储过程时，需要指定一个变量存放返回值，然后再显示出来。如下所示为一个调用这个存储过程的示例：
DECLARE @int c int
EXEC aANDb 6,2,@int c OUTPUT
SELECT ‘两个之和为：’+STR(@INT C)
项目小结
本项目主要讲述了存储过程的基本概念，存储过程的创建、修改以及删除等内容。
存储过程是在服务器端执行的一段T-SQL程序，对于C/S模式的应用系统，服务器只把结果发送给客户端，这样不但减少了网络上的数据流量，而且提高了客户端的效率。
END!

展开更多......

收起↑