每天10分钟学习T-SQL语言基础（Part 5）

【数据库技术】| 作者 / 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语言基础》

这本书的配套源代码和示例数据库，特意上传到了百度云盘中，下载链接：https://pan.baidu.com/s/1jIryBUA

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

The End

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,711评论 5赞 468
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,932评论 2赞 376
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,770评论 0赞 330
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,799评论 1赞 271
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,697评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,069评论 1赞 276
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,535评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,200评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,353评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,290评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,331评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,020评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,610评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,694评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,927评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,330评论 2赞 346
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,904评论 2赞 341