《数据库应用技术》第十一章 SQL程序设计

第十一章SQL程序设计 程序中的批、脚本、注释 程序中的事务 冷 SQL Server变量 ☆程序中的流程控制

第十一章 SQL程序设计 ❖ 程序中的批、脚本、注释 ❖ 程序中的事务 ❖ SQL Server变量 ❖ 程序中的流程控制

第十一章SQL程序设计 ☆程序中的批处理、脚本、注释 当要完成的任务不能由单独的 Transact-SQL语句来完成时,SQL Server使用批处理、脚本、存储过程、触发器等来组织多条 Transact SQL语句 批处理 批处理就是一个或多个 Transact-SQL语句的集合,从应用程序次性发 送到 SQL Server并由 SQL Server编译成一个可执行单元,此单元称为执行计 划。执行计划中的语句每次执行一条。建立批处理时,使用GO语句作为批处 理的结束标记。在一个GO语句行中不能包括其他 Transact-SQL语句,但可以 使用注释文字。当编译器读取到GO语句时,它会把GO语句前面所有的语句 当作一个批处理,并将这些语句打包发送给服务器。GO语句本身并不是 Transact-SQL语句的组成部分,它只是一个用于表示批处理结束的前端指令。 如果在一个批处理中包含任何语法错误,如:引用了一个并不存在的对象 则整个批处理就不能被成功地编译和执行。如果一个批处理中某句有执 行错误,如违反了约束,它仅影响该语句的执行,并不影响批处理中其他语 句的执行

第十一章 SQL程序设计 ❖ 程序中的批处理、脚本、注释 当要完成的任务不能由单独的Transact-SQL语句来完成时，SQL Server使用批处理、脚本、存储过程、触发器等来组织多条Transact￾SQL语句。 1、 批处理 批处理就是一个或多个Transact-SQL语句的集合，从应用程序—次性发 送到SQL Server并由SQL Server编译成—个可执行单元，此单元称为执行计 划。执行计划中的语句每次执行一条。建立批处理时，使用GO语句作为批处 理的结束标记。在一个GO语句行中不能包括其他Transact-SQL语句，但可以 使用注释文字。当编译器读取到GO语句时，它会把GO语句前面所有的语句 当作一个批处理，并将这些语句打包发送给服务器。GO语句本身并不是 Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端指令。 如果在一个批处理中包含任何语法错误，如：引用了一个并不存在的对象， 则整个批处理就不能被成功地编译和执行。如果一个批处理中某句有执 行错误，如违反了约束，它仅影响该语句的执行，并不影响批处理中其他语 句的执行

第十一章SQL程序设计 ☆程序中的批处理、脚本、注释 建立批处理时,应当注意以下几点: 1 CREATEDEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER及 CREATEⅤIEW语句不能与其他语句 放在一个批处理中 2、不能在删除一个对象之后,在同一批处理中再次引用这个对象。 3、不能在一个批处理中引用其他批处理中所定义的变量。 4、不能把规则和默认值绑定到表字段或用户自定义数据类型之后, 立即在同一个批处理中使用它们。 5、不能定义一个 CHECK约束之后,立即在同一个批处理中使用该 约束。 6、不能在修改表中的一个字段名之后,立即在同一个批处理中引用 新字段名。 7、如果一个批处理中的第一个语句是执行某个存储过程的 EXECUTE语句,则 EXECUTE关键字可以省略;如果该句不是第 一语则必须使用 EXECUTE关键字,或者省写为“EXEC

第十一章 SQL程序设计 ❖ 程序中的批处理、脚本、注释 建立批处理时，应当注意以下几点： 1、CREATE DEFAULT，CREATE PROCEDURE，CREATE RULE，CREATE TRIGGER及CREATE VIEW语句不能与其他语句 放在一个批处理中。 2、不能在删除一个对象之后，在同一批处理中再次引用这个对象。 3、不能在一个批处理中引用其他批处理中所定义的变量。 4、不能把规则和默认值绑定到表字段或用户自定义数据类型之后， 立即在同—个批处理中使用它们。 5、不能定义一个CHECK约束之后，立即在同一个批处理中使用该 约束。 6、不能在修改表中的一个字段名之后，立即在同一个批处理中引用 新字段名。 7、如果一个批处理中的第一个语句是执行某个存储过程的 EXECUTE 语句，则EXECUTE关键字可以省略；如果该语句不是第 一个语句，则必须使用EXECUTE关键字，或者省写为“EXEC

第十一章SQL程序设计 ☆程序中的批处理、脚本、注释 2、脚本 脚本是存储在文件中的一系列SQL语句,即一系列按顺序提交的 批处理。 Transact-SQL脚本中可以包含一个或多个批处理。GO语句是批 处理结束的标志。如果没有GO语句,则将它作为单个批处理执行。 使用脚本可以将创建和维护数据库时的操作步骤保存为一个磁盘文件 。将 Transact-SQL语句保存为脚本文件,不仅可以建立起可再用的模 块化代码,还可以在不同计算机之间传送 Transact-SQL语句,使两台 计算机执行同样的操作。 脚本可以在查询分析器中执行,也可以在isq减或oq实用程序中执 行。査询分析器是建立、编辑和使用脚本的一个最好的环境。在查询 分析器中,不仅可以新建、保存、打开脚本文件,而且可以输入和修 改 Transact-SQL语句,还可以通过执行 Transact-SQL语句来查看脚本 的运行结果,从而检验脚本内容的正确性

第十一章 SQL程序设计 ❖ 程序中的批处理、脚本、注释 2 、脚本 脚本是存储在文件中的一系列SQL语句，即一系列按顺序提交的 批处理。 Transact-SQL脚本中可以包含一个或多个批处理。GO语句是批 处理结束的标志。如果没有GO语句，则将它作为单个批处理执行。 使用脚本可以将创建和维护数据库时的操作步骤保存为一个磁盘文件 。将Transact-SQL语句保存为脚本文件，不仅可以建立起可再用的模 块化代码，还可以在不同计算机之间传送Transact-SQL语句，使两台 计算机执行同样的操作。 脚本可以在查询分析器中执行，也可以在isql或osql实用程序中执 行。查询分析器是建立、编辑和使用脚本的一个最好的环境。在查询 分析器中，不仅可以新建、保存、打开脚本文件，而且可以输入和修 改Transact-SQL语句，还可以通过执行Transact-SQL语句来查看脚本 的运行结果，从而检验脚本内容的正确性

第十一章SQL程序设计 ☆程序中的批处理、脚本、注释 3、注释 注释是指程序中用来说明程序内容的语句,它不能执行且不参与 程序的编译。注释用于语句代码的说明,或暂时禁用的部分语句。为 程序加上注释不仅能增强程序的可读性,而且有助于日后的管理和维 护 1)行内注释。如果整行都是注释而并非所要执行的程序行,则该行可用行 内注释,语法为: -注释语句:这种注释形式用来标志一行注释,可以与要执行的代码处在同 行,也可以另起一行。从双连字符(--)开始到行尾均为注释。 2)块注释如果要给程序所加的注释内容较长,则可使用块注释,语法为 /注释语句*/:这种注释形式用来标志多行注释,可以与要执行的代码处在同 行,也可以另起一行,甚至可以放在可执行代码内。对于多行注释,必须 使用开始注释字符对()开始注释,使用结束注释字符对()结束释 /和*之间的全部内容都是注释部分。注意多行注释不能跨越批处理,整个注 释必须包含在一个批处理中

第十一章 SQL程序设计 ❖ 程序中的批处理、脚本、注释 3、 注释 注释是指程序中用来说明程序内容的语句，它不能执行且不参与 程序的编译。注释用于语句代码的说明，或暂时禁用的部分语句。为 程序加上注释不仅能增强程序的可读性，而且有助于日后的管理和维 护。 1）行内注释。如果整行都是注释而并非所要执行的程序行，则该行可用行 内注释，语法为： --注释语句：这种注释形式用来标志一行注释，可以与要执行的代码处在同 一行，也可以另起一行。从双连字符（- -）开始到行尾均为注释。 2）块注释如果要给程序所加的注释内容较长，则可使用块注释，语法为： /*注释语句*/：这种注释形式用来标志多行注释，可以与要执行的代码处在同 一行，也可以另起一行，甚至可以放在可执行代码内。对于多行注释，必须 使用开始注释字符对（/*）开始注释，使用结束注释字符对（*/）结束注释， */和*/之间的全部内容都是注释部分。注意多行注释不能跨越批处理，整个注 释必须包含在一个批处理中

第十一章SQL程序设计 ☆程序中的事务 事务是最小的工作单元。这个工作单元要么成功完成所有操作, 要么就是失败,并将所做的一切复原。 概述 事务可以用于确保数据能够正确地被修改,避免造成数据只修改 部分而导致数据不完整,或是在修改途中受到其他用户的干扰。事 务有4个特性。: 原子性( Atomic)事务是原子的,要么完成整个操作,要么退出所有操作 。如果任何语句失败,则所有作为事务一部分的语句都不会运行。 致性( Con Demoltent)在事务完成或失败时,要求数据库处于一致状态。 由事务引发的从一种状态到另一种状态的变化是一致的。 独立性( Isolated)事务是独立的。它不与数据库的其他事务交互或冲突。 持久性( Durable)称事务是持久的,是因为在事务完成后它无须考虑和数 据库发生的任何事情。如果系统掉电且数据库服务器崩溃,事务保证在服务 器重启后仍是完整的

第十一章 SQL程序设计 ❖ 程序中的事务 事务是最小的工作单元。这个工作单元要么成功完成所有操作， 要么就是失败，并将所做的一切复原。 1 、概述 事务可以用于确保数据能够正确地被修改，避免造成数据只修改 一部分而导致数据不完整，或是在修改途中受到其他用户的干扰。事 务有4个特性。： 原子性（Atomic）事务是原子的，要么完成整个操作，要么退出所有操作 。如果任何语句失败，则所有作为事务一部分的语句都不会运行。 一致性（ConDemoltent）在事务完成或失败时，要求数据库处于一致状态。 由事务引发的从一种状态到另一种状态的变化是一致的。 独立性（Isolated）事务是独立的。它不与数据库的其他事务交互或冲突。 持久性（Durable）称事务是持久的，是因为在事务完成后它无须考虑和数 据库发生的任何事情。如果系统掉电且数据库服务器崩溃，事务保证在服务 器重启后仍是完整的

第十一章SQL程序设计 ☆程序中的事务的类型 1)显式事务 显示事务是手工配置的事务。用保留字标识显式事务的开始和结束 保留字包括 BEGIN TRANSACTION、 COMMIT TRANSACTION、 ROLLBACK TRANSACTION和 SAVE TRANSACTION,其中 TRANSACTIO0小N可以简写为TRAN 要开始显式事务,首先输入关键词 BEGIN TRAN。 要结束显示事务,使用 COMMIT TRAN。 如果要取消事务,使用 ROLL BACK TRAN命令, ROLLBACK TRAN将完全 取消事务,或将事务恢复到初始状态。 可以使用 SAVE TRAN命令生成存储点,然后有选择性地恢复到那些 点。事务内部存储点的数目没有明确的约束,而且可以在一个事务中 出现重复的存储点名字。然而,只有最后那个存储点名字会被用于复 原到指定点

第十一章 SQL程序设计 ❖ 程序中的事务的类型 1）显式事务 显示事务是手工配置的事务。用保留字标识显式事务的开始和结束 。保留字包括 BEGIN TRANSACTION、COMMIT TRANSACTION、ROLLBACK TRANSACTION和SAVE TRANSACTION，其中TRANSACTION可以简写为TRAN 。 要开始显式事务，首先输入关键词BEGIN TRAN。 要结束显示事务，使用COMMIT TRAN。 如果要取消事务，使用ROLL BACK TRAN命令，ROLLBACK TRAN将完全 取消事务，或将事务恢复到初始状态。 可以使用SAVE TRAN命令生成存储点，然后有选择性地恢复到那些 点。事务内部存储点的数目没有明确的约束，而且可以在一个事务中 出现重复的存储点名字。然而，只有最后那个存储点名字会被用于复 原到指定点

第十一章SQL程序设计 ☆程序中的事务的类型 2)隐式事务 有时候看起来没有使用事务,但它们可能隐藏在幕后,这种事务叫 做隐式事务。 要使用这种模式,必须使用 Set Implicit Transactions0n语句 来设置连接。 SQLServer的任何数据修改语句,都是隐式事务。下列的 任何一条语句都可以作为一个隐式事务的开始: ALTER TABLE、 CREATE DELETE、DROP、 FETCH、 GRANT、 INSERT、OPEN、 REVOKE、 SELECT TRUNCATE TABLE、 UPDATE。 要结束隐式事务,必须使用 COMMIT TRANSACTION或 ROLLBACK TRANSACTI0N语句。之后,任何指令的命令都将会开始另一个事务

第十一章 SQL程序设计 ❖ 程序中的事务的类型 2）隐式事务 有时候看起来没有使用事务，但它们可能隐藏在幕后，这种事务叫 做隐式事务。 要使用这种模式，必须使用Set Implicit_Transactions On语句 来设置连接。SQLServer的任何数据修改语句，都是隐式事务。下列的 任何一条语句都可以作为一个隐式事务的开始：ALTER TABLE、CREATE 、 DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REVOKE、SELECT 、TRUNCATE TABLE、UPDATE。 要结束隐式事务，必须使用COMMIT TRANSACTION或ROLLBACK TRANSACTION语句。之后，任何指令的命令都将会开始另一个事务

第十一章SQL程序设计 ☆程序中的事务的工作机制 例: BEGIN TRAN INSERT INT0课程(课程号,课程名,备注) VALUES(0001,大学语文,’) UPDATE教学计划SET启始周=2WER专业代码=0101 DELETE教师WERE姓名 IS NULL COMMIT TRAN 1)当 BEGIN TRAN语句到达数据库时, SQL Server分析出这是显式事务的开始, SQL Server找到下一个可用的内存日志页面,并给新事务分配一个事务ID 2)接着运行 INSERT语句,新的行被记录到事务日志中,数据页面在内存中进 行修改,如果所需页面不在内存中,则从磁盘调出。 3)下一个语句以相似方式运行。 UPDATE语句记录在事务日志中,数据页在内 存中进行了修改。 4)当 SQL Server收到 COMMITTRAN时,日志页面被写到数据库的日志设备上 这样才能保证日志可以被恢复。由于日志变化写入了硬盘,它保证事务是可快 复的,即使掉电了或在数据页写入磁盘时数据库崩溃了,也能进行事务 恢复

第十一章 SQL程序设计 ❖ 程序中的事务的工作机制 例：BEGIN TRAN INSERT INTO 课程 (课程号,课程名,备注) VALUES('0001','大学语文','') UPDATE 教学计划 SET 启始周=2 WHERE 专业代码='0101' DELETE 教师 WHERE 姓名 IS NULL COMMIT TRAN 1）当BEGIN TRAN语句到达数据库时，SQL Server分析出这是显式事务的开始， SQL Server找到下一个可用的内存日志页面，并给新事务分配一个事务ID。 2）接着运行INSERT语句，新的行被记录到事务日志中，数据页面在内存中进 行修改，如果所需页面不在内存中，则从磁盘调出。 3）下一个语句以相似方式运行。UPDATE语句记录在事务日志中，数据页在内 存中进行了修改。 4）当SQL Server收到COMMITTRAN时，日志页面被写到数据库的日志设备上， 这样才能保证日志可以被恢复。由于日志变化写入了硬盘，它保证事务是可恢 复的，即使掉电了或在数据页写入磁盘时数据库崩溃了，也能进行事务 恢复

第十一章SQL程序设计 ☆程序中的检查点的处理 SQL Serverl中有二类检查点:自动检查点和手工检查点 自动检查点是基于 SQL Server计算的。可在 RECOVERY INTERVAL配 置选项中规定检查点处理的频率。该选项指出以分钟为单位的用来恢 复系统中数据库的最大时间间隔。如果 SQL Server认为恢复数据库要 大量时间,它将发出自动检查点。这时,所有修改过的内存中的数据 页面将写入磁盘(包括日志页面),自动检查点每60秒检查一次,并 在各数据库之间循环,以决定该数据库是否需要赋予检查点。注意, 对带有“ trunc. log on Chkpt.”选项设置的数据库,该过程将从日志 中截断已提交的事务 手工检查点可在任何时候输入 Transact-S哑L命令 CHECKPOIN来强 制执行。注意,只有SA或数据库的DBA才能执行该命令。发出手检查 点指令后,内存中所有修改过的页面记入磁盘,就象自动检查点处理 时所发生的情况一样。注意,手工检查点对事务日志无效(即不将它 入磁盘),不考虑是否设置了“ trunk. log on Chkpt,数据库选项

第十一章 SQL程序设计 ❖ 程序中的检查点的处理 SQL Server中有二类检查点：自动检查点和手工检查点。 自动检查点是基于SQL Server计算的。可在RECOVERY INTERVAL配 置选项中规定检查点处理的频率。该选项指出以分钟为单位的用来恢 复系统中数据库的最大时间间隔。如果SQL Server认为恢复数据库要 大量时间，它将发出自动检查点。这时，所有修改过的内存中的数据 页面将写入磁盘（包括日志页面），自动检查点每60秒检查一次，并 在各数据库之间循环，以决定该数据库是否需要赋予检查点。注意， 对带有“trunc.log on Chkpt.”选项设置的数据库，该过程将从日志 中截断已提交的事务。 手工检查点可在任何时候输入Transact-SQL命令CHECKPOINT来强 制执行。注意，只有SA或数据库的DBA才能执行该命令。发出手工检查 点指令后，内存中所有修改过的页面记入磁盘，就象自动检查点处理 时所发生的情况一样。注意，手工检查点对事务日志无效（即不将它 写入磁盘），不考虑是否设置了“trunk.log on Chkpt.”数据库选项