资源简介

(共54张PPT)
7
存储过程、触发器
与游标
第
章
本章导读
存储过程是一条或多条T-SQL语句的集合，在实际应用中调用存储过程能够提高工作效率、增强数据安全性。触发器是一种特殊的存储过程，它的执行不需要调用，而是满足某条件时自动触发，使用触发器能够加强数据的完整性约束和业务规则等。游标通常存在于存储过程中，它能够对查询语句的结果集进行数据处理操作。
本章将介绍存储过程、触发器与游标的基本概念，以及使用T-SQL对它们进行创建、管理等操作的方法。
知识目标
理解存储过程、触发器与游标的基本概念。
能力目标
能够使用T-SQL创建、执行及管理存储过程。
能够使用SSMS创建、执行及管理存储过程。
能够使用T-SQL创建、管理触发器。
能够使用T-SQL对游标进行基本操作。
素质目标
拓展思维宽度，培养实践能力。
项目导航
存储过程
1
触发器
2
游 标
3
01
存储过程
7.1.1初识存储过程——存储过程的概念1存储过程是一组用于完成特定功能的T-SQL语句集，这些语句经过编译后存储在数据库中。存储过程可以被客户机或应用程序调用，使用它既可以保证数据的安全性又能够避免重复输入代码。存储过程的分类2（1）系统存储过程。系统存储过程是系统提供的存储过程，主要存储在master数据库中并以“sp_”为前缀命名，它的主要功能是从系统表中获取信息。（2）用户自定义存储过程。用户自定义存储过程是程序开发人员或数据库管理员为了完成某些特定功能而编写的存储过程，它可以接收用户提供的参数，也可以向客户端返回结果等。用户自定义存储过程的命名不要以“sp_”开头，避免执行发生错误。（3）扩展存储过程。扩展存储过程是用于扩展SQL Server服务器功能的存储过程，通常以“xp_”为前缀命名。对于用户而言，拓展存储过程与其他存储过程一样，都可以通过调用的方式执行。7.1.1初识存储过程——存储过程的优点3（1）存储过程能够加快系统运行速度。（2）存储过程能够简化操作流程。（3）存储过程有助于实现模块化的程序设计。（4）存储过程可以降低网络流量。（5）存储过程能够提高数据的安全性。提示与查询操作类似，存储过程、触发器及游标的相关操作主要使用T-SQL实现，故本章主要介绍使用T-SQL对它们进行操作的方法。7.1.2创建与执行存储过程——1．创建存储过程创建存储过程通常指创建用户自定义存储过程，其T-SQL命令为CREATE PROCEDURE，具体语法格式如下：（1）@parameter表示存储过程的参数，在创建存储过程的语句中可以声明一个或多个参数，当声明多个参数时用“,”隔开。（2）<数据类型>指明参数的数据类型。（3）DEFAULT表示参数的默认值。（4）OUTPUT表示参数是输出参数，设置此项可以向调用者返回信息。（5）[设定选项]中可设置其他设定选项，具体内容读者可查阅相关资料。（6）AS关键字指明该存储过程要执行的操作，其后的<T-SQL语句>指明操作的具体内容。CREATE PROCEDURE <存储过程名称>[{@parameter <数据类型>}[=DEFAULT] [OUTPUT][ , … n]][设定选项]AS <T-SQL语句>——【例 7-1】创建一个存储过程pr_s，用于查询数据表student中所有年龄大于18岁的学生信息。CREATE PROCEDURE pr_s ASSELECT * FROM student WHERE age>18【例 7-2】创建一个存储过程pr_si，用于向数据表student中插入一条记录，记录的内容由参数提供。CREATE PROCEDURE pr_si@sno varchar(6),@sn nvarchar(12),@sex nchar(1),@dept nvarchar(10),@age intAS INSERT INTO student VALUES(@sno,@sn,@sex,@dept,@age)7.1.2创建与执行存储过程1．创建存储过程——【例 7-3】创建一个存储过程pr_ss，用于返回学生的姓名和系别，输入的参数为学生的学号。CREATE PROCEDURE pr_ss@sno varchar(6),@sn nvarchar(15) OUTPUT,@dept nvarchar(10) OUTPUTASSELECT @sn=sn,@dept=dept FROM student WHERE sno=@sno7.1.2创建与执行存储过程1．创建存储过程——知识库使用SSMS创建存储过程的步骤为（以完成例7-1为例），在“对象资源管理器”窗口中依次双击“数据库”→“scs”→“可编程性”节点，右击其下方的“存储过程”节点，在弹出的快捷菜单中选择“新建”→“存储过程”菜单项，打开创建存储过程的查询编辑窗口，其中显示了CREATE PROCEDURE语句的模板，修改存储过程名称，并在BEGIN…END代码块中输入需要添加的T-SQL语句，删除不需要的代码，然后单击“执行”按钮即可，如图7-1所示。图7-1使用SSMS创建存储过程7.1.2创建与执行存储过程1．创建存储过程7.1.2创建与执行存储过程——2．执行存储过程想要发挥存储过程的作用，还需要在创建之后执行它。执行存储过程的T-SQL命令为EXECUTE，具体语法格式如下：（1）EXECUTE命令可简写为EXEC。（2）“@整型变量”表示整数类型变量，用于保存存储过程的返回状态。（3）“@参数”表示存储过程中定义的参数，列举这些参数时需与定义的顺序一致。（4）“参数值”表示执行存储过程时需要输入的参数的值。（5）“@变量”是用于存储参数或返回参数的变量。（6）OUTPUT用于指定参数为输出参数。（7）DEFAULT用于提供参数的默认值。[EXECUTE|EXEC][<@整型变量>=]<存储过程名称>[[@参数=]{<参数值>|<@变量>[OUTPUT] |[DEFAULT]}][ , … n]——【例 7-4】执行存储过程pr_s，查询数据表student中所有年龄大于18岁的学生信息，结果如图7-2所示。EXECUTE pr_s图7-2例7-4的查询结果【例 7-5】执行存储过程pr_si，向数据表student中插入一条记录（12005，张三，男，计算机，20）。EXECUTE pr_si@sno='12005',@sn='张三',@sex='男',@dept='计算机',@age=207.1.2创建与执行存储过程2．执行存储过程——知识库使用SSMS执行存储过程的步骤为（以完成例7-5为例），在“对象资源管理器”窗口中依次双击“数据库”→“scs”→“可编程性”→“存储过程”节点，右击其下方的“dbo.pr_si”节点，在弹出的快捷菜单中选择“执行存储过程”菜单项，打开“执行过程- [dbo].[pr_si]”窗口，在各参数的“值”栏中输入记录的内容，然后单击“确定”按钮，如图7-3所示。图7-3使用SSMS执行存储过程7.1.2创建与执行存储过程2．执行存储过程——【例 7-6】执行存储过程pr_ss，查询学号为“11001”的学生的姓名和系别，结果如图7-4所示。DECLARE @stusno char(6)DECLARE @stuname nvarchar(15)DECLARE @studept nvarchar(10)SET @stusno='11001'EXECUTE pro_ss @stusno,@stuname output,@studept outputPRINT '学号：'+@stusno+'姓名：'+@stuname+'系别：'+@studept图7-4例7-6的查询结果7.1.2创建与执行存储过程2．执行存储过程7.1.3管理存储过程——1．修改存储过程修改存储过程的T-SQL命令为ALTER PROCEDURE，具体语法格式如下：ALTER PROCEDURE <存储过程名称>[{@parameter <数据类型>}[=DEFAULT] [OUTPUT][ , … n]]AS <T-SQL语句>【例 7-7】修改存储过程pr_s，将其功能改为查询所有学生的信息并按学生姓名排序，接着执行该存储过程，结果如图7-5所示。ALTER PROCEDURE pr_sASSELECT * FROM student ORDER BY snGOEXECUTE pr_s图7-5例7-7的查询结果——知识库使用SSMS修改存储过程的步骤为，右击相应的存储过程节点，在弹出的快捷菜单中选择“修改”菜单项，打开该存储过程的查询编辑窗口，在相应的位置修改代码并保存即可，如图7-6所示。图7-6使用SSMS修改存储过程7.1.3管理存储过程1．修改存储过程7.1.3管理存储过程——2．查看存储过程（1）查看存储过程的信息。查看存储过程的信息需要使用存储过程sp_help，具体语法格式如下：EXECUTE sp_help <存储过程名称>【例 7-8】查看存储过程pr_s的信息，结果如图7-7所示。EXECUTE sp_help pr_s图7-7例7-8的执行结果——知识库使用SSMS查看存储过程信息的步骤为（以存储过程pr_s为例），右击相应的存储过程节点，在弹出的快捷菜单中选择“属性”菜单项，打开“存储过程属性- pr_s”窗口，在该窗口中可以查看存储过程的相关信息，如图7-8所示。图7-8查看存储过程的信息7.1.3管理存储过程2．查看存储过程——（2）查看存储过程的源代码。查看存储过程的源代码需要使用存储过程sp_helptext，具体语法格式如下：EXECUTE sp_helptext <存储过程名称>【例 7-9】查看存储过程pr_s的源代码，结果如图7-9所示。EXECUTE sp_helptext pr_s图7-9例7-9的执行结果7.1.3管理存储过程2．查看存储过程7.1.3管理存储过程——3．删除存储过程删除存储过程的T-SQL命令为DROP PROCEDURE，具体语法格式如下：DROP PROCEDURE <存储过程名称>【例 7-10】删除存储过程pr_s。DROP PROCEDURE pr_s提示使用SSMS删除存储过程的步骤为，右击相应的存储过程节点，在弹出的快捷菜单中选择“删除”菜单项，打开“删除对象”窗口，单击“确定”按钮。——课堂总结① 初识存储过程② 创建与执行存储过程③ 管理存储过程
02
触发器
7.2.1初识触发器——触发器的概念1触发器是一个特殊的存储过程，它定义的T-SQL语句不需要手动调用，而是在满足一定条件后自动调用执行。触发器是一个特殊的存储过程，它定义的T-SQL语句不需要手动调用，而是在满足一定条件后自动调用执行。触发器的分类2SQL Server支持3种触发器，分别为数据操纵语言（data manipulation language, DML）触发器、数据定义语言（data definition language, DDL）触发器和登录（LOGIN）触发器。DML触发器可以响应数据表上发生的INSERT、UPDATE和DELETE操作；DDL触发器可以响应数据库或服务器上发生的CREATE、ALTER和DROP操作；LOGIN触发器可以响应用户在登录时的一些操作。7.2.2创建触发器——创建触发器的T-SQL命令为CREATE TRIGGER，具体语法格式如下：CREATE TRIGGER <触发器名称>ON {表名|视图名}[WITH ENCRYPTION]{FOR|AFTER|INSTEAD OF}{[INSERT][,][UPDATE][,][DELETE]}AS <T-SQL语句>（1）{表名|视图名}指定执行触发器的数据表或视图。（2）WITH ENCRYPTION表示对触发器进行加密处理。（3）AFTER表示触发器只有在T-SQL语句中指定的所有操作都执行成功后才被触发；若仅指定FOR关键字，则表示默认为AFTER。7.2.2创建触发器——（4）INSTEAD OF指定执行的是触发器而不是触发T-SQL语句，在使用了WITH CHECK OPTION语句的视图上不能定义该项触发器。（5）[INSERT][,][UPDATE][,][DELETE]指定激活触发器的操作，至少指定一个操作。（6）AS指明触发器响应的操作，其后的<T-SQL语句>指明操作的具体内容，一般不向应用程序返回结果。知识库在创建INSERT、UPDATE与DELETE触发器时，可以使用INSERTED、UPDATED与DELETED表示插入记录的临时表、更新数据的临时表与删除数据的临时表。7.2.2创建触发器——【例 7-11】设计一个触发器tr_d，禁止删除数据表student中的数据，并在执行删除操作后显示“无法删除该表数据”；然后执行删除操作进行验证，结果如图7-10所示。CREATE TRIGGER tr_d ON studentINSTEAD OF DELETEAS PRINT '无法删除该表数据'GODELETE FROM studentWHERE sno='11001'图7-10例7-11的验证结果7.2.2创建触发器——知识库DML触发器与DDL触发器的主要区别在于两者的作用域不同。DML触发器通常作用于数据库中的数据表或视图，如例7-11所示。DDL触发器通常作用于整个数据库或整个服务器。例如，创建一个触发器tr_safe，禁止修改和删除当前数据库中的任何数据表。其中，DATABASE表示作用于当前数据库；ROLLBACK表示回滚，即将数据库恢复到执行此项T-SQL语句之前。CREATE TRIGGER tr_safeON DATABASEFOR DROP_TABLE,ALTER_TABLEAS PRINT'不能删除或修改当前数据库的数据表！'ROLLBACK7.2.3管理触发器——1．修改触发器（1）修改触发器基本信息的T-SQL命令为ALTER TRIGGER，具体语法格式与CREATE TRIGGER语句相同，此处不再赘述。【例 7-12】修改触发器tr_d，将其触发条件修改为插入操作，同时修改输出语句的内容。ALTER TRIGGER tr_d ON studentINSTEAD OF UPDATEAS PRINT '无法更新该表数据'（2）重命名触发器。使用存储过程sp_rename能够修改触发器的名称，具体语法格式如下：【例 7-13】将触发器tr_d更名为tr_de。EXEC sp_rename tr_d,tr_deEXEC sp_rename 原名称,新名称7.2.3管理触发器——2．查看触发器使用存储过程能够查看触发器的相关信息，具体有如下3种存储过程。（1）sp_help，显示触发器名称、类型、创建时间等信息。（2）sp_helptext，显示触发器源代码。（3）sp_depends，显示触发器参考的对象。【例 7-14】查看触发器tr_de的参考对象，结果如图7-11所示。EXEC sp_depends tr_de图7-11例7-14的执行结果——知识库使用SSMS查看触发器的步骤为（以查看触发器tr_de的源代码为例），在“对象资源管理器”窗口中依次双击“数据库”→“scs”→“表”→“dbo.student”→“触发器”节点，右击其下方的“tr_de”节点，在弹出的快捷菜单中选择“修改”菜单项，打开该触发器的查询编辑窗口，即可查看该触发器的源代码，如图7-12所示。图7-12使用SSMS查看触发器的源代码7.2.3管理触发器2．查看触发器7.2.3管理触发器——3．管理触发器（1）禁用触发器的T-SQL命令为DISABLE TRIGGER，具体语法格式如下：【例 7-15】禁用触发器tr_de。DISABLE TRIGGER tr_de ON studentDISABLE TRIGGER{<触发器名>[ , … n]|ALL}ON <作用域>其中，ALL是指作用于全部触发器；<作用域>是指该触发器发生作用的对象，如student、DATABASE等。——（2）启用触发器的T-SQL命令为ENABLE TRIGGER，具体语法格式如下：【例 7-16】启用触发器tr_de。ENABLE TRIGGER tr_de ON studentENABLE TRIGGER{<触发器名称>[ , … n]|ALL}ON <作用域>7.2.3管理触发器3．管理触发器7.2.3管理触发器——4．删除触发器删除触发器的T-SQL命令为DROP TRIGGER，具体语法格式如下：【例 7-17】删除触发器tr_de。DROP TRIGGER tr_deDROP TRIGGER <触发器名称>高手点拨删除DDL触发器时，可能出现因为不具备相关权限而无法使用DROP命令删除触发器的情况。这时可以使用SSMS进行删除，在“对象资源管理器”窗口中打开当前数据库节点，然后双击“可编程性”→“数据库触发器”节点，在展开的列表中右击想要删除的触发器节点，在弹出的快捷菜单中选择“删除”菜单项，打开“删除对象”窗口，确认删除对象后，单击“确定”按钮即可完成删除。——课堂总结① 初识触发器② 创建触发器③ 管理触发器
03
游标
7.3.1初识游标——游标的概念1游标是一种定位并控制结果集的机制，它能够从包括多条记录的结果集中每次提取一条记录。游标可以看作是由结果集和结果集中指向特定记录的游标位置组成的临时文件，它提供了在结果集中向前或向后浏览记录和处理结果集中数据的能力。游标主要具有如下功能。（1）能够定位结果集中的特定行。（2）能够从结果集中检索一行或多行。（3）能够对结果集中的当前行进行数据修改操作。（4）能够提供不同级别的可见性支持。（5）能够在存储过程、触发器和T-SQL脚本中使用。7.3.1初识游标——游标的分类2（1）T-SQL游标。T-SQL游标是指使用T-SQL语句创建的游标，主要用于存储过程、触发器和T-SQL脚本。T-SQL游标主要用在服务器上，由从客户端发送到服务器的T-SQL语句进行管理。（2）应用程序编程接口（API）游标。API游标主要用在服务器上。每次客户端应用程序调用API游标时，SQL Sever Native Client OLE DB访问接口或ODBC驱动程序会将这些请求传送给服务器，以对API游标进行处理。（3）客户端游标。客户端游标主要在客户机上缓存结果集时使用。每次客户端应用程序调用API游标时，SQL Server Native Client ODBC驱动程序或ADO DLL会对客户端上高速缓存的结果集中的记录执行游标操作。7.3.2游标的基本操作——1．声明游标声明游标的T-SQL命令为DECLARE CURSOR，具体语法格式如下：DECLARE <游标名称> CURSOR [LOCAL|GLOBAL][设置选项]FOR <SELECT语句>[FOR UPDATE [OF <列名> [ , … n]]]（1）LOCAL表示该游标的作用域是局部的，仅在创建该游标的批处理、存储过程或触发器内有效。（2）GLOBAL表示该游标的作用域是全局的，在当前数据连接中的任何批处理、存储过程或触发器内都可以引用它。（3）[设置选项]表示其他关于游标的设置选项，感兴趣的读者可查阅相关资料。（4）<SELECT语句>是定义游标结果集的SELECT语句。（5）[FOR UPDATE [OF <列名> [ , … n] ]]用于定义游标内可更新的列，省略时表示所有列均可更新。——【例 7-18】创建游标cr_s，结果集为所有学生的姓名及系别。DECLARE cr_s CURSOR FORSELECT sn,dept FROM student7.3.2游标的基本操作1．声明游标7.3.2游标的基本操作——2．打开游标打开游标的T-SQL命令为OPEN，具体语法格式如下：OPEN 游标名称|游标变量其中，游标名称是指已声明的游标的名称，游标变量是指引用游标的名称。【例 7-19】打开游标cr_s。OPEN cr_s7.3.2游标的基本操作——3．读取游标中的数据读取游标中数据的T-SQL命令为FETCH，具体语法格式如下：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]游标名称}|@游标变量}[INTO @变量 [ , … n]]（1）NEXT表示返回紧邻当前行之后的行，同时将返回的行作为当前行。该项为默认值，在第一次执行时返回结果集中的第一行。（2）PRIOR表示返回紧邻当前行之前的行，同时将返回的行作为当前行。在第一次执行时，没有结果行可以返回，游标将置于第一行之前。（3）FIRST表示返回结果集中的第一行，并将其作为当前行。——（4）LAST表示返回结果集中的最后一行，并将其作为当前行。（5）ABSOLUTE{n|@nvar}表示返回第n行，若n或@nvar为正数，返回从头开始的第n行，并将该行作为当前行；若n或@nvar为负数，返回从尾开始的第n行，并将该行作为当前行；若n或@nvar为0，则不返回任何行。其中，n必须为整型常量，@nvar类型必须为SMALLINT、TINYINT或INT。（6）RELATIVE{n|@nvar}表示返回当前行之后的第n行，若n或@nvar为正数，返回当前行之后的第n行，并将该行作为当前行；若n或@nvar为负数，返回当前行之前的第n行，并将该行作为当前行；若n或@nvar为0，返回当前行。其中，n必须为整型常量，@nvar类型必须为SMALLINT、TINYINT或INT。（7）[INTO @变量[ ,…n]]表示允许将提取操作的列数据放到局部变量中。列表中变量的顺序、数据类型及数目应与游标结果集中的列保持一致。7.3.2游标的基本操作3．读取游标中的数据——【例 7-20】使用游标cr_s并逐行读取其中的数据，结果如图7-13所示。FETCH NEXT FROM cr_sWHILE @@FETCH_STATUS=0BEGINFETCH NEXT FROM cr_sEND图7-13例7-20的读取结果提示@@FETCH_STATUS是SQL Server中的全局变量，其值由SSMS管理。当FETCH命令成功提取数据时，该变量值为0；当FETCH命令无法提取数据时，即数据已全部提取时，该变量值为-1；当FETCH命令想要提取的数据不存在或进程意外中断时，该变量值为-2。7.3.2游标的基本操作3．读取游标中的数据7.3.2游标的基本操作——4．关闭游标关闭游标的T-SQL命令为CLOSE，语法格式与打开游标相同，此处不再赘述。由于打开游标后，服务器会提供一定的内存空间存放游标操作的结果集，同时使用游标也可能会对某些数据进行封锁。因此，当暂时不使用游标时，通常将其关闭，以释放游标结果集占用的服务器资源，并解除游标对数据的封锁。【例 7-21】关闭游标cr_s。CLOSE cr_s7.3.2游标的基本操作——5．释放游标释放游标就是删除游标。关闭游标后虽然游标结果集已被释放，但游标结构仍存在，并占有一定的服务器资源。为了收回游标结构占用的资源，当某游标不再使用时应将其删除。释放游标的T-SQL命令为DEALLOCATE，语法格式与打开游标相同，此处不再赘述。【例 7-22】释放游标cr_s。DEALLOCATE cr_s7.3.2游标的基本操作——6．修改游标结果集中的记录修改游标结果集中记录的T-SQL命令为UPDATE，具体语法格式如下：【例 7-23】使用游标将学生陈月的年龄修改为18。DECLARE cr_age CURSOR /*声明游标*/FORSELECT ageFROM studentWHERE sn='陈月’UPDATE <数据表名称>SET <列名>=<值>WHERE CURRENT OF <游标名称>——OPEN cr_age /*打开游标*/FETCH FROM cr_age /*从游标中获取数据*/UPDATE studentSET age=18WHERE CURRENT OF cr_age /*修改当前游标中的数据*/CLOSE cr_age /*关闭游标*/DEALLOCATE cr_age /*释放游标*/执行上述语句后，“结果”窗口将显示学生陈月修改前的年龄，如图7-14所示。但此时数据表student中的数据已经更改，再次查询该表中的数据，结果如图7-15所示。图7-14例7-23的执行结果图7-15数据表student的数据7.3.2游标的基本操作6．修改游标结果集中的记录7.3.2游标的基本操作——7．删除游标结果集中的记录删除游标结果集中记录的T-SQL命令为DELETE，具体语法格式如下：【例 7-24】使用游标删除学号为11002的学生的选课记录。DECLARE cr_de CURSOR /*声明游标*/FORSELECT *FROM sgradeWHERE sno='11002'OPEN cr_de /*打开游标*/DELETE FROM <数据表名称>WHERE CURRENT OF <游标名称>——FETCH FROM cr_de /*从游标中获取数据*/DELETE FROM sgradeWHERE CURRENT OF cr_de /*删除当前游标中的数据*/CLOSE cr_de /*关闭游标*/DEALLOCATE cr_de /*释放游标*/执行上述语句后，“结果”窗口将显示学号为11002的学生的选课记录，如图7-16所示。但此时数据表sgrade中的数据已经更改，再次查询该表中的数据，结果如图7-17所示。图7-16例7-24的执行结果图7-17数据表sgrade的数据7.3.2游标的基本操作7．删除游标结果集中的记录实战演练——在订货管理系统数据库中创建存储过程与触发器1．实战目的（1）掌握存储过程的基本操作。（2）掌握触发器的基本操作。2．实战内容（1）创建一个存储过程pr_g，用于查询数据表goods中所有价格小于20元的商品信息。CREATE PROCEDURE pr_g ASSELECT * FROM goods WHERE price<20实战演练——在订货管理系统数据库中创建存储过程与触发器（2）创建一个存储过程pr_oi，用于向数据表ordering中插入一条记录，记录的内容由参数提供。CREATE PROCEDURE pr_oi@oid varchar(12),@cid varchar(6),@gid varchar(6),@ordate date,@num intAS INSERT INTO ordering VALUES(@oid,@cid,@gid,@ordate,@num)实战演练——在订货管理系统数据库中创建存储过程与触发器（3）创建一个触发器tr_up，当用户更新商品名称后触发，并返回所更新商品的商品编号与更新前后的商品名称。CREATE TRIGGER tr_upON goodsAFTER UPDATEASBEGINSELECT gid AS 商品编号, gn AS 更新前商品名称 FROM DELETEDSELECT gid AS 商品编号, gn AS 更新后商品名称 FROM INSERTEDEND——课堂总结① 初识游标② 游标的基本操作

展开更多......

收起↑