Microsoft SQL Server 2008技术内幕：T-SQL语言基础（可编程对象）

十、可编程对象

10.1 变量

10.1.1 变量的声明与赋值

• 变量声明
  • declare @variateName as dataType
  • 例如，declare @i as int;
• 赋值
  • 声明后赋值
    • set @variateName = variateValue;
    • 例如，set @i = 10;
  • 声明的同时赋值
    • declare @variateName as dataType = variateValue;
    • 例如，declare @i as int = 10;

10.1.2 对多个变量赋值

• 每个变量调用一次set
  • 例如，declare @firstName as nvarchar(20) , @lastName as nvarchar(40);
    • set @firstName = (select firstname from hr.employees where empid = 3);
    • set @lastName = (select lastname from hr.employees where empid = 3);
  • set语句要求
    • 当为标量变量赋值时，标量子查询的结果必须是一个
    • 否则会报错
• 赋值select语句
  • select @firstName = firstname, @lastName = lastname from hr.employees where empid = 3;
  • 当select语句返回结果有多条时，变量最终的值为查询结果最后一行的值

10.2 批处理

• 批处理
  • 从客户端应用程序发送到SQL Server的一组单条或多条T-SQL语句
  • 是单个可执行的单元
• 批处理要经历的处理阶段
  • 分析（语法检查）
  • 解析（检查引用的对象和列是否存在、是否具有访问权限）
  • 优化（作为一个执行单元）
• 批处理命令
  • 客户端应用程序
    • API，如ADO.NET
  • 客户端工具
    • go作为批处理结束

10.2.1 批处理是语句分析的单元

• 批处理内部SQL语句若存在错误，则整个批处理都将无法执行

10.2.2 批处理和变量

• 在批处理内部声明的变量，不能在其他批处理中访问

10.2.3 不能在同一批处理中编译的语句

• create
  • default
  • function
  • procedure
  • rule
  • schema
  • trigger
  • view
• 需要将上述语句单独放在一个批处理内部

10.2.4 批处理是语句解析的单元

• 如果对数据对象的架构定义进行修改，并试图在同一批处理中对该数据对象进行处理，则SQL Server无法察觉到修改
• DML和DDL应分隔到不同的批处理中

10.2.5 go n 选项

• go语句接收一个正整数作为参数
  • 表示go之前的批处理将执行指定的次数

10.3 流程控制元素

10.3.1 if…else… 流程控制元素

if year(current_timestamp) <> year(dateadd(day,1,current_timestamp))
	begin
		print 'Today is the last day of the year.' 
	end
else
	begin
		print 'Today is not the last day of the year.' 
	end

10.3.2 while流程控制元素

declare @i as int = 1;
while @i <= 10
begin
	if @i = 1 continue;
	if @i = 6 break;
	print @i;
	set @i += 1;
end;

10.4 游标

• 游标
  • 支持以指定的顺序依次只处理一行结果集中的数据
• 游标的缺点
  • 破坏关系模型
  • 带来额外开销
  • 需要编写很多代码
• 游标的优点
  • 可以为每一行应用特定的操作
  • 对于计算型的操作，在某些情况下游标效率更佳
• 使用游标的步骤
  • 在某个查询的基础上声明游标
  • 打开游标
  • 从第一个游标记录中把列值提取到指定的变量
  • 循环遍历游标，直至@@fetch_status<>0
  • 关闭游标
  • 释放游标

declare c cursor fast_forward for
	select custid,ordermonth,qty
	from sales.custorders
	order by custid,ordermonth;
open c
fetch next from c into @custid,@ordermonth,@qty;
select @prvcustid = @custid, @runqty = 0;
while @@fetch_status =0
begin
	if @custid <> @prvcustid
		select @prvcustid = @custid, @runqty = 0;
	set @runqty += @qty;
	insert into @result values(@custid,@ordermonth,@qty,@runqty);
	fetch next from c into @custid,@ordermonth,@qty;
end
close c;
deallocate c;

10.5 临时表

• 临时表都是在tempdb数据库中创建的

10.5.1 局部临时表

• 特点
  • 只对创建它的会话在创建级和调用堆栈内部级是可见
  • 会话断开会自动删除
• 使用场景
  • 需要临时保存中间结果
  • 需要多次访问某个开销昂贵的处理结果
• 声明方式
  • 命名时以单个数字符号（#）作为前缀

if object_id('tempdb.dbo.#MyOrderTotalsByYear') is not null
	drop table dbo.#MyOrderTotalsByYear;
go

select 
	year(o.orderdate) as orderyear,
	sum(od.qty) as qty
into dbo.#MyOrderTotalsByYear
from sales.orders as o
	join sales.orderdetails as oo
	on od.orderid = o.orderid
group by year(orderdate);

select cur.orderyear, cur.qty as curyearqty, 
	prv.qty as prvyearqty
from dbo.#MyOrderTotalsByYear as cur
	left outer join dbo.#MyOrderTotalsByYear as prv
	on cur.orderyear = prv.orderyear + 1;

10.5.2 全局临时表

• 特点
  • 对所有会话都是可见的
  • 会话断开且没有活动在引用全局临时表时会自动删除
  • 所有都可以操作全局临时表，也可以删除
• 使用场景
  • 需要和所有人共享临时数据
• 声明方式
  • 命名时以两个数字符号（##）作为前缀

create table dbo.##globals
(
	id sysname not null primary key,
	val sql_variant not null
);

10.5.3 表变量

• 类似局部临时表
  • 在tempdb中有对应的表作为其物理表示
  • 只对创建它的会话可见，但只对当前批处理可见
• 类似变量
  • 通过declare进行声明
• 对于少量的数据，使用表变量更有意义，否则应该使用临时表

declare @MyOrderTotalsByYear table
(
	orderyear int not null primary key,,
	qty int not null
);
insert into @MyOrderTotalsByYear(orderyear,qty)
	select 
		year(o.orderdate) as orderyear,
		sum(od.qty) as qty
	from sales.orders as o
		join sales.orderdetails as od
		on o.orderid = od.orderid
	group by year(orderdate);
select cur.orderyear, cur.qty as curyearqty, 
	prv.qty as prvyearqty
from @MyOrderTotalsByYear as cur
	left outer join @MyOrderTotalsByYear as prv
	on cur.orderyear = prv.orderyear + 1;

10.5.4 表类型

• 作用
  • 把表定义保存到数据库中，可重复使用

create type dbo.ordertotalsbyyear as table
(
	orderyear int not null primary key,
	qty int not null
);
declare @MyOrderTotalsByYear as dbo.ordertotalsbyyear;

10.6 动态SQL

• 动态SQL
  • 使用字符串动态构造T-SQL
• 作用
  • 自动化管理任务
    • 查询数据库元数据，为其执行backup database
  • 重用以前缓存过的执行计划
  • pivot的in子句

10.6.1 执行动态SQL的方式

10.6.1.1 方式一：exec命令

• exec(sqlStr)
  • 支持普通字符和unicode字符

declare @sql as varchar(100);
set @sql = 'print '' this message
			 was printed by a dynamic sql batch.'';';
exec(@sql);

10.6.1.2 方式二：sp_executesql存储过程

• sp_executesql
  • 只支持unicode字符
  • 支持参数化代码
    • 有助于重用缓存过的执行计划
    • 输入、输出参数
  • 由两个输入参数部分和一个参数赋值部分构成
• 使用步骤
  • 声明并构造参数化字符串
  • 指定输入参数和输出参数
  • 为输入参数和输出参数赋值

declare @sql as nvarchar(100),
@sumfreight as float;
set @sql=N'set @totalfreight = (select SUM(freight)
from Sales.Orders
where custid = @custid)';
exec sp_executesql
	@stmt=@sql,
	@params = N'@custid as int,@totalfreight as float output',
	@custid=85,
	@totalfreight = @sumfreight output;
select @sumfreight as sumfreight;

10.6.2 在pivot中使用动态SQL

declare
	@sql as nvarchar(1000),
	@orderyear as int,
	@first as int;
	
declare c cursor fast_forward for
	select distinct(year(orderdate)) as orderyear
	from sales.orders
	order by orderyear;
set @first = 1;
set @sql = N'select *
from (select shipperid,year(orderdate) as orderyear,freight
	from sales.orders) as d
	pivot(sum(freight) for orderyear in (';

open c
fetch next from c into @orderyear;

while @@fetch_status =0
begin
	if @first = 0
		set @sql += N','
	else
		set @first = 0;
	set @sql += quotename(@orderyear);

	fetch next from c into @orderyear;
end
close c;
deallocate c;

set @sql += N')) as p;';
exec(@sql);

10.7 例程

• 例程
  • 为了计算结果或执行任务而对代码进行封装的一种编程对象

10.7.1 用户定义函数

• UDF
  • user-defined function
• 作用
  • 封装计算的逻辑处理
• 要求
  • 不允许对数据库中的任何架构或数据进行修改
• 分类
  • 标量UDF
  • 表值UDF

create function dbo.fn_age
(
	@birthdate as datetime,
	@eventdate as datetime
)
returns int
as
begin
	return
	datediff(year,@birthdate,@eventdate)
		- case when 100*month(@eventdate)+day(@eventdate)
		< 100*month(@birthdate)+day(@birthdate)
		then 1 else 0
	end
end
go

10.7.2 存储过程

• 与UDF的区别
  • 可以对数据库中的架构或数据进行修改
• 优点
  • 逻辑封装
  • 控制安全性
    • 可授予存储过程的权限，而不是底层表的操作权限
    • 可以做到有限制的操作
  • 错误处理
  • 提高执行性能
    • 可重用缓存计划
    • 减少网络通信流量

if object_id('sales.usp_getcustomerorders','P') is not null
	drop proc sales.usp_getcustomerorders;
go

create proc sales.usp_getcustomerorders
	@custid as int,
	@fromdate as datetime ='19000101',
	@todate as datetime ='99991231',
	@numrows as int output
as
set nocount on;
select orderid,custid,empid,orderdate
from sales.orders
where custid = @custid
	and orderdate >= @fromdate
	and orderdate < @todate;

set @numrows = @@rowcount;
go

declare @rc as int;
exec sales.usp_getcustomerorders
	@custid = 1,
	@fromdate = '20070101',
	@todate = '20080101',
	@numrows = @rc output;
select @rc as numrows;

10.7.3 触发器

• 一种特殊的存储过程，不能被显式执行
• 分类
  • DML触发器
    • 数据操作
  • DDL触发器
    • 数据定义
• SQL Server中，触发器是按语句触发，而不是按被修改的行触发

10.7.3.1 DML触发器

• SQL Server支持两种DML触发器
  • after 触发器
    • 只能在持久化表上定义
    • 在与之关联的事件完成后触发
      • after insert
      • after update
  • instead of 触发器
    • 可在持久化表或视图上定义
    • 代替与之关联的事件
      • instead of insert
        • 将insert操作替换为触发器逻辑
• 在触发器代码中，可以访问 inserted 和 deleted 的两个表
  • inserted
    • insert、update的新数据
  • deleted
    • delete、update要被删除或替换的旧数据

create trigger trg_T1_insert_audit on dbo.T1 after insert
as
set nocount on;
insert into dbo.T1_audit(keycol, datacol)
	select keycol, datacol from inserted;
go

10.7.3.2 DDL触发器

• SQL Server支持在两个作用域内创建DDL触发器
  • 数据库作用域
    • create table
  • 服务器作用域
    • create datebase
• SQL Server只支持 after 类型的DDL触发器

create trigger trg_audit_ddl_events
	on datebase for ddl_datebase_level_events
as
...
go

10.8 错误处理

• error_number()
  • 错误号
• error_message()
  • 错误的消息文本
• error_severity()
  • 错误严重级别
• error_state()
  • 错误状态号
• error_line()
  • 发生错误的行号
• error_procedure()
  • 发生错误的存储过程或触发器名称

begin try
	insert into dbo.employees(empid,empname,mgrid)
		values(1,'Emp1',null);
end try
begin catch
	if error_number() = 2627
	begin
		...
	end
	...
end catch