SQL Server 数据库基础及应用-8

8.1存储过程8.1.1存储过程基础存储过程是一系列预先编辑好的，能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQLServer服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。存储过程分为3类:系统存储过程、扩展存储过程和用户定义存储过程。1)系统存储过程，创建并保存在master数据中，都是以“sp_”为前缀命名的，可以在任何数据库中使用系统存储过程。SQLServer中的许多管理工作都是通过执行系统存储过程来完成的，用户创建的存储过程其名字最好不要以“sp_”为前缀命名，如数据库查看、索引查看、默认和规则的绑定及删除绑定等。下一页返回8.1存储过程2)扩展存储过程，主要提供从SQLServer到外部程序的接口，以便进行各种维护活动。3)用户定义存储过程，用户为完成某一特定功能而编写的存储过程。它主要在应用程序中使用，可以完成特定的任务。8.1.2创建存储过程存储过程的三个组成部分:1.所有的输入参数以及传给调用者的输出参数。2)被执行的针对数据库的操作语句，包括调用其他存储过程的语句。3)返回给调用者的状态值，以指明调用是成功还是失败。上一页下一页返回8.1存储过程创建存储过程的语法如下存储过程的功能主要靠AS后面的语句实现，其中对SELECT语句的灵活应用尤为重要。创建存储过程可以选择“数据库”、“图书管理”、“存储过程”，右击弹出快捷菜单，选择其中的“新建存储过程”命令，打开查询窗口，如图8-1所示。上一页下一页返回8.1存储过程

或者可以直接单击工具栏上的新建查询按钮新建查询(N)，打开新的查询窗口，编写新建存储过程的代码，如图8-2所示。

8.1.3执行存储过程存储过程创建成功后，可以执行存储过程，并在查询结果窗口中查看相应的返回结果。执行存储过程的语法如下:EXECprocedurename在查询窗口执行存储过程“学生信息查询”，并返回结果，如图8-5所示。在查询窗口执行存储过程“成绩查询”，并返回结果，如图8-6所示。另外，要执行存储过程还可以选中相应的存储过程，单击右键，在弹出的快捷菜单中选择“执行存储过程”命令，如图8一7所示。上一页下一页返回8.1存储过程打开“执行过程”窗口，单击其中的“确定”按钮，如图8-8所示。系统执行存储过程后，显示的执行结果如图8一9所示。在该窗口中自动显示了执行存储过程的语句。8.1.4修改、查看、重命名、删除存储过程1.修改存储过程在实际应用开发中，有时需要对已经设计好的存储过程进行修改。一种方法是把旧的存储过程删除，然后再重新建立存储过程，使用这种方法时，所有与该存储过程相关联的权限都将丢失;另一种方法是用单个的步骤更改该存储过程，这种方法可以更改过程或参数定义，但为该存储过程定义的权限将保留。上一页下一页返回8.1存储过程公查看存储过程1)查看存储过程的定义脚本如图8一10所示，格式如下SPHELPTEXTprocedurename上一页下一页返回8.1存储过程2)查看存储过程的信息如图8一11、图8一12所示，格式如下SPHELPprocedurename该命令可以查看存储过程的名称、拥有者、类型、创建日期以及参数的名称、类型、冤度、精度和默认值。3.删除存储过程格式如下:DROPPROCEDUREprocedurename修改、查看、重命名、删除存储过程也可以通过单击鼠标左键选中相应的存储过程，然后单击鼠标右键，在弹出的快捷菜单中，选择要进行的相应操作，如图8一13所示。上一页下一页返回8.1存储过程8.1.5存储过程应用1.带输人参数的存储过程输入参数是指由调用程序向存储过程传递的参数。输入参数在创建存储过程语句中定义，在CREATEPROCEDURE语句中声明;执行存储过程时要给出输入参数的值，并通过EXECpros_name语句进行传递。声明输入参数的语法如下上一页下一页返回8.1存储过程2.带输出参数的存储过程当需要从存储过程中返回一个或多个值时，可以在创建存储过程的语句中定义输出参数，输出参数需要在CREATEPROCEDURE语句中使用OUTPUT关键字说明。上一页返回8.2

触发器8.2.1触发器基础触发器与表是紧密联系的，触发器不能离开表而独立存在。针对数据的插入、修改、删除操作，触发器也有三种类:INSERT,UPDATE,DELETE。虽然触发器的功能比较强大，但是触发器性能通常比较低，运行触发器时，系统处理的大部分时间用于参照其他表的处理上，因此不要创建太多的触发器。在创建每一个触发器时系统都会自动生成逻辑表，即inserted表或‘Ieleted表，其中inserted表用于存储数据操作过程中新的数据，而‘Ieleted用于存储数据操作过程中旧的数据。当触发行为没有执行成功时，系统可以从这两个表里提取信息还原数据库数据。针对Insert命令，插入的新数据同时保存在生成的inserted表中。下一页返回8.2

触发器

针对delete命令，删除的旧数据同时保存在生成的delete表中。针对delete命令，替换的新数据保存在生成的inserted表中，被替换的旧数据保存在生成的delete表中。8.2.2创建触发器创建触发器的语法如下上一页下一页返回8.2

触发器8.2.3查看、修改和删除触发器1.查看触发器查看触发器可以了解触发器的类型、名称、所有者、创建日期、定义以及触发器的触发行为。1)使用存储过程sp_helptrigger查看表中触发器及其类型，如图8-32所示。2)使用存储过程“sphelptext‘成绩修改”’查看触发器定义，如图8-33所示。3)使用存储过程“sp_depends‘成绩修改”’查看触发器的相关性，如图8-34和图8-35所示。上一页下一页返回8.2

触发器4)选择“表”->dlbo.average“->”触发器”、“成绩修改”，单击鼠标右键，弹出的快捷菜单如图8一36所示。选择“查看依赖关系”，可以查看“借书触发”触发器的对象依赖关系，如图8-37所示。公修改触发器可以根据需求对触发器的定义或功能进行修改。1)选择“表”->“dbo.average"->"触发器”、“成绩修改”，单击鼠标右键，选择“修改”，如图8一38所示。在弹出的触发器定义窗口进行修改。上一页下一页返回8.2

触发器3.删除触发器触发器不再使用时，可以删除。1.选择“表”->“dbo.average"->"触发器”、“成绩修改”，单击鼠标右键，选择“删除”，如图8一38所示。上一页下一页返回8.2

触发器2)使用、troptrigger命令。

DROPTRIGGERtriqqername8.2.4触发器应用delete触发器:因删除操作而引起触发行为。上一页返回图8一1新建存储过程返回图8-2存储过程代码编写窗口返回图8-5执行“学生信息查询”的返回结果返回图8一6执行“成绩查询”的返回结果返回图8一7执行存储过程返回图8-8执行过程窗口返回图8一9执行结果返回图8一10查看存储过程的定义脚本返回图8一11查看存储过程的信息(一)返回图8一12查看存储过程的信息(二)返回图8一13修改、查看、重命名、删除存储过程返回图8一14创建存储过程“按姓名查询”返回图8一15