第11章 程序1

13
BEGIN TRANSACTION my_transaction
UPDATE [Products] SET UnitPrice=2*UnitPrice WHERE ProductID=40
SAVE TRANSACTION my_savepoint
UPDATE [Order Details] SET UnitPrice=2*UnitPrice WHERE ProductID=40
第十一章
SQL Server中的程序设计
1
本次课主要内容 第十一章 SQL Server中的程序设计 • 11.1 批处理的基本概念 • 11.2 事务处理 • 11.3 SQL Server的流程控制语句
2
11.1.1 基本概念
批处理
包含一个或 多个T-SQL 语句的组
这些语句被应 用程序一次性 地发送到 SQL Server 执行
12
例：创建一个在COMMIT TRANSACTION 语句之前带有ROLLBACK TRANSACTION 子句的事务my_transaction。该 子句将撤销从SAVE TRANSACTION my_savepoint到ROLLBACK TRANSACTION my_savepoint之间 的语句对数据库所作的任何修改
18
更新锁：用于可更新的资源中， 以防当多个会话在读取、锁定 及资源更新时可能发生的死锁
意向锁：常用于建立锁的层次结构， 主要类型有意向共享、意向排它和 与意向排它共享
19
模式锁：常在执行依赖于表架 构的操作时使用
批量更新锁：向表中大容量复 制数据并指定了TABLOCK提示 时使用
20
例：使SQL Server为事务SELECT count(*) from Products保持共 享锁直到该事务完成，该选项 不确定共享锁是表锁或页锁
CREATE proc my_proc @input_year int
AS IF (@input_year<=2000) RETURN 1 ELSE RETURN 2
37
若要取得存储过程的返回值，可以 通过EXEC @return_status=过程名， 如获得存储过程my_proc的返回值， 命令如下：
22
PAGLOCK：在通常使用单个表锁的 地方采用页锁
TABLOCK：使用表锁代替行级锁或 页级锁，若同时指定 HOLDLOCK， 则在事务结束之前,锁将一直保持 TABLOCKX：使用表的排它锁，该 锁可防止其它事务读取或更新表， 并在语句或事务结束前一直保持
23
UPDLOCK:读取表时使用更新锁，而 不使用共享锁，并将锁一直保留到 语句或事务的结束。UPDLOCK 的优 点是允许用户读取数据并在以后更 新数据，同时确保自从上次读取数 据后数据没有被更改 XLOCK:使用排它锁并一直保持到由 语句处理的所有数据上的事务结束
27
常用的流程控制语言
DECLARE RAISERROR CASE BEGIN … END BREAK RETURN PRINT WHILE IF … ELSE CONTINUE
28
11.3.1 声明变量
用DECLARE可以声明变量 用SELECT语句可以给变量赋值
DECLARE @variable_name datatype [,@variable_name datatype…] SELECT @variable_name value [,@variable_name value…]
EXEC sp_monitor go
34
35
11.3.3 RETURN 语句
RETURN语 句实现从一个 查询或过程中 无条件退出 向调用它的过 程或应用程序 返回一个整数
-1~-99：代表不同的出错原因 0：成功返回； -1：对象丢失 -2：发生数据类型错误
36
例：创建了一个用户存储过程，根 据输入的参数值返回不同的用户自 定义的返回值
系统将批处理语句作为整体编译 成一个可执行单元，即执行计划
3
11.1.2指定批处理的方法
（1）应用程序作为一个执行单元发 出的所有SQL语句构成一个批处理， 并生成单个执行计划 （2）存储过程或触发器内的所有语 句构成一个批处理。每个存储过程 或触发器都编译为一个执行计划 （3）由EXECUTE语句执行的字符 串
25
本次课主要内容 第十一章 SQL Server中的程序设计 • 11.1 批处理的基本概念 • 11.2 事务处理 • 11.3 SQL Server的流程控制语句
26
11.3 SQL Server的流程控制语句
可用于单个的SQL语 句、语句块和存储过 程的执行
通过流程控制语句以控 制程序的流程，允许语 句彼此相关及相互依赖
例：用一个变量存放表名，并将该变 量传递给sp_spaceused系统存储过程， 以查询表空间的使用情况。
DECLARE @next char(30) SELECT @next=' ' WHILE @next is not null BEGIN SELECT @next=min(name) FROM sysobjects WHERE type='U' AND name>@next EXEC sp_spaceused @next END 从sysobjects中提取表名，按升序 Go 将各表名赋给变量@next
DECLARE @return_status int
EXEC @return_status=my_proc 1996
If @return_status=1
Print ‘The input year is before 2000’
If @return_status=2 Print ‘The input year is after 2000’
4
本次课主要内容 第十一章 SQL Server中的程序设计 • 11.1 批处理的基本概念 • 11.2 事务处理 • 11.3 SQL Server的流程控制语句
5
11.2 事务处理
SQL Server 把单个工 作单元定 义为事务 任何单个SQL语 句都可看成一个 单个的工作单元， 在SQL Server中， 事务处理可以通 过一段SQL程序 来完成
6
11.2.1 事务概述
DELETE FROM department WHERE department_id=‘D004’
DELETE FROM employee WHERE department_id=‘D004’
7
事务处理语句包含在如下两行 语句当中
BEGIN TRANSACTION [transaction_name] …… Save TRANSACTION [transaction_name] …… COMMIT TRANSACTION [transaction_name] Rollback TRANSACTION transaction_name]
SELECT count(*) from Products(tablock holdlock)
21
关于表的锁定选项： HOLDLOCK：将共享锁保留到事 务完成，而不是在相应的表、行或 数据页不再需要时就立即释放锁
NOLOCK：不发出共享锁，不提 供排它锁。利用此选项可能会读取 未提交的事务或一组在读取中回滚 的页面,它仅应用于S/修改 的表上， 从而防止 多个事务 并发使用 资源造成 问题
15
将锁最小化(如完成事务后， 立即提交)可以提高并发性和 提高数据库的性能
16
读操作需要的共享锁 基本锁 写操作需要的独占锁
锁
更新锁 意向锁 专用锁 模式锁 批量更新锁
17
共享锁：常用于只读操作， 如SELECT语句 专用锁：确保不会同时对 同一资源进行多重更新， 常用于对数据修改操作如 INSERT、UPDATE或 DELETE
10
例：通过事务来保证Northwind库 中表Products和表Order Details 上的所有ProdcutID为40的产品其 单价翻番
BEGIN TRANSACTION my_transaction --开始一个事务 UPDATE [Products] --更新Products表 SET UnitPrice=2*UnitPrice WHERE ProductID = 40
32
11.3.2 预声明的全局变量
许多预声明的全局变量用 来报告最近一次SQL Server启 动以来的系统活动情况以及连 接信息 预声明的全局变量的特征 是有两个@@放在变量名前
33
例：执行sp_monitor系统存储 过程来获预声明的全局变量 的信息, 以显示有关SQL Server 繁忙程度
11
SAVE TRANSACTION my_savepoint --保存能回滚的点 UPDATE [Order Details] --更新Order Details表 SET UnitPrice = 2*UnitPrice WHERE ProductID = 40
COMMIT TRANSACTION
24
事务隔离级别允许用户为整个SQL Server设置会话级独立级别，可用 SETTRANSACTION ISOLATION LEVEL语句 来设置该级别，当用户设置独立级别 时，用户是为SQL Server会话中所有 的SELECT语句设置了缺省锁行为。在 指定了独立级别后，可以在单个的 SELECT语句中与它一起使用锁指定而 重载它。另外，可以用DBCC USEROPTIONS来查看了什么级别设置
38
11.3.4 CASE表达式
CASE用于计算多个条件并为每 个条件返回单个值 CASE函数的用法：
CASE expression WHEN expression1 THEN expression1 [ELSE expressionN] END