1
第四章 T-SQL程序设计基础
SQL Server数据类型
SQL Server 全局变量和局部变量
SQL Server 函数
T-SQL 控制流程
游标
2
系统数据类型
数据类型指定列、存储过程参数及局部
变量的数据特性
数据按照数据类型存储在列中
? 系统数据类型：系统数据类型是 SQL
Server 支持的内置数据类型,系统数据类
型有 25种。
? 用户定义的数据类型：用户根据系统数据类
型自己定义的数据类型
3
系统数据类型
语法 存储长度 适用范围 备注
字
符
型
Char（ n） N字节 固定长度的非
Unicode字符数据,
输入字符少于 n,以
空格填满 。 若超长
则截掉 。
N为 1-
8000范
围
Varchar
(n)
实际长度 N为最大长度小于 N
时不加空格
可节省空
间
Nchar(n) N单位 Unicode标准，两
个字节为存储单位，
容纳量增加了。
N为 1-
4000范
围 Nvarcha
r(n)
存储大小是
输入数据的
实际长度
4
系统数据类型
语法 存储长度 适用范围 备注
整
型
数
据
类
型
Int 4个字节 231~（ 231-1） 内所有正
负整数
是唯
一可
以与
iden
tiey
属性
一起
使用
的类
型
Smallin
t
2个字节 -215~（ 215-1） 内所有正
负整数
Tinyint 1个字节 0~255范围的所有正整数
5
系统数据类型
语法 存储长度 适用范围 备注
浮
点
数
据
类
型
Real 4个字节 精确到 7位小数 存储十
进制小
数。 Float 8个字节 最多可精确到 15位小数
Decimal 实际存储
空间
Decimal （ p,s）,p
表示总位数,s表示
小数点后的位数。
0<=s<=p<=38。
例如：
decimal(8,6) 则范
围 (
-99.999999
~99.999999）
Numeric
6
系统数据类型
语法 存储长度 适用范围 备注
货
币
型
Money 8个字节 用于存储货币 精确度为
万分之一
Samll
mone
y
4个字节 范围比 Money
小
精确度为
万分之一
位
型
Bit 1个字节 常用作逻辑变
量表示真假
只能输入
0与 1非此
值当为 1
7
系统数据类型
语法 存储长度 适用范围 备注
日
期
与
时
间
型
Dateti
me
8个字节
精度 三百
分之一秒
，即 3.33
毫秒。
MM DD YYYY
hh:mm
AM/PM
1753.1.1~99
99.12.31
23:59:59
存储日
期和时
间的结
合体,
引用时
用单引
号 Small
dateti
me
4个字节
精度 1分钟
1900.1.1~20
79.6.6
8
系统数据类型
语法 存储
长度
适用范围
备注
二
进
制
型
Binary
(n)
取决
于定
义
固定长度的二进
制数据
N为 1~8000，
最后用检索输出
的是二进制
Varbin
ary(n)
N+4
字节
二进制数据的长
度未知或变化较
大时可用
存放 8000字节内
可变长数据
9
系统数据类型
语法 存储长度 适用范围 备注
文
本
型
Text 实际大小 最大可存储
231-1
存储长度大于
8000个字节
的二进制数据
Ntext 实际大小 最大可存储
230-1
存储长度大于
4000个字符
的 unicode字
符串
图
形
image 实际大小 最大可存储 231
照片、图、画
存储长度大于
8000个字符
的字符串
10
系统数据类型
语法 存储长度 适用范围 备注
特
殊
型
Timestam
p
8个
字节
提供数据库范
围内的唯一值
单调上升
的计数器
Unique
identifi
er
16字
节
存储一个 16字节
长的二进制数
全局惟一
标识符
注：全局惟一标识符（ GUID）,由计算机网卡和
CPU时钟产生的，每台机器不会重复。 Newid()
函数可求出。
11
系统数据类型
语法 存储长度 适用范围
新
增
型
Bigin
t
8个字节 263~（ 263-1） 范围内的
所有正负数
Sql_v
arian
t
存储除文本、图形数据和
timestamp类型数据外的其他任何
合法的 SQL server数据。
table
用于存储对表或视图处理后的结果
集。可存储一个表。
12
系统数据类型
只有字符与二进制数据类型需要指定长度，
其他如整型、日期时间、浮点数据类型，定
义时不用指定长度，也就是长度是默认值。
自定义数据类型
建立在 SQL server系统数据类型基础上的。
需要指定该类型的名称、建立在其上的系统
数据类型及是否充许为空。
可以用如下方法来创建自定义数据类型。
13
自定义数据类型
利用系统存储过程,sp_addtype。 其语法为,
sp_addtype type,[system_data_type]
[,’nulltype’ ]
其中,type是用户定义数据类型的名称。数据类型
名称必须遵循标识符规则，并且在每个数据库中必
须是唯一的。
system_data_type是 SQL Server 提供的数据类型，
用户定义的数据类型即基于该类型。
‘null_type’, 指定必须如何处理 null 值。
null_type 是 varchar(8),设置值为 ‘ NULL’
（ 默认）,‘ NOT NULL’ 或 ‘ NONULL’
例,exec SP_addtype tele,smallint,'not
null‘
14
删除用户定义的数据类型
使用 sp_droptype 系统存储过程来撤销用户定
义数据类型。
例删除名为 tele的用户定义数据类型,
EXEC sp_droptype telephone
15
其它语言元素
批处理
注释
变量
运算符
函数
流程控制语句
16
批处理
批处理：指包含一条或多条 T-SQL语句
的语句组，这组语句从应用程序一次性
地发送到 SQL Server服务器执行。
执行单元,SQL Server服务器将批处理
语句编译成一个可执行单元，这种单元
称为执行单元。
若批处理中的某条语句编译出错，则无
法执行。若运行出错，则视情况而定。
17
书写批处理时,go语句作为批处理命令
的结束标志。当编译器读取到 go语句时，
会把 go语句前面所有的语句当作一个批
处理，并将这些语句打包发送给服务器。
注意,go语句本身不是 T-SQL语句的组
成部分，它只是一个用于表示批处理结
束的前端指令。
18
建立批处理时注意
Create default,Create Rule,Create
Trigger 和 Create view等语句在同一个批
处理中只能提交一个。
不能在删除一个对象之后，再同一批处理
中再次引用这个对象
不能把规则和默认值绑定到表字段或者自
定义字段上之后，立即在同一个批处理中
使用它们。
不能定义一个 check约束之后，立即在同一
个批处理中使用。
19
不能修改表中一个字段名之后，立即在
同一个批处理中引用这个新字段。
使用 set 语句设置的某些 set 选项不能应
用于同一个批处理中的查询
若批处理中第一个语句是执行某个存储
过程的 execute语句，则 execute关键
字可以省略。若该语句不是第一个语句，
则必须写上。
20
例如,
Use pubs
Go
Select * from 员工数据表
Go
Create view pm_view as
Select * from 员工数据库
where 所属部门 =‘项目部’
Go
Select * from pm_view
go
21
注释
注释是程序代码中不执行的文本字符串
（也称为注解）。在 SQL Server中，可以使用两
种类型的注释字符：一种是 ANSI标准的注释符
,--”,它用于单行注释；另一种是与 C语言相
同的程序注释符号，即, /* */” 。例如,
--检索部门的员工
/* 检索录入部
的员工 */
22
变量
变量是一种语言中必不可少的组成
部分 。 Transact-SQL语言中有两种形式
的变量, 一种是用户自己定义的局部变
量, 另外一种是系统提供的全局变量 。
23
全局变量
全局变量是 SQL Server系统内部使用的变
量，其作用范围并不仅仅局限于某一程序，
而是任何程序均可以随时调用。全局变量通
常存储一些 SQL Server的配置设定值和统计
数据。用户可以在程序中用全局变量来测试
系统的设定值或者是 Transact-SQL命令执行
后的状态值。
24
使用全局变量时注意
① 全局变量不是由用户的程序定义的, 它们是
在服务器级定义的 。
② 用户只能使用预先定义的全局变量 。
③ 引用全局变量时, 必须以标记符, @@” 开头 。
④ 局部变量的名称不能与全局变量的名称相同,
否则会在应用程序中出现不可预测的结果 。
25
@error全局变量将返回最后执行的 T-
SQL语句的错误代码，数据类型为整型。
在 SQL Server执行一个 T-SQL语句之后，
若成功，则返回值为 0；否则返回相应的
错误代码。
26
局部变量
局部变量是一个能够拥有特定数据类型的对象，它
的作用范围仅限制在程序内部。局部变量可以作为
计数器来计算循环执行的次数，或是控制循环执行
的次数。另外，利用局部变量还可以保存数据值，
以供控制流语句测试以及保存由存储过程返回的数
据值等。局部变量被引用时要在其名称前加上标志
,＠,,而且必须先用 DECLARE命令定义后才可以使
用。
27
声明局部变量
其声明形式如下,
DECLARE @变量名 变量类型 [, @变量
名 变量类型 … ]
其中变量类型可以是 SQL Server 2000 支
持的所有数据类型, 也可以是用户自定义
的数据类型
28
局部变量赋值
在 Transact-SQL 中,不能像在一般的程
序语言中一样使用变量 =变量值来给变量
赋值,必须使用 SELECT 或 SET 命令来
设定变量的值,其语法如下
SELECT @局部变量 = 变量值
或 SET @局部变量 = 变量值
29
例, 声明一个长度为 10 个字符的变量 id 并赋值
declare @id char 10
select @id = ’10010001’
例,declare @temp_counter int
Set @temp_counter = 0
例,declare @max_salary int
Select @max_salary= max(工资 )
From 员工数据表
注意：局部变量的作用范围是从声明该局部变量的
地方开始，到声明局部变量的批处理或存储过程
的结尾。在局部变量的作用范围以外引用该局部
变量将产生语法错误。
30
运算符
运算符是一些符号, 它们能够用来执行
算术运算, 字符串连接, 赋值以及在字段,
常量和变量之间进行比较 。 在 SQL Server
2000中, 运算符主要有以下六大类：算术
运算符, 赋值运算符, 位运算符, 比较运
算符, 逻辑运算符以及字符串串联运算符 。
31
1,算术运算符
算术运算符可以在两个表达式上执行
数学运算，这两个表达式可以是数字数
据类型分类的任何数据类型。算术运算
符包括加（ +）、减（ — ）、乘（ *）、
除（ /）和取模（ %）。
32
2,赋值运算符
Transact-SQL 中只有一个赋值运算符, 即等
号 （ =） 。 赋值运算符使我们能够将数据值指派
给特定的对象 。 另外, 还可以使用赋值运算符
在列标题和为列定义值的表达式之间建立关系 。
33
3,位运算符
位运算符使我们能够在整型数据或者二
进制数据（ image 数据类型除外）之间执
行位操作。
表 1 位运算符
运 算 符 含 义
&（ 按位 AND）
按位 AND（ 两个操作数 ） 。
|（ 按位 OR）
按位 OR（ 两个操作数 ） 。
^（ 按位异或 XOR）
按位异或 XOR（ 两个操作数 ） 。
34
4,比较运算符
比较运算符用于比较两个表达式的大小或是否
相同，其比较的结果是布尔值，即 TRUE（ 表示表
达式的结果为真）,FALSE（ 表示表达式的结果为
假）以及 UNKNOWN。 除了 text,ntext 或 image
数据类型的表达式外，比较运算符可以用于所有
的表达式。
35
5,逻辑运算符
逻辑运算符可以把多个逻辑表达式连接
起来 。 逻辑运算符包括 AND,OR和 NOT等运
算符 。 逻辑运算符和比较运算符一样, 返
回带有 TRUE 或 FALSE 值的布尔数据类
型 。
36
6,字符串串联运算符
字符串串联运算符允许通过加号 (+)
进行字符串串联, 这个加号即被称为字符
串串联运算符 。 例如对于语句 SELECT
‘abc’+’def’,其结果为 abcdef。
37
运算符的优先级从高到低排列
括号,（ ） ；
乘, 除, 求模运算符,*,/,%；
加减运算符,+,- ；
比较运算符,=,>,<,>=,<=,<>,!=,!>,!<；
位运算符,^,&,|；
逻辑运算符,NOT；
逻辑运算符,AND；
逻辑运算符,OR。
38
函数
在 Transact-SQL语言中, 函数被用来执行一
些特殊的运算以支持 SQL Server的标准命令 。
Transact-SQL 编程语言提供了三种函数,
㈠行集函数：行集函数可以在 Transact-SQL
语句中当作表引用 。
㈡聚合函数：聚合函数用于对一组值执行计
算并返回一个单一的值 。
㈢标量函数：标量函数用于对传递给它的一
个或者多个参数值进行处理和计算, 并返回
一个单一的值 。
39
标量函数的分类
配置函数：返回当前的配置信息
游标函数：返回有关游标的信息
日期和时间函数：用于对日期和时间类型的输入值进行操作，
返回一个字符串、数字或日期和时间值
数学函数：用于对作为函数参数提供的输入值执行操作，返
回一个数字值
元数据函数：返回有关数据库和数据库对象的信息
安全函数：返回有关用户和角色的信息
字符串函数：对字符串输入值执行操作，并返回一个字符串
或数字值
系统函数：执行系统操作
系统统计函数：返回系统的统计信息
文本和图像函数：对于文本或图像输入值或列执行操作，返
回有关这些值的信息。
40
系统函数
系统函数用于返回有关 SQL Server系统、
用户、数据库和数据库对象的信息。系统
函数可以让用户在得到信息后，使用条件
语句，根据返回的信息进行不同的操作。
与其它函数一样，可以在 SELECT语句的
SELECT和 WHERE子句以及表达式中使用系统
函数。
41
例：返回 Northwind数据库的 Employees
表中的首列的名称 。
USE Northwind
SELECT
COL_NAME(OBJECT_ID('Employees'),1)
运行结果为,
EmployeeID
其中,object_id:返回对象的 id
42
日期和时间函数
日期和时间函数用于对日期和时间数据
进行各种不同的处理和运算, 并返回一个
字符串, 数字值或日期和时间值 。 表 1列
出了日期类型的名称, 缩写形式以及可接
受的值 。
43
表 1 日期和时间函数的类型
函 数 参 数
DATEADD ( datepart,number,date )
DATEDIFF ( datepart,date1,date2 )
DATENAME ( datepart,date )
DATEPART ( datepart,date )
DAY ( date )
GETDATE （）
MONTH ( date )
YEAR ( date )
44
例：从 getdate函数返回的日期中提取月份数
SELECT DATEPART(month,GETDATE())
AS 'Month Number'
运行结果为,
Month Number
------------
9
45
例：从日期 03/12/1998中返回月份、天数
和年份数
SELECT MONTH('03/12/1998'),
DAY('03/12/1998'),YEAR('03/12/1998')
运行结果为,
----- ------ ------
3 12 1998
46
字符串函数
字符串函数可以对二进制数据、字符串和表达式
执行不同的运算，大多数字符串函数只能用于 char
和 varchar数据类型以及明确转换成 char和 varchar
的数据类型，少数几个字符串函数也可以用于
binary和 varbinary数据类型。此外，某些字符串
函数还能够处理 text,ntext,image数据类型的数
据。
47
字符串函数的分类,
基本字符串函数,UPPER,LOWER,SPACE、
REPLICATE,STUFF,REVERSE,LTRIM、
RTRIM。
字符串查找函数,CHARINDEX,PATINDEX。
长度和分析函数,DATALENGTH,SUBSTRING、
RIGHT。
转换函数,ASCH,CHAR,STR,SOUNDEX、
DIFFERENCE。
48
数学函数
数学函数用于对数字表达式进行数学运
算并返回运算结果。数学函数可以对 SQL
Server提供的数字数据（ decimal、
integer,float,real,money、
smallmoney,smallint 和 tinyint） 进
行处理。
49
例：在同一表达式中使用 CEILING（）、
FLOOR（）,ROUND（） 函数。
select ceiling(13.4),floor(13.4),
round(13.4567,3)
运行结果为,
--------- --------- -------
14 13 13.4570
50
转换函数
一般情况下, SQL Server会自动处理某些数据类型
的转换 。 例如, 如果比较 char 和 datetime 表达式,
smallint 和 int 表达式, 或不同长度的 char 表达
式, SQL Server 可以将它们自动转换, 这种转换被
称为隐性转换 。 但是, 无法由 SQL Server自动转换的
或者是 SQL Server自动转换的结果不符合预期结果的,
就需要使用转换函数做显示转换 。 转换函数有两个：
CONVERT和 CAST。
51
CONVERT和 CAST函数
CAST ( expression AS data_type )
CONVERT函数允许用户把表达式从一种数据类型
转换成另一种数据类型, 还允许把日期转换成不同
的样式 。 其语法形式为,
CONVERT (data_type[(length)],expression
[,style])
52
例,USE pubs
SELECT title,ytd_sales FROM titles
WHERE
CAST(ytd_sales AS char(20)) LIKE '15%'
AND type = 'trad_cook'
运行结果为,
Title ytd_sales
------------------------------------------
Fifty Years in Buckingham Palace Kitchens 15096
53
用户自定义函数
除了使用系统提供的函数外, 用户还可以根据
需要自定义函数 。 用户自定义函数是 SQL
Server 2000 新增的数据库对象, 是 SQL
Server 的一大改进 。
用户自定义函数不能用于执行一系列改变数据
库状态的操作, 但它可以像系统函数一样在查
询或存储过程等的程序段中使用, 也可以像存
储过程一样通过 EXECUTE 命令来执行 。 用户
自定义函数中存储了一个 Transact-SQL 例程可
以返回一定的值 。
在 SQL Server 2000 中根据函数返回值形式的
不同, 将用户自定义函数分为三种类型,
54
标量型函数,,标量型函数返回一个确定类型的标量
值 。 其返回值类型为除了 TEXT,NTEXT,IMAGE、
CURSOR,TIMESTAMP 和 TABLE 类型外的其它
数据类型 。 函数体语句定义在 BEGIN-END语句内,
其中包含了可以返回值的 Transact-SQL 命令 。
内嵌表值函数, 内嵌表值函数以表的形式返回一个返
回值，即它返回的是一个表。内嵌表值型函数没有由
BEGIN-END 语句括起来的函数体, 其返回的表由一
个位于 RETURN 子句中的 SELECT 命令段从数据库
中筛选出来 。 内嵌表值型函数功能相当于一个参数化
的视图。
多语句表值型函数, 多语句表值型函数可以看作标量
型和内嵌表值型函数的结合体，它的返回值是一个表，
但它和标量型函数一样有一个用 BEGIN-END 语句括
起来的函数体 。 返回值的表中的数据是由函数体中的
语句插入的 。
55
创建标量型用户自定义函数
其语法如下,
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
56
< function_option >,:=
{ ENCRYPTION | SCHEMABINDING }
各参数说明如下,
owner_name,指定用户自定义函数的所有者
function_name,指定用户自定义函数的名称 。
database_name.owner_name.function_name
应是唯一的。
@parameter_name,定义一个或多个参数的
名称, 一个函数最多可以定义 1024 个参数, 每个
参数前用 @符号标明 。 参数的作用范围是整个函
数, 参数只能替代常量, 不能替代表名, 列名或
其它 数据库对象的名称, 用户自定义函数不支持
输出参数 。
57
scalar_parameter_data_type,指定标
量型参数的数据类型, 可以为除 TEXT、
NTEXT,IMAGE,CURSOR、
TIMESTAMP 和 TABLE 类型外的其它数据
类型 。
scalar_return_data_type,指定标量型
返回值的数据类型, 可以为除 TEXT,
NTEXT, IMAGE,CURSOR、
TIMESTAMP 和 TABLE 类型外的其它数据
类型 。
scalar_expression,指定标量型用户自
定义函数返回的标量值表达式 。
function_body,指定一系列的 Transact-
SQL 语句, 它们决定了函数的返回值
58
ENCRYPTION,加密选项 。 让 SQL
Server 对系统表中有关 CREATE
FUNCTION 的声明加密, 以防止用户自定
义函数作为 SQL Server 复制的一部分被
发布 。
SCHEMABINDING,计划绑定选项 。 将
用户自定义函数绑定到它所引用的数据库
对象 。 如果指定了此选项, 则函数所涉及
的数据库对象从此将不能被删除或修改,
除非函数被删除或去掉此选项 。 应注意的
是要绑定的数据库对象必须与函数在同一
数据库中 。
59
例 13-19 创建工龄工资计算函数
use pangu
go
create function WorkYearWage(@hiredate datetime,--hiredate 表
示雇佣日期
@today datetime,@per_wage money)
--today 表示当前的日期 per_wage 表示每一年工龄应得的工资额
returns money
as begin
declare @WorkYearWage money
set @WorkYearWage = (year(@today)-
year(@hiredate))*@per_wage
return(@WorkYearWage)
end --结束函数定义
--创建函数
go
60
--调用函数
select pangu.dbo.workyearwage('1991-7-
1',getdate(),15)
as work_year_wage
运行结果如下
work_year_wage
135.0000
(1 row(s) affected)
上例的创建语句也可以写成如下形式
create function WorkYearWage(@hiredate datetime,
@today datetime,@per_wage money)
returns money
as begin
return((year(@today)-year(@hiredate))*@per_wage)
end
61
创建内嵌表值用户自定义函数
其语法如下,
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 [) ]
各参数说明如下
TABLE,指定返回值为一个表
select-stmt,单个 SELECT 语句, 确定返回的表的数据,
其余参数与标量型用户自定义函数相同 。
62
例, 创建返回所有订购某类产品的公司信息函数
use sample
go
create function orderfirms(@productid
varchar(30))
returns table
as
return (select * from products p
where p.p_id = @productid)
go
63
创建多语句表值用户自定义函数
其语法如下,
CREATE FUNCTION [ owner_name,] function_name
( [ { @parameter_name scalar [as]_parameter_data_type [ = default ] }
[,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ WITH < function_option > [,...n ] ]
[ AS ]
BEGIN
function_body
RETURN END
< table_type_definition >,,=
( { column_definition | table_constraint } [,...n ] )
各参数说明如下,
@return_variable,一个 TABLE 类型的变量, 用于存储和累积返回
的表中的数据行 。 其余参数与标量型用户自定义函数相同 。
64
用企业管理器创建用户自定义函数
用 Enterprise Manager 创建用户自定义函
数的方法是, 在 Enterprise Manager 中选
择要创建用户自定义函数的数据库, 在数据
库对象 User Defined Functions 上单击右
键, 从开始菜单中选择 New User Defined
Function 选项, 就会出现定义用户自定义
函数属性对话框, 可以在其中指定要定义的
函数的名称, 并编辑函数的脚本, 单击 OK
按钮则添加用户自定义函数对象到数据库中 。
65
修改和删除用户自定义函数
在 Enterprise Manager 中选择要进行改动的用户自定义
函数, 单击右键, 从快捷菜单中选择属性选项, 则会出现
修改用户自定义函数结构对话框 。 可以修改用户自定义函
数的函数体, 参数等, 从快捷菜单中选择删除选项, 则可
删除用户自定义函数 。
用 ALTER FUNCTION 命令也可以修改用户自定义函数,
此命令的语法与 CREATE FUNCTION 相同,因此使用
ALTER FUNCTION 命令其实相当于重建了一个同名的函
数,用起来不大方便 。 另外可以用 DROP FUNCTION 命令
删除用户自定义函数其语法如下,
DROP FUNCTION { [ owner_name,] function_name }
[,...n ]
例, 删除用户自定义函数 chiefinfo
drop function chiefinfo
66
流程控制语句
流程控制语句是指那些用来控
制程序执行和流程分支的命令，
在 SQL Server 2000中，流程控
制语句主要用来控制 SQL语句、
语句块或者存储过程的执行流程。
67
BEGIN… END语句
BEGIN… END语句能够将多个 Transact-
SQL语句组合成一个语句块，并将它们视为
一个单元处理。在条件语句和循环等控制
流程语句中，当符合特定条件便要执行两
个或者多个语句时，就需要使用
BEGIN… END语句，其语法形式为,
BEGIN
{ sql_statement
| statement_block
}
END
68
例如,
Use sample
Go
Declare @message varchar(200)
If exist(select * from 员工数据表
where 所属部门＝ ‘ 办公室 ’ )
Begin
Set @message=“下列人员在办公室工作：,
Print @message
Select 员工姓名 from 员工数据库
Where 所属部门＝, 办公室,
End
Else
begin
set @message=“没有人在办公室工作,
Print @message
End
go
69
IF… ELSE语句
IF… ELSE语句是条件判断语句，其中,ELSE
子句是可选的，最简单的 IF语句没有 ELSE子
句部分。 IF… ELSE语句用来判断当某一条件
成立时执行某段程序，条件不成立时执行另
一段程序。 SQL Server允许嵌套使用
IF… ELSE语句，而且嵌套层数没有限制。
70
IF… ELSE语句的语法形式
IF Boolean_expression
{ sql_statement |
statement_block }
[ ELSE
{ sql_statement |
statement_block } ]
71
IF … EXISTS语句
If后面的布尔表达式可含有 select 语句，
如果 select语句返回一个值，它可用来与
另一值进行比较，以得到布尔表达式。
如果 Select语句返回不止一个值，可使用
If Exists。 语法如下,
If exists (select statement)
{ sql_statement | statement_block }
[ ELSE [Bolean_expression]
{ sql_statement | statement_block } ]
72
例如：查询标识号为 9933的出版商出版的任何书
的信息
If exists (select * from titles
where pub_id = ?9933?
begin
print,包含如下图书：”
select *from titles
where pub_id = ?9933?
end
else
print,无”
73
注意：一定不要把 if … exists 和聚合函
数一起使用，因为聚合函数总是返回数
据，即使数据是 0。
例如,if exists(select count(*) from
titles where pub_id = ?9933?)
74
WHILE 语句
WHILE语句用于设置重复执行 SQL 语句或
语句块的条件。只要指定的条件为真，就重
复执行语句。其中,CONTINUE语句可以使程
序跳过 CONTINUE语句后面的语句，回到 WHILE
循环的第一行命令。 BREAK语句则使程序完全
跳出循环，结束 WHILE语句的执行。
75
WHILE语句
Break语句将在某些情况发生时，立即无
条件地退出最内层 while循环。语法为,
WHILE 逻辑表达式
begin
…,
Break
…
end
76
While语句
Continue语句在某些情况发生时，控制程序跳
出本次循环，重新开始下一次 while循环。其语
法为,
While 逻辑表达式
Begin
….,
Continue
…,
End
注意：如果 select语句用作 while语句的条件，那
么,select语句必须包含在英文括号中。
77
例, declare @x int,@y int, @c int
select @x = 1,@y=1
while @x < 3
begin
print @x --打印变量 x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c --打印变量 c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
78
CASE表达式
CASE表达式可以计算多个条件式，并将其中一
个符合条件的结果表达式返回。 CASE表达式的语
法为,
CASE 字段名或变量名
WHEN 逻辑表达式 1 THEN 结果表达式 1
WHEN 逻辑表达式 1 THEN 结果表达式 1
WHEN 逻辑表达式 1 THEN 结果表达式 1
…
ELSE 结果表达式
END
79
例, 调整员工工资, 工作级别为 1 的上调 8%,工
作级别为 2 的上调 7%,工作级别为 3 的上调 6%，
其它上调 5%。
use 工资库
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end
80
RETURN语句
RETURN语句用于无条件地终止一个查询, 存
储过程或者批处理, 此时位于 RETURN语句之后
的程序将不会被执行 。 RETURN语句的语法形式
为,
RETURN 整数表达式
通常, 存储过程使用返回代码表示存储过程
执行的成功或失败 。 无错误, 则返回 0,否则,
返回非零值 。
81
WAITFOR语句
WAITFOR语句用于暂时停止执行 SQL语句、语句
块或者存储过程等，直到所设定的时间已过或者
所设定的时间已到才继续执行。 WAITFOR语句的语
法形式为,
WAITFOR DELAY 时间间隔
其中，时间间隔指定执行 waitfor语句之前需要等
待的事件，最多为 24小时。
或者 waitfor time 时间值
其中，时间值指定 waitfor语句将要执行的时间
82
Use sample
Go
--指定在执行 select语句之前等待二秒
Waitfor delay ?00:00:02?
--执行查询
Select 姓名，性别 from 员工数据表
? Where 所属部门＝‘项目部’
83
GOTO语句
GOTO语句可以使程序直接跳到指定的标有标识符
的位置处继续执行，而位于 GOTO语句和标识符之间
的程序将不会被执行。 GOTO语句和标识符可以用在
语句块、批处理和存储过程中，标识符可以为数字
与字符的组合，但必须以,,, 结尾。
GOTO label
……
label,
84
GOTO语句
例：利用 GOTO语句求出从 1加到 5的总和。
declare @sum int,@count int
select @sum=0,@count=1
label_1,
select @sum=@sum+@count
select @count=@count+1
if @count<=5
goto label_1
select @count,@sum
85
PRINT语句
语法格式为,
Print {?any ASCII text?
|@local_varible |
@@global_variable}
注意：可输出的仅仅是 ASCII字符串（字
符串常数）或字符类型的变量
若要输出更复杂的内容，须将字符串存入
字符变量，然后输出该变量。
86
例,Use pubs
Declare @msg varchar(50),
@numWA tinyint
Select @numWA=count(*)
from stores where state =,WA”
Select @msg=?There are ? +
Convert(varchar(3),@numWA)+
?stores in washington.?
Print @msg
87
游标的引入
游标的优点和种类
在数据库开发过程中, 当你检索的数据只
是一条记录时, 你所编写的事务语句代码
往往使用 SELECT,INSERT 语句, 但是我
们常常会遇到这样情况, 即从某一结果集
中逐一地读取一条记录, 那么如何解决这
种问题呢？ 游标为我们提供了一种较好的
解决方案
88
声明游标
每一个游标必须有四个组成部分 。 这四
个关键部分必须符合下面的顺序
1.DECLARE 游标
2.OPEN 游标
3.从一个游标中 FETCH 信息
4.CLOSE 或 DEALLOCATE 游标
通常我们使用 DECLARE 来声明一个游
标 。
89
声明游标
声明一个游标主要包括以下主要内容
游标名字
数据来源表和列
选取条件
属性仅读或可修改
90
其语法格式如下
DECLARE 游标名称 CURSOR
[local|global]
[forward_only|scroll]
[read_only]
FOR 选择语句
[FOR [UPDATE [OF 字段名称 1，字段名称 2,? ]]
其中, local|global指定该游标的作用域是局部的
还是全局的。
如果把 forward_only选择为 forward_only,则游标
只能从第一行滚动到最后一行。
91
,SCROLL,表明所有的提取操作, 如 FIRST,
LAST,PRIOR,NEXT,RELATIVE,
ABSOLUTE都可用 。 如果不使用该保留字那么
只能进行 NEXT 提取操作 。
选择语句,是定义结果集的 SELECT 语句, 应
该注意的是在游标中不能使用 COMPUTE,
COMPUTE BY FOR BROWSE INTO 语句
READ ONLY,表明不允许游标内的数据被更新 。
UPDATE [OF 字段名 1[,… n]],定义在游
标中可被修改的列 。
92
下面给出声明游标的三个例子
例 1,标准游标
declare cur_authors cursor
for
select au_id,au_lname,au_fname,phone,
address,city,state,contract
from authors
例 2,只读游标
declare cur_authors cursor
for
select au_lname,au_fname,phone,address,
city,state
from authors
for read only
93
例, 更新游标
declare cur_authors cursor
for
select au_lname,au_fname
from authors
for update
94
打开游标
游标在声明以后, 如果要从游标中读取数据, 必须打开游标 。
打开一个游标使用 OPEN 命令, 其语法规则为
OPEN 游标名称
注意,在打开游标时, 如果游标声明语句中使用了
INSENSITIVE 保留字, 则 OPEN产生一个临时表来存放结果
集 。 如果在结果集中任何一行数据的大小超过 SQL
SERVER定义的最大行尺寸时, OPEN 命令将失败
INSENSITIVE,表明 SQL SERVER 会将游标定义所选取出来的
数据记录存放在一临时表内,（ 建立在 tempdb 数据库下 ） 对
该游标的读取操作皆由临时表来应答 。 因此, 对基本表的修
改并不影响游标提取的数据, 即游标不会随着基本表内容的
改变而改变, 同时也无法通过游标来更新基本表 。 如果不使
用该保留字, 那么对基本表的更新, 删除都会反映到游标中 。
95
读取游标中的数据－ fetch
当游标被成功打开以后就可以从游标中逐行地
读取数据以进行相关处理 。 从游标中读取数据
主要使用 FETCH 命令, 其语法规则为,
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar} ]
FROM ] cursor_name
[INTO
@variable_name1,@variable_name2..,]
96
各参数含义说明如下,
NEXT:返回结果集中当前行的下一行,并增加当
前行数为返回行行数如果 FETCH NEXT
是第一次读取游标中数据则返回结果集中的是第一
行而不是第二行
PRIOR:返回结果集中当前行的前一行并减少当
前行数为返回行行数 。 如果 FETCH PRIOR是第一
次读取游标中数据则无数据记录返回并把游标位置
设为第一行
FIRST:返回游标中第一行
LAST:返回游标中的最后一行
97
ABSOLUTE {n | @nvar},如果 n 或 @nvar 为
正数, 则表示从游标中返回的数据行数 。 如果
n 或 @nvar 为负数, 则返回游标内从最后一行
数据算起的第 n 或 @nvar 行数据 。
若 n 或 @nvar 超过游标的数据子集范畴, 则
@@FETCH_STARS 返回 -1 。 在该情况下,
如果 n 或 @nvar 为负数, 则执行 FETCH
NEXT 命令会得到第一行数据 ； 如果 n 或
@nvar为正值, 执行 FETCH PRIOR 命令则会
得到最后一行数据 。 n 或 @nvar 可以是一固定
值, 也可以是一 smallint,tinyint 或 int 类型的
变量 。
98
RELATIVE {n | @nvar},若 n 或 @nvar
为正数, 则读取游标当前位置起向后的
第 n 或 @nvar 行数据 。 如果 n 或 @nvar
为负数, 则读取游标当前位置起向前的
第 n 或 @nvar 行数据 。 若 n 或 @nvar 超
过游标的数据子集范畴, 则
@@FETCH_STARS 返回 -1。 在该情况
下, 如果 n 或 @nvar 为负数, 则执行
FETCH NEXT 命令则会得到第一行数据 ；
如果 n 或 @nvar 为正值, 执行
FETCHPRIOR 命令则会得到最后一行数
据 。 n 或 @nvar 可以是一固定值也可以
是一 smallint,tinyint或 int 类型的变量
99
INTO @variable_name[,...n],允许将
使用 FETCH 命令读取的数据存放在多个
变量中 ； 在变量行中的每个变量必须与
游标结果集中相应的列相对应, 每一变
量的数据类型也要与游标中数据列的数
据类型相匹配 。
100
检查游标状态
@@FETCH_STATUS, 全局变量, 返回上次
执行 FETCH 命令的状态 。 在每次用 FETCH从游
标中读取数据时, 都应检查该变量以确定上次
FETCH 操作是否成功, 来决定如何进行下一步
处理 。 @@FETCH_STATUS 变量有三个不同
的返回值 。
0,表示成功取出了一行。
－ 1：表示未取到数据，因为所要求 游标位置超
出了结果集
－ 2：表示返回的行已经不再是结果集的一个成
员。这种情况只有再游标不是 insensitive的情
况下出现，即其它进程已删除了行或改变了游
标打开的关键值。
101
编辑当前游标行
通常情况下, 我们用游标来从基础表中检索数据, 以实
现对数据的行处理 。 但在某些情况下, 我们也常要修改
游标中的数据, 即进行定位更新或删除游标所包含的数
据 。 所以必须执行另外的更新或删除命令, 并在 WHERE
子句中重新给定条件才能修改到该行数据 。 但是如果在
声明游标时使用了 FOR UPDATE 语句, 那么就可以在
UPDATE 或 DELETE命令中以 WHERE CURRENT OF
关键字直接修改或删除当前游标中所存储的数据, 而不
必使用 WHERE 子句重新给出指定条件 。 当改变游标中
数据时, 这种变化会自动地影响到游标的基础表 。 但是
如果在声明游标时选择了 INSENSITIVE 选项时, 该游标
中的数据不能被修改 。
102
进行定位修改或删除游标中数据的语法规则为,
UPDATE table_name
SET column_name1 = {expression1 | NULL
(select_statement)}
[,column_name2 = { expression2 | NULL
(select_statement)}
WHERE CURRENT OF cursor_name
DELETE FROM table_name
WHERE CURRENT OF cursor_name
其中, table_name是 UPDATE 或 DELETE 的表名
column_name,UPDATE 的列名
cursor_name,游标名
103
下例说明如何对游标进行定位更新或删除。
首先声明一个游标
declare authors_cur scroll cursor
For
select * from authors.for update of
au_lname,au_fname
例, 更新 authors 表中的 au_lname 和 au_fname
列
update authors
set au_lname = ‘china’,au_fname = ‘asia’
where current of authors_cur
例, 删除 authors 表中的一行数据
delete from authors
where current of authors_cur
以上更新或删除操作总是基于游标的当前位置
104
例, 下面是一个定位更新的完整例子, 首先查看 authors 表中每一行将 au_id
等于 ’ 172-32-1176’的记录的 au_lname 和 au_fname 分别更改为 ’ Smith’
和 ’ Jake’
declare @au_id id(11),@au_lname varchar(40),@au_fname varchar(20)
/* declare a cursor that will contain the au_id,au_lname,au_fname form
authors table*/
declare authors_cur cursor for
select au_id,au_lname,au_fname from authors
for update of au_id,au_lname,au_fname
open authors_cur /* open the cursor */
/* get the first row from the cursor */
fetch next from authors_cur into @au_id,@au_lname,@au_fname
while @@fetch_status = 0 /* loop the rows in the cursor*/
Begin
if @au_id = ‘172-32-1176’
update authors
set au_lname = ‘smith’,au_fname = ‘jake’
where current of authors_cur
/* get next row */
fetch next from authors_cur into @au_id,@au_lname,@au_fname
end
deallocate authors_cur /* close the cursor */
105
关闭游标
关闭游标, 使用 CLOSE 命令关闭游标
在处理完游标中数据之后, 必须关闭游标
来释放数据结果集和定位于数据记录上的
锁 。
CLOSE 语句关闭游标但不释放游标占用
的数据结构 。 如果准备在随后的使用中再
次打开游标, 则应使用 open 命令 。
关闭游标的语法规则为
CLOSE 游标名称
106
在使用游标时, 各种针对游标的操作或者
引用游标名或者引用指向游标的游标变量,
当 CLOSE 命令关闭游标时并没有释放游
标占用的数据结构 。 因此常使用
DEALLOCATE 命令删除掉游标与游标名
或游标变量之间的联系, 并且释放游标占
用的所有系统资源 。 其语法规则为
DEALLOCATE 游标名称
注意：若真的完成释放游标的操作，再次
使用时，则需重新声明。
释放游标
107
游标变量, 游标变量是从 MS SQL SERVER 7 版本才开
始使用的一种新增数据类型, 定义一个游标变量主要有
两种方法,
首先我们先声明一个游标
declare titleauthor_cur scroll cursor for
select * from titleauthor
然后，使用 SET 语句将一游标赋值给游标变量
declare @cur_ta cursor
set @cur_ta = titleauthor_cur
将声明游标语句放在游标赋值语句中, 如下所示
declare @cur_ta cursor
declare titleauthor_cur scroll cursor for
select * from titleauthor
set @cur_ta = titleauthor_cur
go
/*再引用游标 */
108
例, 下面给出一个具体完整的例子 。 在该例中, 我们对
DEALLOCATE 命令将有更加清晰的了解
use pubs
go
/*声明并打开一个全局游标在批处理以外该游标仍然可见 */
declare titleauthor_cur cursor global scroll for
select * from titleauthor
open titleauthor
go
/*用游标变量引用已声明过的游标 */
declare @cur_ta1 cursor
set @cur_ta1 = titleauthor_cur
/*现在释放对游标的引用 */
deallocate @cur_ta1
/*游标 titleauthor_cur 仍旧存在 */,
fetch next from titleauthor_cur
109
declare @cur_ta2 cursor
set @cur_ta2 = titleauthor_cur
/*释放 titleauthor_cur 游标 */,
deallocate titleauthor_cur
/*由于游标被 @cur_ta2 引用所以仍旧存在 */
fetch next from @cur_ta2
/*当最后一个游标变量超出游标作用域时游标将被释
放 */
go
declare @cur_ta cursor
set @cur_ta = cursor local scroll for
select * from titles
/*由于没有其它变量对其进行引用所以游标被释放 */
deallocate @cur_ta
go
第四章 T-SQL程序设计基础
SQL Server数据类型
SQL Server 全局变量和局部变量
SQL Server 函数
T-SQL 控制流程
游标
2
系统数据类型
数据类型指定列、存储过程参数及局部
变量的数据特性
数据按照数据类型存储在列中
? 系统数据类型：系统数据类型是 SQL
Server 支持的内置数据类型,系统数据类
型有 25种。
? 用户定义的数据类型：用户根据系统数据类
型自己定义的数据类型
3
系统数据类型
语法 存储长度 适用范围 备注
字
符
型
Char（ n） N字节 固定长度的非
Unicode字符数据,
输入字符少于 n,以
空格填满 。 若超长
则截掉 。
N为 1-
8000范
围
Varchar
(n)
实际长度 N为最大长度小于 N
时不加空格
可节省空
间
Nchar(n) N单位 Unicode标准，两
个字节为存储单位，
容纳量增加了。
N为 1-
4000范
围 Nvarcha
r(n)
存储大小是
输入数据的
实际长度
4
系统数据类型
语法 存储长度 适用范围 备注
整
型
数
据
类
型
Int 4个字节 231~（ 231-1） 内所有正
负整数
是唯
一可
以与
iden
tiey
属性
一起
使用
的类
型
Smallin
t
2个字节 -215~（ 215-1） 内所有正
负整数
Tinyint 1个字节 0~255范围的所有正整数
5
系统数据类型
语法 存储长度 适用范围 备注
浮
点
数
据
类
型
Real 4个字节 精确到 7位小数 存储十
进制小
数。 Float 8个字节 最多可精确到 15位小数
Decimal 实际存储
空间
Decimal （ p,s）,p
表示总位数,s表示
小数点后的位数。
0<=s<=p<=38。
例如：
decimal(8,6) 则范
围 (
-99.999999
~99.999999）
Numeric
6
系统数据类型
语法 存储长度 适用范围 备注
货
币
型
Money 8个字节 用于存储货币 精确度为
万分之一
Samll
mone
y
4个字节 范围比 Money
小
精确度为
万分之一
位
型
Bit 1个字节 常用作逻辑变
量表示真假
只能输入
0与 1非此
值当为 1
7
系统数据类型
语法 存储长度 适用范围 备注
日
期
与
时
间
型
Dateti
me
8个字节
精度 三百
分之一秒
，即 3.33
毫秒。
MM DD YYYY
hh:mm
AM/PM
1753.1.1~99
99.12.31
23:59:59
存储日
期和时
间的结
合体,
引用时
用单引
号 Small
dateti
me
4个字节
精度 1分钟
1900.1.1~20
79.6.6
8
系统数据类型
语法 存储
长度
适用范围
备注
二
进
制
型
Binary
(n)
取决
于定
义
固定长度的二进
制数据
N为 1~8000，
最后用检索输出
的是二进制
Varbin
ary(n)
N+4
字节
二进制数据的长
度未知或变化较
大时可用
存放 8000字节内
可变长数据
9
系统数据类型
语法 存储长度 适用范围 备注
文
本
型
Text 实际大小 最大可存储
231-1
存储长度大于
8000个字节
的二进制数据
Ntext 实际大小 最大可存储
230-1
存储长度大于
4000个字符
的 unicode字
符串
图
形
image 实际大小 最大可存储 231
照片、图、画
存储长度大于
8000个字符
的字符串
10
系统数据类型
语法 存储长度 适用范围 备注
特
殊
型
Timestam
p
8个
字节
提供数据库范
围内的唯一值
单调上升
的计数器
Unique
identifi
er
16字
节
存储一个 16字节
长的二进制数
全局惟一
标识符
注：全局惟一标识符（ GUID）,由计算机网卡和
CPU时钟产生的，每台机器不会重复。 Newid()
函数可求出。
11
系统数据类型
语法 存储长度 适用范围
新
增
型
Bigin
t
8个字节 263~（ 263-1） 范围内的
所有正负数
Sql_v
arian
t
存储除文本、图形数据和
timestamp类型数据外的其他任何
合法的 SQL server数据。
table
用于存储对表或视图处理后的结果
集。可存储一个表。
12
系统数据类型
只有字符与二进制数据类型需要指定长度，
其他如整型、日期时间、浮点数据类型，定
义时不用指定长度，也就是长度是默认值。
自定义数据类型
建立在 SQL server系统数据类型基础上的。
需要指定该类型的名称、建立在其上的系统
数据类型及是否充许为空。
可以用如下方法来创建自定义数据类型。
13
自定义数据类型
利用系统存储过程,sp_addtype。 其语法为,
sp_addtype type,[system_data_type]
[,’nulltype’ ]
其中,type是用户定义数据类型的名称。数据类型
名称必须遵循标识符规则，并且在每个数据库中必
须是唯一的。
system_data_type是 SQL Server 提供的数据类型，
用户定义的数据类型即基于该类型。
‘null_type’, 指定必须如何处理 null 值。
null_type 是 varchar(8),设置值为 ‘ NULL’
（ 默认）,‘ NOT NULL’ 或 ‘ NONULL’
例,exec SP_addtype tele,smallint,'not
null‘
14
删除用户定义的数据类型
使用 sp_droptype 系统存储过程来撤销用户定
义数据类型。
例删除名为 tele的用户定义数据类型,
EXEC sp_droptype telephone
15
其它语言元素
批处理
注释
变量
运算符
函数
流程控制语句
16
批处理
批处理：指包含一条或多条 T-SQL语句
的语句组，这组语句从应用程序一次性
地发送到 SQL Server服务器执行。
执行单元,SQL Server服务器将批处理
语句编译成一个可执行单元，这种单元
称为执行单元。
若批处理中的某条语句编译出错，则无
法执行。若运行出错，则视情况而定。
17
书写批处理时,go语句作为批处理命令
的结束标志。当编译器读取到 go语句时，
会把 go语句前面所有的语句当作一个批
处理，并将这些语句打包发送给服务器。
注意,go语句本身不是 T-SQL语句的组
成部分，它只是一个用于表示批处理结
束的前端指令。
18
建立批处理时注意
Create default,Create Rule,Create
Trigger 和 Create view等语句在同一个批
处理中只能提交一个。
不能在删除一个对象之后，再同一批处理
中再次引用这个对象
不能把规则和默认值绑定到表字段或者自
定义字段上之后，立即在同一个批处理中
使用它们。
不能定义一个 check约束之后，立即在同一
个批处理中使用。
19
不能修改表中一个字段名之后，立即在
同一个批处理中引用这个新字段。
使用 set 语句设置的某些 set 选项不能应
用于同一个批处理中的查询
若批处理中第一个语句是执行某个存储
过程的 execute语句，则 execute关键
字可以省略。若该语句不是第一个语句，
则必须写上。
20
例如,
Use pubs
Go
Select * from 员工数据表
Go
Create view pm_view as
Select * from 员工数据库
where 所属部门 =‘项目部’
Go
Select * from pm_view
go
21
注释
注释是程序代码中不执行的文本字符串
（也称为注解）。在 SQL Server中，可以使用两
种类型的注释字符：一种是 ANSI标准的注释符
,--”,它用于单行注释；另一种是与 C语言相
同的程序注释符号，即, /* */” 。例如,
--检索部门的员工
/* 检索录入部
的员工 */
22
变量
变量是一种语言中必不可少的组成
部分 。 Transact-SQL语言中有两种形式
的变量, 一种是用户自己定义的局部变
量, 另外一种是系统提供的全局变量 。
23
全局变量
全局变量是 SQL Server系统内部使用的变
量，其作用范围并不仅仅局限于某一程序，
而是任何程序均可以随时调用。全局变量通
常存储一些 SQL Server的配置设定值和统计
数据。用户可以在程序中用全局变量来测试
系统的设定值或者是 Transact-SQL命令执行
后的状态值。
24
使用全局变量时注意
① 全局变量不是由用户的程序定义的, 它们是
在服务器级定义的 。
② 用户只能使用预先定义的全局变量 。
③ 引用全局变量时, 必须以标记符, @@” 开头 。
④ 局部变量的名称不能与全局变量的名称相同,
否则会在应用程序中出现不可预测的结果 。
25
@error全局变量将返回最后执行的 T-
SQL语句的错误代码，数据类型为整型。
在 SQL Server执行一个 T-SQL语句之后，
若成功，则返回值为 0；否则返回相应的
错误代码。
26
局部变量
局部变量是一个能够拥有特定数据类型的对象，它
的作用范围仅限制在程序内部。局部变量可以作为
计数器来计算循环执行的次数，或是控制循环执行
的次数。另外，利用局部变量还可以保存数据值，
以供控制流语句测试以及保存由存储过程返回的数
据值等。局部变量被引用时要在其名称前加上标志
,＠,,而且必须先用 DECLARE命令定义后才可以使
用。
27
声明局部变量
其声明形式如下,
DECLARE @变量名 变量类型 [, @变量
名 变量类型 … ]
其中变量类型可以是 SQL Server 2000 支
持的所有数据类型, 也可以是用户自定义
的数据类型
28
局部变量赋值
在 Transact-SQL 中,不能像在一般的程
序语言中一样使用变量 =变量值来给变量
赋值,必须使用 SELECT 或 SET 命令来
设定变量的值,其语法如下
SELECT @局部变量 = 变量值
或 SET @局部变量 = 变量值
29
例, 声明一个长度为 10 个字符的变量 id 并赋值
declare @id char 10
select @id = ’10010001’
例,declare @temp_counter int
Set @temp_counter = 0
例,declare @max_salary int
Select @max_salary= max(工资 )
From 员工数据表
注意：局部变量的作用范围是从声明该局部变量的
地方开始，到声明局部变量的批处理或存储过程
的结尾。在局部变量的作用范围以外引用该局部
变量将产生语法错误。
30
运算符
运算符是一些符号, 它们能够用来执行
算术运算, 字符串连接, 赋值以及在字段,
常量和变量之间进行比较 。 在 SQL Server
2000中, 运算符主要有以下六大类：算术
运算符, 赋值运算符, 位运算符, 比较运
算符, 逻辑运算符以及字符串串联运算符 。
31
1,算术运算符
算术运算符可以在两个表达式上执行
数学运算，这两个表达式可以是数字数
据类型分类的任何数据类型。算术运算
符包括加（ +）、减（ — ）、乘（ *）、
除（ /）和取模（ %）。
32
2,赋值运算符
Transact-SQL 中只有一个赋值运算符, 即等
号 （ =） 。 赋值运算符使我们能够将数据值指派
给特定的对象 。 另外, 还可以使用赋值运算符
在列标题和为列定义值的表达式之间建立关系 。
33
3,位运算符
位运算符使我们能够在整型数据或者二
进制数据（ image 数据类型除外）之间执
行位操作。
表 1 位运算符
运 算 符 含 义
&（ 按位 AND）
按位 AND（ 两个操作数 ） 。
|（ 按位 OR）
按位 OR（ 两个操作数 ） 。
^（ 按位异或 XOR）
按位异或 XOR（ 两个操作数 ） 。
34
4,比较运算符
比较运算符用于比较两个表达式的大小或是否
相同，其比较的结果是布尔值，即 TRUE（ 表示表
达式的结果为真）,FALSE（ 表示表达式的结果为
假）以及 UNKNOWN。 除了 text,ntext 或 image
数据类型的表达式外，比较运算符可以用于所有
的表达式。
35
5,逻辑运算符
逻辑运算符可以把多个逻辑表达式连接
起来 。 逻辑运算符包括 AND,OR和 NOT等运
算符 。 逻辑运算符和比较运算符一样, 返
回带有 TRUE 或 FALSE 值的布尔数据类
型 。
36
6,字符串串联运算符
字符串串联运算符允许通过加号 (+)
进行字符串串联, 这个加号即被称为字符
串串联运算符 。 例如对于语句 SELECT
‘abc’+’def’,其结果为 abcdef。
37
运算符的优先级从高到低排列
括号,（ ） ；
乘, 除, 求模运算符,*,/,%；
加减运算符,+,- ；
比较运算符,=,>,<,>=,<=,<>,!=,!>,!<；
位运算符,^,&,|；
逻辑运算符,NOT；
逻辑运算符,AND；
逻辑运算符,OR。
38
函数
在 Transact-SQL语言中, 函数被用来执行一
些特殊的运算以支持 SQL Server的标准命令 。
Transact-SQL 编程语言提供了三种函数,
㈠行集函数：行集函数可以在 Transact-SQL
语句中当作表引用 。
㈡聚合函数：聚合函数用于对一组值执行计
算并返回一个单一的值 。
㈢标量函数：标量函数用于对传递给它的一
个或者多个参数值进行处理和计算, 并返回
一个单一的值 。
39
标量函数的分类
配置函数：返回当前的配置信息
游标函数：返回有关游标的信息
日期和时间函数：用于对日期和时间类型的输入值进行操作，
返回一个字符串、数字或日期和时间值
数学函数：用于对作为函数参数提供的输入值执行操作，返
回一个数字值
元数据函数：返回有关数据库和数据库对象的信息
安全函数：返回有关用户和角色的信息
字符串函数：对字符串输入值执行操作，并返回一个字符串
或数字值
系统函数：执行系统操作
系统统计函数：返回系统的统计信息
文本和图像函数：对于文本或图像输入值或列执行操作，返
回有关这些值的信息。
40
系统函数
系统函数用于返回有关 SQL Server系统、
用户、数据库和数据库对象的信息。系统
函数可以让用户在得到信息后，使用条件
语句，根据返回的信息进行不同的操作。
与其它函数一样，可以在 SELECT语句的
SELECT和 WHERE子句以及表达式中使用系统
函数。
41
例：返回 Northwind数据库的 Employees
表中的首列的名称 。
USE Northwind
SELECT
COL_NAME(OBJECT_ID('Employees'),1)
运行结果为,
EmployeeID
其中,object_id:返回对象的 id
42
日期和时间函数
日期和时间函数用于对日期和时间数据
进行各种不同的处理和运算, 并返回一个
字符串, 数字值或日期和时间值 。 表 1列
出了日期类型的名称, 缩写形式以及可接
受的值 。
43
表 1 日期和时间函数的类型
函 数 参 数
DATEADD ( datepart,number,date )
DATEDIFF ( datepart,date1,date2 )
DATENAME ( datepart,date )
DATEPART ( datepart,date )
DAY ( date )
GETDATE （）
MONTH ( date )
YEAR ( date )
44
例：从 getdate函数返回的日期中提取月份数
SELECT DATEPART(month,GETDATE())
AS 'Month Number'
运行结果为,
Month Number
------------
9
45
例：从日期 03/12/1998中返回月份、天数
和年份数
SELECT MONTH('03/12/1998'),
DAY('03/12/1998'),YEAR('03/12/1998')
运行结果为,
----- ------ ------
3 12 1998
46
字符串函数
字符串函数可以对二进制数据、字符串和表达式
执行不同的运算，大多数字符串函数只能用于 char
和 varchar数据类型以及明确转换成 char和 varchar
的数据类型，少数几个字符串函数也可以用于
binary和 varbinary数据类型。此外，某些字符串
函数还能够处理 text,ntext,image数据类型的数
据。
47
字符串函数的分类,
基本字符串函数,UPPER,LOWER,SPACE、
REPLICATE,STUFF,REVERSE,LTRIM、
RTRIM。
字符串查找函数,CHARINDEX,PATINDEX。
长度和分析函数,DATALENGTH,SUBSTRING、
RIGHT。
转换函数,ASCH,CHAR,STR,SOUNDEX、
DIFFERENCE。
48
数学函数
数学函数用于对数字表达式进行数学运
算并返回运算结果。数学函数可以对 SQL
Server提供的数字数据（ decimal、
integer,float,real,money、
smallmoney,smallint 和 tinyint） 进
行处理。
49
例：在同一表达式中使用 CEILING（）、
FLOOR（）,ROUND（） 函数。
select ceiling(13.4),floor(13.4),
round(13.4567,3)
运行结果为,
--------- --------- -------
14 13 13.4570
50
转换函数
一般情况下, SQL Server会自动处理某些数据类型
的转换 。 例如, 如果比较 char 和 datetime 表达式,
smallint 和 int 表达式, 或不同长度的 char 表达
式, SQL Server 可以将它们自动转换, 这种转换被
称为隐性转换 。 但是, 无法由 SQL Server自动转换的
或者是 SQL Server自动转换的结果不符合预期结果的,
就需要使用转换函数做显示转换 。 转换函数有两个：
CONVERT和 CAST。
51
CONVERT和 CAST函数
CAST ( expression AS data_type )
CONVERT函数允许用户把表达式从一种数据类型
转换成另一种数据类型, 还允许把日期转换成不同
的样式 。 其语法形式为,
CONVERT (data_type[(length)],expression
[,style])
52
例,USE pubs
SELECT title,ytd_sales FROM titles
WHERE
CAST(ytd_sales AS char(20)) LIKE '15%'
AND type = 'trad_cook'
运行结果为,
Title ytd_sales
------------------------------------------
Fifty Years in Buckingham Palace Kitchens 15096
53
用户自定义函数
除了使用系统提供的函数外, 用户还可以根据
需要自定义函数 。 用户自定义函数是 SQL
Server 2000 新增的数据库对象, 是 SQL
Server 的一大改进 。
用户自定义函数不能用于执行一系列改变数据
库状态的操作, 但它可以像系统函数一样在查
询或存储过程等的程序段中使用, 也可以像存
储过程一样通过 EXECUTE 命令来执行 。 用户
自定义函数中存储了一个 Transact-SQL 例程可
以返回一定的值 。
在 SQL Server 2000 中根据函数返回值形式的
不同, 将用户自定义函数分为三种类型,
54
标量型函数,,标量型函数返回一个确定类型的标量
值 。 其返回值类型为除了 TEXT,NTEXT,IMAGE、
CURSOR,TIMESTAMP 和 TABLE 类型外的其它
数据类型 。 函数体语句定义在 BEGIN-END语句内,
其中包含了可以返回值的 Transact-SQL 命令 。
内嵌表值函数, 内嵌表值函数以表的形式返回一个返
回值，即它返回的是一个表。内嵌表值型函数没有由
BEGIN-END 语句括起来的函数体, 其返回的表由一
个位于 RETURN 子句中的 SELECT 命令段从数据库
中筛选出来 。 内嵌表值型函数功能相当于一个参数化
的视图。
多语句表值型函数, 多语句表值型函数可以看作标量
型和内嵌表值型函数的结合体，它的返回值是一个表，
但它和标量型函数一样有一个用 BEGIN-END 语句括
起来的函数体 。 返回值的表中的数据是由函数体中的
语句插入的 。
55
创建标量型用户自定义函数
其语法如下,
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
56
< function_option >,:=
{ ENCRYPTION | SCHEMABINDING }
各参数说明如下,
owner_name,指定用户自定义函数的所有者
function_name,指定用户自定义函数的名称 。
database_name.owner_name.function_name
应是唯一的。
@parameter_name,定义一个或多个参数的
名称, 一个函数最多可以定义 1024 个参数, 每个
参数前用 @符号标明 。 参数的作用范围是整个函
数, 参数只能替代常量, 不能替代表名, 列名或
其它 数据库对象的名称, 用户自定义函数不支持
输出参数 。
57
scalar_parameter_data_type,指定标
量型参数的数据类型, 可以为除 TEXT、
NTEXT,IMAGE,CURSOR、
TIMESTAMP 和 TABLE 类型外的其它数据
类型 。
scalar_return_data_type,指定标量型
返回值的数据类型, 可以为除 TEXT,
NTEXT, IMAGE,CURSOR、
TIMESTAMP 和 TABLE 类型外的其它数据
类型 。
scalar_expression,指定标量型用户自
定义函数返回的标量值表达式 。
function_body,指定一系列的 Transact-
SQL 语句, 它们决定了函数的返回值
58
ENCRYPTION,加密选项 。 让 SQL
Server 对系统表中有关 CREATE
FUNCTION 的声明加密, 以防止用户自定
义函数作为 SQL Server 复制的一部分被
发布 。
SCHEMABINDING,计划绑定选项 。 将
用户自定义函数绑定到它所引用的数据库
对象 。 如果指定了此选项, 则函数所涉及
的数据库对象从此将不能被删除或修改,
除非函数被删除或去掉此选项 。 应注意的
是要绑定的数据库对象必须与函数在同一
数据库中 。
59
例 13-19 创建工龄工资计算函数
use pangu
go
create function WorkYearWage(@hiredate datetime,--hiredate 表
示雇佣日期
@today datetime,@per_wage money)
--today 表示当前的日期 per_wage 表示每一年工龄应得的工资额
returns money
as begin
declare @WorkYearWage money
set @WorkYearWage = (year(@today)-
year(@hiredate))*@per_wage
return(@WorkYearWage)
end --结束函数定义
--创建函数
go
60
--调用函数
select pangu.dbo.workyearwage('1991-7-
1',getdate(),15)
as work_year_wage
运行结果如下
work_year_wage
135.0000
(1 row(s) affected)
上例的创建语句也可以写成如下形式
create function WorkYearWage(@hiredate datetime,
@today datetime,@per_wage money)
returns money
as begin
return((year(@today)-year(@hiredate))*@per_wage)
end
61
创建内嵌表值用户自定义函数
其语法如下,
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 [) ]
各参数说明如下
TABLE,指定返回值为一个表
select-stmt,单个 SELECT 语句, 确定返回的表的数据,
其余参数与标量型用户自定义函数相同 。
62
例, 创建返回所有订购某类产品的公司信息函数
use sample
go
create function orderfirms(@productid
varchar(30))
returns table
as
return (select * from products p
where p.p_id = @productid)
go
63
创建多语句表值用户自定义函数
其语法如下,
CREATE FUNCTION [ owner_name,] function_name
( [ { @parameter_name scalar [as]_parameter_data_type [ = default ] }
[,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ WITH < function_option > [,...n ] ]
[ AS ]
BEGIN
function_body
RETURN END
< table_type_definition >,,=
( { column_definition | table_constraint } [,...n ] )
各参数说明如下,
@return_variable,一个 TABLE 类型的变量, 用于存储和累积返回
的表中的数据行 。 其余参数与标量型用户自定义函数相同 。
64
用企业管理器创建用户自定义函数
用 Enterprise Manager 创建用户自定义函
数的方法是, 在 Enterprise Manager 中选
择要创建用户自定义函数的数据库, 在数据
库对象 User Defined Functions 上单击右
键, 从开始菜单中选择 New User Defined
Function 选项, 就会出现定义用户自定义
函数属性对话框, 可以在其中指定要定义的
函数的名称, 并编辑函数的脚本, 单击 OK
按钮则添加用户自定义函数对象到数据库中 。
65
修改和删除用户自定义函数
在 Enterprise Manager 中选择要进行改动的用户自定义
函数, 单击右键, 从快捷菜单中选择属性选项, 则会出现
修改用户自定义函数结构对话框 。 可以修改用户自定义函
数的函数体, 参数等, 从快捷菜单中选择删除选项, 则可
删除用户自定义函数 。
用 ALTER FUNCTION 命令也可以修改用户自定义函数,
此命令的语法与 CREATE FUNCTION 相同,因此使用
ALTER FUNCTION 命令其实相当于重建了一个同名的函
数,用起来不大方便 。 另外可以用 DROP FUNCTION 命令
删除用户自定义函数其语法如下,
DROP FUNCTION { [ owner_name,] function_name }
[,...n ]
例, 删除用户自定义函数 chiefinfo
drop function chiefinfo
66
流程控制语句
流程控制语句是指那些用来控
制程序执行和流程分支的命令，
在 SQL Server 2000中，流程控
制语句主要用来控制 SQL语句、
语句块或者存储过程的执行流程。
67
BEGIN… END语句
BEGIN… END语句能够将多个 Transact-
SQL语句组合成一个语句块，并将它们视为
一个单元处理。在条件语句和循环等控制
流程语句中，当符合特定条件便要执行两
个或者多个语句时，就需要使用
BEGIN… END语句，其语法形式为,
BEGIN
{ sql_statement
| statement_block
}
END
68
例如,
Use sample
Go
Declare @message varchar(200)
If exist(select * from 员工数据表
where 所属部门＝ ‘ 办公室 ’ )
Begin
Set @message=“下列人员在办公室工作：,
Print @message
Select 员工姓名 from 员工数据库
Where 所属部门＝, 办公室,
End
Else
begin
set @message=“没有人在办公室工作,
Print @message
End
go
69
IF… ELSE语句
IF… ELSE语句是条件判断语句，其中,ELSE
子句是可选的，最简单的 IF语句没有 ELSE子
句部分。 IF… ELSE语句用来判断当某一条件
成立时执行某段程序，条件不成立时执行另
一段程序。 SQL Server允许嵌套使用
IF… ELSE语句，而且嵌套层数没有限制。
70
IF… ELSE语句的语法形式
IF Boolean_expression
{ sql_statement |
statement_block }
[ ELSE
{ sql_statement |
statement_block } ]
71
IF … EXISTS语句
If后面的布尔表达式可含有 select 语句，
如果 select语句返回一个值，它可用来与
另一值进行比较，以得到布尔表达式。
如果 Select语句返回不止一个值，可使用
If Exists。 语法如下,
If exists (select statement)
{ sql_statement | statement_block }
[ ELSE [Bolean_expression]
{ sql_statement | statement_block } ]
72
例如：查询标识号为 9933的出版商出版的任何书
的信息
If exists (select * from titles
where pub_id = ?9933?
begin
print,包含如下图书：”
select *from titles
where pub_id = ?9933?
end
else
print,无”
73
注意：一定不要把 if … exists 和聚合函
数一起使用，因为聚合函数总是返回数
据，即使数据是 0。
例如,if exists(select count(*) from
titles where pub_id = ?9933?)
74
WHILE 语句
WHILE语句用于设置重复执行 SQL 语句或
语句块的条件。只要指定的条件为真，就重
复执行语句。其中,CONTINUE语句可以使程
序跳过 CONTINUE语句后面的语句，回到 WHILE
循环的第一行命令。 BREAK语句则使程序完全
跳出循环，结束 WHILE语句的执行。
75
WHILE语句
Break语句将在某些情况发生时，立即无
条件地退出最内层 while循环。语法为,
WHILE 逻辑表达式
begin
…,
Break
…
end
76
While语句
Continue语句在某些情况发生时，控制程序跳
出本次循环，重新开始下一次 while循环。其语
法为,
While 逻辑表达式
Begin
….,
Continue
…,
End
注意：如果 select语句用作 while语句的条件，那
么,select语句必须包含在英文括号中。
77
例, declare @x int,@y int, @c int
select @x = 1,@y=1
while @x < 3
begin
print @x --打印变量 x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c --打印变量 c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
78
CASE表达式
CASE表达式可以计算多个条件式，并将其中一
个符合条件的结果表达式返回。 CASE表达式的语
法为,
CASE 字段名或变量名
WHEN 逻辑表达式 1 THEN 结果表达式 1
WHEN 逻辑表达式 1 THEN 结果表达式 1
WHEN 逻辑表达式 1 THEN 结果表达式 1
…
ELSE 结果表达式
END
79
例, 调整员工工资, 工作级别为 1 的上调 8%,工
作级别为 2 的上调 7%,工作级别为 3 的上调 6%，
其它上调 5%。
use 工资库
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end
80
RETURN语句
RETURN语句用于无条件地终止一个查询, 存
储过程或者批处理, 此时位于 RETURN语句之后
的程序将不会被执行 。 RETURN语句的语法形式
为,
RETURN 整数表达式
通常, 存储过程使用返回代码表示存储过程
执行的成功或失败 。 无错误, 则返回 0,否则,
返回非零值 。
81
WAITFOR语句
WAITFOR语句用于暂时停止执行 SQL语句、语句
块或者存储过程等，直到所设定的时间已过或者
所设定的时间已到才继续执行。 WAITFOR语句的语
法形式为,
WAITFOR DELAY 时间间隔
其中，时间间隔指定执行 waitfor语句之前需要等
待的事件，最多为 24小时。
或者 waitfor time 时间值
其中，时间值指定 waitfor语句将要执行的时间
82
Use sample
Go
--指定在执行 select语句之前等待二秒
Waitfor delay ?00:00:02?
--执行查询
Select 姓名，性别 from 员工数据表
? Where 所属部门＝‘项目部’
83
GOTO语句
GOTO语句可以使程序直接跳到指定的标有标识符
的位置处继续执行，而位于 GOTO语句和标识符之间
的程序将不会被执行。 GOTO语句和标识符可以用在
语句块、批处理和存储过程中，标识符可以为数字
与字符的组合，但必须以,,, 结尾。
GOTO label
……
label,
84
GOTO语句
例：利用 GOTO语句求出从 1加到 5的总和。
declare @sum int,@count int
select @sum=0,@count=1
label_1,
select @sum=@sum+@count
select @count=@count+1
if @count<=5
goto label_1
select @count,@sum
85
PRINT语句
语法格式为,
Print {?any ASCII text?
|@local_varible |
@@global_variable}
注意：可输出的仅仅是 ASCII字符串（字
符串常数）或字符类型的变量
若要输出更复杂的内容，须将字符串存入
字符变量，然后输出该变量。
86
例,Use pubs
Declare @msg varchar(50),
@numWA tinyint
Select @numWA=count(*)
from stores where state =,WA”
Select @msg=?There are ? +
Convert(varchar(3),@numWA)+
?stores in washington.?
Print @msg
87
游标的引入
游标的优点和种类
在数据库开发过程中, 当你检索的数据只
是一条记录时, 你所编写的事务语句代码
往往使用 SELECT,INSERT 语句, 但是我
们常常会遇到这样情况, 即从某一结果集
中逐一地读取一条记录, 那么如何解决这
种问题呢？ 游标为我们提供了一种较好的
解决方案
88
声明游标
每一个游标必须有四个组成部分 。 这四
个关键部分必须符合下面的顺序
1.DECLARE 游标
2.OPEN 游标
3.从一个游标中 FETCH 信息
4.CLOSE 或 DEALLOCATE 游标
通常我们使用 DECLARE 来声明一个游
标 。
89
声明游标
声明一个游标主要包括以下主要内容
游标名字
数据来源表和列
选取条件
属性仅读或可修改
90
其语法格式如下
DECLARE 游标名称 CURSOR
[local|global]
[forward_only|scroll]
[read_only]
FOR 选择语句
[FOR [UPDATE [OF 字段名称 1，字段名称 2,? ]]
其中, local|global指定该游标的作用域是局部的
还是全局的。
如果把 forward_only选择为 forward_only,则游标
只能从第一行滚动到最后一行。
91
,SCROLL,表明所有的提取操作, 如 FIRST,
LAST,PRIOR,NEXT,RELATIVE,
ABSOLUTE都可用 。 如果不使用该保留字那么
只能进行 NEXT 提取操作 。
选择语句,是定义结果集的 SELECT 语句, 应
该注意的是在游标中不能使用 COMPUTE,
COMPUTE BY FOR BROWSE INTO 语句
READ ONLY,表明不允许游标内的数据被更新 。
UPDATE [OF 字段名 1[,… n]],定义在游
标中可被修改的列 。
92
下面给出声明游标的三个例子
例 1,标准游标
declare cur_authors cursor
for
select au_id,au_lname,au_fname,phone,
address,city,state,contract
from authors
例 2,只读游标
declare cur_authors cursor
for
select au_lname,au_fname,phone,address,
city,state
from authors
for read only
93
例, 更新游标
declare cur_authors cursor
for
select au_lname,au_fname
from authors
for update
94
打开游标
游标在声明以后, 如果要从游标中读取数据, 必须打开游标 。
打开一个游标使用 OPEN 命令, 其语法规则为
OPEN 游标名称
注意,在打开游标时, 如果游标声明语句中使用了
INSENSITIVE 保留字, 则 OPEN产生一个临时表来存放结果
集 。 如果在结果集中任何一行数据的大小超过 SQL
SERVER定义的最大行尺寸时, OPEN 命令将失败
INSENSITIVE,表明 SQL SERVER 会将游标定义所选取出来的
数据记录存放在一临时表内,（ 建立在 tempdb 数据库下 ） 对
该游标的读取操作皆由临时表来应答 。 因此, 对基本表的修
改并不影响游标提取的数据, 即游标不会随着基本表内容的
改变而改变, 同时也无法通过游标来更新基本表 。 如果不使
用该保留字, 那么对基本表的更新, 删除都会反映到游标中 。
95
读取游标中的数据－ fetch
当游标被成功打开以后就可以从游标中逐行地
读取数据以进行相关处理 。 从游标中读取数据
主要使用 FETCH 命令, 其语法规则为,
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar} ]
FROM ] cursor_name
[INTO
@variable_name1,@variable_name2..,]
96
各参数含义说明如下,
NEXT:返回结果集中当前行的下一行,并增加当
前行数为返回行行数如果 FETCH NEXT
是第一次读取游标中数据则返回结果集中的是第一
行而不是第二行
PRIOR:返回结果集中当前行的前一行并减少当
前行数为返回行行数 。 如果 FETCH PRIOR是第一
次读取游标中数据则无数据记录返回并把游标位置
设为第一行
FIRST:返回游标中第一行
LAST:返回游标中的最后一行
97
ABSOLUTE {n | @nvar},如果 n 或 @nvar 为
正数, 则表示从游标中返回的数据行数 。 如果
n 或 @nvar 为负数, 则返回游标内从最后一行
数据算起的第 n 或 @nvar 行数据 。
若 n 或 @nvar 超过游标的数据子集范畴, 则
@@FETCH_STARS 返回 -1 。 在该情况下,
如果 n 或 @nvar 为负数, 则执行 FETCH
NEXT 命令会得到第一行数据 ； 如果 n 或
@nvar为正值, 执行 FETCH PRIOR 命令则会
得到最后一行数据 。 n 或 @nvar 可以是一固定
值, 也可以是一 smallint,tinyint 或 int 类型的
变量 。
98
RELATIVE {n | @nvar},若 n 或 @nvar
为正数, 则读取游标当前位置起向后的
第 n 或 @nvar 行数据 。 如果 n 或 @nvar
为负数, 则读取游标当前位置起向前的
第 n 或 @nvar 行数据 。 若 n 或 @nvar 超
过游标的数据子集范畴, 则
@@FETCH_STARS 返回 -1。 在该情况
下, 如果 n 或 @nvar 为负数, 则执行
FETCH NEXT 命令则会得到第一行数据 ；
如果 n 或 @nvar 为正值, 执行
FETCHPRIOR 命令则会得到最后一行数
据 。 n 或 @nvar 可以是一固定值也可以
是一 smallint,tinyint或 int 类型的变量
99
INTO @variable_name[,...n],允许将
使用 FETCH 命令读取的数据存放在多个
变量中 ； 在变量行中的每个变量必须与
游标结果集中相应的列相对应, 每一变
量的数据类型也要与游标中数据列的数
据类型相匹配 。
100
检查游标状态
@@FETCH_STATUS, 全局变量, 返回上次
执行 FETCH 命令的状态 。 在每次用 FETCH从游
标中读取数据时, 都应检查该变量以确定上次
FETCH 操作是否成功, 来决定如何进行下一步
处理 。 @@FETCH_STATUS 变量有三个不同
的返回值 。
0,表示成功取出了一行。
－ 1：表示未取到数据，因为所要求 游标位置超
出了结果集
－ 2：表示返回的行已经不再是结果集的一个成
员。这种情况只有再游标不是 insensitive的情
况下出现，即其它进程已删除了行或改变了游
标打开的关键值。
101
编辑当前游标行
通常情况下, 我们用游标来从基础表中检索数据, 以实
现对数据的行处理 。 但在某些情况下, 我们也常要修改
游标中的数据, 即进行定位更新或删除游标所包含的数
据 。 所以必须执行另外的更新或删除命令, 并在 WHERE
子句中重新给定条件才能修改到该行数据 。 但是如果在
声明游标时使用了 FOR UPDATE 语句, 那么就可以在
UPDATE 或 DELETE命令中以 WHERE CURRENT OF
关键字直接修改或删除当前游标中所存储的数据, 而不
必使用 WHERE 子句重新给出指定条件 。 当改变游标中
数据时, 这种变化会自动地影响到游标的基础表 。 但是
如果在声明游标时选择了 INSENSITIVE 选项时, 该游标
中的数据不能被修改 。
102
进行定位修改或删除游标中数据的语法规则为,
UPDATE table_name
SET column_name1 = {expression1 | NULL
(select_statement)}
[,column_name2 = { expression2 | NULL
(select_statement)}
WHERE CURRENT OF cursor_name
DELETE FROM table_name
WHERE CURRENT OF cursor_name
其中, table_name是 UPDATE 或 DELETE 的表名
column_name,UPDATE 的列名
cursor_name,游标名
103
下例说明如何对游标进行定位更新或删除。
首先声明一个游标
declare authors_cur scroll cursor
For
select * from authors.for update of
au_lname,au_fname
例, 更新 authors 表中的 au_lname 和 au_fname
列
update authors
set au_lname = ‘china’,au_fname = ‘asia’
where current of authors_cur
例, 删除 authors 表中的一行数据
delete from authors
where current of authors_cur
以上更新或删除操作总是基于游标的当前位置
104
例, 下面是一个定位更新的完整例子, 首先查看 authors 表中每一行将 au_id
等于 ’ 172-32-1176’的记录的 au_lname 和 au_fname 分别更改为 ’ Smith’
和 ’ Jake’
declare @au_id id(11),@au_lname varchar(40),@au_fname varchar(20)
/* declare a cursor that will contain the au_id,au_lname,au_fname form
authors table*/
declare authors_cur cursor for
select au_id,au_lname,au_fname from authors
for update of au_id,au_lname,au_fname
open authors_cur /* open the cursor */
/* get the first row from the cursor */
fetch next from authors_cur into @au_id,@au_lname,@au_fname
while @@fetch_status = 0 /* loop the rows in the cursor*/
Begin
if @au_id = ‘172-32-1176’
update authors
set au_lname = ‘smith’,au_fname = ‘jake’
where current of authors_cur
/* get next row */
fetch next from authors_cur into @au_id,@au_lname,@au_fname
end
deallocate authors_cur /* close the cursor */
105
关闭游标
关闭游标, 使用 CLOSE 命令关闭游标
在处理完游标中数据之后, 必须关闭游标
来释放数据结果集和定位于数据记录上的
锁 。
CLOSE 语句关闭游标但不释放游标占用
的数据结构 。 如果准备在随后的使用中再
次打开游标, 则应使用 open 命令 。
关闭游标的语法规则为
CLOSE 游标名称
106
在使用游标时, 各种针对游标的操作或者
引用游标名或者引用指向游标的游标变量,
当 CLOSE 命令关闭游标时并没有释放游
标占用的数据结构 。 因此常使用
DEALLOCATE 命令删除掉游标与游标名
或游标变量之间的联系, 并且释放游标占
用的所有系统资源 。 其语法规则为
DEALLOCATE 游标名称
注意：若真的完成释放游标的操作，再次
使用时，则需重新声明。
释放游标
107
游标变量, 游标变量是从 MS SQL SERVER 7 版本才开
始使用的一种新增数据类型, 定义一个游标变量主要有
两种方法,
首先我们先声明一个游标
declare titleauthor_cur scroll cursor for
select * from titleauthor
然后，使用 SET 语句将一游标赋值给游标变量
declare @cur_ta cursor
set @cur_ta = titleauthor_cur
将声明游标语句放在游标赋值语句中, 如下所示
declare @cur_ta cursor
declare titleauthor_cur scroll cursor for
select * from titleauthor
set @cur_ta = titleauthor_cur
go
/*再引用游标 */
108
例, 下面给出一个具体完整的例子 。 在该例中, 我们对
DEALLOCATE 命令将有更加清晰的了解
use pubs
go
/*声明并打开一个全局游标在批处理以外该游标仍然可见 */
declare titleauthor_cur cursor global scroll for
select * from titleauthor
open titleauthor
go
/*用游标变量引用已声明过的游标 */
declare @cur_ta1 cursor
set @cur_ta1 = titleauthor_cur
/*现在释放对游标的引用 */
deallocate @cur_ta1
/*游标 titleauthor_cur 仍旧存在 */,
fetch next from titleauthor_cur
109
declare @cur_ta2 cursor
set @cur_ta2 = titleauthor_cur
/*释放 titleauthor_cur 游标 */,
deallocate titleauthor_cur
/*由于游标被 @cur_ta2 引用所以仍旧存在 */
fetch next from @cur_ta2
/*当最后一个游标变量超出游标作用域时游标将被释
放 */
go
declare @cur_ta cursor
set @cur_ta = cursor local scroll for
select * from titles
/*由于没有其它变量对其进行引用所以游标被释放 */
deallocate @cur_ta
go
