7-3

昵称233866 2009-11-07

展开全文

概述

　　由于标准的SQL数据操纵语言（Data Manipulation Language，DML）命令只能用于修改或返回数据，没有提供用于开发过程和算法的编程结构，也没有包含用于控制和调整服务器的数据库专用命令。所以，每种功能完备的数据库产品都必须使用一些它们各自专有的SQL语言扩展来弥补SQL标准的不足之处。在SQL Server中，这种扩展称为Transact-SQL，简称T-SQL。

7.3.1 T-SQL批处理

1.T-SQL批处理的执行
(1)批处理由一条或多条T-SQL语句构成。
(2)前端应用程序会将所有这些语句作为单个代码单元发送给SQL Server。
(3)SQL Server将整个批处理作为一个整体来加以解析，如果发现了任何语法错误，都会导致整个批处理失败，也就是说，不会执行批处理中的任何语句。

2.批处理分隔符
(1)SQL脚本文件或查询分析器的窗口都可以包含多个批处理。
(2)在这种情况下需要使用批处理分隔符关键字来结束每一个批处理。
(3)默认的批处理分隔符是go，它只能放在单独的一行，否则不起作用。
(4)在结束一个批处理时，将会释放所有由该批处理所创建的局部变量、临时表和游标。

3.使用批处理要注意下面两点：
(1)CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 语句不能在批处理中与其它语句组合使用。批处理必须以CREATE语句开始。所有跟在该批处理后的其它语句将被解释为第一个CREATE语句定义的一部分。
(2)不能在同一个批处理中更改表，然后引用新列。

4.选择数据库命令
在T-SQL中使用use命令来选定当前的数据库，如use pubs。

5.调用存储过程方式
(1)在T-SQL批处理中使用exec命令来调用存储过程。
(2)如果一个批处理的第一行是调用存储过程，那么exec可以省略。

7.3.2 变量

1.T-SQL变量的两种类型
局部变量和全局变量

2.变量定义
(1)局部变量由用户定义和维护，名称以单个@字符开头；全局变量由系统定义和维护，以两个@字符开头，用户不能定义全局变量。
(2)声明变量时使用declare命令，并且要指定声明的变量名及其数据类型。
(3)可以使用的数据类型包括create table命令中的所有数据类型，以及table和SQLvariant数据类型。在单个declare命令中声明多个变量时，需要使用都好将它们互相隔开。

3.declare命令的格式
(1)declare @local_variable data_type [, @local_variable data_type…]
(2)例如：declare @var1 int , @var2 varchar(20)

4.变量的作用域
局部变量的作用域（即可以使用变量的应用程序和变量的声明周期）只能是当前的批处理。

5.变量赋值
(1)新声明的变量的默认值为空值，在表达式中使用它们之前必须为它们赋初值。
(2)为变量赋值可以使用set命令和select命令。
(3)它们之间的主要区别在于：select可以从表、子查询或者视图中检索数据，并且也可以包含其它的select子句；而set命令则只能从表达式中获取数据。在set和select命令中都可以使用函数。
(4)当然，select语句可以检索多列，而每列中的数据都可以赋值给一个变量。如果select语句返回了多行，那么将会把结果集中最后一行的数据赋值给变量，系统不会报告任何的错误。
(5)实例
　　 ①目的
　　说明变量的作用域以及set和select命令的用法。
　　 ②语句

use pubs
declare @var1 int, @var2 varchar(50)
select @var1, @var2
set @var1 = 123
select @var2 = job_desc from jobs
select @var1, @var2
go
select @var1, @var2

　　 ③返回结果

--------------------------------
NULL NULL
--------------------------------
123 Designer
--------------------------------

　　服务器: 消息 137，级别 15，状态 2，行 1
　　必须声明变量 '@var1'。
　　 ④说明
　　第一行返回结果说明变量的默认值都是空值NULL。set命令的格式为set @local_variable = expression，select命令的格式为select @local_variable = expression。在本例中select语句选出的结果不止一行，而@var2的值为结果集中最后一行对应的值。由于变量的作用域只是当前的批处理，所以在go后面的批处理发生错误，找不到变量@var1的定义。
(6)在T-SQL中使用变量
　　 ①T-SQL中可以直接在SQL查询中使用变量，而无需通过创建复杂的动态SQL字符串将变量串联到代码中。
　　 ②例如：

use pubs
declare @varstate varchar(10)
set @varstate = 'KS'
select * from authors where state = @varstate
go

　　 ③执行结果如下：
　　341-22-1782 Smith Meander 913 843-0462 10 Mississippi Dr. Lawrence KS 66044 0

7.3.3 流程控制

1.If…Else语句
(1)作用
　　 ①IF…Else语句是条件判断语句，
　　 ②根据表达式的真假，选择执行某个语句或者语句块。
(2)语法形式
　　IF Boolean_expression SQL_statement | SQL_statement_block
　　[ELSE SQL_statement | SQL_statement_block]
(3)例如

IF 1=0
　PRINT 'TRUE'
ELSE
　PRINT 'FALSE'

　　执行结果如下：
　　FALSE

2.Begin…End语句
(1)作用
　　使用begin…End语句可以将多条SQL语句封装起来，形成一个语句块，使这些语句作为一个整体执行。
(2)例如

IF 1=0
　PRINT 'TRUE'
ELSE
begin
　PRINT 'FALSE'
　PRINT 'Begin...end'
end

3.While语句
(1)作用
　　 ①while语句可以重复执行一个语句块，直到条件为假为止。
　　 ②与if命令类似，while命令只能循环执行其后的一条T-SQL命令。
　　 ③如果想循环执行一组命令，则需要使用begin…end。
(2)语句格式
　　WHILE Boolean_condition [SQL_statement | SQL_statement_block]
(3)格式说明
　　 ①while语句首先测试循环条件，如果循环条件为真，则执行循环体；如果为假，就跳到循环体之后的第一条SQL语句继续执行。在执行完循环体的最后一条语句后，重新回到while语句来判断循环条件。
　　 ②在while语句的循环体中可以使用break和continue语句。Break语句可以立即中止循环，继续执行循环体之后的语句，而continue语句可以跳过循环体内continue之后的语句，重新判断循环条件。
(4)实例
　　下面的例子演示了while、break、continue语句的用法和作用。

declare @tmp int
set @tmp = 0
while @tmp < 5
begin
　set @tmp = @tmp + 1
　if @tmp = 2 continue
　if @tmp = 4 break
　print 'tmp:' + Str(@tmp)
end
print 'while end'

　　执行结果为：
　　tmp: 1
　　tmp: 3
　　while end

4.Goto语句
(1)说明
　　 ①T-SQL中的Goto语句可以使SQL语句的执行流程无条件的转移到指定的标签处，但是该标签只能在同一个批处理或者过程中。
　　 ②定义标签时只需要在标签的名字后面放上一个冒号：Lablename:
(2)实例
　　下面的例子使用Goto语句跳转到指定标签处。

goto Label1
print '1'
Label1:
print '2'

　　执行结果为：
　　2

7.3.4 临时表和表变量

1.作用
(1)临时表和表变量与标准的用户表扮演的是不同的角色。
(2)由于它们是临时的，所以很适合用来在不同的对象之间传递数据或者为临时性的任务短时间保存数据。

2.局部临时表
(1)创建临时表的方法与创建用户定义表的方式基本相同，只是在创建局部临时表时必须使用由符号“#”开头的表名。实际上，临时表是创建并保存在tempdb数据库中的。
(2)如create table #tmptable (id int primary key,value varchar(100))
(3)局部临时表的生命周期很短暂。当创建它的批处理或者过程结束时，临时表就被删除了。局部临时表的作用域也很有限，只有创建局部临时表的连接才能够看到它。

3.全局临时表
(1)全局临时表与局部临时表类似，区别在于作用域更广泛，所有用户都可以引用全局临时表，它直到最后一个引用它的会话结束后才会被删除。
(2)创建临时表需要使用两个符号“#”开头的表名。
(3)下面的例子中首先检查指定的全局临时表是否存在，如果不存在就创建它：

IF NOT EXISTS (
SELECT * FROM Tempdb.dbo.Sysobjects
WHERE Name = '##Temptable')
CREATE TABLE ##Temptable( ID INT Primary key, value varchar(20))

4.表变量
(1)表变量的概念
　　 ①表变量类似于临时表，它的优点在于它存在于内存中。表变量与变量具有同样的作用域和生命周期。
　　 ②应尽可能使用表变量而不使用临时表。
(2)表变量有以下优点
　　 ①表变量的行为类似于局部变量，有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。
　　 ②在其作用域内，表变量可像常规表那样使用。表变量可应用于SELECT、INSERT、UPDATE和DELETE语句中用到表或表的表达式的地方。
　　 ③在定义table变量的函数、存储过程或批处理结束时，自动清除表变量。
　　 ④在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
　　 ⑤涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
(3)不支持在表变量之间进行赋值操作。另外，由于表变量作用域有限，并且不是持久数据库的一部分，因而不受事务回滚的影响。
(4)例如：