第09章 数据库的高级应用存储过程和触发器第3稿Word文件下载.docx
《第09章 数据库的高级应用存储过程和触发器第3稿Word文件下载.docx》由会员分享，可在线阅读，更多相关《第09章 数据库的高级应用存储过程和触发器第3稿Word文件下载.docx（33页珍藏版）》请在冰点文库上搜索。
要想解决这个问题，我们可以把欲执行的T-SQL语句做成一个相对固定的语句组，用户想查询学生的成绩只要执行这个T-SQL语句组就可以了。
且如果每一次查询都需要通过网络提交该T-SQL查询语句，那么势必造成一个相同的SQL语句在网络中频繁传输，对网络的压力是非常大的，而且这样直接传输数据也不安全，还会造成应用系统的性能下降。
根据SQLServer2000中所提供的存储过程的特点，建议采用存储过程方式予以解决。
解决办法：
在数据库服务器端创建一个带输入输出参数的存储过程，由查询操作提交学生姓名和课程名称，在服务器端调用存储过程去实现查询并返回查询结果。
这样就实现了降低了用户的操作难度，同时也减少了网络传输的数据量提高了系统性能。
并且在多次查询时，直接调用该存储过程的编译结果去查询速度则更快。
下面我们就介绍存储过程。
9.2存储过程
存储过程（StoredProcedure）是一组为了完成特定功能、可以接受和返回用户提供的参数的T-SQL语句的预编译集合，经编译后存储在数据库中。
用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程在第一次执行时进行语法检查和编译，执行后他的执行计划就驻留在高速缓存中，用于后续调用。
存储过程可以接受和输出参数、返回执行存储过程的状态值，还可以嵌套调用。
用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。
在SQLServer2000中存储过程分为四类：
●系统提供的存储过程
系统存储过程主要存储在master数据库中并以“sp_”为前缀，其主要是从系统表中获取信息为系统管理员管理SQLserver提供支持，也为系统管理员和合适的用户（即有权限的用户）提供更新系统表的途径。
●用户自定义的存储过程
用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需的数据信息）而编写的存储过程。
在本章中所涉及到的存储过程主要是指用户自定义存储过程。
●临时存储过程
专用和全局临时存储过程与临时表类似，在该过程名称添加“#”和“##”前缀的方法进行创建。
#表示本地临时存储过程，##表示全局临时存储过程。
SQLServer关闭后，这些存储过程将不再存在。
●扩展存储过程
扩展存储过程是对动态链接库（DLL）函数的调用，其名称是以“xp_”为前缀。
扩展存储过程直接在SQLServer的地址空间运行，并使用SQLServer开放式数据服务（ODS）API编程。
扩展存储过程使得我们可以使用像C这样的编程语言创建自己的外部例程，它的使用只是扩展了SQLServer的功能。
对用户来说，扩展存储过程与普通的存储过程一样，执行方法也相同。
存储过程具有如下优点：
（1）执行数度快，改善系统性能。
存储过程在服务器端运行，可以利用服务器强大的计算能力和速度，执行速度快。
而且存储过程是预编译的，第一次执行后的存储过程会驻留在高速缓存中，以后直接调用，执行速度很快，如果某个操作需要大量的T-SQL语句或重复执行，那么使用存储过程比直接使用T-SQL语句之行得更快。
（2）减少网络流量。
用户可以通过发送一条执行存储过程的语句实现一个复杂的操作，而不需要在网络上百条T-SQL语句，这样可以减少在服务器和客户端之间传递语句的数量，减轻了服务器的负担。
（3）增强代码的重用性和共享性。
存储过程在被创建后，可以在程序中被多次调用，而不必重新编写。
所有的客户端都可以使用相同的存储过程来确保数据访问和修改的一致性。
而且存储过程可以独立于应用程序而进行修改，大大提高了程序的可移植性。
（4）提供了安全机制。
如果存储过程支持用户需要执行的所有业务功能，SQLServe可以不授予用户直接访问表、视图的权限，而是授权用户执行该村出过程，这样，可以防止把数据库中表的细节暴露给用户，保证表中数据的安全性。
9.3创建存储过程
在SQLServer2000中，创建存储过程有以下三种方法：
●使用企业管理器创建存储过程
●使用创建存储过程向导创建存储过程
●使用T-SQL命令创建存储过程
在创建存储过程时，需要确定存储过程的三个组成部分：
●所有的输入参数以及传给调用者的输出参数；
●被执行的针对数据库的操作语句，包括调用其他存储过程的语句；
●返回给调用者的状态值，以指明调用是成功还是失败。
下面就开始进行介绍创建存储过程的方法。
1．使用企业管理器创建存储过程
按照下述步骤用企业管理器创建一个能够解决【任务9.1】问题的存储过程：
（l）启动【企业管理器】，登录到要使用的服务器。
图9.1企业管理器中显示的存储过程详细信息
（2）选择要创建存储过程的数据库，在右窗口中双击【存储过程】图标，文件夹此时在右窗格中显示该数据库的所有存储过程。
如图9.1所示。
（3）右击【存储过程】文件夹，在弹出菜单中选择【新建存储过程（S）…】，此时打开创建存储过程对话框，输入存储过程正文。
如图9.2所示。
图9.2创建存储过程对话框
（4）可以单击【检查语法】按钮，检查欲创建的存储过程的语法是否正确。
（5）最后单击【确定】，即可创建成功。
2.使用创建存储过程向导创建存储过程
使用向导创建存储过程具体操作步骤如下：
（1）展开一个服务器组，再展开要在其中创建视图的服务器。
（2）展开【数据库】文件夹。
（3）在【工具】菜单上单击【向导】命令。
（4）双击【创建存储过程向导】命令，在出现的创建存储过程向导欢迎界面中，点击【下一步】。
在随后出现如图9.3所示的界面中选择相应的数据库myDB之后，点击【下一步】。
图9.3选择保存存储过程的数据库
图9.4为创建的存储过程选择操作
图9.5创建存储过程成功界面
（6）根据存储过程的操作特性，在如图9.4所示的界面中为将要创建的存储过程选择一个或多个操作。
在此可以创建用于插入、删除和更新行的存储过程。
选择完毕后，点击【下一步】。
在如图9.5所示的界面中，可以编辑存储过程的内容。
确认无误后，点击【完成】即可创建三个存储过程。
3.使用T-SQL创建存储过程
可以使用T-SQL语句CREATEPROCEDURE创建存储过程。
在创建存储过程之前，应该考虑到以下几个方面：
●不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。
●数据库所有者具有默认的创建存储过程的权限，它可将该权限传递给其他的用户。
●存储过程是一种数据库对象，其名称必须遵守标识符规则。
●只能在当前数据库中创建属于当前数据库的存储过程。
CREATEPROCEDURE语句的语法格式如下：
CREATEPROC[EDURE]procedure_name[;
number]
[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
ASsql_statement[...n]
各参数的解释说明如下：
Ø
procedure_name：
新建存储过程的名称。
要创建局部临时过程，可以在procedure_name前面加一个编号符（#procedure_name），要创建全局临时过程，可以在procedure_name前面加两个编号符（##procedure_name）。
完整的名称（包括#或##）不能超过128个字符。
;
number：
是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。
@parameter：
存储过程的参数。
在CREATEPROCEDURE语句中可以声明一个或多个参数。
用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。
存储过程最多可以有1024个参数。
若参数的形式以＠parameter=value出现，则参数的次序可以不同，否则用户给出的参数值必须与参数列表中参数的顺序保持一致。
若某一参数以@parameter=value形式给出，那么其他参数也必须以该形式给出。
data_type：
参数的数据类型。
VARYING：
指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。
仅适用于游标参数。
default：
参数的默认值。
OUTPUT：
表明参数是返回参数。
n：
是表示此过程可以包含多条T-SQL语句的占位符。
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}：
RECOMPILE表明SQLServer不会缓存该存储过程的执行计划，该过程将在运行时重新编译。
ENCRYPTION表明SQLServer加密了syscomments表，该表的text字段是包含有createProcedure语句的存储过程文本，使用该关键字无法通过查看sysoomments表来查看存储过程内容。
FORREPLICATION：
指定不能在订阅服务器上执行为复制创建的存储过程。
.使用FORREPLICATION选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。
本选项不能和WITHRECOMPILE选项一起使用。
AS：
指定过程要执行的操作。
sql_statement：
过程中要包含的任意数目和类型的T-SQL语句。
【任务9.2】使用存储过程解决【任务9.1】中的问题。
解决【任务9.1】中的问题，可以在查询分析器中执行下列SQL语句：
USEmyDB–-打开数据库
--检查欲创建的存储是否存在，如果存在则删除
IFEXISTS（SELECTnameFROMsysobjectsWHEREname='
proc_Q_stuGrade'
ANDtype='
P'
）
DROPPROCEDUREproc_Q_stuGrade
GO
--创建存储过程proc_Q_stuGrade
CREATEPROCproc_Q_stuGrade
@stuNamechar（20）,
@kcnamechar（20）
AS
if（@stuNameisnull）
print'
请输入学生姓名！
'
else
if（@kcnameisnull）
请输入课程名称！
else
selectgradeNum
fromgrade
wheresID=（selectsIDfromstudentwheresName=@stuName）andkcID=（selectkcIDfromcoursewherekcName=@kcname）
go
9.4执行存储过程
当需要执行存储过程时，需要使用T-SQL语句中的EXECUTE语句。
如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程。
其语法格式如下：
[[EXEC[UTE]]
{[@return_status=]
{procedure_name[;
number]|@procedure_name_var}
[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n]
[WITHRECOMPILE]}
各参数说明如下：
@return_status：
是一个可选的整型变量，保存存储过程的返回状态。
这个变量在用于EXECUTE语句前，必须在批处理、存储过程或函数中声明过。
在用于唤醒调用标量值用户定义函数时，@return_status变量可以是任何标量数据类型。
procedure_name：
是拟执行（或调用）的存储过程的名称。
@procedure_name_var：
是局部定义变量名，代表存储过程名称。
其它参数的说明，请参考CREATEPROCEDURE语句的语法说明。
【任务9.3】请通过调用存储过程proc_Q_stuGrade来查询“刘常福”同学的《商务网站建设》这门课的成绩。
usemyDB
--执行存储过程，查询“刘常福”同学的《商务网站建设》这门课的成绩
execproc_Q_stuGrade'
刘常福'
'
商务网站建设'
上述T-SQL语句执行结果如图9.6所示。
图9.6执行存储过程的结果
9.5管理存储过程
9.5.1修改存储过程
如果需要修改存储过程中的语句或参数，可以采用删除该存储过程然后再重新创建的方法，也可以采用直接修改存储过程的定义文本和过程代码的方法。
需要注意的是，如果是先删除再重新创建存储过程，那么所有与该存储过程相关联的权限设置都将丢失。
而直接修改存储过程的定义文本和过程代码，为该存储过程定义的权限信息将会被保留。
1．使用企业管理器修改存储过程
在企业管理器中，首先找到要修改的存储过程。
然后用鼠标右健单击所要修改的存储过程，在弹出菜单中选择【属性】命令，可以查看并修改该存储过程的定义文本和过程代码。
2.使用存储过程修改存储过程
修改存储过程可以使用ALTERPROCEDURE语句实现。
在进行修改前，首先要确保拥有相应的权限。
ALTERPROCEDURE权限默认授予sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员以及存储过程的所有者，并且该权限不可转让。
ALTERPROCEDURE语句的语法格式如下：
ALTERPROC[EDURE]procedure_name[;
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
sql_statement[...n]
各参数的说明，请参考CREATEPROCEDURE语句的语法说明。
【任务9.4】由于【任务9.3】中的存储过程文本可以使用存储过程sp_helptext进行查看，出于对代码安全性的考虑，请将【任务9.3】中创建的存储过程进行加密处理。
只要将该存储过程加密即或解决这个问题。
在查询分析器中执行下列T-SQL语句：
USEmyDB--打开数据库
--修改存储过程proc_Q_stuGrade
ALTERPROCproc_Q_stuGrade
withencryption
9.5.2重命名存储过程
存储过程也可以被重新命名。
新的名称必须遵守标识符规则。
要重命名的存储过程必须位于当前数据库中，并且要拥有相应的权限。
1．使用企业管理器重命名存储过程
然后用鼠标右健单击所要重命名的存储过程，在弹出菜单中选择“重命名”命令，就可以重新命名该存储过程。
需要注意的是，重命名存储过程不会更改该存储过程在定义文本中指定的名称。
2.使用存储过程重命名存储过程
使用系统存储过程sp_rename可以重命名存储过程。
sp_rename[@objname=]'
object_name'
[@newname=]'
new_name'
[,[@objtype=]'
object_type'
]
[@objname=]'
：
是存储过程或触发器的当前名称。
[@newname=]'
是指定存储过程或触发器的新名称。
[@objtype=]'
是要重命名的对象的类型。
对象类型为存储过程或触发器时，其值为OBJECT。
需要注意的是：
只能修改当前数据库中的存储过程或触发器。
【任务9.5】请使用存储过程将【任务9.3】中创建的存储过程proc_Q_stuGrade重新命名为proc_Q_stuGrade_new。
请在查询分析器中执行下列T-SQL语句：
execsp_rename'
proc_Q_stuGrade_new'
OBJECT'
9.5.3删除存储过程
1．使用企业管理器删除存储过程
删除存储过程的操作步骤如下：
（1）用鼠标右键单击待删除存储过程，在弹出菜单中选择【删除】命令，或单击要删除的存储过程，接着按下delete键。
（2）当出现“除去对象”对话框时，可以单击【显示相关性】按钮查看删除该存储过程对数据库有什么影响。
（3）单击【全部删去】按钮，即完成删除存储过程。
2.使用T-SQL语句删除存储过程
使用T-SQL语句中的drop命令可以将一个或多个存储过程或存储过程组从当前数据库中删除，其语法格式如下：
DROPPROCEDURE{procedure}[,...n]
参数说明如下：
procedure：
是要删除的存储过程或存储过程组的名称。
【任务9.6】请使用存储过程将存储过程【任务9.5】中重命名的存储过程从数据库中删除。
解决【任务9.6】中的任务，可以在查询分析器中执行下列T-SQL语句：
DROPPROCEDUREproc_Q_stuGrade_new
9.6查看存储过程
查看存储过程通常包括以下几个方面：
●查看用于创建存储过程的T-SQL语句。
●获得有关存储过程的信息（如存储过程的所有者、创建时间及其参数）。
●列出指定存储过程所使用的对象及使用指定存储过程的过程。
1．使用企业管理器查看存储过程
然后用鼠标右键单击所要查看的存储过程，在弹出菜单中选择“属性”命令，可以查看该存储过程的定义文本。
要想查看存储过程的相关性可用鼠标右键单击所要查看相关性的存储过程，在弹出菜单中选择“所有任务”命令，然后选择“显示相关性”，则可以在相关性对话框中查看到依附于该存储过程的对象和该存储过程依附的对象。
2.使用存储过程查看存储过程
（1）查看存储过程的定义
使用系统存储过程sp_helptext，可以查看未加密的存储过程的文本。
sp_helptext[@objname=]'
name'
存储过程的名称，将显示该存储过程的定义文本。
该存储过程必须在当前数据库中。
【任务9.7】请使用存储过程显示存储过程proc_Q_stuGrade的定义文本。
解决【任务9.7】中的任务，可以在查询分析器中执行下列T-SQL语句：
USEmyDB
EXECsp_helptext'
（2）查看有关存储过程的信息
使用系统存储过程sp_help可以查看当前数据库中存储过程的信息（如存储过程的所有者、创建时间及其参数），其语法格式如下：
sp_help[[@objname=]name]
[@objname=]name：
是sysobjects中的存储过程的名称。
当没有指定name时，sp_help列出当前数据库中所有对象的名称、所有者和对象类型。
【任务9.8】请使用存储过程显示存储过程'
的信息。
解决【任务9.8】中的任务，可以在查询分析器中执行下列T-SQL语句：
EXECsp_help'
（3）查看存储过程的相关性
使用系统存储过程sp_depends，可以显示有关数据库对象相关性的信息（例如，依赖表或视图的视图和过程，以及视图或过程所依赖的表和视图），但不包括对当前数据库以外对象的引用。
其语法如下：
sp_depends[@objname=]'
object'
被检查相关性的数据库对象。
对象可以是表、视图、存储过程或触发器。
【任务9.9】请使用存储过程显示存储过程proc_Q_stuGrade的相关性的信息。
解决【任务9.9】中的任务，可以在查询分析器中执行下列T-SQL语句：
EXECsp_depends'
9.7触发器
【任务9.10】在《晓灵学
升级会员 