项目六 程序设计(课件）-《网站数据库应用技术-SQL Server》同步教学（电子工业版）

项目六 程序设计 任务1 程序设计基础 任务2 事务处理 任务3 使用游标 任务1 程序设计基础 任务引入 小林不仅想查询数据，还想对表中的数据进行运算和控制。那么，怎么利用SQL语言进行编程满足要求呢？编写的程序中会用到什么变量以及流程控制语句呢？ 知识准备 一、变量 在SQL Server中，变量分为局部变量和全局变量。全局变量名称前面有两个at符号（＠＠），由系统定义和维护。局部变量前面有一个at符号（＠），由用户定义和使用。 1．局部变最 局部变量由用户定义，仅在声明它的批处理、存储过程或者触发器中有效。批处理结束后，局部变量将变成无效。 各参数含义如下： @local_variable：是变量的名称。 data_type：是任何由系统提供的或用户定义的数据类型。 案例——查询并输出学院号为03的院长姓名 案例——查询score表中选修课程号为“01054010”且成绩高于80的记录 查询03号学院的院长名称 查询成绩高于80的记录 2．全局变量 全局变量记录了SQL Server的各种状态信息，它们不能被显式地赋值或声明，而且不能由用户定义。在SQL Server中，定义了全局变量，如表所示。 二、流程控制语句 SQL提供称为控制流的特殊关键字，用于控制SQL语句、语句块和存储过程的执行流。这些关键字可用于SQL语句、批处理和存储过程中。 控制流语句就是用来控制程序执行流程的语句，使用控制流语句可以在程序中组织语句的执行流程，提高编程语言的处理能力。SQL Server提供的控制流语句如表所示。 1．BEGIN…END语句 BEGIN…END语句用于将多个SQL语句组合为一个逻辑块。在执行时，该逻辑块作为一个整体被执行。 案例——设置变量值 设置变量值 下面几种情况经常要用到BEGIN和END语句： WHILE循环需要包含语句块。 CASE函数的元素需要包含语句块。 IF或ELSE子句需要包含语句块。 2．IF…ELSE语句 使用IF…ELSE语句，可以有条件地执行语句。 案例——查询大学语文课程的成绩状况 查询成绩 3．CASE语句 使用CASE语句可以进行多个分支的选择。CASE具有两种格式： 简单CASE格式 将某个表达式与一组简单表达式进行比较以确定结果。 搜索CASE格式 计算一组布尔表达式以确定结果。 （1）简单CASE格式 案例——查询课程的课程类型 （2）搜索CASE格式 案例——查询学生成绩 查询老师的单位和职称 查询学生成绩 4．WHILE语句 WHILE语句可以设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行。 案例——在查询到成绩表中没有学生成绩超过95分的情况下，将所有成绩提高5分，反复执行，直到成绩超过95分。 5．GOTO语句 GOTO语句可以实现无条件的跳转。 高学生成绩 案例——输出18032142学号学生的平均成绩 6．RETURN语句 使用RETURN语句，可以从查询或过程中无条件退出。可在任何时候用于从过程、批处理或语句块中退出，而不执行位于RETURN之后的语句。 案例——查询18032142学号学生的成绩和平均分 7．WAITFOR语句 使用WAITFOR语句，可以在指定的时间或者过了一定时间后，执行语句块、存储过程或者事务。 输出18032142学号学生的平均成绩 输出18032142学号学生的成绩和平均分 任务2 事务处理 任务引入 小林需要对之前做的数据库进行大量的修改，但是他又担心修改后不对还得一点一点恢复，他咨询老师，怎么处理这个问题，老师告诉他可以采用事物处理来解决这一问题。那么，什么是事物，事物又分为哪些类别呢？显示事务有哪些步骤呢？ 知识准备 事务是SQL Server中的单个逻辑单元，一个事务内的所有SQL语句作为一个整体执行，要么全部执行，要么都不执行。 一个逻辑工作单元必须有4个属性，称为ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。 原子性（Atomicity）：事务必须是原子工作单元。 一致性（Consistency）：事务在完成时，必须使所有的数据都保持一致状态。 隔离性（Isolation）：由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。 持久性（Durability）：事务完成之后，它对于系统的影响是永久性的。 一、事务分类 按事务的启动和执行方式，可以将事务分为3类： 显式事务：也称为用户定义或用户指定的事务，即可以显式地定义启动和结束的事务。 自动提交事务：自动提交模式是SQL Server的默认事务管理模式。 隐性事务：当连接以隐性事务模式进行操作时，SQL Server将在提交或回滚当前事务后自动启动新事务。 二、显式事务 显式事务需要显式地定义事务的启动和结束。它是通过BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION或ROLLBACK WORK等SQL语句来完成的。 1．启动事务 启动事务使用BEGIN TRANSACTION语句，该语句将@@TRANCOUNT加1。 2．结束事务 如果没有遇到错误，可使用COMMIT TRANSACTION语句成功地结束事务。 3．回滚事务 如果事务中出现错误，或者用户决定取消事务，可回滚该事务。 案例——启动一个事务向college表中插入一个记录，然后回滚该事务 4．在事务内设置保存点 设置保存点使用SAVE TRANSACTION语句 案例——在事务内设置保存点 查询表 查询表 5．标记事务 WITH MARK选项使事务名置于事务日志中。将数据库还原到早期状态时，可使用标记事务替代日期和时间。 6．不能用于事务的操作 在事务处理中，并不是所有的SQL语句都可以取消执行，一些不能撤消的操作（如创建、删除和修改数据库的操作），即使SQL Server取消了事务执行或者对事务进行了回滚，这些操作对数据库造成的影响也是不能恢复的。因此，这些操作不能用于事务处理。这些操作如表所示。 7．自动提交事务 SQL Server使用BEGIN TRANSACTION语句启动显式事务，或隐性事务模式设置为打开之前，将以自动提交模式进行操作。 不能用于事务的操作 三、隐式事务 在为连接将隐性事务模式设置为打开之后，当SQL Server首次执行某些SQL语句时，都会自动启动一个事务，而不需要使用BEGIN TRANSACTION语句。 任务3 使用游标 任务引入 小刘老师让小林帮忙从学校的数据库中查询他们专业前十名的学生信息并打印出来，可是小林查询出来是所有学生信息按名次排名，这时小林想到用游标来提取数据。那么，怎么使用游标呢？ 知识准备 关系数据库中的操作会对整个行集产生影响。由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。由语句所返回的这一完整的行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制，以便每次处理一行或一部分行。游标就是用来提供这种机制的结果集扩展。 游标包括以下两个部分： 游标结果集（Cursor Result Set）：由定义该游标的SELECT语句返回的行的集合。 游标位置（Cursor Position）：指向这个集合中某一行的指针。 游标使得SQL Server语言可以逐行处理结果集中的数据，游标具有以下优点： 允许定位在结果集的特定行。 从结果集的当前位置检索一行或多行。 支持对结果集中当前位置的行进行数据修改。 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。 提供脚本、存储过程和触发器中使用的访问结果集中的数据的SQL语句。 使用游标的典型过程如下： （1）声明SQL变量包含游标返回的数据。 （2）使用DECLARE CURSOR语句把SQL游标与一个SELECT语句相关联。 （3）使用OPEN语句执行SELECT语句并生成游标。 （4）使用FETCH INTO语句提取单个行，并把每列中的数据转移到指定的变量中。 （5）结束游标时使用CLOSE语句。 游标的典型使用过程 1．声明游标 声明游标使用DECLARE CURSOR语句 2．打开游标 打开游标使用OPEN语句 3．从打开的游标中提取行 游标声明，而且被打开以后，游标位置位于第一行。 4．关闭游标 关闭游标使用CLOSE语句 5．释放游标 释放游标将释放所有分配给此游标的资源。 @@FETCH_STATUS()函数的取值及其含义 案例——使用游标提取表中的第一行数据 案例——使用游标打印一个简单的学生信息表 提取第一行数据 打印学生信息表 项目总结 项目实战 实战一 输出部门号为20的员工工资水平 实战二 启动一个事务向dept表中插入一个记录，然后回滚该事务 输出部门号20的员工工资水平 查询表 根节点 中间节点 …… …… 叶节点 节点 $$