Microsoft SQL Server 2008技術内幕：T-SQL語言基礎（可程式設計對象）

T-SQL語言基礎

• 十、可程式設計對象
• • 10.1 變量
  • • 10.1.1 變量的聲明與指派
    • 10.1.2 對多個變量指派
  • 10.2 批處理
  • • 10.2.1 批處理是語句分析的單元
    • 10.2.2 批處理和變量
    • 10.2.3 不能在同一批進行中編譯的語句
    • 10.2.4 批處理是語句解析的單元
    • 10.2.5 go n 選項
  • 10.3 流程控制元素
  • • 10.3.1 if...else... 流程控制元素
    • 10.3.2 while流程控制元素
  • 10.4 遊标
  • 10.5 臨時表
  • • 10.5.1 局部臨時表
    • 10.5.2 全局臨時表
    • 10.5.3 表變量
    • 10.5.4 表類型
  • 10.6 動态SQL
  • • 10.6.1 執行動态SQL的方式
    • • 10.6.1.1 方式一：exec指令
      • 10.6.1.2 方式二：sp_executesql存儲過程
    • 10.6.2 在pivot中使用動态SQL
  • 10.7 例程
  • • 10.7.1 使用者定義函數
    • 10.7.2 存儲過程
    • 10.7.3 觸發器
    • • 10.7.3.1 DML觸發器
      • 10.7.3.2 DDL觸發器
  • 10.8 錯誤處理

十、可程式設計對象

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