《SQLServer2005数据库案例教程》第7章SQLServer2005高级技术

第7章

SQLServer2005高级技术

在对数据库的操作中，我们经常要进行各种重复性的查询、删除和修改，如果每一次操作都要输入所有的查询指令，查询的效率必然十分低下，当我们引入存储过程来处理这些操作的话，就能很好的利用SQL服务器强大的运算能力和速度，另一方面避免和服务器间的大量数据交换，在很大程度上提高工作效率。我们将在“教学管理数据库”中建立若干提高数据库操作效率的存储过程，以提高整个教学管理系统的运行性能。本章主要围绕教学管理系统这个案例的数据库及其数据表进行存储过程的构建，并在创建和管理这些存储过程的实例中阐述相关的知识点。7.1存储过程

存储过程是存储在SQLServer数据库中的由Transact-SQL语句组成的一种编程对象。其作用好比是一个编程语言中的过程，可以被编译之后执行，可以被客户机管理工具、应用程序、其他存储过程调用（可以传递参数）。一般将一些固定的或特定的操作编写存储过程由SQLServer数据库服务器来完成，以提高程序的效率或实现某特定任务。7.1.1案例实现过程创建存储过程的方法有以下两种，使用SQLServer管理平台提供的快速创建存储过程，和使用查询编辑器书写Transact-SQL语句创建存储过程。在该案例中使用第一种方法，在后面的知识点中将阐述第二种方法。（1）在SQLServer管理平台中，双击【教学管理系统数据库】文件夹或单击其左边的加号，在出现的子文件夹列表中再双击【可编程性】或者单击其左边加号，然后在【存储过程】文件上单击右键，如图7.1所示。图7.1新建存储过程的快捷菜单（2）从弹出的快捷菜单中选择【新建存储过程】选项，出现如图7.2所示的出现创建存储过程窗口。图7.2创建存储过程的窗口

在模板中，存储过程的名称和参数是用模板参数来代替的。（3）指定模板参数的值。单击【查询】菜单，选择【指定模板参数值】，在该对话框中对模板参数进行更改，如图7.3所示。指定创建者的姓名和创建时间，命名存储过程的名称为“成绩查询”，参数@p1我们用来表示要查询的成绩，参数@p2我们用来表示课程编号，并设定好参数的数据类型。图7.3指定模板参数的值的对话框（4）单击【确定】，然后修改模板中的SELECT语句为我们需要执行的语句，查询成绩表中，课程编号为701的成绩大于80分的所有学生的学号。SELECT学号from成绩表where成绩>@p1AND课程编号=@p2

（5）完成模板的修改后，单击【查询】菜单的【分析】选项，进行语法分析，如果出现错误提示，重新修改语句直至错误消失。

（6）单击【查询】菜单中的【执行】选项，完成创建存储过程。

（7）对象资源管理器中，在【存储过程】文件夹上单击右键，选择【刷新】选项，就会看到创建好的存储过程：成绩查询，如图7.4所示。图7.4新建立的存储过程7.1.2相关知识及注意事项1．使用存储过程的意义

存储过程会在被创建时进行编译，以后每次执行都不需再重新编译，而通常使用的SQL语句每次执行都要进行一次编译,使用存储过程可大大提高数据库执行速度。2．创建存储过程的基本语句（1）procedure_name新建的存储过程的名称。（2）number是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。（3）@parameter过程中的参数。（4）data_type参数的数据类型。（5）VARYING指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。（6）default参数的默认值。（7）OUTPUT表明参数是返回参数。（8）n表示最多可以指定2100个参数的占位符。（9）{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}RECOMPILE表明SQLServer不会缓存该过程的计划，该过程将在运行时重新编译。（10）FORREPLICATION指定不能在订阅服务器上执行为复制创建的存储过程。（11）AS指定过程要执行的操作。（12）sql_statement过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。（13）n是表示此过程可以包含多条Transact-SQL语句的占位符。

在创建一个新的存储过程时，应该注意以下几点：①在一个批处理中，CREATEPROCEDURE语句不能与其它SQL语句和并在一起。②数据库所有者具有默认的创建存储过程的权限，它可以把该权限传递给其他用户。③只能在当前数据库中创建属于当前数据库的存储过程。④尽量时存储过程的功能单一化。⑤存储过程的最大大小为128M。【例7.1】创建一个存储过程，用来查询教工姓名。程序清单如下。CREATEPROCEDURE查询教工姓名ASSELECT姓名from教师表GO【例7.2】创建一个带参数存储过程，用来查询符合条件的教师姓名。其中用@sex参数来表示性别，用@branch来表示所在部门。程序清单如下。CREATEPROCEDURE查询教工带参数@sexchar(8),@branchchar(20)ASSELECT姓名from教师表where性别=@sexand所在部门=@branchGO【例7.3】创建使用通配符的存储过程，在参数中指定通配符%以模糊查询符合条件的教师的信息。程序清单如下。CREATEPROCEDURE查询教工带通配符@namechar(8)='张%'ASSELECT所在部门from教师表where姓名like@nameGO3．执行存储过程

存储过程的执行者必须在存储过程上拥有EXECUTE权限以后才能执行该存储过程。

当需要执行存储过程时，我们使用EXECUTE语句。如果执行存储过程语句是批处理中的第一条语句，那么可以直接输入存储过程名执行。可以用以下两种方式使用EXECUTE。单独使用EXECUTE执行存储过程的语法

执行存储过程时参数的使用方法由以下几种：1）用参数名传递值：@参数名=参数值2）按照参数顺序来传值：不使用参数名，直接给出参数值，但必须严格按照参数顺序对应赋值，且对于缺省默认值只能放到后面而不能位于中间。3）输出参数的使用：@输出参数名OUTPUT4）系统存储过程系统存储过程就是系统创建的存储过程，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务，SQLServer2005中的许多管理活动都是通过系统存储过程执行的。系统过程以“sp_"为开头,在Master数据库中创建并保存在该数据库中，为数据库管理者所有。5．使用扩展存储过程扩展存储过程时系统存储过程的一种，提供从SQLSEVRVER到外部程序的接口，以便进行各种维护活动。扩展存储过程的前缀是XP_.7.1.3管理存储过程1．查看存储过程的信息

要查看已创建的存储过程，可在存储过程所在的数据库中执行SP_helptext,并使用过程名作为参数。使用ENCRYPTION选项创建的存储过程不能使用sp_helptext查看。2重新命名存储过程

修改存储过程的名字使用系统存储过程sp_rename。其命令格式为：sp_rename原存储过程名,新存储过程名3．修改存储过程

如果需要更改存储过程中的语句或参数，可以删除或重新创建该存储过程，也可以直接修改该存储过程。删除或重新创建存储过程时，所有与该存储过程相关的权限都将丢失；而修改存储过程时，过程或参数定义会更改，但权限将保留。修改存储过程使用语句ALTERPROCEDURE来完成。4．删除存储过程

删除存储过程使用的语法如下：DROP{PROC|PROCEDURE}{存储过程名}[...n]5.存储过程的加密

如果用户不想让其他人查看存储过程的定义文本，可以在定义存储过程时对其进行加密。加密存储过程的关键字是WITHENCRYPTION，使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布。存储过程一旦被加密，其定义将无法解密，任何人（包括该存储过程的所有者或系统管理员）都将无法查看该存储过程的定义。

1）@@ERROR系统检测函数

2）TRY...CATCH错误捕获

3）自定义错误消息7．CLR存储过程

SQLServer2005的一大新特性便是整合了.net的CLR。整合了.netCLR的好处在于，可以很方便地使开发者可以使用自己熟悉的.net语言来创建存储过程，触发器，自定义函数等。T-SQL在某些场合有其局限性，比如T-SQL不是面向对象的，某些语法过于复杂等。而如果使用面向对象的.NET语言来编写如存储过程等数据对象时，由于.net语言强大的特性，因此能写出更健壮和更优秀的存储过程。

通过SQLServer2005用.NET编写的存储过程，都是和用.NET语言编写一般应用的程序一样，都是managedcode。与T-SQL（它是一种解释语言）相比，CLR编程语言之所以具有更好的性能，是因为托管代码是已编译的，托管代码的性能可能要优于T-SQL。

1）创建CLR存储过程

2）删除CLR存储过程7.2开发用户自定义函数

与编程语言中的函数类似，MicrosoftSQLServer2005除了内置一些常用的函数外，还允许用户定义函数，它其实是由一个或多个Transact-SQL语句组成的子程序，能够接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回。返回值可以是单个标量值或结果集。7.2.1案例实现过程【技术要点】

使用SQLServerManagementStudio来创建用户自定义函数，对象资源管理器中选择要创建自定义函数的数据库，这里我们选择【教学管理系统】数据库，然后在其中【可编程性】文件夹中选择【函数】，打开后即可见到相应的函数目录，右击鼠标选择要创建的函数类型，如图7.18所示。图7.18在对象资源管理器中选择创建自定义函数

这里我们选择【新建一个内联表值函数】，就会在SQLServerManagementStudio的右边窗口打开一个如图7.19所示的模板窗口。图7.19SQLServerManagementStudio中创建自定义函数的模板

然后在SQLServerManagementStudio的【查询】菜单中选择【指定模板参数的值】选项，弹出如图7.20所示对话框，在其中根据提示指定模板各参数的值。

图7.20指定模板参数的值

确定后，在模板中根据任务更改语句体实现所需功能，按F5键分析无错误后，选择【查询】菜单中的【执行】或按CTRL+F5键完成创建。7.2.2相关知识及注意事项1.为何使用用户定义函数

用户自定义函数在实际编程中用处相当大，可以针对特定应用程序问题提供解决方案。自定义函数可以简单到计算一个值，也可能复杂如定义和实现数据表的约束。在SQLServer中使用用户定义函数有以下优点：（1）允许模块化程序设计。（2）执行速度更快。（3）减少网络流量。2.自定义函数的类型

根据自定义函数返回值的类型，自定义函数可以分为三类,

SQLServer2005

中为三种类型的用户自定义函数提供了不同的命令创建格式。1)标量型函数（Scalarfunctions）

标量型函数返回一个确定类型的标量值，其返回值类型为除了text,ntext,image,cursor,timestampt和table类型外的其它数据类型。2)内嵌表值型函数（Inlinetable-valuedfunctions）

内嵌表值型函数以表的形式返回一个返回值，即它返回的是一个表。3)多语句表值型函数（Multi-statementtable-valuedfunctions）

多语句表值型函数可以看作标量型和内联表值型函数的结合体。3.用户自定义函数的管理1）查看用户自定义函数

通过系统存储过程sp_help可以查看自定义函数的信息。2）修改用户自定义函数

要对自定义函数进行修改，可以在SQLServerManagementStudio的对象资源管理器中找到要修改的函数，然后右击鼠标，在弹出的菜单中选择【修改】选项就可打开修改窗口，如图7.26所示。3）删除用户自定义函数

要删除自定义函数，只要可以在SQLServerManagementStudio的对象资源管理器中找到要修改的函数，然后右击鼠标，在弹出的菜单中选择【修改】选项就可删除，参见图7.26所示。图7.26通过SQLServerManagementStudio修改自定义函数

7.3触发器【技术要点】

触发器是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如Update、Insert、Delete这些操作时，SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。

触发器一个应用就是保持和维护数据的完整性及合法性，那么怎么来理解呢？就是说你可以在程序里提交任意数据，然后由触发器来判断数据的完整性及合法性，当然这里只是举例说明，实际应用中不推荐这样用，应该由应用程序来验证数据的完整性及合法性。7.3.1案例实现过程

下面我还是以实例的方式来描述触发器的应用。

【例7.22】创建UPDATE触发器，当对教师表中的所在部门修改时，显示修改前后的部门。

教学管理系统数据库中的“教师表”和“授课表”，它们都包含有“教工编号”字段。现在，我们要修改“教师表”中的教工编号，那么“授课表”中的“教工编号”也要随之更改，我们可以用触发器来完成“授课表”中的记录修改过程。这个例子中我们使用SQLServerManagementStudio的模板来创建触发器，在对象资源管理器窗口中，打开【教学管理系统】数据库的文件夹，展开其中【表】目录，选择我们要建立触发器的“教师表”上单击鼠标，在右边弹出的窗口中选择【触发器】并右击鼠标，在弹出的菜单中选择【新建触发器】，如图7.27所示。图7.27在SQLServerManagementStudio中新建触发器的操作

在右侧查询编辑器中出现触发器设计模板，用户可以在此基础上编辑触发器，如图7.28所示。图7.28在SQLServerManagementStudio中创建触发器的模板窗口

然后，【查询】菜单中选择【指定模板参数的值】选项，弹出如图7.29所示对话框，在该对话框中指定模板各参数的值。图7.29在“指定模板参数的值”对话框中设定触发器的基本参数

最后，我们对代码编辑窗口中的程序代码进行编辑。7.3.2相关知识及注意事项1．数据库触发器的作用

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：

1）强化约束（Enforcerestriction）

2）跟踪变化Auditingchanges

3）级联运行（Cascadedoperation）。

4）存储过程的调用（Storedprocedureinvocation）。2．数据库触发器的类型1）DML触发器（1）AFTER后触发器（2）INSTEADOF前触发器2）DDL触发器3．创建触发器1）创建INSERT触发器

INSERT触发器当向表中添加记录时触发，为了维护数据完整性，当表中添加了新的记录后，应该对其关联表的数据进行调整，以实时反映数据的变化。2）创建UPDATE触发器

UPDATE触发器和INSTEAD触发器的工作过程基本一样，修改一条记录等于插入了一条新的记录并删除一条旧的记录。

在有些更新中，更新的内容并不是整个记录，而仅仅是一列或几列，这时就要用到用于检查列改变的更新型触发器。它与通常意义上的触发器不同之处主要表现在它使用IFUPDATE（列名）来实现特定列被更新时激活触发器，而不论该更新影响的是表中的一行还是多行。如果需要实现多个特定列中的任意一列被更新激活触发器，可以在触发器定义中使用多个IFUPDATE（列名）语句。3）创建DELETE触发器

DELETE触发器当删除表中数据时触发，用它可以实现级联删除。4）INSTEADOF触发器

INSTEADOF触发器被某一操作（INSERT、DELETE、UPDATE）触发时，相应的操作并不被执行，运行的仅是触发器SQL语句本身。5）嵌套触发器

当某一触发器执行时，其能够触发另外一个触发器，这种情况称之为触发器嵌套。在MSSQLServer中触发器能够嵌套至32层。如果不需要嵌套触发器，可以通过sp_configure选项来进行设置。图7.31触发INSTEAD触发器的结果6）创建DDL触发器4.管理触发器1）查看触发器信息

用户必须在当前数据库中查看触发器的信息，而且被查看的触发器必须已经被创建。系统存储过程sp_helpsp_helptext和sp_depends分别提供有关触发器的不同信息。2）修改删除触发器

通过EnterpriseManager和系统过程或Transaction_SQL命令，可以修改触发器的名字和正文。（1）使用sp_rename命令修改触发器的名字（2）通过EnterpriseManager修改触发器（3）通过Alerttrigger命令修改触发器正文（4）禁用与启用触发器（5）删除触发器

7.4游标

关系数据库中的操作会对整个行集起作用。由SELECT语句返回的行集包括满足该语句的WHERE子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展。7.4.1案例实现过程【技术要点】①定义一个游标变量，声明游标存储的结果集。②打开游标。③通过循环从游标里取得数据并进行处理。④关闭游标。⑤删除游标。

由于使用游可以将结果集一条条取出来处理，所以增加了服务器的负担。再者使用游标的效率远远没有使用默认的结果集效率高。在默认结果集中，从客户端发送到服务器端，只有一个待执行语句的数据包，而使用游标时，每次循环都要从客户端发送一个数据包给服务器，然后在服务器上要对每一个数据包进行分析、编译并执行，因此效率会远远降低，所以，在能不使用游标时，请尽量不要使用，除非是要在SQLServer服务器上进行很复杂的数据操作。在本章里的救命是为了介绍游标而创建的，每一个示例都有更节省资源的方法得到相同的结果。7.4.2相关知识及注意事项1.什么是游标

游标是取用一组数据并能够一次与一个单独的记录进行交互的方法。它几乎不会如想象的那样很经常地发生。然而，有时，确实不能通过在整个行集中修改或者甚至选取数据来获得所需要的结果。行集是由所有行共有的一些东西产生（通过SELECT语句定义），但是，接下来需要逐一处理这些行。2.游标的生命周期

在进入到实际的语法之前必须明白，游标的使用需要不止一个语句——实际上，要用到几个语句。主要的部分有：1）声明；2）打开；3）使用或导航；4）关闭；5）释放。3.游标的操作

游标