sql server 动态定义游标 sql游标的定义

文章目录

• 一 、概述

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

• 二、语法 T-SQL

• 1-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操作是否成功，来决定如何进行下一步处理。返回值有三种：

返回值	描述
0	FETCH命令被成功执行
1	FETCH命令失败或者行数据超过游标数据结果集的范围
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

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

创建举例用的数据

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

可以将下方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