Chapter 10 Programmable Objects
声明和赋值一个变量：
DECLARE @i AS INT;
SET @i = 10;
变量可以让你暂时存一个值进去，然后之后再用，作用域是同一个Batch（批处理）。
也可以这么用：
DECLARE @firstname AS NVARCHAR(10), @lastname AS NVARCHAR(20);
SELECT
@firstname = firstname,
@lastname = lastname
FROM HR.Employees
一个batch是从客户端发送到SQL Server的一条或多条T-SQL语句，作为一个单独的单元（我的理解就是作为一个整体）来解析并执行。batch经历的阶段有：语法分析，解析（检查引用的对象和列是否存在），检查是否有访问权限，优化。
batch和transaction（事务）是完全不同的概念。一个Batch里可以有很多个事务；也可以把一个事务分散到很多个batch里去。上面的加粗部分是我自己感觉的batch和事务的区别。
像SSMS这种客户端工具有GO这个命令，这个命令signals the end of a batch（是一个batch结束的信号）。注意：GO命令是一个客户端命令而不是一个T-SQL Server命令。GO可以加参数，比如GO 100就是执行一百次这个batch。像ADO.NET这些框架也会提供方法for submitting a batch of code to SQL Server for execution（用于提交一个batch的代码到SQL Server执行）。
举个例子：
-- Valid batch
PRINT 'First batch';
USE TSQL2012;
GO
-- Invalid batch
PRINT 'Second batch';
SELECT custid FROM Sales.Customers;
SELECT orderid FOM Sales.Orders;
GO
-- Valid batch
PRINT 'Third batch';
SELECT empid FROM HR.Employees;
注意第二个batch里面有个语法错误（FROM拼成了FOM），所以这整一个batch都没有被提交到 SQL Server执行（the whole batch is not submitted to SQL Server for execution），也就是说它的两个SELECT都没有被执行，而它前后两个Batch都被执行了。
规定有一些语句（大多是CREATE）不能和其他语句一起处于同一个批处理。
由于批处理中的语句作为一个整体进行解析，所以比如以下SQL语句：
ALTER TABLE dbo.T1 ADD col2 INT;
SELECT col1, col2 FROM dbo.T1;
如果以上两条语句作为一个batch的话，会解析失败。因为解析的时候还没有真正执行，所以解析到SELECT那句的时候，col2并不存在。所以应该在SELECT前面加个GO。
控制流：
IF...ELSE...。如果IF里面的东西算出来是FALSE或者UNKNOWN，那么就会运行ELSE块。所以注意NULL的情况。如果你要写多个语句，那么要用BEGIN，END，其实就相当于编程语言里面的大括号，比如：
IF DAY(SYSDATETIME()) = 1
BEGIN
PRINT '....';
PRINT '....';
END
ELSE
BEGIN
PRINT '....';
PRINT '....';
END
可以ELSE IF，也可以嵌套IF ELSE。
WHILE里面可以BREAK和CONTINUE，语法如下：
DECLARE @i AS INT = 1;
WHILE @i <= 10
BEGIN
IF @i = 6 BREAK;
PRINT @i;
SET @i = @i + 1;
END;
输出1 2 3 4 5。
游标（Cursor）：一个没有ORDER BY的query返回一个set（或multiset），而一个带ORDER BY的query返回一个游标。T-SQL提供一个游标对象，可以让你从一个query的结果集中一行一行地处理，并根据指定顺序。作者不建议使用游标，原因如下：1.违背了集合论 2.造成性能损失 3.可读性不好。所以能不用尽量不用。用游标好比钓鱼，而用集合就像用网捕鱼。一般是当需要对每一行都做点工作的时候，才使用游标。例子不写了，需要的时候参考吧。
SQL Server提供了三种临时表：local temporary tables, global temporary tables, and table variables。他们都是被创建在tempdb数据库中。当你需要存储某些中间结果，或者说某个查询很昂贵，而且你要多次查询的话，可以考虑临时表。
一个local temporary table的scope有点类似于编程语言中的local variable，比如四个存储过程：Proc1调用Proc2，Proc2调用Proc3，Proc3调用Proc4，如果在Proc2中创建了一个local temporary table（并且在调用Proc3之前），那么这个临时表对Proc2,3,4都是可见的，但对Proc1不可见，Proc2完成时会被自动销毁。范围最大大到当前session。创建临时表只要把它命名为#tablename：
IF OBJECT_ID('tempdb.dbo.#MyOrderTotalsByYear') IS NOT NULL
DROP TABLE dbo.#MyOrderTotalsByYear;
GO
CREATE TABLE #MyOrderTotalsByYear
(
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 OD.orderid = O.orderid
GROUP BY YEAR(orderdate);
如果你从另一个session想访问这个临时表的话，你做不到。
Global Temporary Tables对所有session可见，当创建这个table的session断开连接，并且there are no active references to the table（没有对这个table有效的引用）时，被自动销毁。创建的时候只要##tablename就行了：
CREATE TABLE dbo.##Globals
(
id sysname NOT NULL PRIMARY KEY,
val SQL_VARIANT NOT NULL
);
一般是当你想跟别人共享的时候用。
Table variables有点类似于local temporary tables，它会被存到tempdb中，而不是只存在于内存中。但是它的scope更有限：只对当前Batch可见，对之后调用的，比如别的存储过程都不可见。如果回滚事务，对temporary tables的修改会被roll back，但对table variables的已经完成的修改不会被roll back。创建语法如下：
DECLARE @MyOrderTotalsByYear TABLE
(
orderyear INT NOT NULL PRIMARY KEY,
qty INT NOT NULL
);
由于性能的原因，数据量小的话就用table variable，否则用local temporary tables。
SQL Server 2008及以上支持Table Types，比如：
CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(
orderyear INT NOT NULL PRIMARY KEY,
qty INT NOT NULL
);
然后你就可以：DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;
Table Types不仅可以用来定义Table，也可以作为存储过程和用户自定义函数的输入参数的类型。
SQL Server允许你创建一个字符串，这个字符串里面是a batch of T-SQL语句，然后可以执行它，这个叫dynamic SQL。可以用EXEC这个命令，它接受一个字符串：
DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);
也可以用sp_executesql这个存储过程，首先看一下用法：
DECLARE @sql AS NVARCHAR(100);
SET @sql = N'SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;';
EXEC sp_executesql
@stmt = @sql,
@params = N'@orderid AS INT',
@orderid = 10248;
结果：
| orderid | custid | empid | orderdate |
|---|---|---|---|
| 10248 | 85 | 5 | 2006-07-04 00:00:00.000 |
@stmt就是你要执行的字符串，@params是输入和输出参数的声明，然后给这些声明的参数赋值（逗号隔开）。那么用这个sp_executesql有什么好处？一是可以防止SQL注入，因为用户输入只能作为操作数（通过 @params）。二是可以复用cached execution plans。cached execution plans就是一个physical processing plan，SQL Server为了某个query产生的，它包括一些列指令：用什么索引、什么算法、目标是哪个表什么的。如果一个query跟以前执行过的某个query一模一样，那么这个cached execution plan就可以得到复用，所以说最好的可以复用cached execution plan的方法就是一个接受输入参数的存储过程，因为即使参数变了，query string不变。类似地，用sp_executesql也会增加复用cached execution plans的机会。
Using PIVOT with Dynamic SQL这部分跳过
Routines（例程）是为了计算结果或者执行任务而对代码封装的可编程对象，SQL Server支持三种Routine：user-defined functions, stored procedures, and triggers。
SQL Server提供给你两种选择来develop一个routine：T-SQL或者.NET code。如果有很多data manipulation，T-SQL更好；如果有很多iterative logic（估计就是foreach这种）, 字符串操作或者很密集的计算的时候，.NET code更好。
User-Defined Functions用作封装一些计算某些结果的逻辑，我们简称UDF。UDF不允许有side effects，比如修改数据库里的数据，以及一些会导致副作用的函数：比如RANK和NEWID。用法：
CREATE FUNCTION dbo.GetAge
(
@birthdate AS DATE,
@eventdate AS DATE
)
RETURNS INT
AS
BEGIN
RETURN 基于输入参数做一些计算得到一个值
END;
END;
然后就可以用了：
SELECT empid, dbo.GetAge(birthdate, SYSDATETIME()) AS age
FROM HR.Employees;
除了返回一个scalar，也可以返回一个table value，这样的话就可以被用在FROM子句。
Stored Procedures比UDF更爽，因为可以包含副作用，也就是可以修改数据，对数据库构架进行修改什么的。相比普通的代码，存储过程的好处在于：封装了逻辑、可以对某个用户赋予可以调用某个存储过程的权限、可以提高性能（由于cached execution plan的复用）并减少网络间传输的数据量（因为你只要告诉数据库你想调用的存储过程名就行了）。用法举例：
CREATE PROC Sales.GetCustomerOrders
@custid AS INT,
@fromdate AS DATETIME = '19000101', --设置默认值
@todate AS DATETIME = '99991231', --设置默认值
@numrows AS INT OUTPUT --这里的OUTPUT表示这个是输出参数
AS
SET NOCOUNT ON; --意思是不要显示“多少行affected”的消息
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = @custid
AND orderdate >= @fromdate
AND orderdate < @todate;
SET @numrows = @@rowcount;
使用：
DECLARE @rc AS INT;
EXEC Sales.GetCustomerOrders
@custid = 1,
@fromdate = '20070101',
@todate = '20080101',
@numrows = @rc OUTPUT;
SELECT @rc AS numrows;
结果：
Triggers（触发器）封装的代码是只有当某些个事件发生，才会被执行。SQL Server支持两种可以给触发器的事件：data manipulation events（DML，比如INSERT什么的）、data definition events（DDL，比如CREATE TABLE什么的）。在一个事务中，如果发生了某个会trigger某个触发器的事件，然后又roll back了，那么触发器造成的改变也会被roll back。在SQL Server中，一个语句触发一个触发器。举例：
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
解释一下：trg_T1_insert_audit是触发器的名字。ON就是你要监听哪个表的事件。AFTER INSERT意思就是在INSERT之后触发（当然MERGE也有可能）。这里的AFTER也可以换成INSTEAD，可以用来代替相关的事件操作（但是书上没给例子），AFTER只能定义在持久化的表上，而INSTEAD可以定义在持久化的表和视图上。后面的FROM inserted的inserted是指有新值的表，也可以换成deleted，表示有老值的表。
再举一个基于DDL的例子（复习的时候可以直接跳过，自认为不太重要）：
CREATE TRIGGER trg_audit_ddl_events
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON;
DECLARE @eventdata AS XML = eventdata();
INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
@eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'VARCHAR(23)'),
@eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'),
@eventdata);
GO
ON DATABASE是指在数据库scope（还有个server scope，当你CREATE DATABASE的时候就是server scope），DDL_DATABASE_LEVEL_EVENTS是指所有的DDL事件。EVENTDATA()这个函数可以给你所有关于事件的信息（XML类型）。然后用XQuery表达式来提取其中的某些属性。然后你执行几个CREATE TABLE和ALTER TABLE的语句，然后再查一下：SELECT * FROM dbo.AuditDDLEvents;，得到结果：
（部分结果）
错误处理。语法如下：
BEGIN TRY
PRINT 10/2;
PRINT 'No error';
END TRY
BEGIN CATCH
PRINT 'Error';
END CATCH;
执行的流程和编程语言中一模一样，所以不再赘述。错误处理中最常用的估计就是ERROR_NUMBER()函数，它会返回一个数字，代表某种错误，比如2627是主键冲突，具体查文档吧。还有其他很多函数，比如ERROR_MESSAGE()代表错误信息，ERROR_SEVERITY()代表严重程度，等等。SQL Server 2012开始也可以用THROW;来在CATCH块里面把异常re-throw出来。