SQL SERVER技术内幕之10 可编程对象

一.变量

变量用于临时保存数据值,以供在声明它们的同一批处理语句中引用。例如,以下代码先声明一个数据类型为INT的变量@i，再将它赋值为10;

DECLARE @i as INT;
SET @i = 10;

SQL SERVER 2008新增加了对在同一语句中同时声明和初始化变量的支持,如下所示:

DECLARE @i as INT = 10;

可以使用一个DECLARE语句同时声明多个变量，但是SET语句只能对一个变量进行操作，所以如果需要把值赋值给多个变量，就必须使用多个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);

SELECT @firstname AS firstname, @lastname AS lastname;

SQL SERVER 还支持一种非标准的赋值SELECT语句，允许在单独的语句中既查询数据，也把从同一行中获得的多个值分给给多个变量，以下是这种用法的一个例子:

DECLARE @firstname AS NVARCHAR(20), @lastname AS NVARCHAR(40);

SELECT @firstname = firstname,@lastname = lastname from HR.Employees where empid = 3;

SELECT @firstname AS firstname, @lastname AS lastname;

当满足条件的查询结果只有一行时，赋值SELECT语句的行为和我们预料的一样。但是，如果查询返回多个满足条件的结果行时，这段代码也不会失败，都会进行赋值，当访问每一行时，就会用当前行的值覆盖掉变量的原有值。当赋值SELECT语句完成时，变量中保存的值是SQL SERVER随机访问到的最后一行中的值。SET语句比赋值SELECT语句更安全，因为它要求使用标题子查询来从表中提取数据。

二.批处理

批处理是从客户端应用程序发送到SQL SERVER的一组单条或多条TSQL语句，SQL SERVER将批处理语句作为单个可执行的单元。SSMS工具提供了一个客户端命令GO,可以发出一批TSQL语句结束的信号，注意，GO命令是客户端工具的命令，而不是TSQL服务器的命令。

1.批处理是语句分析的单元

批处理是作为一个单元而进行分析和执行的一组命令，如果分析成功，SQL SERVER接着就会尝试执行处理，例如，以下代码包含3个批处理，其中第二个存在语法错误。因为第二个批处理存在语法错误，所以整个批处理不会提交到SQL SERVER执行，而第一个和第三个批处理能够通过语法检查，因而可以提交到SQL SERVER执行。

2.批处理和变量

变量是属于定义它们的批处理的局部变量，如果试图引用在其他批处理中定义的变量，SQL SERVER引擎就会报告引用的变量还没有定义。

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

下列语句不能在同一批处理中和其他语句同时编译:CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE SCHEMA、CREATE TRIGGER及CREATE VIEW。

为了避开这个问题，可以在IF语句之后添加一个GO命令，从而把IF和CREATE VIEW语句分隔到不同的批处理中。

4.批处理是语句解析的单元

批处理是语句解析的单元，这意味着检查数据对象和列是否存在，是在批处理上进行的。当设计批处理的边界时，应该牢记这一事实。如果对数据对象的架构定义了修改，并试图在同一批处理中对该对象进行处理，那么SQL SERVER在解析时还不知道架构发生了变化，因而无法执行数据处理语句，报告解析错误。

当解析SELECT语句时，T1表还只有一列，对col2列的引用将导致错误，避免这种问题的最佳实践就是把DDL语句和DML语句分隔到不同的批处理中。

5.GO n选项

SQL SERVER 2005对GO命令这个客户端工具进行了增强，让它可以支持一个正整数参数，表示GO之前的批处理将执行指定的次数。

三.流程控制元素

1.IF ELSE流程控制元素

IF ELSE元素用于根据条件来控制代码的执行流程。如果条件取值为True，则执行指定的语句或语句块;如果取值为False或Unknown，则执行指定的另一语句或语句块(可选)。

以下代码检查今天是否是一年的最后一天:

 

-- The IF ... ELSE Flow Element
IF YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(day, 1, CURRENT_TIMESTAMP))
  PRINT 'Today is the last day of the year.'
ELSE
  PRINT 'Today is not the last day of the year.'
GO

 

如果需要在IF或ELSE部分运行多条语句，则可以使用语句块。语句块的边界是用一对BEGIN和END关键字标识的。

-- The IF ... ELSE Flow Element
IF YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(day, 1, CURRENT_TIMESTAMP))
BEGIN
  DECLARE @msg as varchar(100) = 'Today is the last day of the year.';
  PRINT @msg;
END 
ELSE
BEGIN
  DECLARE @msg2 as varchar(100) = 'Today is not the last day of the year.';
  PRINT @msg;
END
  
GO

2.WHILE流程控制元素

TSQL提供的WHILE流程控制元素可以用于循环执行代码。当在WHILE关键字后指定的条件取值为True时，WHILE元素可以重复执行一条语句或语句块，当指定的条件或False或Unknown时，循环将会终止。可以使用Break命令退出当前循环，继续执行循环体之后的语句，也可使用CONTINUE命令跳过当前循环的后续处理，继续进行下一次循环。

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

 四.临时表

有时需要把数据临时保存在表中，而且在某些情况下，你可能并不想使用永久性的表。例如，假设你需要让数据只对当前会话可见，或者甚至只对当前批处理可见。SQL Server支持三种类型的临时表:局部临时表、全局临时表及表变量。

1.局部临时表

要创建局部临时表，只需要在命名时以单个数字符号(#)作为前缀。局部临时表只对创建它的会话在创建级和调用堆栈内部级是可见的。当创建级例程退出调用堆栈，SQL Server就会自动删除相应的临时表。假设一个存储过程Proc1调用了另一个存储过程Proc2.而Proc2又调用了另一个Proc3,Proc3又调用了Proc4。Proc2在调用Proc3之前创建了一个临时表#T1,这时表#T1对Proc2(创建级)、Proc3以及Proc4(调用堆栈)是可见的，而对Proc1是不可见的。当Proc2完成时，SQL Server就会自动删除这个表。

使用临时表的一个明显场合是当你的处理需要把中间结果临时保存起来，以供以后查询这些临时数据。另一种场合是需要多次访问某个开销昂贵的处理结果。以下是使用临时表的一个示例。

USE TSQLFundamentals2008;

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);

2.全局临时表

如果创建的是全局临时表，则它对其他所有会话都是可见的。当创建临时表的会话断开数据库联接，并且也没有其他活动在引用全局临时表时，SQL Server会自动删除相应的全局临时表。要创建全局临时表，只需要在命名时用两个数字符号(##)作为前缀，如##T1。使用方式与局部临时表类似。

3.表变量

和使用局部临时表一样，表变量在tempdb数据库中也有对应的表作为其物理表示，而不是像通常所理解的那样，以为表变量只在内存中存在。和局部临时表类似，表变量的访问范围更有限，它只对当前批处理可见，表变量对调用堆栈中当前批处理的内部批处理是不可见的，对会话中随后的批处理也是不可见的。使用示例:

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 OD.orderid = O.orderid
  GROUP BY YEAR(orderdate);

4.表类型

SQL Server2008引用了对表类型的支持，通过创建表类型，可以把表的定义保存到数据库中，以后在定义表变量、存储过程和用户定义的函数的输入参数时，可以将表类型作为 表的定义而重用。

IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL
  DROP TYPE dbo.OrderTotalsByYear;

CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(
  orderyear INT NOT NULL PRIMARY KEY,
  qty       INT NOT NULL
);
GO

 五.动态SQL

SQL Server允许用字符串来动态构造TSQL代码的一个批处理，接着再执行这个批处理，这个运通称为动态SQL.SQL Server提供了两种执行动态SQL的方法:使得EXEC命令和使用sp_executesql存储过程。

1.EXEC命令

EXEC接受一个字符串作为在圆括号中输入的参数，执行字符串中包含的批处理代码。示例如下:

DECLARE @sql as varchar(100) = 'select firstname,lastname from HR.Employees';
EXEC(@sql);

2.sp_executesql存储过程

    从sp_executesql的调用接口来说，使用这个存储过程更安全和更灵活，因为它支持输入和输出参数。正因为在动态SQL代码中可以使用输入和输出参数，这样就有助于写出更安全和更有效的代码。从安全性的角度来说，在代码中出现的参数并不是代码的一部分，而只是表达式中的运算对象，所以，通过使用参数，可以不必受SQL注入的困扰了。

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