第 17 章 Transact-SQL 结构化程序设计

第17章 Transact-SQL结构化程序设计 学习导读 • 前面介绍SQL语言都是根据其功能来划分的，如与视图有关的SQL语句有CREATE VIEW、ALTER VIEW、DROP VIEW等，因此这些SQL语句都是孤立的SQL语句，而且只能按照顺序执行，没有流程控制，也没有太强调中间数据的处理；而本章介绍的结构化程序设计将借助于变量、运算符、控制流语句，以及一些特殊语句，将SQL语句进行良好地组合，以结构化的程序形式实现更强大的功能。

变量 • 变量主要是用来存储SQL语句执行后的值，以便更好地为其他SQL语句所用。变量是程序自动化执行的关键，设想一下：如果没有变量存储SQL语句执行结果，那么用户就需要手工记录其结果，并在下一次用到该结果时再输入。可以说，没有变量就没有程序。

变量的作用域与GO语句 • 提到变量，其实前面在创建存储过程或函数时，已经用到了变量，而且存储过程（包括触发器）、函数以及SQL脚本是变量最常用的3个地方。变量在这3个地方声明和使用的方法类似，但是其作用域不同，具体如下。 • 存储过程和函数中变量的作用域都是局部的。也就是说，在存储过程和函数中定义的变量不能够被其所在的存储过程和函数之外的SQL语句访问，即使访问到，也不是其在存储过程和函数中的值。一般情况下，创建存储过程和函数的SQL语句要位于开头或两个GO语句之间。

变量的作用域与GO语句 • SQL脚本中的变量是全局变量。不过，并不是每一个SQL脚本中的变量都能够在整个SQL脚本中使用GO语句将SQL脚本划分为几个独立的全局区域。被GO语句分割的区域间的变量是不能够共享的。 • GO语句是很重要的SQL语句。它的功能具体表现在：当执行引擎遇到GO语句时，将会清除前面语句定义的全局变量。如下语句演示GO语句的用途：

变量的作用域与GO语句 DECLARE @var_INT INT=100 SELECT @var_INT GO SELECT @var_INT

变量的作用域与GO语句 • 显然，上述第一个SELECT语句成功执行，而GO语句之后的SELECT语句却失败，其失败的原因就在于GO语句清楚了GO语句之前的全局变量@var_INT。也就是说，在GO语句之前定义的所有变量都将失效。 • GO语句还有一个重要功能，就是等待其之前的语句执行完毕后，才开始执行其之后的语句，这个功能很重要，用户可能会经常碰到下面一条语句依赖于上面SQL语句的情况，那么就需要确切地保证上一条SQL语句成功执行后，再执行该SQL语句。

变量的作用域与GO语句 • 对于下面的SQL语句，本意是先设置database_demo为当前数据库，然后在database_demo中创建一个表tb_go，并插入一条记录。 USE database_demo CREATE TABLE tb_go(id INT PRIMARY KEY,name NCHAR(100),salary INT) INSERT INTO tb_go VALUES(2157,’LXP’,1000)

变量的作用域与GO语句 • 上述SQL语句在执行时，可能会出错。这是因为SQLCMD或SSMS在执行SQL语句时，不是同步的，即不等待本条语句结束就自动开始执行下一条语句。对于本例来说，CREATE TABLE语句还没有成功执行，即表还没有创建成功，INSERT语句就开始向表中插入行记录信息了。 • 而GO语句可以强制等待到本条SQL语句成功执行后，再开始执行下一条SQL语句。可以将上述的SQL语句改写。 USE database_demo GO CREATE TABLE tb_go(id INT PRIMARY KEY,name NCHAR(100),salary INT) GO INSERT INTO tb_go VALUES(2157,’LXP’,1000)

定义变量 • 在SQL Server中，变量是通过DECLARE语句声明的，并且使用SET语句和SELECT语句对变量进行赋值。如果在声明变量时，没有为变量指定初始值，那么在默认情况下，DECLARE声明的变量初始值为NULL。完整的DECLARE语句的语法较为复杂，笔者依据变量的数据类型，将其简化为以下几种情况。

定义变量 1．系统数据类型如果定义变量的数据类型为SQL Server系统数据类型，可以用如下方式定义： DECLARE @local_var AS data_type [=value][,..n] 组成元素的意义： • @local_var是变量的名称，需要符合SQL Server标识符规则。另外，变量的名称必须要以@开头，否则DECLARE语句将会变为声明游标。

定义变量 • value是变量的初始值，其类型必须与变量声明类型匹配，或者在SQL Server自动转换下是匹配的。 • data_type是SQL Server提供的系统数据类型（除TEXT, NTEXT或IMAGE外）。

定义变量 下面的SQL语句声明了几种常用的变量，并指定了初始值： DECLARE @var_INT INT=2157 DECLARE @var_DATETIME DATETIME=GETDATE() DECLARE @var_FLOAT FLOAT=21.57 DECLARE @var_BINARY BINARY(1000)=120 DECLARE @var_NCHAR NCHAR(10)=‘LXP’ DECLARE @var_XML XML=‘<student>LXP</student>’ BINARY这种变量最好少用，毕竟这种变量占用的内存较多。况且，BINARY运算起来也会过多地消耗资源，降低SQL Server处理速度。

定义变量 2．表类型如果定义变量的数据类型为表类型，那么可以使用如下方式定义表变量。 DECLARE @table_var AS <table_type> 组成元素的意义： • @table_var是TABLE类型的变量的名称。变量同样要以@开头。 • <table_type>是表类型的结构，包括列定义、名称、数据类型和约束。约束只允许主键、唯一、空和检查。

定义变量 下面的SQL语句声明了一个表变量： DECLARE @var_table AS TABLE(id INT NOT NULL,name NCHAR(10) NULL,PRIMARY KEY(id),UNIQUE(name),CHECK(id>2003)) INSERT INTO @var_table VALUES(20033705,’LXP’) UPDATE @var_table SET name=‘LIN’ WHERE id=20033705 SELECT * FROM @var_table

定义变量 3．用户自定义表类型用户自定义表类型与表类型不同。因为用户自定义表类型是使用CREATE TYPE创建的，并保存在数据库中；而表类型将在执行后消失，并不保存到数据库中。如果定义变量的数据类型为用户自定义类型，那么可以用如下方式定义变量： DECLARE @user_var AS <user_table>

定义变量 如下SQL语句使用CREATE TYPE创建了一个用户自定义表类型，并使用DECLARE基于该表类型定义了一个表变量。无论是定义还是使用，表变量都和普通表类似。 USE database_demo GO CREATE TYPE userdefine_table AS TABLE(id INT NOT NULL,name NCHAR(10) NULL,PRIMARY KEY(id),UNIQUE(name),CHECK(id>2003))

定义变量 GO DECLARE @var_table AS userdefine_table INSERT INTO @var_table VALUES(20033705,’LXP’) UPDATE @var_table SET name=‘LIN’ WHERE id=20033705 SELECT * FROM @var_table

常量 • 常量也称标量值，其格式取决于其所表示值的数据类型。常量按照其值的类型不同，大致可以分为字符串常量、Unicode字符串、二进制常量、BIT常量、DATETIME常量、INTEGER常量、DECIMAL常量、FLOAT、REAL常量、MONEY常量、UNIQUEIDENTIFIER常量。其中，在表示负的数值常量时，只需在数值常量前加减号（-）即可。

常量 1．字符串常量在SQL Server中，字符串常量包含在单引号中。一般情况下，字符串常量是由字母（a～z、A～Z）、数字字符（0～9）以及特殊字符（!,@,#）组成的。如果单引号中的字符串包含一个嵌入的引号，可以使用两个单引号表示嵌入的单引号。其中，空字符串是由两个没有任何字符的单引号（’’）表示。 SELECT ‘The level #job_id:’’30%!,fox@foxma’’il.com’

常量 2．Unicode字符串 Unicode字符串的格式就是在普通字符串前面有一个大写的N，如’LXP’是字符串常量，而N’LXP’是一个Unicode字符串变量。对于字符数据存储Unicode数据时，每个字符使用2个字节，而不是1个字节。 3．二进制常量二进制常量是以0x开头的数字串，但不是字符串，不需要单引号。由于采用十六进制表时，所以看起来像字符串。

常量 4．BIT常量 BIT常量其实是一种特殊的INT常量，其有效值要么为0，要么为1，不需要单引号。 5．DATETIME常量 DATETIME常量是由字符串组成的日期表示，如’2008-07-10 00:00:00.000’。 6．INTEGER常量 INTEGER常量是由数字（0～9）组成，没有小数点。

常量 7．DECIMAL常量 DECIMAL常量是由数字和小数点组成的小数。 8．FLOAT和REAL常量 FLOAT和REAL常量是由科学记数法表示的小数，如101.5E5（十进制为10150000）。 9．MONEY常量以货币符号开头且包含小数点的数字串表示，一般情况下没有单引号。

常量 10．UNIQUEIDENTIFIER常量用来表示GUID的，可以使用字符串或二进制表示，’6F9619FF-8B86-B42D-00C04FC964CC’和0xff19966f868b11d0b42d00c04fc964cc是完全相同的GUID。

运算符 • 在SQL Server 2008的Transact-SQL语言中，运算符是很重要的部分，可以说对于数据的处理都要用到这些运算符。其实，运算符还是主要用于连接表达式、变量以及常量。不同的运算符其使用意义不同，返回值亦不同。本节将详细介绍这些运算符。

算术运算符 • 在SQL脚本中，算术运算符用来对两个表达式进行数学运算，其返回值的数据类型由参与运算的两个表达式的数据类型决定。而这两个表达式值的数据类型可以是相同的数据类型，也可以是能够被SQL Server进行自动类型转换的数据类型。如果参与算术运算的两个表达式不能够被SQL Server自动转换类型，那么将出错。

逻辑运算符 • 在SQL脚本中，逻辑运算符主要用于条件语句中，其返回值为TRUE或FALSE；算术运算符主要有ALL、ANY（SOME）、EXISTS、IN、LIKE、AND、NOT、OR、BETWEEN。 1．ALL关键字主要用于比较特定值和结果集的所有值。一般情况下，ALL与比较运算符配合使用。特定值 {=|<>|!=|>|>=|!>|<|<=|!<} ALL {结果集}

逻辑运算符 其中，当SELECT语句结果集中的值都满足与特定值的比较时，才能返回TRUE。 USE database_demo GO CREATE TABLE tb_logic(salary INT) GO INSERT INTO tb_logic VALUES(1100) INSERT INTO tb_logic VALUES(1200) GO IF 1211>ALL(SELECT salary FROM tb_logic) PRINT ‘所有的值都小于1211’ IF 1111>ALL(SELECT salary FROM tb_logic) PRINT ‘所有的值都小于1111’

逻辑运算符 2．ANY（SOME）关键字主要用于比较特定值和结果集中的所有值。一般也和比较运算符配合使用。特定值 {=|<>|!=|>|>=|!>|<|<=|!<} ALL {结果集} 当SELECT语句结果集中的值与特定值的比较时，有一个能满足就返回TRUE。

逻辑运算符 USE database_demo GO CREATE TABLE tb_logic(salary INT) GO INSERT INTO tb_logic VALUES(1100) INSERT INTO tb_logic VALUES(1200) GO IF 1211>ANY(SELECT salary FROM tb_logic) PRINT ‘有值小于1211’ IF 1111>ANY(SELECT salary FROM tb_logic) PRINT ‘有值小于1111’

逻辑运算符 3．EXISTS关键字用于测试一个子查询的结果集是否存在。如果结果集不为空，那么将返回TRUE。 EXISTS {SELECT语句} 使用EXISTS的例子： USE database_demo GO CREATE TABLE tb_logic(salary INT) GO INSERT INTO tb_logic VALUES(1100) INSERT INTO tb_logic VALUES(1200) GO

逻辑运算符 IF NOT EXISTS (SELECT * FROM tb_logic WHERE salary=1200) INSERT INTO tb_logic VALUES(1200) IF NOT EXISTS (SELECT * FROM tb_logic WHERE salary=1300) INSERT INTO tb_logic VALUES(1300) SELECT * FROM tb_logic

逻辑运算符 4．IN关键字用于测试特定值是否在子查询的结果集中。如果特定值存在于结果集中，将返回TRUE。特定值 [NOT] INT (SELECT查询|值[,…n]) 使用IN的例子： USE database_demo GO CREATE TABLE tb_logic(salary INT) GO INSERT INTO tb_logic VALUES(1100) INSERT INTO tb_logic VALUES(1200) GO

逻辑运算符 DECLARE @it INT=1300 IF @it NOT IN (SELECT * FROM tb_logic) INSERT INTO tb_logic VALUES(1300) SELECT * FROM tb_logic SELECT * FROM tb_logic WHERE salary IN (1100,1200)

逻辑运算符 5．LIKE关键字用于测试特定字符串是否与指定模式匹配。在模式匹配过程中，模式的常规字符必须与特定字符串指定的字符完全匹配。但是，模式中的通配符可以与特定字符串的任意部分相匹配。LIKE是一种字符串的模糊匹配。特定字符串 [NOT] LIKE 模式[ESCAPE escchar]

逻辑运算符 USE database_demo GO CREATE TABLE tb_logic(name NCHAR(10)) GO INSERT INTO tb_logic VALUES(‘LIU K’) INSERT INTO tb_logic VALUES(‘LIU C’) INSERT INTO tb_logic VALUES(‘LIN’) GO SELECT name FROM tb_logic WHERE name LIKE ‘LIU%’

逻辑运算符 6．逻辑运算符（AND、NOT、OR、BETWEEN） BETWEEN主要用于范围条件。 USE database_demo GO CREATE TABLE tb_logic(salary INT) GO INSERT INTO tb_logic VALUES(1100) INSERT INTO tb_logic VALUES(1200) INSERT INTO tb_logic VALUES(1300) GO SELECT * FROM tb_logic WHERE salary BETWEEN 1000 AND 1300

赋值运算符 • 在SQL Server中，赋值运算符只有一个，即等号（=）。而=主要用于SELECT语句和SET语句。如下SQL语句将定义一个@name变量，并使用赋值运算符给该变量赋值。 DECLARE @name NCHAR(10) SET @name=‘LXP’ SELECT @name SELECT @name=‘LIN’ SELECT @name

字符串运算符 • 加号（+）是常用的连接子字符串的运算符，其他的子字符串操作都是通过字符串函数进行的（如LTRIM函数等）。如下SQL语句将演示字符串运算符的用法 DECLARE @name NCHAR(10),@result NCHAR(10) SET @name=‘I MISS YOU’ SELECT @result=@name+’, LIN’ SELECT @result=SUBSTRING(@result,1,7)+’LIN’ SELECT @result

按位运算符 • 在SQL Server中，位运算是一种特殊的运算符，主要是对两个整数转化为二进制后，再执行位运算，这种运算使用较少，但功能很强大，也很重要。

按位运算符 DECLARE @var_INT_1 INT=223 DECLARE @var_INT_2 INT=123 SELECT @var_INT_1,@var_INT_2,@var_INT_1^@var_INT_2 AS ‘结果’

比较运算符 • 在SQL Server中，比较运算符是常用的运算符，主要用于比较两个表达式。DECLARE @var_INT_1 INT=223 DECLARE @var_INT_2 INT=123 IF @var_INT_1>@var_INT_2 PRINT ‘比较运算符：>’

复合运算符 • 在SQL Server中，除了上面的运算符外，还有一组复合运算符可以使用。这些符合运算符如表所示。

控制流语句 • 在没有控制流语句的情况下，SQL语句只能顺序执行。控制流语句是结构化程序设计的关键保障，也称为控制流语言。显然，控制流语言为编写复杂的SQL结构化程序提供了支持。有了上述的控制流语句后，才开始了实质性的SQL结构化程序设计。在SQL Server的Transact-SQL中，控制流语言主要是指这样一组关键字，如IF语句、WHILE语句、BEGINEND语句、RETURN语句等。在SQL Server中，控制流语句可以支持嵌套，但是一个语句不能够跨多个批处理、用户自定义函数或存储过程使用。

BEGIN END语句 • BEGIN END语句可以将多个SQL语句限制在其中，将多个SQL语句当作一个逻辑执行块，所以BEGIN END语句中的内容又被称为语句块。BEGIN END语句至少要包括一条SQL语句，否则将出错。

BEGIN END语句 • 当在控制流语句中含有两条或多条SQL语句时，需要使用BEGIN END语句。BEGIN和END语句主要用于以下情况： • IF或ELSE子句的执行体。 • WHILE循环的执行体。 • CASE函数的执行体。

BEGIN END语句 如下IF语句中含有多条SQL语句，所以需要使用BEGIN END语句。 IF @ret=100 BEGIN INSERT INTO tb_logic VALUES(100) SELECT salary FROM tb_logic END

BEGIN END语句 如下不使用BEGIN END语句，那么SQL Server在执行如下语句时，将仅会把INSERT语句作为IF的一部分，而SELECT语句则不属于IF的执行体。 IF @ret=100 INSERT INTO tb_logic VALUES(100) SELECT salary FROM tb_logic

IF语句 • IF语句是最常用的控制流语句，用于简单条件的判断。IF语句将会根据条件的值，指定SQL语句的执行方向。当然，也可以使用IF语句和ELSE子句配合以及IF嵌套，编写复杂的条件判断。得到的控制流取决于是否指定了可选的ELSE语句。

IF语句 1．不含ELSE的IF语句在不使用嵌套的情况下，这个IF语句是最简单的情景。此时，IF语句只有一条执行路径，即要么执行，要么不执行。不含ELSE的IF语句的语法结构如下： IF (条件) 语句或语句块也就是说，当IF语句取值为TRUE时，将执行IF语句后的语句或语句块；IF语句取值为FALSE时，跳过IF语句后的语句或语句块。

IF语句 USE database_demo GO DELETE FROM tb_logic DECLARE @ret INT=100 IF (@ret=100) BEGIN INSERT INTO tb_logic VALUES(100) SELECT salary FROM tb_logic END

第 17 章 Transact-SQL 结构化程序设计