SQL -- 游标（详细）

文章目录

一 、概述1-1 游标基本概念1-2 游标的实现功能及使用步骤

二、语法 T-SQL1-1 声明游标1-2 打开游标1-3 读取游标1-4 关闭游标1-5 删除游标

三、 创建举例用的数据库及表

一 、概述

1-1 游标基本概念

什么是游标

游标是一种处理数据的方法，具有对结果集进行逐行处理的能力。

可以将游标看作一种特殊的指针，它与某个查询结果集相关联，可以指向结果集的任意位置，可以将数据放在数组、应用程序中或其他的地方，允许用户对指定位置的数据进行处理。

为什么使用游标

出来 SELECT 的 WHERE 可以限制一条记录被选中外，T-SQL 没有提供查询表中单条记录的方法，但我们常常会遇到需要逐行读取记录的情况。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序往往采取非数据库语言（如 C#、VB、ASP或其他开发工具）内嵌T-SQL的形式来开发，而这些非数据库语言无法将表作为一个单元来处理，因此，这些应用程序需要一种机制以便每次处理一行或一部分行。

1-2 游标的实现功能及使用步骤

游标的实现功能

允许对 SELECT 返回的表中的每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；从表中的当前位置检索一行或多行数据；游标允许应用程序对当前位置的数据进行修改、删除的能力；对于不同用户对结果集包含的数据所做的修改，支持不同的可见性级别；提供脚本、存储过程、触发器中用于访问结果集中的数据的语句。

游标的使用步骤：前4个步骤是必须的。

声明游标 DECLARE：将游标与 T-SQL 语句的结果集相关联，并定义游标的名称、类型和属性，如游标中的记录是否可以更新、删除打开游标 OPEN：执行 T-SQL 语句以填充数据。读取游标 FETCH：从游标的结果集中检索想要查看的行，进行逐步操作。关闭游标 CLOSE：停止游标使用的查询，但并不删除游标的定义，可以使用 OPEN 再次打开。释放游标 DEALLOCATE：删除资源并释放其占用的所有资源。

二、语法 T-SQL

1-1 声明游标

声明游标是指用 DECLARE 语句创建一个游标。声明游标主要包括以下内容：游标名称、数据来源、选取条件和属性。

声明游标语法格式

DECLARE 游标名称 CURSOR

[ LOCAL | GLOBAL ] --游标的作用域

[ FORWORD_ONLY | SCROLL ] --游标的移动方向

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] --游标的类型

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] --游标的访问类型

[ TYPE_WARNING] --类型转换警告语句

FOR SELECT 语句 --SELECT查询语句

[ FOR { READ ONLY | UPDATE [OF 列名称]}][,...n] --可修改的列

举例： 要求：创建游标 curl，使 curl 可以对 student 表所有的数据进行操作，并将游标变量 @var_curl 与 curl相关联。

声明游标 T-SQL 语句

-- 声明游标

DECLARE curl CURSOR

FOR SELECT * FROM student

DECLARE @var_curl CURSOR

SET @var_curl=curl

1-2 打开游标

游标声明后，如果要从游标中读取数据必须要打开游标。打开游标是指打开已经声明但尚未打开的游标，并执行游标中定义的查询。

如果声明游标使用 STATIC 类型，则 打开游标时产生一个临时表来存放结果集；如果声明游标使用 KEYSET 类型，则 OPEN 产生一个临时表来存放键值。所有的临时表都存放在 tempdb 数据库中。

打开游标语法格式

OPEN 游标名称

在游标被成功打开后，全局 变量 @@CORSOR_ROWS 用来记录游标内的数据行数。返回值有四种：

返回值描述-m表示仍在从基础表向游标读入数据，m表示当前在游标中的数据行数-1该游标是一个动态游标，其返回值无法确定0无符合调剂的记录或游标已经关闭n从基础表向游标读入数据已结束，n 为游标中已有的数据记录行数

举例： 要求：创建游标 curl，使 curl 可以对 student 表所有的数据进行操作，然后打开该游标，输出游标中的行数。

打开游标 T-SQL 语句

-- 打开 curl 游标

OPEN curl

SELECT '游标curl数据行数' =@@CURSOR_ROWS

执行结果

1-3 读取游标

当游标打开后就可以使用 FETCH 命令从游标中逐行地读取数据，以进行相关处理。

读取游标语法格式

FETCH

[[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE{n|@nvar }| RELATIVE { n|@nvar }] FROM ] -- 读取数据的位置

{{[ GLOBAL] 游标名称} | @游标变量名称 }

[ INTO @游标变量名称 ][,...n] -- 将读取的游标数据存放到指定变量中

FETCH语句执行时，可以使用 全局变量 @@FETCH_STATUS 返回上次执行 FETCH 命令的状态。在每次用 fetch 从游标中读取数据时，都应检查该变量，以确定上次 FETCH操作是否成功，来决定如何进行下一步处理。返回值有三种：

返回值描述0FETCH命令被成功执行1FETCH命令失败或者行数据超过游标数据结果集的范围2所读取的数据已经不存在

举例： 要求：创建游标 curl，从游标中提取数据，并查看 FETCH 命令的执行状态。

读取游标 T-SQL 语句

-- 读取curl 数据

FETCH NEXT FROM curl

SELECT 'NEXT_FETCH执行情况'=@@FETCH_STATUS

执行结果

1-4 关闭游标

游标使用完以后要及时关闭。关闭游标使用 CLOSE ，但不释放游标占用的资源。

关闭游标语法格式

CLOSE {{[ GLOBAL] 游标名称} | @游标变量名称 }

举例： 要求：关闭游标 curl。

关闭游标 T-SQL 语句

-- 关闭游标

CLOSE curl

1-5 删除游标

游标关闭后定义还在，需要时可以再使用 OPEN 打开游标继续使用。若确定不再使用则使用 CLOSE 删除游标，释放占用系统中的资源。

删除游标语法格式

DEALLOCATE {{[ GLOBAL] 游标名称} | @游标变量名称 }

举例： 要求：删除游标 curl。

关闭游标 T-SQL 语句

-- 删除游标

DEALLOCATE curl

三、 创建举例用的数据库及表

创建举例用的数据

创建StuInfo数据库，若存在则直接使用创建student表，若已存在删除后创建插入数据

可以将下方SQL语句 复制到 SSMS 工具上直接执行，如果有时间，建议自己敲一遍。扩展一点，SQL语句关键字最好使用大写。如果使用小写，数据库会将关键字从小写转换为大写，增加这一过程则性能会降低，所以最好直接使用大写。

use master

go

-- 判断数据库是否已经存在，若存在不删除直接使用，反之新建

IF EXISTS(SELECT * FROM sysdatabases WHERE name='StuInfo')

PRINT 'StuInfo数据库已存在'

ELSE

BEGIN

--创建数据库

CREATE DATABASE StuInfo

ON

(

NAME=StuInfo,

FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StuInfo.mdf',

SIZE=3MB,

MAXSIZE=UNLIMITED,

FILEGROWTH=10%

)

LOG ON

(

NAME=StuInfo_log,

FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StuInfo_log.ldf',

SIZE=1MB,

MAXSIZE=100MB,

FILEGROWTH=10%

)

END

GO

USE StuInfo

GO

-- 判断数据表是否已经存在，若存在删除后创建

IF OBJECT_ID(N'StuInfo..student',N'U') IS NOT NULL

DROP TABLE student

CREATE TABLE student

(

[s_id][char](10)NOT NULL,

[sname][nvarchar](5)NULL,

[ssex][nvarchar](1)NULL,

[sbirthday][date]NULL,

[sdepartment][nvarchar](10)NULL,

[smajor][nvarchar](10)NULL,

[spoliticalStatus][nvarchar](4)NULL,

[phoneName][varchar](100)NULL,

[photo][varchar](max)NULL,

[smemo][nvarchar](max)NULL,

CONSTRAINT[PK_student]PRIMARY KEY CLUSTERED

(

[s_id] ASC

))on [PRIMARY]

GO

-- 向数据库添加数据

USE StuInfo

INSERT INTO

student ([s_id],[sname],[ssex],[sbirthday],[sdepartment],[smajor],[spoliticalStatus],[photo],[smemo])

VALUES

('20070101',N'张莉',N'女','1/30/1998',N'信息工程学院',N'计算机',N'党员',NULL,NULL)

,

('20070102',N'张建',N'男','1/30/1998',N'信息工程学院',N'计算机',N'党员',NULL,NULL)

GO