《数据库SQL ServeSQLite教程》 教案 第8章 存储过程、触发器和游标

数据库SQLServeSQLite教程课程教案课程名称：数据库SQLServeSQLite教程课程号：课程学分：2参考学时：32课程性质：专业必修课适用专业：计算机类专业上课时间：授课教师：负责人：审核单位：年月日-1-一、课程基本信息课程名称数据库SQLServeSQLite教程课程号课程性质专业必修课开课部门课程负责人课程团队授课学期学分/学时2/32授课语言汉语先修课程数据库SQLServeSQLite教程课程简介从理论实践一体化角度出发，介绍了数据库的基本概念、理论、模型和方法，同时从知识技能的逻辑顺序出发，深入浅出地介绍了数据库技术的相关内容，使学生可以快速掌握数据库系统的原理，应用有关工具学习SQL，并在C#、Python等相关课程中应用，在实际应用中解决实际问题。授课班级

二、课程授课教案第8章，共11章讲课主题存储过程、触发器和游标学时2学时教学目标从理论实践一体化角度出发，介绍了数据库的基本概念、理论、模型和方法，同时从知识技能的逻辑顺序出发，深入浅出地介绍了数据库技术的相关内容，使学生可以快速掌握数据库系统的原理，应用有关工具学习SQL，并在C#、Python等相关课程中应用，在实际应用中解决实际问题。教学重点、难点存储过程是存储在数据库中的一个封装的模块或例程，能够完成特定的操作任务，其特点是可重复执行。触发器是一种特殊的存储过程，对表的操作可以自动触发；而游标好比一个“容器”，可以对结果集进行操作，主要用于存储过程或触发器中。教学设计授课提纲及重难点分析教学方法及课程思政设计教学时间8.1存储过程8.1.1存储过程的概念和分类1.存储过程的概念存储过程(StoredProcedure)是一组预先编译好的存储在服务器上的完成特定功能并且可以接受和返回用户提供的参数的Tansact-SQL语句的集合。存储过程是为了完成某一特定功能而编写的。在数据库中，存储过程可以提高程序运行的效率和可复用性。换句话说，存储过程是将常用的或很复杂的工作预先以SQL程序的形式编写好，并指定一个程序名称保存起来。存储过程中可以包含变量声明、数据存取语句、流程控制语句、错误处理语句等，在使用上非常灵活。要使用相应的功能，只需要调用该存储过程即可自动完成该项工作。2.存储过程的分类存储过程可以大大提高执行效率，增加复用性，减轻网络负担，安全性也高。如果要修改存储过程，就要进入数据库中进行修改。1)系统存储过程系统存储过程(SystemStoredProcedure)以sp_开头，如sys.sp_addgroup(在当前数据库中创建一个组)。此类存储过程是SQLServer内置的存储过程，通常用来进行系统的各项设置、读取信息或执行相关管理工作。比如sp_addserver，其功能是定义远程服务器或本地SQLServer的名称。2)扩展存储过程扩展存储过程(ExtendedStoredProcedure)通常以xp_开头，如xp_logininfo(授予登录权限的Windows组和用户信息)。此类存储过程大多是用其他编程语言如C++编写而成的，其内容并不是保存在SQLServer中，而是以DLL的形式单独存在的。3)用户自定义存储过程用户自定义存储过程(User-DefinedStoredProcedure)是由用户设计的存储过程。其名称可以是任意组合SQLServer命令规则的字符组合，通常以“usp_”开头，避免以“sp_”或“xp_”开头，以免造成混淆。自定义的存储过程会被添加到所属数据库的存储过程中，并以对象的形式保存。8.1.2创建存储过程1.使用createprocedure语句创建存储过程1)语法格式createproc[edure]procedure_name[;number][{@parameterdata_type}[varying][=default][output]][,...n][with{recompile|encryption|recompile,encryption}]--with选项[forreplication]--for选项assql_statement[...n]2)存储过程的返回值(1)返回状态值(整数)。使用return语句，-99～0为系统保留，存储过程成功执行时系统返回“0”，用户可以返回-99～0之外的整数值来反映存储过程的运行状态。(2)返回参数值。使用output参数，执行存储过程时可以将值返回给execute语句中指定的变量。2.使用SSMS创建存储过程操作步骤如下：(1)在“对象资源管理器”中展开“数据库”节点，展开要创建存储过程的数据库。(2)展开“可编程性”节点，选择“存储过程”选项，右击鼠标弹出快捷菜单，选择“新建存储过程”命令，打开创建存储过程对话框。(3)在右侧查询编辑器中出现存储过程的模板，显示了createprocedure语句的框架，可以修改要创建的存储过程的名称，然后加入存储过程所包含的T-SQL语句即完成创建。8.1.3调用或执行存储过程1.使用execute语句在查询编辑器中调用2.在SSMS中执行存储过程操作步骤如下：(1)在“对象资源管理器”中展开“数据库”节点，再展开要执行存储过程的数据库。(2)展开“可编程性”节点，再展开“存储过程”选项，在用户定义的存储过程samp上右击鼠标弹出快捷菜单，选择“执行存储过程”命令，打开“执行过程”对话框。(3)在“执行过程”对话框输出参数为否的行后面的“值”处输入部门名称“办公室”，然后单击“确定”按钮，显示执行结果。40min8.2触发器8.2.1触发器的概念和分类1.触发器的概念触发器(Trigger)是针对某个表或视图所编写的特殊类型的存储过程，不能被直接调用执行，只有当该表或视图中的数据发生添加、更新或删除操作等事件时才自动触发，并执行触发器中所定义的相关操作。触发器可以对表实施复杂的完整性约束，保持数据的一致性。当触发器所保护的数据发生变化时，触发器会自动触发，响应同时执行一定的操作。如果发现触发器执行的T-SQL语句执行了非法操作，则回滚到该事件执行前的数据库状态。触发器执行的命令被当作一次事务处理，具有事务的所有特征。2.触发器的分类在SQLServer2008R2中，有3种类型的触发器：(1)DML触发器是指在数据库中发生数据操作语言(DML)事件时启用的触发器。DML事件即指在表或视图中修改数据的insert、update、delete语句。(2)DDL触发器是指当服务器或数据库中发生数据定义语言(DDL)事件时启用的触发器。DDL事件即指在表或索引中的create、alter、drop语句。(3)登录触发器是指当用户登录SQLServer实例建立会话时启用的触发器。8.2.2创建触发器1.使用createtrigger语句创建触发器其语法格式如下：createtriggertrigger_nameon{table|view}[withencryption]{for|after|insteadof}{[insert][,][delete][,][update]}[withappend][nonforreplication]as[{ifupdate(column)[{and|or}update(column)][...n]|if(columns_update(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}]sql_statement[...n]参数说明：(1)trigger_name是触发器的名称。(2)table|view是在其上创建触发器的表或视图。(3)withencryption加密syscomments表中包含createtrigger语句文本的条目。(4)after指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。(5)insteadof指定执行触发器而不是执行触发器语句，从而替代触发语句的操作。(6)[insert][,][delete][,][update]指定在表或视图上执行哪些数据操作语句时将激活触发器的关键字。(7)withappend指定应该添加现有类型的其他触发器。只有当兼容级别是6.5或更低时，才需要使用该可选子句。(8)nonforreplication表示当复制进程更改触发器所涉及的表时，不应执行该触发器。(9)as是触发器要执行的操作。(10)sql_statement是触发器的条件和操作。触发器条件指定其他准则，以确定delete、insert、update语句是否导致执行触发器操作。2.在SSMS中创建触发器操作步骤如下：(1)在“对象资源管理器”中展开“数据库”节点，在需要创建触发器的数据库中展开相应的表，找到“触发器”节点，在触发器图标或名称上右击鼠标弹出快捷菜单，选择“新建触发器”命令。(2)在“查询编辑器”工作界面，按触发器的格式显示编码。(3)用户根据需要修改触发器名称，添加触发器内容，输入触发器的编码。(4)单击“执行”按钮，当出现“命令已成功完成”的提示后，即完成创建。8.2.3修改触发器1.使用SSMS修改触发器操作步骤如下：(1)在“对象资源管理器”中展开“数据库”节点、“表”节点，再展开categories表和“触发器”节点。(2)在“TG_categories_insupd”上右击鼠标，在弹出的快捷菜单中选择“修改”命令，调出查询编辑窗口。在“有记录被修改”后添加“或插入！”字样，(3)单击“执行”按钮，提示“命令已成功完成”，修改即完成。2.使用altertrigger语句修改触发器其语法格式如下(简化)：altertriggertrigger_nameon(table|view)assql_statement参数说明：与createtrigger的参数相同。8.2.4删除触发器1.使用droptrigger语句删除触发器语法如下：droptriggertrigger_name[,...n]2.在SSMS中删除触发器操作步骤为：在“对象资源管理器”中，依次展开“数据库”节点、“表”节点和“categories”节点，找到触发器TG_categories_indupd，右击鼠标，在弹出的快捷菜单中选择“删除”命令，在“删除对象”对话框中单击“确定”按钮，删除该触发器。使用触发器可实现许多复杂的功能，但是要慎用，滥用会造成数据库及应用程序的维护困难。在数据库操作中，可以通过关系、触发器、存储过程、应用程序等来实现数据操作，同时规则、约束、缺省值也是保证数据完整性的重要保障。如果过分依赖触发器，势必影响数据库的结构，同时增加维护的复杂程度。40min8.3游标8.3.1游标的概念在数据库开发过程中，常常会遇到这种情况，即从某一结果集中逐一地读取每一条记录。对应这样的操作，游标(Cursor)就是一种非常好的解决方案。在数据库中，游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T-SQL选择语句相关联，因为游标由结果集和结果集中指向特定记录的游标位置组成，所以当决定对结果集进行操作时，必须声明一个指向该结果集的游标。可以把游标看成是一个用来保存数据集的对象，将select语句挑选出来的结果先放入游标中，然后利用循环将每一条记录从游标中取出来处理。当然，游标是面向行的，在性能上游标会占用更多的内存，减少可用的并发，占用带宽，锁定资源，还有更多的代码量。作为一个备用方式，当使用while、子查询、临时表、变量和函数或其他方式都无法实现某些查询时，可以使用游标来实现。8.3.2创建游标使用游标有3个基本的步骤：声明游标、打开和使用游标以及关闭和释放游标。1.声明游标游标是定义在以select开始的数据集上的，可以将游标理解成一个定义在特定数据集上的指针，控制指针遍历数据集，或仅仅指向特定的行。游标分为游标类型和游标变量。对于游标变量来说，遵循T-SQL变量的定义方法。游标变量支持两种方式赋值：定义时赋值和先定义后赋值。定义游标变量像定义其他局部变量一样，在游标前加“@”。注意，如果定义了全局游标，则只支持定义时直接赋值，并且不能在游标名称前面加“@”。1)SQL-92语法其语法格式如下：declarecursor_name[insensitive][scroll]cursorforselect_statement[for{readonly|update[ofcolumn_name[,...n]]}]参数说明：(1)cursor_name是指游标的名字。(2)insensitive表明SQLServer会将游标定义所选取出来的数据记录存放在一个临时表内(在tempdb数据库)，对该游标的读取操作皆由临时表来应答。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。当遇到以下情况时，游标将自动设定insensitive选项：①在select语句中使用distinct、groupby、having、union语句。②使用outerjoin。③所选取的任意表没有索引。④将实数值当做选取的列。(3)scroll表明所有的提取操作(如first、last、prior、next、relative、absollute)都可用，如果不使用该保留字，那么只能进行next提取操作。(4)select_statement定义结果集的select语句。在游标中不能使用compute、computeby、forbrowse、into语句。(5)readonly表明不允许游标内的数据被更新，尽管在缺省状态下游标是允许更新的。在update或delete语句的wherecurrentof子句中，不允许对该游标进行引用。(6)update[ofcolumn_name[,...n]]定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。2)Transact-SQL扩展语法其语法格式如下：declarecursor_namecursor[local|global][forward_only|scroll][static|keyset|dynamic|fast_forward][read_only|scroll_locks|optimistic][type_warning]forselect_sta