数据技术应用 5

项目9使用存储过程

管理数据DATABASETECHNOLOGY项目描述与学习目标任务9.1定义和使用存储过程任务9.2使用事务实现数据完整性目录01020304CONTENTS知识总结与技术规范项目描述与学习与目标01Part-DATABASETECHNOLOGY随着学生成绩管理数据库数据量的增长和业务复杂度的提升，频繁通过简单SQL语句执行数据操作不仅效率低下，还可能因代码冗余导致维护困难。此外，涉及多表联动的操作（如同时更新学生表和成绩表的学号）需要确保数据一致性，单纯依赖逐条执行SQL语句存在一定风险。为解决上述问题，存储过程和事务管理成为关键。存储过程可将高频、复杂的SQL语句封装为可复用的模块，提升执行效率并降低代码冗余；而事务则通过原子性、一致性等特性，确保多步骤操作要么全部成功，要么全部回滚，避免数据不一致问题。本项目任务将以学生成绩管理系统数据库为例，学习存储过程的创建、调用与管理，以及如何通过事务机制保障关键业务逻辑的可靠性，帮助读者掌握高效、安全的数据管理方法。项目描述能力目标1.能够根据需求和数据库设计，独立创建存储过程，满足不同业务场景的需求。2.能在存储过程中熟练使用事务控制语句，实现数据完整性，确保复杂业务操作的可靠性。知识目标1.理解存储过程的作用，认识到它在提高数据库操作效率和简化业务逻辑方面的重要性。2.掌握存储过程的创建和执行方法，包括语法、参数传递等核心要点。3.理解事务的作用和特性，明确事务在保证数据完整性和一致性方面的关键作用。职业素质目标1.通过多样化的存储过程使用方法，培养主动分析问题的能力，能够灵活运用所学知识解决实际问题。2.通过使用事务实现业务规则和数据完整性，引导行业从业人员养成规范做事、严谨做人的职业素养，树立讲规矩、守底线的职业意识。学习目标任务9.1定义和使用存储过程02Part-DATABASETECHNOLOGY在学生成绩管理系统中，学生、成绩数据的批量处理，如班级人数汇总、学业预警等，涉及大量重复性SQL操作。采用传统SQL脚本方式，存在代码冗余度高、执行效率低下及后期维护困难等问题。存储过程是预编译并存储在SQLServer数据库中的数据库对象，具有执行效率高、可重复调用、安全性好等优势，能有效提升系统整体性能。例如，在进行多次相同的复杂查询时，存储过程只需编译一次，后续调用速度更快。本任务主要内容包括设计并实现查询所有学生基本信息及其对应班级名称的存储过程；开发基于班级名称参数的学生信息查询存储过程；创建统计各班级学生人数的计算型存储过程。通过本任务，掌握存储过程的创建语法、参数传递、流程控制和异常处理等核心技术，理解其在提高数据库操作效率、保证数据一致性和简化业务逻辑中的重要作用。任务描述01存储过程的概念

存储过程（Stored

Procedure）是一组为完成特定功能的T-SQL语句集，经编译后存储在SQL

Server服务器端数据库中，是数据库的重要对象。在数据量庞大时，可加速SQL语句执行，提升数据库系统运行效率。优点众多：1.它允许模块化程序设计，创建后可多次调用，提高程序设计效率和应用程序可维护性，也允许应用程序统一访问数据库；2.只在创建时编译，以后执行一般不需重新编译，比一般SQL语句执行速度快，通常快2-10倍；可简化对象权限管理，用户可被授予执行存储过程的权限，而不必拥有直接对存储过程所引用对象的执行权限；3.可提高应用程序安全性，防止SQL注入攻击，因为不能通过拼接字符串调用程序；是一组命名代码，允许延迟绑定，可引用当前不存在但执行时存在的对象；4.可减少网络通信流量，如千条T-SQL语句的命令写成一条执行存储过程的命令，可大大减少客户端和服务器间的通信量。知识储备03存储过程的定义在SQL

Server中，用CREATE

PROCEDURE语句创建存储过程，语法格式如下：CREATE{PROC|PROCEDURE}存储过程名称[;数值选项][{@参数数据类型}[VARYING][=参数的默认值][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]AS{<SQL语句>[;][...n]知识储备1.参数：过程中的参数。参数名必须以“@”为前缀，在CREATEPROCEDURE语句中可以声明一个或多个参数，每个参数都需要确定一个数据类型。其中OUTPUT关键字用来表明该参数是输出参数。2.RECOMPILE：用来表明数据库引擎不缓存该存储过程的执行计划，每次运行时都自动重新编译。3.ENCRYPTION：用来对存储过程的定义进行加密。4.SQL语句：在存储过程中需要执行的操作。参数含义04存储过程的执行创建存储过程后，可以使用EXECUTE（或EXEC）语句来执行存储过程，其语法格式为：[{EXEC|EXECUTE)]{[@返回状态=]{存储过程名}}[[@参数=]{参数值|@变量[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]在上面的语法描述中，各参数说明如下：1.@返回状态：保存存储过程的返回状态，该变量必须在EXEC语句之前声明。2.@参数：输入参数的名称。参数值用来传递给该输入参数的值。3.@变量：用来存放返回参数的值。OUTPUT关键字用来声明输出参数。4.DEFAULT：根据过程的定义，提供参数的默认值。5.WITHRECOMPILE：执行存储过程时强制重新编译。知识储备05存储过程的修改与删除在SQLServer中，可以使用ALTERPROCEDURE语句以命令方式实现存储过程的修改，其语法格式为：ALTER{PROC|PROCEDURE}存储过程名称[;数值][{@参数数据类型}[VARYING][=参数的默认值][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]AS{<SQL语句>[;][...n]其中，各参数的含义与创建时相同。需要注意的是在修改存储过程时要考虑以下因素：（1）ALTERPROCEDURE语句不会更改原存储过程的权限。（2）在创建存储过程时使用的选项，在ALTERPROCEDURE语句中应继续保留。（3）ALTERPROCEDURE语句一般也不能与其他的T-SQL语句混合使用。删除存储过程可以使用DROP命令，其语法格式为：DROPPROCEDURE{存储过程名)[,…n]知识储备使用T-SQL创建存储过程例9-1：创建存储过程proc_stu，查询所有学生信息以及所在班级名称；例9-2：创建存储过程proc_stu_by_class（带有输入参数），查询指定班级的学生信息。任务实施使用T-SQL执行存储过程使用T-SQL执行存储过程例9-4：执行存储过程proc_stu，查询所有学生信息以及所在班级名称；例9-5：执行存储过程proc_stu_by_class，查询“23计算机1”班的学生信息。任务实施使用T-SQL修改与删除存储过程使用T-SQL执行存储过程例9-7：修改存储过程proc_stu，查询所有女生信息以及所在班级名称；例9-8：删除存储过程proc_stu。任务实施任务9.2使用事务实现数据完整性03Part-DATABASETECHNOLOGY在学生成绩管理过程中，学籍信息变更涉及多表数据联动更新，必须确保操作的原子性和数据一致性。例如，将学号为“2023001”（计算机应用技术专业）的同学学号更新为“2023015”（软件技术专业），需同时更新student表学号字段和grade表相关成绩记录。本任务主要内容如下：1.编写事务脚本实现student表和grade表的同步更新；2.验证事务执行效果，确保成功时两表数据同步更新，失败时数据完整回滚。通过本任务学习，理解事务的ACID特性，掌握使用BEGIN

TRANSACTION开启事务、COMMIT提交事务以及出错时ROLLBACK回滚的完整流程。任务描述事务是指用户为完成某项任务所定义的多个操作的序列。在序列中的操作要么全部完成，要么全部不执行。整个序列构成一个不可分割的工作单位，是数据库中不可再分的部分。事务的概念1.自动提交事务；2.显式事务；3.隐式事务；4.批处理级事务。事务类型1.

BEGINTRANSACTION（开始事务）；2.COMMITTRANSACTION（提交事务）；3.SAVETRANSACTION（保存事务）；4.ROLLBACKTRANSACTION（回滚事务）。事务控制语句知识储备知识储备——事务的ACID特性事务的原子性是指事务中包含的所有操作要么全做，要么全不做。原子性（Atomicity）事务开始之前，数据库处于一致性的状态；事务结束后，数据库必须仍处于一致性状态。一致性（Consistency）在事务的处理过程中暂时不一致的数据不能被其他事务应用，直到数据再次一致。隔离性（Isolation）一个事务成功完成后，它对数据库的改变就被保护起来，即便是在系统遇到故障的情况下也不会丢失。持久性（Durability）知识储备——事务的4种类型自动提交事务是指每个单独的T-SQL语句都是一个事务，并且每个T-SQL语句在完成时都被提交或回滚。如果一个语句成功完成，则提交该语句；如果遇到错误，则回滚该语句。自动提交事务显式事务也称为用户定义或用户指定的事务，是指可以显式地在其中定义事务的启动、提交、回滚和结束。每个事务均以BEGINTRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。显式事务在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。隐式事务只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动回滚。批处理级事务知识储备——事务的4个控制语句BEGINTRANSACTION语句标记一个本地显式事务的起始点，用于开始事务。其语法如下：BEGIN{TRAN|TRANSACTION}[{transaction_name|@tran_name_variable} [WITHMARK[‘description’]]BEGINTRANSACTION（开始事务）COMMITTRANSACTION语句用于提交事务。标志一个隐性事务或显式事务的结束，将事务所做的数据修改保存到数据库。其语法如下：COMMIT{TRAN|TRANSACTION}[transaction_name|@tran_name_variable]COMMITTRANSACTION（提交事务）SAVETRANSACTION语句用于在事务执行期设置保存点。该保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。其语法如下：SAVE{TRAN｜TRANSACTION}{savepoint_name｜savepoint_variable}SAVETRANSACTION（保存事务）ROLLBACKTRANSACTION语句用于取消（回滚）事务对数据的修改，将显式事务或隐形事务回滚到事务的起点或事务内的某个保存点。其语法如下：ROLLBACK{TRAN|TRANSACTION}[transaction_name|@tran_name_variable|savepoint_name|savepoint_variableROLLBACKTRANSACTION（回滚事务）任务实施——使用T-SQL执行开始与提交事务【例9-9】开始一个事务，student表中studentid的值进行修改，同时修改grade表中studentid的值。任务实施——使用T-SQL执行回滚事务【例9-9】在【例9-9】基础上添加如果操作失败进行事务回滚。知识总结与技术规范04Part-DAT