12009-7-28 Information College · ChangJun
第 8章 SQL Server 的编程结构
22009-7-28 Information College · ChangJun
[本章概要 ]
8.1 基本概念
8.2 变量
8.3 函数
8.4 流程控制语句
8.5 游标
8.6 事务
32009-7-28 Information College · ChangJun
8.1 编程结构基本概念
8.1.1 注释语句
l,/*” 和,*/”括起来表示可进行部分、
单行或多行语句的注释。
l,--”表示只可进行单行语句的注释。
l 养成良好的书写注释的习惯
42009-7-28 Information College · ChangJun
8.1.2 脚本和批处理
1、脚本 (script)
是存储在文件中用于执行某项操作的一系列
Transact-SQL 语句集合。
Transact-SQL脚本文件通常带有,sql 扩展名。
Transact-SQL脚本可以用于：
a) 保存用于创建和填充服务器上的数据库的操作的永久复本（备份机制）。
b) 必要时将语句从一台计算机转移到另一台计算机。
c) 通过让新雇员发现代码中的问题、了解代码或更改代码从而快速对其进行培训。
52009-7-28 Information College · ChangJun
2、批处理 (batch)
批处理是客户端作为一个单元发出的一个或多个 SQL 语句的集合。它们作为一个组一起提交给服务器并加以执行。
服务器将每个批处理编译为一个执行计划。
Transact-SQL脚本包含一个或多个批处理。 GO
命令作为批处理结束的标志。如果 Transact-SQL
脚本没有 GO 命令，则将它作为单个批处理执行。
批处理的方法：
a) Create Table,Create View只能单独放在一个批中。
b) 使用事务来保证批处理
c) 包含存储过程的批处理，除第一个可以不用 EXEC来执行，其余都必须用。
62009-7-28 Information College · ChangJun
8.2 变量
Transact-SQL 中可以使用两种变量：
局部变量和全局变量。
8.2.1 局部变量局部变量是用户可自定义的变量，它的作用范围仅在一个批内。特点：
1、在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。
2、局部变量必须以 @开头，而且必须先用
DECLARE 命令说明。其说明形式如下：
DECLARE @变量名 变量类型 [,...n]
3、必须使用 SELECT或 SET 命令来设定变量的值。
72009-7-28 Information College · ChangJun
其语法如下：
SELECT @局部变量 = 变量值
（或） SET @局部变量量 = 变量值例如,
声明一个长度为 8 个字符的变量 id，
并赋值。
declare @id char（ 8）
select @id =?10010001?
或 set @id =?10010001?
82009-7-28 Information College · ChangJun
8.2.2 全局变量全局变量是 SQL Server 系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。
特点：
1、全局变量通常存储一些 SQL Server 的配置设定值和效能统计数据。
2、用户可在程序中用全局变量来测试系统的设定值或 Transact-SQL 命令执行后的状态值。
3、全局变量不是由用户的程序定义的，它们是在服务器级定义的，只能使用预先说明及定义的全局变量。
4,SQL Server一共提供了 30多个全局变量。
5、引用全局变量时必须以,@@”开头。
6、局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。
92009-7-28 Information College · ChangJun
常用全局变量：
1,@@ERROR,返回执行上一条 Transact-
SQL语句所返回的错误号。返回 0，表示成功。返回非 0值，表示错误的相应编号。
2,@@FATCH_STATUS：返回执行上一次使用游标 FETCH操作所返回的状态值。返回 0，表示成功。返回 -1，表示操作失败或超出了游标所能操作的数据行的范围。返回 -2，表示返回的值已经丢失。
3,@@ROWCOUNT,返回执行上一条
Transact-SQL语句所影响到的数据行数目。
102009-7-28 Information College · ChangJun
8.3 函数
8.3.1 内置函数（ Built-in）
1、数学函数
a) 算术函数可对数据类型为整型、浮点型、
实型、货币型和 SMALLMONEY 的列进行操作。
b) 它的返回值是 6 位小数，如果使用出错，
则返回 NULL值,并显示警告信息。
c) 可以在 SELECT 语句的 SELECT 和 WHERE
子句以及表达式中使用算术函数。
d) 教材附录 A P235-P236
112009-7-28 Information College · ChangJun
2、字符串函数
a) 字符串函数对二进制数据、字符串和表达式执行不同的运算。
b) 此类函数作用于 CHAR,VARCHAR,BINARY
和 VARBINARY 数据类型以及可以隐式转换为 CHAR
或 VARCHAR的数据类型。
c) 可以在 SELECT语句的 SELECT 和 WHERE 子句以及表达式中使用字符串函数。
d)教材附录 A P236-P241
122009-7-28 Information College · ChangJun
3、日期函数
a) 日期函数用来操作 DATETIME 和 SMALLDATETIME
类型的数据，执行算术运算。
b) 与其它函数一样，可以在 SELECT 语句的
SELECT 和 WHERE 子句以及表达式中使用日期函数。
c)教材附录 A P231-P235
132009-7-28 Information College · ChangJun
4、其他常用函数
Convert函数教材附录 A P230-P242
142009-7-28 Information College · ChangJun
8.3.2 用户定义函数（ user -defined）
在 SQL Server 2000 中用户定义函数是作为一个数据库对象来管理的。
创建用户定义函数，它是返回值的已保存的
Transact-SQL 例程。
可以传递 0个或多个参数。
根据返回的数据类型可分为：
a) 数量型（标量）
返回一个简单的数值型数据。函数体被封装在 BEGIN … AND 之间。
b) 直接表值型（内嵌表值）
返回一个 table型数据，结果只是一个 SELECT
语句所返回的一系列表值，没有明确的函数体。
152009-7-28 Information College · ChangJun
c) 多语句表值型（多语句表值）
返回一个 table型数据，函数体被封装在
BEGIN … AND 之间。
用户定义函数不能用于执行一组修改全局数据库状态的操作。
用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。
162009-7-28 Information College · ChangJun
1、使用企业管理器
a) 创建
[数据库 ]——[用户自定义函数 ]—右键 —[新建 ]
b) 修改
[数据库 ]——[用户自定义函数 ]——[函数 ]—右键 —[属性 ]
c) 删除
[数据库 ]——[用户自定义函数 ]——[函数 ]—右键 —[删除 ]
172009-7-28 Information College · ChangJun
2、使用 Transact-SQL 命令
a) 创建标量函数,
CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS]
scalar_parameter_data_type [ = default ] }
[,...n ] ] )
RETURNS scalar_return_data_type
[ WITH < function_option> [ [,],..n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
182009-7-28 Information College · ChangJun
例：
用户定义函数 Multi,计算任意两个数的乘积。
CREATE FUNCTION Multi (@a real,@b real)
RETURNS real
AS
BEGIN
DECLARE @Multi real
SET @Multi=@a*@b
RETURN (@Multi)
END
192009-7-28 Information College · ChangJun
内嵌表值函数
CREATE FUNCTION [ owner_name.]
function_name
( [ { @parameter_name [AS]
scalar_parameter_data_type [ = default ] }
[,...n ] ] )
RETURNS TABLE
[ WITH < function_option > [ [,],..n ] ]
[ AS ]
RETURN [ ( ) select-stmt [ ] ]
202009-7-28 Information College · ChangJun
例：返回内嵌表值函数。
USE school
GO
CREATE FUNCTION SearchGrade (@sid
varchar(10))
RETURNS TABLE
AS
RETURN (SELECT cname,grade
FROM c,sc
WHERE c.cno = sc.cno
and sno=@sid)
212009-7-28 Information College · ChangJun
多语句表值函数
CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS]
scalar_parameter_data_type [ = default ] }
[,...n ] ] )
RETURNS @return_variable TABLE <
table_type_definition >
[ WITH < function_option > [ [,],..n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
< function_option >,:=
{ ENCRYPTION | SCHEMABINDING }
< table_type_definition >,:=
( { column_definition | table_constraint }
[,...n ] )
222009-7-28 Information College · ChangJun
8.4 流程控制语句
Transact-SQL 语言使用的流程控制命令与常见的程序设计语言类似，主要有以下几种控制命令。
8.4.1 BEGIN… END
BEGIN… END 用来设定一个程序块，将在
BEGIN… END 内的所有程序视为一个单元执行。
其语法如下：
BEGIN
<命令行或程序块块 >
END
BEGIN… END 经常在条件语句（如 IF… ELSE）中使用。
在 BEGIN… END 中可嵌套另外的 BEGIN… END 来定义另一程序块。
232009-7-28 Information College · ChangJun
8.4.2 IF … ELSE
IF… ELSE 用来判断当某一条件成立时执行某段程序，
条件不成立时执行另一段程序。其语法如下：
IF <条件表达式式 >
<命令行或程序块块 >
[ELSE [条件表达式式 ]
<命令行或程序块块 >]
其中,<条件表达式 >可以是各种表达式的组合，但表达式的值必须是逻辑值,真,或,假,。
ELSE 子句是可选的，最简单的 IF 语句没有 ELSE 子句部分。
如果不使用程序块,IF 或 ELSE 只能执行一条命令。
IF ELSE 可以进行嵌套，在 Transact-SQL 中最多可嵌套 32 级。
242009-7-28 Information College · ChangJun
例,从 SC数据表中求出学号为,9521102” 同学的平均成绩，如果此平均成绩大于或等于 60分，
则输出,pass”信息。
if (select avg(grade) from sc where
sno='9521102 ' group by sno)>=60
begin
print 'pass'
end
252009-7-28 Information College · ChangJun
8.4.3 CASE表达式
CASE 命令有两种语句格式：
1、格式 1：
CASE <运算式 >
WHEN <运算式 > THEN <运算式 >
…
WHEN <运算式 > THEN <运算式 >
[ELSE <运算式 >]
END
该语句的执行过程是：
将 CASE后面表达式的值与各 WHEN子句中的表达式的值进行比较，如果二者相等，则返回 THEN后的表达式的值，然后跳出 CASE语句，否则返回 ELSE子句中的表达式的值。
ELSE子句是可选项。当 CASE语句中不包含 ELSE子句时，如果所有比较失败时,CASE语句将返回 NULL。
262009-7-28 Information College · ChangJun
例：从学生表 S中，选取 SNO,SEX，如果 SEX为
,男,则输出,M”，如果为,女,输出,F”。
SELECT SNO,
SEX=
CASE sex
WHEN '男 ' THEN 'M'
WHEN '女 ' THEN 'F'
END
FROM S
272009-7-28 Information College · ChangJun
2、格式 2：
CASE
WHEN <条件表达式 > THEN <运算式 >
…
WHEN <条件表达式 > THEN <运算式 >
[ELSE <运算式 >]
END
该语句的执行过程是：
首先测试 WHEN后的表达式的值，如果其值为真，则返回 THEN后面的表达式的值，否则测试下一个 WHEN子句中的表达式的值，如果所有 WHEN子句后的表达式的值都为假，则返回 ELSE后的表达式的值，如果在 CASE语句中没有 ELSE子句，则 CASE表达式返回 NULL。
注,CASE 命令可以嵌套到 SQL 命令中。
282009-7-28 Information College · ChangJun
例：从 SC表中查询所有同学选课成绩情况，凡成绩为空者输出,未考,,小于 60分输出,不及格,,60分至 70分输出,及格,,70分至 90分输出,良好,,大于或等于 90分时输出,优秀,。
SELECT SNO,CNO,
SCORE=
CASE
WHEN grade IS NULL THEN?未考 ’
WHEN grade<60 THEN?不及格 ’
WHEN grade>=60 AND grade<70 THEN?及格 ’
WHEN grade>=70 AND grade<90 THEN?良好 ’
WHEN grade>=90 THEN?优秀 ’
END
FROM SC
292009-7-28 Information College · ChangJun
8.4.4 WHILE… CONTINUE… BREAK
WHILE 命令在设定的条件成立时，会重复执行命令行或程序块。其语法如下：
WHILE <条件表达式 >
BEGIN
<命令行或程序块 >
[BREAK]
[CONTINUE]
[命令行或程序块 ]
END
1） CONTINUE 命令可以让程序跳过 CONTINUE 命令之后的语句，回到 WHILE 循环的第一行，继续进行下一次循环。
2） BREAK 命令则让程序完全跳出循环，结束 WHILE 命令的执行。
3） WHILE 语句也可以嵌套。
302009-7-28 Information College · ChangJun
例：以下程序计算 1－ 100之间所有能被 3整除的数的个数及总和。
DECLARE @S SMALLINT,@I SMALLINT,@NUMS SMALLINT
SET @S=0
SET @I=1
SET @NUMS=0
WHILE (@I<=100)
BEGIN
IF (@I%3=0)
BEGIN
SET @S=@S+@I
SET @NUMS=@NUMS+1
END
SET @I=@I+1
END
PRINT @S
PRINT @NUMS
312009-7-28 Information College · ChangJun
8.4.5 其它语句
1,BACKUP,用于将数据库内容或其事务处理日志备份到存储介质上（软盘、硬盘、磁带等）。
2,CHECKPOINT,用于将当前工作的数据库中被更改过的数据页或日志页从数据缓冲器中强制写入硬盘。
3,DBCC,用于验证数据库完整性、查找错误、分析系统使用情况等。
4,DECLARE,用于声明一个或多个局部变量、游标变量或表变量。声明之后，所有的变量都被赋予初值 NULL。
5,EXECUTE,用来执行存储过程。
6,KILL,用于终止某一过程的执行。
7,PRINT,向客户端返回一个用户自定义的信息，即显示一个字符串、局部变量或全局变量。
322009-7-28 Information College · ChangJun
8,RAISERROR,用于在 SQL Server 系统返回错误信息时，
同时返回用户指定的信息。
9,READTEXT,用于从数据类型为 TEXT,NTEXT 或 IMAGE
的列中读取数据。
10,RESTORE,用来将数据库或其事务处理日志备份文件由存储介质回存到 SQL Server系统中。
11,SELECT,可用于给变量赋值，语法如下：
12,SET：用于给局部变量赋值或设定用户执行 SQL 命令时,SQL Server 的处理选项设定。
13,SHUTDOWN：用于停止 SQL Server 的执行。
14,WRITETEXT,用于向数据类型为 TEXT,NTEXT 或 IMAGE
的列中写入数据。
15,USE,用于改变当前使用的数据库为指定的数据库。
332009-7-28 Information College · ChangJun
8.5 游标（ Cursor）
8.5.1 基本概念
1、什么是游标关系数据库中的操作会对整个行集产生影响。
由 SELECT 语句返回的行集包括所有满足该语句
WHERE 子句中条件的行。由语句所返回的这一完整的行集被称为结果集。
应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。
这些应用程序需要一种机制以便每次处理一行或一部分行。
游标就是提供这种机制的结果集扩展。
342009-7-28 Information College · ChangJun
2,SQL Server 2000 支持三种游标：
1） Transact-SQL游标（服务器端）
基于 DECLARE CURSOR 语法，主要用在 Transact-SQL
脚本、存储过程和触发器中。
Transac-SQL游标在服务器上实现并由从客户端发送到服务器的 Transact-SQL 语句管理。
它们还包含在批处理、存储过程或触发器中。
2）应用编程接口（ API）服务器游标（服务器端）
支持 OLE DB,ODBC 和 DB-Library 中的 API 游标函数。
API 服务器游标在服务器上实现。每次客户应用程序调用 API游标函数时,SQL Server OLE DB 提供程序、
ODBC 驱动程序或 DB-Library 动态链接库（ DLL）就把请求传送到服务器，以便对 API 服务器游标进行操作。
352009-7-28 Information College · ChangJun
3）客户端游标由 SQL Server ODBC 驱动程序,DB-
Library DLL 和实现 ADO API 的 DLL 在内部实现。
客户端游标通过在客户端高速缓存所有结果集行来实现。每次客户应用程序调用 API 游标函数时,SQL Server ODBC 驱动程序,DB-
Library DLL 或 ADO DLL 就对高速缓存在客户端中的结果集行执行游标操作。
362009-7-28 Information College · ChangJun
8.5.2 声明游标（ DECLARE）
SQL-92 语法：
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ]
CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name
[,...n ] ] } ]
例：声明一个包括 school数据库的 s表中的所有行和列的游标，可以更新该游标的所有列。因为没指定 SCROLL 选项,FETCH NEXT 是唯一可用的提取选项。
DECLARE s_cursor CURSOR
FOR SELECT * FROM s
372009-7-28 Information College · ChangJun
8.5.3 打开游标（ OPEN）
在使用游标前，必须先打开服务器执行声明游标中使用的 SELECT语句。
语法：
OPEN { { cursor_name } |
cursor_variable_name }
例：
OPEN s_cursor
382009-7-28 Information College · ChangJun
8.5.4 从游标提取数据（ FETCH）
从 Transact-SQL 服务器游标中检索特定的一行。语法：
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } |
@cursor_variable_name }
[ INTO @variable_name [,...n ] ]
392009-7-28 Information College · ChangJun
例：在游标 s_cursor中使用 FETCH逐个提取这些行。
1,FETCH 语句以单行结果集形式返回由 DECLARE
CURSOR 指定的列的值。
FETCH NEXT FROM s_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM s_cursor
END
402009-7-28 Information College · ChangJun
2、使用 FETCH 将值存入变量下例与上例相似，但 FETCH 语句的输出存储于局部变量而不是直接返回给客户端。
PRINT 语句将变量组合成单一字符串并将其返回到客户端。
USE school
GO
DECLARE @sid varchar(10),@name
varchar(20),
@sex varchar(2),@age int,
@dept varchar(20)
412009-7-28 Information College · ChangJun
OPEN s_cursor
-- Perform the first fetch and store the values
in variables.
FETCH NEXT FROM s_cursor
INTO @sid,@name,@sex,@age,@dept
-- Check @@FETCH_STATUS to see if there are any
more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values
in the variables.
PRINT @name +,的学号是,+ @sid +
“，今年,+ @age +,岁，在,+@dept+
,xuexi 学习。,
FETCH NEXT FROM s_cursor
INTO @sid,@name,@sex,@age,@dept
END
422009-7-28 Information College · ChangJun
3、声明 SCROLL 游标并使用其它
FETCH 选项创建一个 SCROLL 游标，使其通过 LAST,PRIOR,RELATIVE 和
ABSOLUTE 选项支持所有滚动能力。
USE school
GO
--Declare the cursor.
DECLARE s1_cursor SCROLL CURSOR FOR
SELECT sname,sdept FROM s
ORDER BY sname,sdept
432009-7-28 Information College · ChangJun
OPEN s1_cursor
-- Fetch the last row in the cursor.
FETCH LAST FROM s1_cursor
-- Fetch the row immediately prior to the current
row in the cursor.
FETCH PRIOR FROM s1_cursor
-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM s1_cursor
-- Fetch the row that is three rows after the
current row.
FETCH RELATIVE 3 FROM s1_cursor
-- Fetch the row that is two rows prior to the
current row.
FETCH RELATIVE -2 FROM s1_cursor
442009-7-28 Information College · ChangJun
8.5.5 关闭游标（ CLOSE）
通过释放当前结果集并且解除定位游标的行上的游标锁定，关闭一个开放的游标。
CLOSE 使得数据结构可以重新打开，但不允许提取和定位更新，直到游标重新打开为止。
CLOSE 必须在一个开放游标上颁发，不允许在一个仅仅声明的游标或一个已经关闭的游标上颁发。
语法,
CLOSE { { [ GLOBAL ] cursor_name } |
cursor_variable_name }
例：
CLOSE s_cursor
452009-7-28 Information College · ChangJun
8.5.6 释放游标（ DEALLOCATE）
删除游标引用。当释放最后的游标引用时，
组成该游标的数据结构由 SQL Server释放。
语法,
DEALLOCATE { { [ GLOBAL ] cursor_name } |
@cursor_variable_name }
例：
DEALLOCATE s_cursor
462009-7-28 Information College · ChangJun
8.6 事务（ TRANSACTION）
8.6.1 基本概念
1、什么是事务事务是 SQL Server中的单个逻辑工作单元，一个事务内的所有语句被作为整体执行。遇到错误时，可以回滚事务，取消事务内所作的所有改变，从而保证数据库的一致性和可恢复性。
一个事务逻辑工作单元必须有四个属性，称为 ACID
（原子性、一致性、隔离性和持久性）属性：
1） 原子性（ Atomicity）
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
472009-7-28 Information College · ChangJun
2） 一致性 (Consistency)
事务在完成时，必须使所有的数据都保持一致状态。
在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。
3） 隔离性 (Isolation)
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
4） 持久性 (Durability))
事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。
482009-7-28 Information College · ChangJun
2、批与事务
a) 批是一组被 整体编译 的 Transact-SQL语句，
而事务是一组被作为 单个逻辑工作单元 执行的
Transact-SQL语句。
b) 批语句的的组合发生在 编译时刻,而事务中语句的组合发生在 执行时刻 。
c) 当在编译时，如果批中某语句存在 语法错误 时,SQL Server将取消整个批中所有语句的执行。
d) 在运行时，如果批内产生一个 运行时刻错误,默认时,SQL Server只回滚产生该错误的语句。而如果事务中某个数据修改操作违反约束、
规则、触发器等条件时,SQL Server将回滚整个事务。
492009-7-28 Information College · ChangJun
8.6.2 管理事务
SQL Server的事务模式分为：
l 显式事务
l 隐式事务
l 自动事务
1、显式事务只由用户执行 Transact-SQL事务语句显式地定义事务的启动和结束，也称为用户定义事务。
用于定义显式事务的 Transact-SQL 语句包括：
1) BEGIN TRANSACTION,启动事务为连接标记显式事务的起始点。
2) COMMIT TRANSACTION,提交事务如果没有遇到错误，可使用该语句成功地结束事务。
该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。
502009-7-28 Information College · ChangJun
3) ROLLBACK TRANSACTION,回滚事务用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。
4) SAVE TRANSACTION,设置保存点例：建立一个显式事务并进行事务的回滚操作和事务中的存储点回滚操作。
Use school
Begin transaction demo
Select * from c
Insert c values(?c00?,?ABC?,3,3)
Save transaction save_demo
Insert c values(?c11?,?123?,2,2)
Select * from c
Rollback transaction save_demo
Select * from c
Rollback transaction
第 8章 SQL Server 的编程结构
22009-7-28 Information College · ChangJun
[本章概要 ]
8.1 基本概念
8.2 变量
8.3 函数
8.4 流程控制语句
8.5 游标
8.6 事务
32009-7-28 Information College · ChangJun
8.1 编程结构基本概念
8.1.1 注释语句
l,/*” 和,*/”括起来表示可进行部分、
单行或多行语句的注释。
l,--”表示只可进行单行语句的注释。
l 养成良好的书写注释的习惯
42009-7-28 Information College · ChangJun
8.1.2 脚本和批处理
1、脚本 (script)
是存储在文件中用于执行某项操作的一系列
Transact-SQL 语句集合。
Transact-SQL脚本文件通常带有,sql 扩展名。
Transact-SQL脚本可以用于：
a) 保存用于创建和填充服务器上的数据库的操作的永久复本（备份机制）。
b) 必要时将语句从一台计算机转移到另一台计算机。
c) 通过让新雇员发现代码中的问题、了解代码或更改代码从而快速对其进行培训。
52009-7-28 Information College · ChangJun
2、批处理 (batch)
批处理是客户端作为一个单元发出的一个或多个 SQL 语句的集合。它们作为一个组一起提交给服务器并加以执行。
服务器将每个批处理编译为一个执行计划。
Transact-SQL脚本包含一个或多个批处理。 GO
命令作为批处理结束的标志。如果 Transact-SQL
脚本没有 GO 命令，则将它作为单个批处理执行。
批处理的方法：
a) Create Table,Create View只能单独放在一个批中。
b) 使用事务来保证批处理
c) 包含存储过程的批处理，除第一个可以不用 EXEC来执行，其余都必须用。
62009-7-28 Information College · ChangJun
8.2 变量
Transact-SQL 中可以使用两种变量：
局部变量和全局变量。
8.2.1 局部变量局部变量是用户可自定义的变量，它的作用范围仅在一个批内。特点：
1、在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。
2、局部变量必须以 @开头，而且必须先用
DECLARE 命令说明。其说明形式如下：
DECLARE @变量名 变量类型 [,...n]
3、必须使用 SELECT或 SET 命令来设定变量的值。
72009-7-28 Information College · ChangJun
其语法如下：
SELECT @局部变量 = 变量值
（或） SET @局部变量量 = 变量值例如,
声明一个长度为 8 个字符的变量 id，
并赋值。
declare @id char（ 8）
select @id =?10010001?
或 set @id =?10010001?
82009-7-28 Information College · ChangJun
8.2.2 全局变量全局变量是 SQL Server 系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。
特点：
1、全局变量通常存储一些 SQL Server 的配置设定值和效能统计数据。
2、用户可在程序中用全局变量来测试系统的设定值或 Transact-SQL 命令执行后的状态值。
3、全局变量不是由用户的程序定义的，它们是在服务器级定义的，只能使用预先说明及定义的全局变量。
4,SQL Server一共提供了 30多个全局变量。
5、引用全局变量时必须以,@@”开头。
6、局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。
92009-7-28 Information College · ChangJun
常用全局变量：
1,@@ERROR,返回执行上一条 Transact-
SQL语句所返回的错误号。返回 0，表示成功。返回非 0值，表示错误的相应编号。
2,@@FATCH_STATUS：返回执行上一次使用游标 FETCH操作所返回的状态值。返回 0，表示成功。返回 -1，表示操作失败或超出了游标所能操作的数据行的范围。返回 -2，表示返回的值已经丢失。
3,@@ROWCOUNT,返回执行上一条
Transact-SQL语句所影响到的数据行数目。
102009-7-28 Information College · ChangJun
8.3 函数
8.3.1 内置函数（ Built-in）
1、数学函数
a) 算术函数可对数据类型为整型、浮点型、
实型、货币型和 SMALLMONEY 的列进行操作。
b) 它的返回值是 6 位小数，如果使用出错，
则返回 NULL值,并显示警告信息。
c) 可以在 SELECT 语句的 SELECT 和 WHERE
子句以及表达式中使用算术函数。
d) 教材附录 A P235-P236
112009-7-28 Information College · ChangJun
2、字符串函数
a) 字符串函数对二进制数据、字符串和表达式执行不同的运算。
b) 此类函数作用于 CHAR,VARCHAR,BINARY
和 VARBINARY 数据类型以及可以隐式转换为 CHAR
或 VARCHAR的数据类型。
c) 可以在 SELECT语句的 SELECT 和 WHERE 子句以及表达式中使用字符串函数。
d)教材附录 A P236-P241
122009-7-28 Information College · ChangJun
3、日期函数
a) 日期函数用来操作 DATETIME 和 SMALLDATETIME
类型的数据，执行算术运算。
b) 与其它函数一样，可以在 SELECT 语句的
SELECT 和 WHERE 子句以及表达式中使用日期函数。
c)教材附录 A P231-P235
132009-7-28 Information College · ChangJun
4、其他常用函数
Convert函数教材附录 A P230-P242
142009-7-28 Information College · ChangJun
8.3.2 用户定义函数（ user -defined）
在 SQL Server 2000 中用户定义函数是作为一个数据库对象来管理的。
创建用户定义函数，它是返回值的已保存的
Transact-SQL 例程。
可以传递 0个或多个参数。
根据返回的数据类型可分为：
a) 数量型（标量）
返回一个简单的数值型数据。函数体被封装在 BEGIN … AND 之间。
b) 直接表值型（内嵌表值）
返回一个 table型数据，结果只是一个 SELECT
语句所返回的一系列表值，没有明确的函数体。
152009-7-28 Information College · ChangJun
c) 多语句表值型（多语句表值）
返回一个 table型数据，函数体被封装在
BEGIN … AND 之间。
用户定义函数不能用于执行一组修改全局数据库状态的操作。
用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。
162009-7-28 Information College · ChangJun
1、使用企业管理器
a) 创建
[数据库 ]——[用户自定义函数 ]—右键 —[新建 ]
b) 修改
[数据库 ]——[用户自定义函数 ]——[函数 ]—右键 —[属性 ]
c) 删除
[数据库 ]——[用户自定义函数 ]——[函数 ]—右键 —[删除 ]
172009-7-28 Information College · ChangJun
2、使用 Transact-SQL 命令
a) 创建标量函数,
CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS]
scalar_parameter_data_type [ = default ] }
[,...n ] ] )
RETURNS scalar_return_data_type
[ WITH < function_option> [ [,],..n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
182009-7-28 Information College · ChangJun
例：
用户定义函数 Multi,计算任意两个数的乘积。
CREATE FUNCTION Multi (@a real,@b real)
RETURNS real
AS
BEGIN
DECLARE @Multi real
SET @Multi=@a*@b
RETURN (@Multi)
END
192009-7-28 Information College · ChangJun
内嵌表值函数
CREATE FUNCTION [ owner_name.]
function_name
( [ { @parameter_name [AS]
scalar_parameter_data_type [ = default ] }
[,...n ] ] )
RETURNS TABLE
[ WITH < function_option > [ [,],..n ] ]
[ AS ]
RETURN [ ( ) select-stmt [ ] ]
202009-7-28 Information College · ChangJun
例：返回内嵌表值函数。
USE school
GO
CREATE FUNCTION SearchGrade (@sid
varchar(10))
RETURNS TABLE
AS
RETURN (SELECT cname,grade
FROM c,sc
WHERE c.cno = sc.cno
and sno=@sid)
212009-7-28 Information College · ChangJun
多语句表值函数
CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS]
scalar_parameter_data_type [ = default ] }
[,...n ] ] )
RETURNS @return_variable TABLE <
table_type_definition >
[ WITH < function_option > [ [,],..n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
< function_option >,:=
{ ENCRYPTION | SCHEMABINDING }
< table_type_definition >,:=
( { column_definition | table_constraint }
[,...n ] )
222009-7-28 Information College · ChangJun
8.4 流程控制语句
Transact-SQL 语言使用的流程控制命令与常见的程序设计语言类似，主要有以下几种控制命令。
8.4.1 BEGIN… END
BEGIN… END 用来设定一个程序块，将在
BEGIN… END 内的所有程序视为一个单元执行。
其语法如下：
BEGIN
<命令行或程序块块 >
END
BEGIN… END 经常在条件语句（如 IF… ELSE）中使用。
在 BEGIN… END 中可嵌套另外的 BEGIN… END 来定义另一程序块。
232009-7-28 Information College · ChangJun
8.4.2 IF … ELSE
IF… ELSE 用来判断当某一条件成立时执行某段程序，
条件不成立时执行另一段程序。其语法如下：
IF <条件表达式式 >
<命令行或程序块块 >
[ELSE [条件表达式式 ]
<命令行或程序块块 >]
其中,<条件表达式 >可以是各种表达式的组合，但表达式的值必须是逻辑值,真,或,假,。
ELSE 子句是可选的，最简单的 IF 语句没有 ELSE 子句部分。
如果不使用程序块,IF 或 ELSE 只能执行一条命令。
IF ELSE 可以进行嵌套，在 Transact-SQL 中最多可嵌套 32 级。
242009-7-28 Information College · ChangJun
例,从 SC数据表中求出学号为,9521102” 同学的平均成绩，如果此平均成绩大于或等于 60分，
则输出,pass”信息。
if (select avg(grade) from sc where
sno='9521102 ' group by sno)>=60
begin
print 'pass'
end
252009-7-28 Information College · ChangJun
8.4.3 CASE表达式
CASE 命令有两种语句格式：
1、格式 1：
CASE <运算式 >
WHEN <运算式 > THEN <运算式 >
…
WHEN <运算式 > THEN <运算式 >
[ELSE <运算式 >]
END
该语句的执行过程是：
将 CASE后面表达式的值与各 WHEN子句中的表达式的值进行比较，如果二者相等，则返回 THEN后的表达式的值，然后跳出 CASE语句，否则返回 ELSE子句中的表达式的值。
ELSE子句是可选项。当 CASE语句中不包含 ELSE子句时，如果所有比较失败时,CASE语句将返回 NULL。
262009-7-28 Information College · ChangJun
例：从学生表 S中，选取 SNO,SEX，如果 SEX为
,男,则输出,M”，如果为,女,输出,F”。
SELECT SNO,
SEX=
CASE sex
WHEN '男 ' THEN 'M'
WHEN '女 ' THEN 'F'
END
FROM S
272009-7-28 Information College · ChangJun
2、格式 2：
CASE
WHEN <条件表达式 > THEN <运算式 >
…
WHEN <条件表达式 > THEN <运算式 >
[ELSE <运算式 >]
END
该语句的执行过程是：
首先测试 WHEN后的表达式的值，如果其值为真，则返回 THEN后面的表达式的值，否则测试下一个 WHEN子句中的表达式的值，如果所有 WHEN子句后的表达式的值都为假，则返回 ELSE后的表达式的值，如果在 CASE语句中没有 ELSE子句，则 CASE表达式返回 NULL。
注,CASE 命令可以嵌套到 SQL 命令中。
282009-7-28 Information College · ChangJun
例：从 SC表中查询所有同学选课成绩情况，凡成绩为空者输出,未考,,小于 60分输出,不及格,,60分至 70分输出,及格,,70分至 90分输出,良好,,大于或等于 90分时输出,优秀,。
SELECT SNO,CNO,
SCORE=
CASE
WHEN grade IS NULL THEN?未考 ’
WHEN grade<60 THEN?不及格 ’
WHEN grade>=60 AND grade<70 THEN?及格 ’
WHEN grade>=70 AND grade<90 THEN?良好 ’
WHEN grade>=90 THEN?优秀 ’
END
FROM SC
292009-7-28 Information College · ChangJun
8.4.4 WHILE… CONTINUE… BREAK
WHILE 命令在设定的条件成立时，会重复执行命令行或程序块。其语法如下：
WHILE <条件表达式 >
BEGIN
<命令行或程序块 >
[BREAK]
[CONTINUE]
[命令行或程序块 ]
END
1） CONTINUE 命令可以让程序跳过 CONTINUE 命令之后的语句，回到 WHILE 循环的第一行，继续进行下一次循环。
2） BREAK 命令则让程序完全跳出循环，结束 WHILE 命令的执行。
3） WHILE 语句也可以嵌套。
302009-7-28 Information College · ChangJun
例：以下程序计算 1－ 100之间所有能被 3整除的数的个数及总和。
DECLARE @S SMALLINT,@I SMALLINT,@NUMS SMALLINT
SET @S=0
SET @I=1
SET @NUMS=0
WHILE (@I<=100)
BEGIN
IF (@I%3=0)
BEGIN
SET @S=@S+@I
SET @NUMS=@NUMS+1
END
SET @I=@I+1
END
PRINT @S
PRINT @NUMS
312009-7-28 Information College · ChangJun
8.4.5 其它语句
1,BACKUP,用于将数据库内容或其事务处理日志备份到存储介质上（软盘、硬盘、磁带等）。
2,CHECKPOINT,用于将当前工作的数据库中被更改过的数据页或日志页从数据缓冲器中强制写入硬盘。
3,DBCC,用于验证数据库完整性、查找错误、分析系统使用情况等。
4,DECLARE,用于声明一个或多个局部变量、游标变量或表变量。声明之后，所有的变量都被赋予初值 NULL。
5,EXECUTE,用来执行存储过程。
6,KILL,用于终止某一过程的执行。
7,PRINT,向客户端返回一个用户自定义的信息，即显示一个字符串、局部变量或全局变量。
322009-7-28 Information College · ChangJun
8,RAISERROR,用于在 SQL Server 系统返回错误信息时，
同时返回用户指定的信息。
9,READTEXT,用于从数据类型为 TEXT,NTEXT 或 IMAGE
的列中读取数据。
10,RESTORE,用来将数据库或其事务处理日志备份文件由存储介质回存到 SQL Server系统中。
11,SELECT,可用于给变量赋值，语法如下：
12,SET：用于给局部变量赋值或设定用户执行 SQL 命令时,SQL Server 的处理选项设定。
13,SHUTDOWN：用于停止 SQL Server 的执行。
14,WRITETEXT,用于向数据类型为 TEXT,NTEXT 或 IMAGE
的列中写入数据。
15,USE,用于改变当前使用的数据库为指定的数据库。
332009-7-28 Information College · ChangJun
8.5 游标（ Cursor）
8.5.1 基本概念
1、什么是游标关系数据库中的操作会对整个行集产生影响。
由 SELECT 语句返回的行集包括所有满足该语句
WHERE 子句中条件的行。由语句所返回的这一完整的行集被称为结果集。
应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。
这些应用程序需要一种机制以便每次处理一行或一部分行。
游标就是提供这种机制的结果集扩展。
342009-7-28 Information College · ChangJun
2,SQL Server 2000 支持三种游标：
1） Transact-SQL游标（服务器端）
基于 DECLARE CURSOR 语法，主要用在 Transact-SQL
脚本、存储过程和触发器中。
Transac-SQL游标在服务器上实现并由从客户端发送到服务器的 Transact-SQL 语句管理。
它们还包含在批处理、存储过程或触发器中。
2）应用编程接口（ API）服务器游标（服务器端）
支持 OLE DB,ODBC 和 DB-Library 中的 API 游标函数。
API 服务器游标在服务器上实现。每次客户应用程序调用 API游标函数时,SQL Server OLE DB 提供程序、
ODBC 驱动程序或 DB-Library 动态链接库（ DLL）就把请求传送到服务器，以便对 API 服务器游标进行操作。
352009-7-28 Information College · ChangJun
3）客户端游标由 SQL Server ODBC 驱动程序,DB-
Library DLL 和实现 ADO API 的 DLL 在内部实现。
客户端游标通过在客户端高速缓存所有结果集行来实现。每次客户应用程序调用 API 游标函数时,SQL Server ODBC 驱动程序,DB-
Library DLL 或 ADO DLL 就对高速缓存在客户端中的结果集行执行游标操作。
362009-7-28 Information College · ChangJun
8.5.2 声明游标（ DECLARE）
SQL-92 语法：
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ]
CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name
[,...n ] ] } ]
例：声明一个包括 school数据库的 s表中的所有行和列的游标，可以更新该游标的所有列。因为没指定 SCROLL 选项,FETCH NEXT 是唯一可用的提取选项。
DECLARE s_cursor CURSOR
FOR SELECT * FROM s
372009-7-28 Information College · ChangJun
8.5.3 打开游标（ OPEN）
在使用游标前，必须先打开服务器执行声明游标中使用的 SELECT语句。
语法：
OPEN { { cursor_name } |
cursor_variable_name }
例：
OPEN s_cursor
382009-7-28 Information College · ChangJun
8.5.4 从游标提取数据（ FETCH）
从 Transact-SQL 服务器游标中检索特定的一行。语法：
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } |
@cursor_variable_name }
[ INTO @variable_name [,...n ] ]
392009-7-28 Information College · ChangJun
例：在游标 s_cursor中使用 FETCH逐个提取这些行。
1,FETCH 语句以单行结果集形式返回由 DECLARE
CURSOR 指定的列的值。
FETCH NEXT FROM s_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM s_cursor
END
402009-7-28 Information College · ChangJun
2、使用 FETCH 将值存入变量下例与上例相似，但 FETCH 语句的输出存储于局部变量而不是直接返回给客户端。
PRINT 语句将变量组合成单一字符串并将其返回到客户端。
USE school
GO
DECLARE @sid varchar(10),@name
varchar(20),
@sex varchar(2),@age int,
@dept varchar(20)
412009-7-28 Information College · ChangJun
OPEN s_cursor
-- Perform the first fetch and store the values
in variables.
FETCH NEXT FROM s_cursor
INTO @sid,@name,@sex,@age,@dept
-- Check @@FETCH_STATUS to see if there are any
more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values
in the variables.
PRINT @name +,的学号是,+ @sid +
“，今年,+ @age +,岁，在,+@dept+
,xuexi 学习。,
FETCH NEXT FROM s_cursor
INTO @sid,@name,@sex,@age,@dept
END
422009-7-28 Information College · ChangJun
3、声明 SCROLL 游标并使用其它
FETCH 选项创建一个 SCROLL 游标，使其通过 LAST,PRIOR,RELATIVE 和
ABSOLUTE 选项支持所有滚动能力。
USE school
GO
--Declare the cursor.
DECLARE s1_cursor SCROLL CURSOR FOR
SELECT sname,sdept FROM s
ORDER BY sname,sdept
432009-7-28 Information College · ChangJun
OPEN s1_cursor
-- Fetch the last row in the cursor.
FETCH LAST FROM s1_cursor
-- Fetch the row immediately prior to the current
row in the cursor.
FETCH PRIOR FROM s1_cursor
-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM s1_cursor
-- Fetch the row that is three rows after the
current row.
FETCH RELATIVE 3 FROM s1_cursor
-- Fetch the row that is two rows prior to the
current row.
FETCH RELATIVE -2 FROM s1_cursor
442009-7-28 Information College · ChangJun
8.5.5 关闭游标（ CLOSE）
通过释放当前结果集并且解除定位游标的行上的游标锁定，关闭一个开放的游标。
CLOSE 使得数据结构可以重新打开，但不允许提取和定位更新，直到游标重新打开为止。
CLOSE 必须在一个开放游标上颁发，不允许在一个仅仅声明的游标或一个已经关闭的游标上颁发。
语法,
CLOSE { { [ GLOBAL ] cursor_name } |
cursor_variable_name }
例：
CLOSE s_cursor
452009-7-28 Information College · ChangJun
8.5.6 释放游标（ DEALLOCATE）
删除游标引用。当释放最后的游标引用时，
组成该游标的数据结构由 SQL Server释放。
语法,
DEALLOCATE { { [ GLOBAL ] cursor_name } |
@cursor_variable_name }
例：
DEALLOCATE s_cursor
462009-7-28 Information College · ChangJun
8.6 事务（ TRANSACTION）
8.6.1 基本概念
1、什么是事务事务是 SQL Server中的单个逻辑工作单元，一个事务内的所有语句被作为整体执行。遇到错误时，可以回滚事务，取消事务内所作的所有改变，从而保证数据库的一致性和可恢复性。
一个事务逻辑工作单元必须有四个属性，称为 ACID
（原子性、一致性、隔离性和持久性）属性：
1） 原子性（ Atomicity）
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
472009-7-28 Information College · ChangJun
2） 一致性 (Consistency)
事务在完成时，必须使所有的数据都保持一致状态。
在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。
3） 隔离性 (Isolation)
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
4） 持久性 (Durability))
事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。
482009-7-28 Information College · ChangJun
2、批与事务
a) 批是一组被 整体编译 的 Transact-SQL语句，
而事务是一组被作为 单个逻辑工作单元 执行的
Transact-SQL语句。
b) 批语句的的组合发生在 编译时刻,而事务中语句的组合发生在 执行时刻 。
c) 当在编译时，如果批中某语句存在 语法错误 时,SQL Server将取消整个批中所有语句的执行。
d) 在运行时，如果批内产生一个 运行时刻错误,默认时,SQL Server只回滚产生该错误的语句。而如果事务中某个数据修改操作违反约束、
规则、触发器等条件时,SQL Server将回滚整个事务。
492009-7-28 Information College · ChangJun
8.6.2 管理事务
SQL Server的事务模式分为：
l 显式事务
l 隐式事务
l 自动事务
1、显式事务只由用户执行 Transact-SQL事务语句显式地定义事务的启动和结束，也称为用户定义事务。
用于定义显式事务的 Transact-SQL 语句包括：
1) BEGIN TRANSACTION,启动事务为连接标记显式事务的起始点。
2) COMMIT TRANSACTION,提交事务如果没有遇到错误，可使用该语句成功地结束事务。
该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。
502009-7-28 Information College · ChangJun
3) ROLLBACK TRANSACTION,回滚事务用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。
4) SAVE TRANSACTION,设置保存点例：建立一个显式事务并进行事务的回滚操作和事务中的存储点回滚操作。
Use school
Begin transaction demo
Select * from c
Insert c values(?c00?,?ABC?,3,3)
Save transaction save_demo
Insert c values(?c11?,?123?,2,2)
Select * from c
Rollback transaction save_demo
Select * from c
Rollback transaction
