【数据库技术】| 作者 / Edison Zhou

《Microsoft SQL Server 2008技术内幕：T-SQL语言基础》是一本关于T-SQL方面的好书，可能现在我们在京东上都买不到了，我也是在2014年在淘宝上淘的。看完之后，我总结了一些精华笔记，现将其分成一个系列的笔记文章分享与你，每篇预计阅读时间为10分钟左右。上一篇介绍了SQL Server的数据修改的各种方式，本篇会介绍一些可编程对象。

变量与批处理

（1）变量：DECLARE+SET/SELECT

DECLARE语句可以声明一个或多个变量，然后使用SET/SELECT语句可以把一个变量设置成指定的值。

① SET语句每次只能针对一个变量进行操作

--set方式declare @i as intset @i=10;--SQL Server 2008可以在同一语句同时声明和初始化变量declare @i as int = 10;

② SELECT语句允许从同一行中获得的多个值分配给多个变量。

--select方式
declare @firstname as nvarchar(20), @lastname as nvarchar(40);
select  @firstname = firstname,  @lastname = lastnamefrom hr.Employeeswhere empid=3;
select @firstname as firstname, @lastname as lastname;

SET语句比复制SELECT语句更加安全，因为它要求使用标量子查询来从表中提取数据。如果在运行时，标量子查询返回了多个值，则查询会失败。例如下面的代码在运行时会报错：

--set比select语句更安全
declare @empname as nvarchar(61);
set @empname = (s
                elect firstname + N' '+ lastname                from hr.Employees                where mgrid=2);                
select @empname as empname;

（2）批处理：客户端应用程序发送到SQL Server的一组单条或多条T-SQL语句，SQL Server将批处理语句作为单个可执行的单元。

下面是一个批处理的示例，但要注意的是如果批处理中存在语法错误，整个批处理是不会提交到SQL Server执行的。

-- A Batch as a Unit of Parsing-- Valid batch
  PRINT 'First batch';
USE TSQLFundamentals2008;
GO
  -- Invalid batch
  PRINT 'Second batch';
SELECT custid FROM Sales.Customers;SELECT orderid FOM Sales.Orders; 
-- 这一句有语法错误，故整个批处理不能提交到SQL Server执行
GO
  -- Valid batch
PRINT 'Third batch';
SELECT empid FROM HR.Employees;
GO

Tip：批处理和事务不同，事务是工作的原子工作单元，而一个批处理可以包含多个事务，一个事务也可以在多个批处理中的某些部分提交。当事务在执行中被取消或者回滚时，SQL Server会撤销自事务开始以来的部分活动，而不考虑批处理是从哪里开始的。

流程控制

（1）IF...ELSE

这个大家应该都知道，但是需要注意的是：T-SQL使用的是三值逻辑，当条件取值为FALSE或UNKNOWN时，都可以激活ELSE语句块。如果条件取值可能为FALSE或UNKNOWN（例如，涉及到NULL值），而且对每种情况需要进行不同的处理时，必须用IS NULL谓词对NULL值进行显式地测试。

下面的IF-ELSE代码演示了：如果今天是一个月的第一天，则对数据库进行完整备份；如果今天是一个月的最后一天，则对数据库进行差异备份（所谓差异备份，就是指只保存上一次完整备份以来做过的更新）。

IF DAY(CURRENT_TIMESTAMP) = 1BEGIN  
PRINT 'Today is the first day of the month.'; 
PRINT 'Starting a full database backup.';  
BACKUP DATABASE TSQLFundamentals2008    TO DISK = 'C:\Temp\TSQLFundamentals2008_Full.BAK' WITH INIT;  
PRINT 'Finished full database backup.';
ENDELSEBEGIN  PRINT 'Today is not the first day of the month.' 
PRINT 'Starting a differential database backup.';  
BACKUP DATABASE TSQLFundamentals2008    TO DISK = 'C:\Temp\TSQLFundamentals2008_Diff.BAK' WITH INIT;  
PRINT 'Finished differential database backup.';
END
GO

这里假设备份的文件路径目录C:Temp已经存在。

（2）WHILE：不解释了，各位应该都懂。

DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 10
BEGIN  
PRINT @i;  
SET @i = @i + 1;
END;
GO

游标

T-SQL中支持一种叫做游标的对象，可以用它来处理查询返回的结果集中的各行，以指定的顺序一次只处理一行。这种处理方式与使用基于集合的查询相反，普通的查询是把集合作为一个整体来处理，不依赖任何顺序。

换句话说，使用游标，就像是用鱼竿钓鱼，一次只能勾到一条鱼一样。而使用集合，就像用渔网捕鱼，一次能捕到整整一网鱼。因此，使用游标的场景我们应该多多斟酌。一般来说，如果按固定顺序一次处理一行的游标方式涉及到的数据访问要比基于集合的方式少得多，则使用游标会更加有效，前一篇提到的连续聚合就是这样的一个例子。

如何使用游标呢？

下面来看看一个实例，它使用游标来计算CustOrders视图中每个客户每个月的连续总订货量（连续聚合案例）：

-- Example: Running Aggregations
SET NOCOUNT ON;
USE TSQLFundamentals2008;
DECLARE @Result TABLE
(  custid     INT,  
 ordermonth DATETIME, 
 qty        INT,  
 runqty     INT,  
 PRIMARY KEY(custid, ordermonth)
);
DECLARE  @custid     AS INT,  
  							@prvcustid  AS INT,  
                @ordermonth DATETIME,  
                @qty        AS INT,  
                @runqty     AS INT;
DECLARE C CURSOR FAST_FORWARD 
/* read only, forward only */ 
FOR  SELECT custid, ordermonth, qty  FROM Sales.CustOrders  ORDER BY custid, ordermonth;
OPEN CFETCH NEXT FROM C INTO @custid, @ordermonth, @qty;SELECT @prvcustid = @custid, @runqty = 0;
WHILE @@FETCH_STATUS = 0BEGIN  IF @custid <> @prvcustid    
SELECT @prvcustid = @custid, @runqty = 0;  
SET @runqty = @runqty + @qty;  
INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty);   
FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
ENDCLOSE C;
DEALLOCATE C;
SELECT   
				custid,  CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth,  qty,  runqty
FROM @ResultORDER BY custid, ordermonth;
GO

执行结果如下图所示：

临时表

有时需要把数据临时保存到表中，而且在有些情况下，我们可能不太想要使用永久性的表。在这种情况下，使用临时表可能会更方便。

（1）局部临时表：只对创建它的会话在创建级和对调用对战的内部级（内部的过程、函数、触发器等）是可见的，当创建会话从SQL Server实例断开时才会自动删除它。

创建临时局部表，只需要在命名时以单个#号作为前缀：

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 OD    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;
GO

（2）全局临时表：可以对其他所有会话都可见，当创建临时表的会话断开数据库的连接，而且也没有活动在引用全局临时表时，SQL Server才会自动删除相应的全局临时表。

创建全局局部表，只需要在命名时以两个#号作为前缀：

-- Global Temporary Tables
CREATE TABLE dbo.##Globals
(  
id  sysname     NOT NULL PRIMARY KEY,  
  val SQL_VARIANT NOT NULL
);

动态SQL

SQL Server允许用字符串来动态构造T-SQL代码地一个批处理，接着再执行这个批处理，这种功能叫做动态SQL（Daynamic SQL）。

（1）使用EXEC（EXECUTE的缩写）命令

-- Simple example of EXEC
DECLARE @sql AS VARCHAR(100)
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);
GO

（2）使用sp_executesql存储过程

sp_executesql存储过程有两个输入参数和一个参数赋值部分：第一个参数需要指定包含想要运行的批处理代码地Unicode字符串，第二个参数是一个Unicode字符串，包含第一个参数中所有输入和输出参数的生命。接着为输入和输出参数指定取值，各参数之间用逗号分隔。

-- Simple example 
using sp_executesql
DECLARE @sql AS NVARCHAR(100);
SET @sql = N'SELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE orderid = @orderid;';
EXEC sp_executesql  @stmt = @sql,  @params = N'@orderid AS INT',  @orderid = 10248;
GO

Tips:

①sp_executesql存储过程在执行性能上比EXEC要好，因为它的参数化有助于重用缓存过的执行计划。

②sp_executesql存储过程在安全上也比EXEC要好，它的参数化也可以不必受SQL注入的困扰。

例程：用户自定义函数、存储过程 与 触发器

（1）用户定义函数：封装计算的逻辑处理，有可能需要基于输入的参数，并返回结果。

下面的示例创建了一个用户定义函数dbo.fn_age，对于给定出生日期和事件日期，这个函数可以返回某个人在时间日期当时的年龄：

IF OBJECT_ID('dbo.fn_age') IS NOT NULL 
DROP FUNCTION dbo.fn_age;
GO
CREATE FUNCTION dbo.fn_age(  
  @birthdate AS DATETIME, 
  @eventdate AS DATETIME
)
RETURNS INTAS
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

（2）存储过程：封装T-SQL代码地服务器端例程，可以有输入和输出参数，可以返回多个查询的结果集。

下面的示例创建了一个存储过程usp_GetCustomerOrders，它接受一个客户ID和日期范围作为输入参数，返回Orders表中由指定客户在指定日期范围内所下的订单组成的结果集，同时也将受查询影响的行为作为输出参数。

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 OUTPUTASSET NOCOUNT ON;
SELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE custid = @custid  AND orderdate >= @fromdate  AND orderdate < @todate;SET @numrows = @@rowcount;GODECLARE @rc AS INT;EXEC Sales.usp_GetCustomerOrders  @custid   = 1, -- Also try with 100  @fromdate = '20070101',  @todate   = '20080101',  @numrows  = @rc OUTPUT;SELECT @rc AS numrows;GO

Tips: 存储过程可以封装业务逻辑处理，更好地控制安全性（有助于避免SQL注入），提高执行性能（减少网络通信流量）。

（3）触发器：一种特殊的存储过程，只要特定事件发生，就会调用触发器，运行它的代码。SQL Server支持两种类型相关的触发器，分别是：DML触发器和DDL触发器。

下面的示例演示了一个简单的DML触发器，对插入到表的数据进行审核（插入到Audit审核表）。

CREATE TRIGGER trg_T1_insert_audit ON dbo.T1 AFTER INSERTASSET NOCOUNT ON;INSERT INTO dbo.T1_Audit(keycol, datacol)  SELECT keycol, datacol FROM inserted;GO

错误处理

T-SQL代码中提供了一种成为TRY...CATCH的结构，在SQL Server 2005中引入的。

BEGIN TRY 
				PRINT 10/2; 
PRINT 'No error';
END TRY
BEGIN CATCH  
				PRINT 'Error';
END CATCH
GO

对于错误处理代码，在实际开发中，可以封装创建一个存储过程来重用错误代码。

小结

本文介绍了MS SQL Server 2008的多种可编程的对象，下一篇会介绍一个重头戏：事务与并发，考虑到文字的数量，可能会分为两篇文章来发布。

参考资料

[美] Itzik Ben-Gan 著，成保栋 译，《Microsoft SQL Server 2008技术内幕：T-SQL语言基础》

强烈建议大家阅读完每一章节后，练习一下课后习题，相信或多或少都会有一些收获。

The End