编写脚本和批处理

定义：脚本就是一连串命令的总称。

案例：

DECLARE @Ident int;--什么一个变量

INSERT INTO Orders
(CustomerNo,OrderDate,EmployeeID)VALUES(2,GETDATE(),1);
SELECT @Ident = SCOPE_IDENTITY();
INSERT INTO OrderDetails
(OrderID,PartNo,Description,UnitPrice,Qty)VALUES(@Ident,'2R2416','Cylinder Head',1300,2);

SELECT 'The OrderID of the INSERTed row is'+CONVERT(varchar(8),@Ident)

使用USE语句选中数据库环境

定义：用于设置当前数据库

声明变量（DECLARE）

表达式：DECLARE @<variable name><variable type> [=<value>],@<variable name><variable type> [=<value>],@<variable name><variable type> [=<value>]

一次可以什么一个变量也可以什么多个变量

SCOPE_IDENTITY():是一个系统函数。它总是可用的，并提供在当前作用域中分配的最近标识值。

声明一个表格类型数据

DECLARE @<variable name> TABLE(<column spec>[,.....])

案例：

DECLARE @InterestingRows TABLE(
RowID INT NOT NULL IDENTITY PRIMARY KEY,
Descriptor VARCHAR(255) NOT NULL
);

设置变量中的值

修改变量值的方法有3种：在DECLARE语句中初始化变量，或者可以使用SELECT语句或SET语句。

从功能上看SET和SELECT的作用几乎是相同的。不同的是SELECT语句能够做更多的工作:

·.运行数据源值来自SELECT语句中的某一列。

·.SELECT可以在一条语句中将值分配给多个变量。

1.使用SET设置变量

SET @TotalCost =10

SET @TotalCost = @UnitCost * 1.1

注意：不能将查询到的值直接赋给变量----必须将查询和SET分开。

案例：

DECLARE @Test money;
SET @Test = (SELECT MAX(UnitPrice) FROM Sales.SalesOrderDetail);
SELECT @Test

2.初始化变量

DECLARE @Counter INT = 0;

DECLARE @@MaxPrice money =(SELECT MAX(UnitPrice) FROM Sales.SalesOrderDetail);
SELECT @@MaxPrice

3.使用SELECT 设置变量

当变量中存储的信息来源于查询时，经常用SELECT给变量赋值。

DECLARE @Test money;
SELECT @Test = MAX(UnitPrice) FROM Sales.SalesOrderDetail;
SELECT @Test;

注意

1.当执行简单的变量赋值时，使用SET——这是已知值是一个显示值或者其他变量。

2.当基于查询进行变量赋值时，使用SELECT。

系统函数回顾

检索标识值

SCOPE_IDENTITY 是所有系统函数中最重要的一个。标识列是这样的一种列，既不对器提供一个值，而是有SQL SERVER 自动的插入一个已编号的值。

案例：

DECLARE @Ident INT;
INSERT INTO TestIdent DEFAULT VALUES;
SET @Ident = SCOPE_IDENTITY();
PRINT 'The value we you got originally from SCOPE_IDENTITY() was' + convert(varchar(2),@Ident);
PRINT 'The value currently in SCOPE_IDENTITY() is '+CONVERT(varchar(2),SCOPE_IDENTITY());
INSERT INTO TestChild1
VALUES (@Ident);
PRINT 'The value you got originally from SCOPE_IDENTITY() was'+CONVERT(varchar(2),@Ident);
IF (SELECT SCOPE_IDENTITY()) IS NULL
PRINT 'The value currently in SCOPE_IDENTITY() IS NULL';
ELSE
PRINT 'The value currently in SCOPE_IDENTITY() IS'+CONVERT(varchar(2),SCOPE_IDENTITY());
PRINT '';
INSERT INTO TestChild2
VALUES
(@Ident)

必须将SCOPE_IDENTITY 的值赋给变量，否则多次插入值中会发生改变。

注意:@@IDENTITY与SCOPE_IDENTITY()效果相同。

生成序列

定义：序列返回请求的下一个值，按照在创建时定义的值进行递增。由于序列是作为独立的对象存在，因此可以在执行插入之前检索序列中的下一个值（获得标识），将其应用与多个表，按照任意列列表进行排序，甚至可以根据需要在到达最大值时循环遍历到最小值。序列是一种主动的解决方案，而SCOPE_IDENTITY()则是反应性的解决方案。标识值检索函数的唯一作用就是指出分配了那些值。使用SEQUENCE对象可以从特定的值开始，将其保存到所需的任意多个位置。

创建序列

CREATE TABLE TestSequence(
SeqCol int  not null primary key
);


CREATE TABLE TestSeqChild1(
SeqCol int primary key foreign key references TestSequence(SeqCol)
);


CREATE TABLE TestSeqChild2(
SeqCol int primary key foreign key references TestSequence(SeqCol)
);


CREATE SEQUENCE SeqColSequence AS int START WITH 1
INCREMENT BY 1 MINVALUE 0;

------------------------------------------

测试Sequence

DECLARE @Seq INT;
SELECT @Seq = NEXT VALUE FOR SeqColSequence;
PRINT 'The value you got from SeqColSequence was '+CONVERT(varchar(2),@Seq);
INSERT INTO TestSequence(SeqCol) VALUES(@Seq);
INSERT INTO TestSeqChild1 VALUES(@Seq);
INSERT INTO TestSeqChild2 VALUES(@Seq);

注意：

使用标识列

（1）声明变量。

（2）插入父值。

（3）使用SCOPE_IDENTITY()填充变量。

（4）插入子值

使用序列（只有sqlserver2012才能用）

（1）声明变量。

（2）获取序列中的下一个值。

（3）插入父值和子值。

使用@@ROWCOUNT

定义：获取增删查改影响行数。

案例：

use AdventureWorks2012
GO
DECLARE @PersonCount int;
SELECT * FROM Person.Person;
SELECT @PersonCount = @@ROWCOUNT;
PRINT 'The value of @@ROWCOUNT was'+CONVERT(varchar(6),@PersonCount);

将语句分组到批处理中

定义：批处理是作为一个逻辑单元的一组T-SQL语句。一个批处理中的所有语句被组合成一个执行计划，因此所有语句一起进行语法分析，并且必须通过语法验证，否则将不执行任何一条语句。如果发生运行时错误，那么任何在发生运行时错误之前执行的语句将仍然是有效的。如果一条语句不能通过语法分析，那么就不会运行任何语句。为了将一个脚本划分为多个批处理，可使用GO语句。

GO语句的特点

1.必须自成一行（并且只有注释可以在同一行上）。

2.使得从脚本的开始部分或者最近一个GO语句（任何一个较近的GO语句）以后的所有语句编译成功一个执行计划并发送到服务器，与任何其他批处理无关。

3.不是T-SQL命令，而是由各种SQL Server命令使用程序（sqlcmd）和 Management  Studio中的“查询”窗口）识别的命令。

何时使用批处理

使用批处理有若干目的，但是所有的批处理具有一个共同点——单脚本中的一些事情必须发生在另一件事情之前或者分开发生时，就需要使用批处理。

1.要出有自己的批处理的语句

● CREATE DEFAULT

● CREATE PROCEDURE

● CREATE RULE

● CREATE TRIGGER

● CREATE VIEW

如果想在单个脚本中将这些语句中的任意一个和其他的语句进行组合，那么需要通过使用GO语句将它们分散到各自的批处理中。

1.使用批处理建立优先级

案例：use Test;
alter table TestTable add col3 int;
GO//这里不用GO出现col3不存在错误
insert into TestTable (col1,col2,col3) values(1,1,1);

动态SQL:用EXEC命令动态生成代码

动态生成代码

定义：在运行时使用字符串操作动态构建SQL语句。

EXEC({<string variable>|'<literal command string>'})或者EXECUTE({<string variable>|'<literal command string>'})

案例：

-------------------------------------获取整个数据库中的表的架构和表的名称----------------------------------------------------------------------------------

use AdventureWorks2012;
go
create table DynamicSQLExample(
TableID int IDENTITY NOT NULL CONSTRAINT PK_DynamicSQLExample_TableID PRIMARY KEY,
SchemaName varchar(128) NOT NULL,
TableName varchar(128) NOT NULL
);
GO
INSERT INTO DynamicSQLExample
SELECT s.name AS SchemaName,t.name AS TableName FROM sys.schemas s join sys.tables t ON s.schema_id = t.schema_id;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @SchemaName varchar(128);
DECLARE @TableName varchar(128);
SELECT  @SchemaName=SchemaName,@TableName = TableName FROM DynamicSQLExample WHERE TableID = 25;
EXEC('SELECT * FROM '+@SchemaName+'.'+@TableName)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

理解动态SQL的危险性

1.EXEC和调用它的代码都在单独的作用域下运行——也就是说，调用代码不能引用EXEC语句中的变量，并且在调用代码中的变量被解析为用于EXEC 语句的字符串之后，EXEC不能引用这些变量。如果过需要在动态SQL和调用它的例程间传递值，考虑使用sp_executesql。

2.EXEC 与调用对象运行在相同的连接和事务环境下。

3.对EXEC字符串执行的要求函数调用的串联必须先于实际调用的EXEC语句——不能在执行EXEC调用的小童语句中执行函数的串联。

4.EXEC不能在用户自定义函数内使用。

5.如果您没有谨慎对待，那么EXEC会给黑客提供可攻击的漏洞。

EXEC的作用域

案例：必须什么在EXEC 内部

USE AdventureWorks2012;
GO
DECLARE @InVar varchar(50);
SET @InVar = 'DECLARE @OutVar varchar(50);
SELECT @OutVar=FirstName FROM Person.Person WHERE BusinessEntityID = 1;
SELECT ''The Value Is''+@OutVar';
EXEC(@InVar);

EXEC的例外情况

在EXEC的作用域内有一个例外情况，这可在执行EXEC之后看到——即系统函数，像@@ROWCOUNT这样的函数仍然可以使用。

USE AdventureWorks2012;
EXEC ('SELECT * FROM Production.UnitMeasure');
SELECT 'The RowCount is ' + CAST(@@ROWCOUNT as varchar);

函数串联和EXEC

不能对EXEC命令中的EXEC字符串运行函数

注：就是不能吧函数放入EXEC()括号中执行。

DECLARE @NumberOfLetters AS int =15;
DECLARE @Str AS varchar(255);
SET @Str='SELECT LEFT(Name,'+CAST(@NumberOfLetters as varchar)+') AS ShortName FROM Production.Product';
EXEC(@Str)

使用控制流语句

IF...ELSE语句

定义：IF<Boolean Expression>

<SQL statement>|BEGIN<code series> END

ELSE

<SQL statement>|BEGIN<code series> END

案例：

1.

IF NOT EXISTS(
SELECT s.name AS SchemaName,t.name AS TableName
FROM sys.schemas S JOIN sys.tables t ON S.schema_id= t.schema_id WHERE s.name = 'dbo' AND t.name = 'MyIFTest'
)
CREATE TABLE MyIFTest(Col1 int PRIMARY KEY);
----------------以上为不存在的范围
SELECT * FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id WHERE s.name = 'dbo'AND t.name = 'MyIFTest';

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2.

IF NOT EXISTS(
SELECT s.name AS SchemaName,t.name AS TableName
FROM sys.schemas S JOIN sys.tables t ON S.schema_id= t.schema_id WHERE s.name = 'dbo' AND t.name = 'MyIFTest'
)
BEGIN
CREATE TABLE MyIFTest(Col1 int PRIMARY KEY);
PRINT N'表创建成功';
END
ELSE
BEGIN
PRINT N'表已经存在';
PRINT N'无需重新创建';
END
----------------以上为不存在的范围
SELECT * FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id WHERE s.name = 'dbo'AND t.name = 'MyIFTest';

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

注意：BEGIN....END 代码块嵌套何以无限

CASE语句

T-SQL语句的唯一缺点是，他基本上只是一个替换操作符而不是控制语句。

使用方式：输入表达式或布尔值。

语法1：CASE<input expression>

WHEN<when expression> THEN <result expression>

[...n]

[ELSE <result expression>]

END

语法1：CASE

WHEN<Boolean  expression> THEN <result expression>

[...n]

[ELSE <result expression>]

END

优点：CASE语句最大的优点是它可与SELECT语句“内联”使用（也就是作为其不可分割的一部分）

案例1

USE AdventureWorks2012
GO
SELECT TOP 10 SalesOrderID,
SalesOrderID%10 AS "Last Digit",
Position=CASE SalesOrderID%10
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
ELSE 'Something Else'
END
 FROM Sales.SalesOrderHeader;

注意：无论何时列表中有匹配值，都会调用THEN子句。由于有一个ELSE子句，与前面的值都不匹配的值将不诶赋予ELSE 子句的值

案例2

SELECT 
 TOP 10 SalesOrderID%10 AS 'OrderLastDigit',
 ProductID%10 AS 'ProductLastDigit',
 "How Close? " = CASE SalesOrderID%10
 WHEN ProductID%1 THEN 'Exact Match!'
 WHEN ProductID%1-1 THEN 'Within 1'
 WHEN ProductID%1+1 THEN 'Within 1'
 ELSE 'More Than One Apart'
 END
  FROM Sales.SalesOrderDetail ORDER BY SalesOrderID DESC;

搜索CASE

案例：

SELECT 
 TOP 10 SalesOrderID%10 AS 'OrderLastDigit',
 ProductID %10 AS 'ProductLastDigit',
 "How Close? " = CASE
 WHEN (SalesOrderID%10)<3 THEN 'Ends With Less Than Three'
 WHEN ProductID =6 THEN 'ProductID is 6'
 WHEN ABS(SalesOrderID%10 -ProductID)<=1 THEN 'Within 1'
 ELSE 'More Than One Apart'
 END
 FROM Sales.SalesOrderDetail ORDER BY SalesOrderID DESC;

注意：

1.即使两个条件为TRUE,也只使用第一个条件。

2.可以在条件表达式中混合搭配使用的字段。

3.可以执行任何表达式，只要最后被求值为布尔结果。

案例：

USE AdventureWorks2012;
   DECLARE @Markup money;
   DECLARE @Multiplier money;
   SELECT @Markup  = 0.10;
   SELECT @Multiplier = @Markup+ 1;

   SELECT 
   TOP 10 ProductID,Name,ListPrice,
   ListPrice * @Multiplier AS "Marked Up Price",
   "New Price" = CASE WHEN FLOOR(ListPrice * @Multiplier+0.24)>FLOOR(ListPrice * @Multiplier)
   THEN FLOOR(ListPrice * @Multiplier)+0.95
   WHEN FLOOR(ListPrice * @Multiplier+0.5)>FLOOR(ListPrice * @Multiplier)
   THEN FLOOR(ListPrice * @Multiplier)+0.75
   ELSE FLOOR(ListPrice * @Multiplier)+0.49
   END
   FROM Production.Product
   WHERE ProductID%10 = 0
   ORDER BY ProductID DESC;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

用WHILE语句进行循环

定义：用于循环

语法：

WHILE<Boolean expression>

<sql statement>|

 [BEGIN 

<statement block>

[BREAK]

<sql statement> |<statement block>

[CONTINUE]

END]

案例：每日凌晨1点更新每个表的统计信息，并将日志写入SQL Server 日志和Windows应用程序日志。

WHILE 1 = 1
BEGIN
   WAITFOR TIME '01:00';
   EXEC sp_updatestats;
   RAISERROR('Statistics Updated for Database', 1, 1) WITH LOG;
END

WAITFOR语句

waitfor所做的就是等待参数指定的操作发生。可以为某操作的发生指定明确的时间，或是指定等待一段时间后执行该操作。

1.DELAY参数

定义：DELAY参数指定了等待的时间段。不能指定天数——只能指定小时数、分钟数和秒数。允许延迟的最长时间为24小时。

案例：WAITFOR DELAY '01:00';将运行的WAITFOR语句之前的任何代码，然后到达WAITFOR语句，停止一个小时，之后继续执行下一条语句中的代码。

2.TIME参数

TIME 参数指定到达指定时间的等待时间。这也不能指定日期——只能是24小时制的某个时间。同样可延迟的最长时间也是一天。

WAITFOR TIME '01：00'

将运行WAITFOR 语句前的任何代码，然后到达WAITFOR语句，直到凌晨1点才停止执行，之后执行WAITFOR语句的下一条语句。

使用TRY/CATCH块处理错误

BEGIN TRY

{<sql statement(s)>}

END TRY

BEGIN CATCH

{<sql statement(s)>}

END CATCH[;]

案例：

BEGIN TRY
CREATE TABLE MyIFTest1(
Col1 int PRIMARY KEY 
);
END TRY


BEGIN CATCH
DECLARE @ErrorNo int,
@Severity tinyint,
@State smallint,
@LineNo int,
@Message nvarchar(4000);


SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE();


IF @ErrorNo = 2714
PRINT N'跳过创建表因为表已经存在';
ELSE
BEGIN
PRINT @Message+'ErrorNo:'+CONVERT(nvarchar(5),@ErrorNo)+'Severity:'+CONVERT(nvarchar(5),@Severity);
RAISERROR(@Message,16,1);
END


END CATCH