游标及函数的使用及举例

游标的使用及举例
一、声明游标
在使用一个游标之前，要先声明游标。

可以采用DECLARE语句声明一个游标，语法见表8-22所示。

表8-22 DECLARE声明游标的语法
序号语法说明
1DECLARE 游标名称CURSOR定义游标名
2[ LOCAL | GLOBAL ]指定游标的作用域是局部或是全局的
3[ FORWARD_ONLY | SCROLL ]指定游标只能逐次向下一行或是可以移动到
任意指定位置
4[ READ_ONLY ]指定游标为只读游标
5FOR SELECT 语句定义游标结果集的SELECT 语句
6[ FOR UPDATE [ OF列名[ ,...n ] ] ]定义游标内可更新的列，如果在UPDATE
中未指定列的列表，除非指定了
READ_ONLY选项，否则所有列均可更新
二、打开游标
用DECLARE语句声明游标时并不执行SELECT查询语句，只有当用OPEN语句打开游标时才能执行查询语句，OPEN语句的语法见表8-23所示。

表8-23 OPEN语句的语法
序号语法说明
1OPEN [GLOBAL] 游标名称打开游标
三、从游标中提取数据
在游标打开后，可以使用FETCH语句从游标中提取数据并进行操作，FETCH语句的语法见表8-24所示。

表8-24 FETCH语句的语法
序号语法说明
1FETCH [NEXT|PRIOR|FIRST|LAST |ABSOLUTE n |RELATIVE n]根据指定读取记录的位置提取游标中的数据，指定位置分别为当前行的下一行、当前行的上一行、第一行、最后一行、第n行（参照第一行）、第n行（参照当前行）
2FROM [GLOBAL]游标名称指定要从中提取数据的游标名称
3[INTO @变量名[ ,...n ] ]将提取的数据存放到指定的局部变量中FETCH语句每次只能提取一行数据，其执行的状态值保存在全局变量
@@FETCH_STATUS中，当@@FETCH_STATUS为0时，表示FETCH语句执行成功；当@@FETCH_STATUS为-1时，表示FETCH 语句失败或此行不在结果集中；当@@FETCH_STATUS为-2时，表示被提取的行不存在。

四、利用游标修改和删除数据
在SQL Server中，利用游标可以修改或删除当前记录，其语句的语法见表8-25所示。

表8-25 利用游标修改、删除数据的语法
序号语法说明
1UPADTE 表名SET 列名=表达式WHERE CURRENT OF游标名称通过游标的当前数据行，修改指定数据表中的数据
2DELETE FROM 表名
WHERE CURRENT OF游标名称通过游标的当前数据行，删除指定数据表中的数据
五、关闭游标
游标使用完毕后，应及时关闭游标，以释放当前的结果集并解除定位在游标记录行上的游标锁定，使用CLOSE语句可以关闭游标。

在关闭游标后，当需要时仍然可以通过OPEN语句打开并使用该游标。

关闭游标的CLOSE语句的语法见表8-26所示。

表8-26 CLOSE语句的语法
序号语法说明
1CLOSE [GLOBAL] 游标名称关闭游标
六、释放游标
关闭游标并没有删除游标，因此游标仍然占用着系统的资源，对于一个不再使用的游标，可以使用DEALLOCATE语句删除，其语句的语法见表8-27所示。

表8-27 DEALLOCATE语句的语法
序号语法说明
1DEALLOCATE [GLOBAL] 游标名称释放游标
应用举例
例8-15：使用游标将学生表中编号、姓名、性别和家庭住址逐行显示出来，语句及结
果见图8-14。

use student
go
declare @xs_xh char(12),@xs_xm varchar(12),
@xs_xb char(2),@xs_jtzz varchar(40)
--声明游标
declare xs_cursor cursor scroll READ_ONLY
for select xh,xm,xb,jtzz from student
--打开游标
open xs_cursor
--提取结果集中的第一行记录
fetch next from xs_cursor into @xs_xh,@xs_xm,@xs_xb,@xs_jtzz
print ' 学号'+ ' 姓名'+ ' 性别'+' 家庭住址'
print '------------------------------------------'
while @@fetch_status=0
begin
-- 显示学号、姓名、性别、家庭住址
print @xs_xh+' '+ @xs_xm+' '+@xs_xb+' '+@xs_jtzz
print '-----------------------------------------'
-- 从打开的游标中提取下一条
fetch next from xs_cursor into @xs_xh,@xs_xm,@xs_xb,@xs_jtzz
end
--关闭与释放游标
close xs_cursor
deallocate xs_cursor
返回
函数
（1）创建标量值函数
创建标量值函数的语法见表8-29。

表8-29 创建标量值函数的语法
序号语法说明
1CREATE FUNCTION [所有者或架构名.]函数名称定义函数名，所有者或架构名常采用dbo（默认值）
2([输入参数[AS] 数据类型[=默认值] [,...n])定义输入参数的名称、数据类型和默
认值
3RETURNS 返回值数据类型指定返回值数据类型
4AS
BEGIN
<T-SQL语句或语句块>
RETURN @返回值变量实现函数功能的函数体，指定返回值
图8-14 游标使用示例
END
例8-21：创建函数，在学生表中根据给定的入学年份统计学生人数。

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='Stu_Number' AND TYPE='FN')
DROP FUNCTION Stu_Number
GO
CREATE FUNCTION dbo.Stu_Number(@Vyear INT)
RETURNS INT
AS
BEGIN
DECLARE @R_Num int
SELECT @R_Num=count(*) from student where year(rxnf)=@Vyear
RETURN @R_Num
END
创建自定义函数时，若数据库中已有此对象，可以通过EXISTS测试语句删除该对象重新创建，其中标量值函数、内联表值函数和多语句表值函数在SYSOBJECTS系统表中的TYPE值分别为“FN”、“IF”和“TF”。

函数创建成功后，可采用以下T-SQL语句调用该函数：
SELECT dbo.Stu_Number(2008) AS '该年份的入学人数'
创建和调用语句及其结果见图8-20。

（2）创建内联表值函数
创建内联表值函数的语法见表8-30。

表8-30 创建内联表值函数的语法
序号语法说明
1CREATE FUNCTION [所有者或架构名.]函数名称定义函数名，所有者或架构名常采用dbo（默认值）
2([输入参数[AS] 数据类型[=默认值] [,...n])定义输入参数的名称、数据类型和默
认值
3RETURNS TABLE返回表
图8-20 标量函数示例
4[AS] RETURN (SELECT 语句)SELECT语句指定返回表中的数据例8-22：创建函数，在学生表中根据给定的入学年份查询出学生信息（包括学号、姓名、性别、出生日期和专业）。

USE student
GO
CREATE FUNCTION dbo.Stu_Info(@Vyear INT)
RETURNS TABLE
AS
RETURN (SELECT xh,xm,xb,csrq,zy
from student where year(rxnf)=@Vyear)
GO
函数创建成功后，可采用以下T-SQL语
句调用该函数：
SELECT * from dbo.Stu_Info(2008)
创建和调用语句及其结果见图8-21。

（3）创建多语句表值函数
创建多语句表值函数的语法见表8-31。

表8-31 创建多语句表值函数的语法
序号语法说明
1CREATE FUNCTION [所有者或架构名.]函数名称定义函数名，所有者或架构名常采用dbo （默认值）
2([输入参数[AS] 数据类型[=默认值] [,...n])定义输入参数的名称、数据类型和默
认值
3RETURNS @表名变量TABLE 表的定义定义返回表
4[AS]
BEGIN
<T-SQL语句或语句块>
RETURN
END
实现函数功能的函数体
例8-23：创建函数，根据给定的专业查询该专业学生及其修课信息（包括学号、姓名、性别、班级、修课门数）。

CREATE FUNCTION dbo.Stu_ZyInfo(@Vzy VARCHAR(20))
RETURNS @Stu_Zy TABLE(
xh char(12),xm varchar(12),xb char(2),
bjmc varchar(20),ms int )
图8-21 内联表值函数示例
AS
BEGIN
DECLARE @Cou_MS TABLE(xh char(12),ms int)
INSERT INTO @Cou_MS
SELECT xh,COUNT(*) AS ms FROM GRADE GROUP BY xh
INSERT INTO @Stu_Zy
SELECT A.xh,xm,xb,bjmc,ms from student A
INNER JOIN @Cou_MS B ON A.xh=B.xh
where zy=@Vzy
RETURN
END
GO
函数创建成功后，可采用以下T-SQL
语句调用该函数：
select * from dbo.Stu_ZyInfo('信息管
理与信息系统')
图8-22 多语句表值函数示例创建和调用语句及其结果见图8-22。

返回。