数据库应用技术SQL程序设计.ppt
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
要开始显式事务，首先输入关键词BEGIN TRAN。 要结束显示事务，使用COMMIT TRAN。 如果要取消事务，使用ROLL BACK TRAN命令，ROLLBACK TRAN将完全 取消事务，或将事务恢复到初始状态。
可以使用SAVE TRAN命令生成存储点，然后有选择性地恢复到那些 点。事务内部存储点的数目没有明确的约束，而且可以在一个事务中 出现重复的存储点名字。然而，只有最后那个存储点名字会被用于复 原到指定点。
第十一章 SQL程序设计
❖ 程序中的事务
事务是最小的工作单元。这个工作单元要么成功完成所有操作， 要么就是失败，并将所做的一切复原。 1 、概述
事务可以用于确保数据能够正确地被修改，避免造成数据只修改 一部分而导致数据不完整，或是在修改途中受到其他用户的干扰。事 务有4个特性。：
原子性（Atomic） 事务是原子的，要么完成整个操作，要么退出所有操作 。如果任何语句失败，则所有作为事务一部分的语句都不会运行。
一致性（ConDemoltent）在事务完成或失败时，要求数据库处于一致状态。 由事务引发的从一种状态到另一种状态的变化是一致的。
独立性（Isolated）事务是独立的。它不与数据库的其他事务交互或冲突。 持久性（Durable）称事务是持久的，是因为在事务完成后它无须考虑和数 据库发生的任何事情。如果系统掉电且数据库服务器崩溃，事务保证在服务 器重启后仍是完整的。
第十一章 SQL程序设计
❖ 程序中的事务的类型
1）显式事务 显示事务是手工配置的事务。用保留字标识显式事务的开始和结束
。保留字包括 BEGIN TRANSACTION、COMMIT TRANSACTION、ROLLBACK TRANSACTION和SAVE TRANSACTION，其中TRANSACTION可以简写为TRAN 。
第十一章 SQL程序设计
❖ 编写有效的事务
在实际程序设计和管理过程中，应尽可能使事务保持简短，以减少 并发连接间的资源锁定争夺。在有少量用户的系统中，运行时间长、 效率低的事务可能不会成为问题，但是在拥有成千上万用户的大型数 据库系统中，这样的事务将导致无法预知的后果。设计时，需要遵循 以下原则：
要结束隐式事务，必须使用COMMIT TRANSACTION或ROLLBACK TRANSACTION语句。之后，任何指令的命令都将会开始另一个事务。
第十一章 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 Server就没 有防止多个用户同时更新同一数据的机制。一个锁就是在多用户环境 中对某一种正在使用中的资源的一个限制，它阻止其他用户访问或修 改资源中的数据。SQL Server为了保证用户操作的一致性，自动对资 源设置和释放锁。例如，当用户正在更新一个表时，没有任何其他用 户能修改甚至查看已经更新过的记录。当所有的与该用户相关的更新 操作都完成后，锁便会释放，并且记录变成可访问的。 通常，SQL Server中有三类锁：
如果在一个批处理中包含任何语法错误，如：引用了一个并不存在的对象， 则整个批处理就不能被成功地编译和执行。如果一个批处理中某句有执
行错误，如违反了约束，它仅影响该语句的执行，并不影响批处理中其他语 句的执行。
第十一章 SQL程序设计
❖ 程序中的批处理、脚本、注释
建立批处理时，应当注意以下几点： 1、CREATE DEFAULT，CREATE PROCEDURE，CREATE
第十一章 SQL程序设计
❖ 程序中的批处理、脚本、注释
2 、脚本 脚本是存储在文件中的一系列SQL语句，即一系列按顺序提交的
批处理。 Transact-SQL脚本中可以包含一个或多个批处理。GO语句是批
处理结束的标志。如果没有GO语句，则将它作为单个批处理执行。 使用脚本可以将创建和维护数据库时的操作步骤保存为一个磁盘文件 。将Transact-SQL语句保存为脚本文件，不仅可以建立起可再用的模 块化代码，还可以在不同计算机之间传送Transact-SQL语句，使两台、脚本、注释 ❖ 程序中的事务 ❖ SQL Server变量 ❖ 程序中的流程控制
第十一章 SQL程序设计
❖ 程序中的批处理、脚本、注释
当要完成的任务不能由单独的Transact-SQL语句来完成时，SQL Server使用批处理、脚本、存储过程、触发器等来组织多条TransactSQL语句。
第十一章 SQL程序设计
❖ 程序中的变量
变量是SQL Server用来在语句之间传递数据的方式之一。SQL Server中的 变量分为两种，即全局变量和局部变量，其中，全局变量的名称以两个@@字符 开始，由系统定义和维护；局部变量的名称以一个@字符开始，由用户自己定 义和赋值。
全局变量是SQL Server系统提供并赋值的变量。用户不能建立全局变量， 也不能修改全局变量。在SQL Server 2000中，全局变量是—组特殊的函数， 它们的名称是以@@开始，而且不需要任何参数，在调用时无需在函数名后面加 上—对圆括号，这些函数也称为无参函数。
复的，即使掉电了或在数据页写入磁盘时数据库崩溃了，也能进行事务 恢复。
第十一章 SQL程序设计
❖ 程序中的检查点的处理
SQL Server中有二类检查点：自动检查点和手工检查点。
自动检查点是基于SQL Server计算的。可在RECOVERY INTERVAL配 置选项中规定检查点处理的频率。该选项指出以分钟为单位的用来恢 复系统中数据库的最大时间间隔。如果SQL Server认为恢复数据库要 大量时间，它将发出自动检查点。这时，所有修改过的内存中的数据 页面将写入磁盘（包括日志页面），自动检查点每60秒检查一次，并 在各数据库之间循环，以决定该数据库是否需要赋予检查点。注意， 对带有“trunc.log on Chkpt.”选项设置的数据库，该过程将从日志 中截断已提交的事务。
不要在事务处理期间要求用户输入。 在事务启动之前，获得所有需要的用户输入。 在浏览数据时，尽量不要打开事务。 在所有预备的数据分析完成之前，不应启动事务。 保持事务尽可能简短。 在知道了必须要进行的修改之后，启动事务，执行修改语句，然后立即提交 或回滚。只有在需要时，才打开事务。 灵活使用更低的事务隔离级别。 灵活地使用更低的游标并发选项。 在事务中尽量使访问的数据量最小。 尽量减少锁定的行数，从而减少事务之间的争夺。 小心地管理隐性事务，以便避免并发问题。
手工检查点可在任何时候输入Transact-SQL命令CHECKPOINT来强 制执行。注意，只有SA或数据库的DBA才能执行该命令。发出手工检查 点指令后，内存中所有修改过的页面记入磁盘，就象自动检查点处理 时所发生的情况一样。注意，手工检查点对事务日志无效（即不将它 写入磁盘），不考虑是否设置了“trunk.log on Chkpt.”数据库选项。
RULE，CREATE TRIGGER及CREATE VIEW语句不能与其他语句 放在一个批处理中。
2、不能在删除一个对象之后，在同一批处理中再次引用这个对象。 3、不能在一个批处理中引用其他批处理中所定义的变量。 4、不能把规则和默认值绑定到表字段或用户自定义数据类型之后， 立即在同—个批处理中使用它们。 5、不能定义一个CHECK约束之后，立即在同一个批处理中使用该 约束。 6、不能在修改表中的一个字段名之后，立即在同一个批处理中引用 新字段名。 7、如果一个批处理中的第一个语句是执行某个存储过程的 EXECUTE 语句，则EXECUTE关键字可以省略；如果该语句不是第 一个语句，则必须使用EXECUTE关键字，或者省写为“EXEC”。
1、 批处理
批处理就是一个或多个Transact-SQL语句的集合，从应用程序—次性发 送到SQL Server并由SQL Server编译成—个可执行单元，此单元称为执行计 划。执行计划中的语句每次执行一条。建立批处理时，使用GO语句作为批处 理的结束标记。在一个GO语句行中不能包括其他Transact-SQL语句，但可以 使用注释文字。当编译器读取到GO语句时，它会把GO语句前面所有的语句 当 作 一 个 批 处 理 ， 并 将 这 些 语 句 打 包 发 送 给 服 务 器 。 GO 语 句 本 身 并 不 是 Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端指令。
2）块注释如果要给程序所加的注释内容较长，则可使用块注释，语法为： /*注释语句*/：这种注释形式用来标志多行注释，可以与要执行的代码处在同 一行，也可以另起一行，甚至可以放在可执行代码内。对于多行注释，必须 使用开始注释字符对（/*）开始注释，使用结束注释字符对（*/）结束注释， */和*/之间的全部内容都是注释部分。注意多行注释不能跨越批处理，整个注 释必须包含在一个批处理中。
第十一章 SQL程序设计
❖ 程序中的变量
局部变量是指在批处理或脚本中用来保存数据值的对象。局部变量常用于 作为计数器计算循环执行的次数或控制循环执行的次数，也可以用于保存由存 储过程代码返回的数据值，此外，还可以使用Table数据类型的局部变量来代 替临时表。 1、声明局部变量 使用一个局部变量之前，必须使用DECLARE语句来声明这个局部变量，给它指 定一个变量名和数据类型，对于数值变量，还需要指定其精度和小数位数。 DECLARE语句的语法格式为：
第十一章 SQL程序设计
❖ 程序中的批处理、脚本、注释
3、 注释 注释是指程序中用来说明程序内容的语句，它不能执行且不参与
程序的编译。注释用于语句代码的说明，或暂时禁用的部分语句。为 程序加上注释不仅能增强程序的可读性，而且有助于日后的管理和维 护。
1）行内注释。如果整行都是注释而并非所要执行的程序行，则该行可用行 内注释，语法为： --注释语句：这种注释形式用来标志一行注释，可以与要执行的代码处在同 一行，也可以另起一行。从双连字符（- -）开始到行尾均为注释。
脚本可以在查询分析器中执行，也可以在isql或osql实用程序中执 行。查询分析器是建立、编辑和使用脚本的一个最好的环境。在查询 分析器中，不仅可以新建、保存、打开脚本文件，而且可以输入和修 改Transact-SQL语句，还可以通过执行Transact-SQL语句来查看脚本 的运行结果，从而检验脚本内容的正确性。
第十一章 SQL程序设计
❖ 程序中的事务的类型
2）隐式事务 有时候看起来没有使用事务，但它们可能隐藏在幕后，这种事务叫
做隐式事务。 要使用这种模式，必须使用Set Implicit_Transactions On语句
来设置连接。SQLServer的任何数据修改语句，都是隐式事务。下列的 任何一条语句都可以作为一个隐式事务的开始：ALTER TABLE、CREATE 、 DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REVOKE、SELECT 、TRUNCATE TABLE、UPDATE。
共享锁：也称为读锁，是加在正在读取的数据上的。共享锁防止别的 用户在加锁的情况下修改该数据，共享锁和别的共享锁是相容的。
更新锁：和独占锁很相似，更新锁防止别人在改变数据的过程中修改 数据。
独占锁：当要改变数据时使用独占锁。它防止别的用户读取或修改正 在工作的数据。直到将锁释放为止，独占锁不与别的锁兼容。
可以使用SAVE TRAN命令生成存储点，然后有选择性地恢复到那些 点。事务内部存储点的数目没有明确的约束，而且可以在一个事务中 出现重复的存储点名字。然而，只有最后那个存储点名字会被用于复 原到指定点。
第十一章 SQL程序设计
❖ 程序中的事务
事务是最小的工作单元。这个工作单元要么成功完成所有操作， 要么就是失败，并将所做的一切复原。 1 、概述
事务可以用于确保数据能够正确地被修改，避免造成数据只修改 一部分而导致数据不完整，或是在修改途中受到其他用户的干扰。事 务有4个特性。：
原子性（Atomic） 事务是原子的，要么完成整个操作，要么退出所有操作 。如果任何语句失败，则所有作为事务一部分的语句都不会运行。
一致性（ConDemoltent）在事务完成或失败时，要求数据库处于一致状态。 由事务引发的从一种状态到另一种状态的变化是一致的。
独立性（Isolated）事务是独立的。它不与数据库的其他事务交互或冲突。 持久性（Durable）称事务是持久的，是因为在事务完成后它无须考虑和数 据库发生的任何事情。如果系统掉电且数据库服务器崩溃，事务保证在服务 器重启后仍是完整的。
第十一章 SQL程序设计
❖ 程序中的事务的类型
1）显式事务 显示事务是手工配置的事务。用保留字标识显式事务的开始和结束
。保留字包括 BEGIN TRANSACTION、COMMIT TRANSACTION、ROLLBACK TRANSACTION和SAVE TRANSACTION，其中TRANSACTION可以简写为TRAN 。
第十一章 SQL程序设计
❖ 编写有效的事务
在实际程序设计和管理过程中，应尽可能使事务保持简短，以减少 并发连接间的资源锁定争夺。在有少量用户的系统中，运行时间长、 效率低的事务可能不会成为问题，但是在拥有成千上万用户的大型数 据库系统中，这样的事务将导致无法预知的后果。设计时，需要遵循 以下原则：
要结束隐式事务，必须使用COMMIT TRANSACTION或ROLLBACK TRANSACTION语句。之后，任何指令的命令都将会开始另一个事务。
第十一章 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 Server就没 有防止多个用户同时更新同一数据的机制。一个锁就是在多用户环境 中对某一种正在使用中的资源的一个限制，它阻止其他用户访问或修 改资源中的数据。SQL Server为了保证用户操作的一致性，自动对资 源设置和释放锁。例如，当用户正在更新一个表时，没有任何其他用 户能修改甚至查看已经更新过的记录。当所有的与该用户相关的更新 操作都完成后，锁便会释放，并且记录变成可访问的。 通常，SQL Server中有三类锁：
如果在一个批处理中包含任何语法错误，如：引用了一个并不存在的对象， 则整个批处理就不能被成功地编译和执行。如果一个批处理中某句有执
行错误，如违反了约束，它仅影响该语句的执行，并不影响批处理中其他语 句的执行。
第十一章 SQL程序设计
❖ 程序中的批处理、脚本、注释
建立批处理时，应当注意以下几点： 1、CREATE DEFAULT，CREATE PROCEDURE，CREATE
第十一章 SQL程序设计
❖ 程序中的批处理、脚本、注释
2 、脚本 脚本是存储在文件中的一系列SQL语句，即一系列按顺序提交的
批处理。 Transact-SQL脚本中可以包含一个或多个批处理。GO语句是批
处理结束的标志。如果没有GO语句，则将它作为单个批处理执行。 使用脚本可以将创建和维护数据库时的操作步骤保存为一个磁盘文件 。将Transact-SQL语句保存为脚本文件，不仅可以建立起可再用的模 块化代码，还可以在不同计算机之间传送Transact-SQL语句，使两台、脚本、注释 ❖ 程序中的事务 ❖ SQL Server变量 ❖ 程序中的流程控制
第十一章 SQL程序设计
❖ 程序中的批处理、脚本、注释
当要完成的任务不能由单独的Transact-SQL语句来完成时，SQL Server使用批处理、脚本、存储过程、触发器等来组织多条TransactSQL语句。
第十一章 SQL程序设计
❖ 程序中的变量
变量是SQL Server用来在语句之间传递数据的方式之一。SQL Server中的 变量分为两种，即全局变量和局部变量，其中，全局变量的名称以两个@@字符 开始，由系统定义和维护；局部变量的名称以一个@字符开始，由用户自己定 义和赋值。
全局变量是SQL Server系统提供并赋值的变量。用户不能建立全局变量， 也不能修改全局变量。在SQL Server 2000中，全局变量是—组特殊的函数， 它们的名称是以@@开始，而且不需要任何参数，在调用时无需在函数名后面加 上—对圆括号，这些函数也称为无参函数。
复的，即使掉电了或在数据页写入磁盘时数据库崩溃了，也能进行事务 恢复。
第十一章 SQL程序设计
❖ 程序中的检查点的处理
SQL Server中有二类检查点：自动检查点和手工检查点。
自动检查点是基于SQL Server计算的。可在RECOVERY INTERVAL配 置选项中规定检查点处理的频率。该选项指出以分钟为单位的用来恢 复系统中数据库的最大时间间隔。如果SQL Server认为恢复数据库要 大量时间，它将发出自动检查点。这时，所有修改过的内存中的数据 页面将写入磁盘（包括日志页面），自动检查点每60秒检查一次，并 在各数据库之间循环，以决定该数据库是否需要赋予检查点。注意， 对带有“trunc.log on Chkpt.”选项设置的数据库，该过程将从日志 中截断已提交的事务。
不要在事务处理期间要求用户输入。 在事务启动之前，获得所有需要的用户输入。 在浏览数据时，尽量不要打开事务。 在所有预备的数据分析完成之前，不应启动事务。 保持事务尽可能简短。 在知道了必须要进行的修改之后，启动事务，执行修改语句，然后立即提交 或回滚。只有在需要时，才打开事务。 灵活使用更低的事务隔离级别。 灵活地使用更低的游标并发选项。 在事务中尽量使访问的数据量最小。 尽量减少锁定的行数，从而减少事务之间的争夺。 小心地管理隐性事务，以便避免并发问题。
手工检查点可在任何时候输入Transact-SQL命令CHECKPOINT来强 制执行。注意，只有SA或数据库的DBA才能执行该命令。发出手工检查 点指令后，内存中所有修改过的页面记入磁盘，就象自动检查点处理 时所发生的情况一样。注意，手工检查点对事务日志无效（即不将它 写入磁盘），不考虑是否设置了“trunk.log on Chkpt.”数据库选项。
RULE，CREATE TRIGGER及CREATE VIEW语句不能与其他语句 放在一个批处理中。
2、不能在删除一个对象之后，在同一批处理中再次引用这个对象。 3、不能在一个批处理中引用其他批处理中所定义的变量。 4、不能把规则和默认值绑定到表字段或用户自定义数据类型之后， 立即在同—个批处理中使用它们。 5、不能定义一个CHECK约束之后，立即在同一个批处理中使用该 约束。 6、不能在修改表中的一个字段名之后，立即在同一个批处理中引用 新字段名。 7、如果一个批处理中的第一个语句是执行某个存储过程的 EXECUTE 语句，则EXECUTE关键字可以省略；如果该语句不是第 一个语句，则必须使用EXECUTE关键字，或者省写为“EXEC”。
1、 批处理
批处理就是一个或多个Transact-SQL语句的集合，从应用程序—次性发 送到SQL Server并由SQL Server编译成—个可执行单元，此单元称为执行计 划。执行计划中的语句每次执行一条。建立批处理时，使用GO语句作为批处 理的结束标记。在一个GO语句行中不能包括其他Transact-SQL语句，但可以 使用注释文字。当编译器读取到GO语句时，它会把GO语句前面所有的语句 当 作 一 个 批 处 理 ， 并 将 这 些 语 句 打 包 发 送 给 服 务 器 。 GO 语 句 本 身 并 不 是 Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端指令。
2）块注释如果要给程序所加的注释内容较长，则可使用块注释，语法为： /*注释语句*/：这种注释形式用来标志多行注释，可以与要执行的代码处在同 一行，也可以另起一行，甚至可以放在可执行代码内。对于多行注释，必须 使用开始注释字符对（/*）开始注释，使用结束注释字符对（*/）结束注释， */和*/之间的全部内容都是注释部分。注意多行注释不能跨越批处理，整个注 释必须包含在一个批处理中。
第十一章 SQL程序设计
❖ 程序中的变量
局部变量是指在批处理或脚本中用来保存数据值的对象。局部变量常用于 作为计数器计算循环执行的次数或控制循环执行的次数，也可以用于保存由存 储过程代码返回的数据值，此外，还可以使用Table数据类型的局部变量来代 替临时表。 1、声明局部变量 使用一个局部变量之前，必须使用DECLARE语句来声明这个局部变量，给它指 定一个变量名和数据类型，对于数值变量，还需要指定其精度和小数位数。 DECLARE语句的语法格式为：
第十一章 SQL程序设计
❖ 程序中的批处理、脚本、注释
3、 注释 注释是指程序中用来说明程序内容的语句，它不能执行且不参与
程序的编译。注释用于语句代码的说明，或暂时禁用的部分语句。为 程序加上注释不仅能增强程序的可读性，而且有助于日后的管理和维 护。
1）行内注释。如果整行都是注释而并非所要执行的程序行，则该行可用行 内注释，语法为： --注释语句：这种注释形式用来标志一行注释，可以与要执行的代码处在同 一行，也可以另起一行。从双连字符（- -）开始到行尾均为注释。
脚本可以在查询分析器中执行，也可以在isql或osql实用程序中执 行。查询分析器是建立、编辑和使用脚本的一个最好的环境。在查询 分析器中，不仅可以新建、保存、打开脚本文件，而且可以输入和修 改Transact-SQL语句，还可以通过执行Transact-SQL语句来查看脚本 的运行结果，从而检验脚本内容的正确性。
第十一章 SQL程序设计
❖ 程序中的事务的类型
2）隐式事务 有时候看起来没有使用事务，但它们可能隐藏在幕后，这种事务叫
做隐式事务。 要使用这种模式，必须使用Set Implicit_Transactions On语句
来设置连接。SQLServer的任何数据修改语句，都是隐式事务。下列的 任何一条语句都可以作为一个隐式事务的开始：ALTER TABLE、CREATE 、 DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REVOKE、SELECT 、TRUNCATE TABLE、UPDATE。
共享锁：也称为读锁，是加在正在读取的数据上的。共享锁防止别的 用户在加锁的情况下修改该数据，共享锁和别的共享锁是相容的。
更新锁：和独占锁很相似，更新锁防止别人在改变数据的过程中修改 数据。
独占锁：当要改变数据时使用独占锁。它防止别的用户读取或修改正 在工作的数据。直到将锁释放为止，独占锁不与别的锁兼容。